<!-- PART OF: ARCHITECTURE.md — The 20-Minute Hero Complete Architecture -->
<!-- DOCUMENT: 07-migrations-deploy-mobile.md -->
<!-- CONTENTS: Drizzle-kit Migrations, Docker Compose (§21), Group Quests, Migration 002, Monitoring, Mobile App -->
<!-- SPLIT: lines 8902–10473 of original file -->

## 20. Drizzle-kit — Migracje Bazy Danych

### 20.1 Setup

```bash
cd /srv/20hero
pnpm add -D drizzle-kit
pnpm add drizzle-orm postgres
# → konfiguracja w packages/db/drizzle.config.ts
# → migracje generowane do packages/db/migrations/
```

### 20.2 `packages/db/drizzle.config.ts` — konfiguracja

```typescript
// packages/db/drizzle.config.ts
import { defineConfig } from 'drizzle-kit'

export default defineConfig({
  schema: './packages/db/src/schema.ts',
  out: './packages/db/migrations',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
})
```

### 20.3 `packages/db/migrations/0001_initial_schema.sql`

```typescript
// packages/db/src/migrate.ts
import { migrate } from 'drizzle-orm/postgres-js/migrator'
import { drizzle } from 'drizzle-orm/postgres-js'
import postgres from 'postgres'

const connection = postgres(process.env.DATABASE_URL!, { max: 1 })
const db = drizzle(connection)

await migrate(db, { migrationsFolder: './migrations' })
await connection.end()
console.log('Migrations complete')
```

```sql
-- packages/db/migrations/0001_initial_schema.sql
-- Auto-generated by drizzle-kit, manual extensions prepended

-- ── Extensions ────────────────────────────────────────────────────────────
CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS "pgcrypto";   -- gen_random_uuid()

-- ── ENUM types ────────────────────────────────────────────────────────────
CREATE TYPE character_class AS ENUM (
    'techno_mag', 'chrom_paladin', 'widmo_biegacz', 'bio_szaman',
    'inzynier_spoleczny', 'kurier_cieni', 'architekt_danych', 'koderyta'
);
CREATE TYPE quest_difficulty AS ENUM ('easy', 'medium', 'hard', 'legendary');
CREATE TYPE proof_status AS ENUM (
    'pending', 'ai_verifying', 'ai_verified', 'ai_rejected',
    'community_voting', 'accepted', 'rejected'
);
-- vote_value ENUM usunięty — tabela `votes` używa SMALLINT CHECK (vote IN (-1, 1))
-- ENUM 'approve'/'reject' był zdefiniowany ale nieużywany. Konwencja: -1=reject, +1=approve.

-- ── users ─────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS "users" (
    id                       UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    firebase_uid             TEXT        NOT NULL UNIQUE,
    email                    TEXT,
    username                 TEXT        UNIQUE,
    display_name             TEXT,
    photo_url                TEXT,                       -- z Firebase profile
    is_active                BOOLEAN     NOT NULL DEFAULT TRUE,
    is_admin                 BOOLEAN     NOT NULL DEFAULT FALSE,
    auth_provider            TEXT        NOT NULL DEFAULT 'firebase',  -- 'firebase' | 'google' | 'apple'
    timezone                 TEXT        DEFAULT 'Europe/Warsaw',
    -- device_tokens USUNIĘTE — patrz tabela user_devices (relacja 1:N, osobna migracja)
    notification_prefs       JSONB       NOT NULL DEFAULT '{}'::JSONB,
    reviewer_accuracy        FLOAT       NOT NULL DEFAULT 0.5,  -- 0.0-1.0; głosy accuracy>0.7 mają wagę 2x
    is_showcase              BOOLEAN     NOT NULL DEFAULT FALSE, -- team account; cold start seed (§19.6)
    last_location            GEOGRAPHY(POINT, 4326),
    last_location_updated_at TIMESTAMPTZ,
    created_at               TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at               TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    last_login_at            TIMESTAMPTZ,
    deleted_at               TIMESTAMPTZ                        -- NULL = aktywny (soft delete)
);
CREATE INDEX idx_users_firebase ON users(firebase_uid);
CREATE INDEX idx_users_username ON users(username);
CREATE INDEX idx_users_created_at ON users(created_at DESC);

-- refresh_tokens USUNIĘTE — auth model uproszczony (remediation plan P1.3):
-- Backend akceptuje Firebase ID token (Bearer) bezpośrednio; brak własnych access/refresh tokenów.
-- Firebase SDK auto-odnawia ID token po stronie mobile. Admin panel używa session_token (nie JWT).

-- ── onboarding_sessions ───────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS "onboarding_sessions" (
    id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id    UUID NOT NULL UNIQUE REFERENCES users(id) ON DELETE CASCADE,
    messages   JSONB NOT NULL DEFAULT '[]'::JSONB,
    status     TEXT NOT NULL DEFAULT 'in_progress',
    turn_count INT NOT NULL DEFAULT 0,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- ── characters ────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS "characters" (
    id                    UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id               UUID        NOT NULL UNIQUE REFERENCES users(id) ON DELETE CASCADE,
    name                  TEXT        NOT NULL,
    class_key             character_class NOT NULL,
    title                 TEXT        NOT NULL,      -- e.g. "Mag Logistyki II Stopnia"
    backstory             TEXT        NOT NULL,      -- AI-generated backstory
    level                 INT         NOT NULL DEFAULT 1,
    xp_total              BIGINT      NOT NULL DEFAULT 0,
    xp_to_next            INT         NOT NULL DEFAULT 500,

    -- Atrybuty (skala 1-100)
    attr_sila             INT         NOT NULL DEFAULT 10,  -- Siła / Wytrzymałość
    attr_intelekt         INT         NOT NULL DEFAULT 10,  -- Intelekt
    attr_charyzma         INT         NOT NULL DEFAULT 10,  -- Charyzma
    attr_zrecznosc        INT         NOT NULL DEFAULT 10,  -- Zręczność
    attr_percepcja        INT         NOT NULL DEFAULT 10,  -- Percepcja

    equipment             JSONB       NOT NULL DEFAULT '{}'::JSONB,
    personality_traits    TEXT[]      NOT NULL DEFAULT '{}',
    preferred_proof_types TEXT[]      NOT NULL DEFAULT '{"photo","video","audio","text","multi_photo"}',
    excluded_categories   TEXT[]      NOT NULL DEFAULT '{}',
    privacy_level         TEXT        NOT NULL DEFAULT 'public',  -- 'private' | 'friends' | 'public'
    streak_days           INT         NOT NULL DEFAULT 0,
    streak_last_quest_at  TIMESTAMPTZ,
    onboarding_transcript JSONB,
    visual_anchor         JSONB,      -- cechy wizualne dla spójności avatarów między tierami
    is_active             BOOLEAN     NOT NULL DEFAULT TRUE,
    created_at            TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at            TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_characters_user ON characters(user_id);
CREATE INDEX idx_characters_class ON characters(class_key);
CREATE INDEX idx_characters_level ON characters(level DESC);

-- ── character_avatars ─────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS "character_avatars" (
    id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    character_id UUID NOT NULL REFERENCES characters(id) ON DELETE CASCADE,
    level_tier   INT NOT NULL CHECK (level_tier BETWEEN 1 AND 20),
    image_url    TEXT NOT NULL,
    is_current   BOOLEAN NOT NULL DEFAULT FALSE,
    generated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_avatars_character ON character_avatars(character_id);
CREATE UNIQUE INDEX idx_avatars_current
    ON character_avatars(character_id)
    WHERE is_current = TRUE;

-- ── quest_templates ───────────────────────────────────────────────────────
-- NAMING NOTE: Two quest-related tables exist with different roles:
-- - `quests` (Section 1 DDL): per-user quest instances (character_id, user_id, full context)
--   Created when: (a) AI generates personalized quest for user, OR
--                 (b) user accepts a quest_template → creates a quest instance
-- - `quest_templates` (this table): location-anchored catalog, no user context,
--   visible on the map for multiple nearby users, reusable across users.
--   FK in quest_attempts references `quests` (user instances), NOT templates directly.
--   The geospatial nearby query (Section 10) queries quest_templates for discovery,
--   then acceptance creates a quest instance in `quests`.
-- Note: quest_templates are seeded manually or by admin; `quests` are AI-generated at runtime.
CREATE TABLE IF NOT EXISTS "quest_templates" (
    id               UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title            TEXT NOT NULL,
    description      TEXT NOT NULL,
    objective        TEXT NOT NULL,
    completion_hint  TEXT NOT NULL,
    lore_blurb       TEXT,
    difficulty       quest_difficulty NOT NULL,
    quest_tags       TEXT[] NOT NULL DEFAULT '{}',
    location         GEOGRAPHY(POINT, 4326),
    radius_visible_m INT NOT NULL DEFAULT 500,
    min_level        INT DEFAULT 1,
    is_active        BOOLEAN NOT NULL DEFAULT TRUE,
    is_ai_generated  BOOLEAN NOT NULL DEFAULT FALSE,
    created_at       TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_quest_templates_location
    ON quest_templates USING GIST (location);
CREATE INDEX idx_quest_templates_active ON quest_templates(is_active);

-- ── quest_seen (deduplication) ────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS "quest_seen" (
    user_id     UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    template_id UUID NOT NULL REFERENCES quest_templates(id) ON DELETE CASCADE,
    seen_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (user_id, template_id)
    -- template_id (not quest_id) to avoid ambiguity with quests.id (user instances)
);
CREATE INDEX idx_quest_seen_user_time ON quest_seen(user_id, seen_at);

-- ── quests (per-user quest instances, AI-generated) ──────────────────────
-- Distinct from quest_templates (admin/AI catalog pinned on map).
-- Created here because quest_attempts.quest_id REFERENCES quests(id).
CREATE TABLE IF NOT EXISTS "quests" (
    id                     UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    character_id           UUID NOT NULL REFERENCES characters(id) ON DELETE CASCADE,
    user_id                UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,

    title                  TEXT NOT NULL,
    description            TEXT NOT NULL,
    objective              TEXT NOT NULL,
    completion_hint        TEXT NOT NULL,
    lore_blurb             TEXT,

    energy_required        TEXT NOT NULL DEFAULT 'medium',
    needed_items           TEXT[],
    companion_mode         TEXT NOT NULL DEFAULT 'solo',
    budget_level           TEXT NOT NULL DEFAULT 'free',
    allowed_proof_types    TEXT[] NOT NULL
        DEFAULT '{"photo","video","audio","text","multi_photo"}',

    difficulty             quest_difficulty NOT NULL DEFAULT 'easy',
    estimated_minutes      INT NOT NULL DEFAULT 20,
    location_type          location_type,
    weather_at_gen         JSONB,
    lat                    DOUBLE PRECISION,
    lng                    DOUBLE PRECISION,
    geohash                TEXT,
    place_name             TEXT,

    ai_model               TEXT NOT NULL DEFAULT 'gemini-3-flash',
    generation_prompt_hash TEXT,
    generation_context     JSONB,

    xp_base                INT NOT NULL DEFAULT 100,
    xp_bonus_max           INT NOT NULL DEFAULT 50,

    status                 quest_status NOT NULL DEFAULT 'available',
    accepted_at            TIMESTAMPTZ,
    expires_at             TIMESTAMPTZ,
    submitted_at           TIMESTAMPTZ,
    completed_at           TIMESTAMPTZ,

    proof_id               UUID,
    created_at             TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at             TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_quests_character_id ON quests(character_id);
CREATE INDEX idx_quests_user_id ON quests(user_id);
CREATE INDEX idx_quests_status ON quests(status);
CREATE INDEX idx_quests_geohash ON quests(geohash);
CREATE INDEX idx_quests_created_at ON quests(created_at DESC);
CREATE INDEX idx_quests_expires_at ON quests(expires_at)
    WHERE status IN ('accepted', 'in_progress');

-- ── quest_attempts ────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS "quest_attempts" (
    id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id           UUID NOT NULL REFERENCES users(id),
    quest_id          UUID NOT NULL REFERENCES quests(id),  -- references user quest instance, not template
    status            TEXT NOT NULL DEFAULT 'in_progress',
    -- Wybrany przez gracza typ dowodu (przy akceptacji questa).
    -- NULL gdy gracz nie wybrał z góry (zdecyduje przy upload).
    -- Używany do analityki popularności formatów + pre-fill UI
    chosen_proof_type TEXT DEFAULT NULL
                      CHECK (chosen_proof_type IN
                             ('photo','multi_photo','video','audio','text')),
    accepted_at       TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    expires_at        TIMESTAMPTZ NOT NULL,
    completed_at      TIMESTAMPTZ,
    abandoned_at      TIMESTAMPTZ,
    xp_awarded        INT DEFAULT 0,
    tokens_awarded    INT DEFAULT 0
);
CREATE INDEX idx_attempts_user ON quest_attempts(user_id);
CREATE INDEX idx_attempts_status ON quest_attempts(status);
CREATE INDEX idx_attempts_expires ON quest_attempts(expires_at);

-- ── quest_proofs ──────────────────────────────────────────────────────────
-- UWAGA: Ta tabela to legacy schema z Migration 001.
-- Zostaje zastąpiona przez tabelę `proofs` tworzoną w Migration 002 (Sekcja 24).
-- Migration 002 migruje dane quest_proofs → proofs i usuwa quest_proofs.
CREATE TABLE IF NOT EXISTS "quest_proofs" (
    id               UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    attempt_id       UUID NOT NULL UNIQUE REFERENCES quest_attempts(id),
    user_id          UUID NOT NULL REFERENCES users(id),
    s3_key           TEXT,   -- NULL dla text proofs
    media_type       TEXT NOT NULL CHECK (media_type IN
                         ('photo','multi_photo','video','audio','text')),
    status           proof_status NOT NULL DEFAULT 'pending',
    ai_verdict       TEXT,
    ai_confidence    FLOAT,
    ai_feedback      TEXT,
    ai_flags         TEXT[] DEFAULT '{}',
    vote_count       INT NOT NULL DEFAULT 0,
    vote_approve     INT NOT NULL DEFAULT 0,
    vote_reject      INT NOT NULL DEFAULT 0,
    voting_opens_at  TIMESTAMPTZ,
    voting_closes_at TIMESTAMPTZ,
    submitted_at     TIMESTAMPTZ,
    resolved_at      TIMESTAMPTZ,
    created_at       TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_proofs_attempt ON quest_proofs(attempt_id);
CREATE INDEX idx_proofs_user ON quest_proofs(user_id);
CREATE INDEX idx_proofs_voting ON quest_proofs(voting_closes_at) WHERE status = 'community_voting';

-- ── votes ─────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS "votes" (
    id                 UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    proof_id           UUID NOT NULL REFERENCES quest_proofs(id) ON DELETE CASCADE,
    voter_user_id      UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    voter_character_id UUID REFERENCES characters(id),
    vote               SMALLINT NOT NULL CHECK (vote IN (-1, 1)),
    comment            TEXT,
    voted_at           TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE UNIQUE INDEX idx_votes_proof_voter ON votes(proof_id, voter_user_id);
CREATE INDEX idx_votes_proof_id ON votes(proof_id);
CREATE INDEX idx_votes_voter ON votes(voter_user_id);

-- ── follows ───────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS "follows" (
    follower_id  UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    following_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (follower_id, following_id),
    CHECK (follower_id != following_id)
);
CREATE INDEX idx_follows_following ON follows(following_id);

-- ── reactions ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS "reactions" (
    id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    proof_id   UUID NOT NULL REFERENCES quest_proofs(id) ON DELETE CASCADE,
    user_id    UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    reaction   TEXT NOT NULL DEFAULT '👍',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (proof_id, user_id)
);
CREATE INDEX idx_reactions_proof ON reactions(proof_id);

-- ── comments ──────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS "comments" (
    id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    proof_id   UUID NOT NULL REFERENCES quest_proofs(id) ON DELETE CASCADE,
    user_id    UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    content    TEXT NOT NULL CHECK (length(content) <= 300),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_comments_proof ON comments(proof_id);

-- ── leaderboard_snapshots ─────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS "leaderboard_snapshots" (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id     UUID NOT NULL REFERENCES users(id),
    snapshot_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    rank_global INT,
    rank_class  INT,
    rank_local  INT,
    xp_total    INT NOT NULL,
    quests_done INT NOT NULL DEFAULT 0,
    period      TEXT NOT NULL DEFAULT 'weekly'
);
CREATE INDEX idx_leaderboard_user ON leaderboard_snapshots(user_id);
CREATE INDEX idx_leaderboard_period ON leaderboard_snapshots(period, snapshot_at DESC);

-- ── token_balances ────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS "token_balances" (
    user_id    UUID PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
    balance    BIGINT NOT NULL DEFAULT 0 CHECK (balance >= 0),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- ── token_ledger ──────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS "token_ledger" (
    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id       UUID NOT NULL REFERENCES users(id),
    event_type    TEXT NOT NULL,
    amount        BIGINT NOT NULL,
    balance_after BIGINT NOT NULL,
    metadata      JSONB NOT NULL DEFAULT '{}'::JSONB,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_ledger_user ON token_ledger(user_id, created_at DESC);

-- ── custodial_wallets ─────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS "custodial_wallets" (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id     UUID NOT NULL UNIQUE REFERENCES users(id),
    phase       TEXT NOT NULL DEFAULT 'virtual',
    kms_key_arn TEXT,
    evm_address TEXT,
    sol_address TEXT,
    kyc_status  TEXT NOT NULL DEFAULT 'not_required',
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- ── cosmetic_purchases ────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS "cosmetic_purchases" (
    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id       UUID NOT NULL REFERENCES users(id),
    item_key      TEXT NOT NULL,
    item_category TEXT NOT NULL,
    price_paid    BIGINT NOT NULL,
    purchased_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- ── game_config ───────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS "game_config" (
    key        TEXT PRIMARY KEY,
    value      JSONB NOT NULL,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- ── prompt_templates ──────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS "prompt_templates" (
    key           TEXT PRIMARY KEY,
    system_prompt TEXT NOT NULL DEFAULT '',
    user_template TEXT NOT NULL DEFAULT '',
    updated_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- ── admin_audit_log ───────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS "admin_audit_log" (
    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    admin_user_id UUID NOT NULL REFERENCES users(id),
    action        TEXT NOT NULL,
    resource_type TEXT NOT NULL,
    resource_key  TEXT NOT NULL,
    old_value     JSONB,
    new_value     JSONB NOT NULL,
    note          TEXT,
    changed_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_audit_changed ON admin_audit_log(changed_at DESC);
CREATE INDEX idx_audit_key ON admin_audit_log(resource_key);

-- ── Post-MVP: Group Quests (tabele tworzone od razu, feature wyłączony przez game_config) ──
CREATE TYPE group_visibility AS ENUM ('public', 'private', 'invite_only');
CREATE TYPE member_role AS ENUM ('member', 'moderator', 'admin');

CREATE TABLE IF NOT EXISTS "groups" (
    id               UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name             TEXT NOT NULL,
    slug             TEXT UNIQUE NOT NULL,
    description      TEXT,
    avatar_url       TEXT,
    visibility       group_visibility NOT NULL DEFAULT 'public',
    owner_user_id    UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    member_count     INT NOT NULL DEFAULT 1,
    total_xp         BIGINT NOT NULL DEFAULT 0,
    quests_completed INT NOT NULL DEFAULT 0,
    require_approval BOOLEAN NOT NULL DEFAULT FALSE,
    invite_code      TEXT UNIQUE,
    created_at       TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at       TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_groups_slug ON groups(slug);
CREATE INDEX idx_groups_visibility ON groups(visibility);
CREATE INDEX idx_groups_total_xp ON groups(total_xp DESC);

CREATE TABLE IF NOT EXISTS "group_members" (
    id             UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    group_id       UUID NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
    user_id        UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    character_id   UUID REFERENCES characters(id),
    role           member_role NOT NULL DEFAULT 'member',
    joined_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    last_active_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE UNIQUE INDEX idx_group_members_unique ON group_members(group_id, user_id);
CREATE INDEX idx_group_members_group ON group_members(group_id);
CREATE INDEX idx_group_members_user ON group_members(user_id);
```

### 20.4 Workflow migracji

```bash
# Pierwsza instalacja
pnpm drizzle-kit generate    # Generuje SQL z packages/db/src/schema.ts
pnpm drizzle-kit migrate     # Uruchamia 0001_initial_schema.sql
npx tsx packages/db/src/seed.ts  # Wypełnia game_config i prompt_templates

# Nowa zmiana schematu (np. dodanie kolumny)
# → Edytuj packages/db/src/schema.ts
pnpm drizzle-kit generate    # Tworzy 0002_add_column_groups.sql automatycznie
pnpm drizzle-kit migrate     # Aplikuje nową migrację

# Push schema bezpośrednio (tylko dev, bez pliku migracji)
pnpm drizzle-kit push

# Sprawdź stan
pnpm drizzle-kit studio      # GUI do przeglądania schematu i danych
```

---

## 21. Docker Compose + Deployment

### 21.1 Struktura plików

```
/srv/20hero/
├── docker-compose.yml          # Produkcja
├── docker-compose.dev.yml      # Override dla local dev
├── pnpm-workspace.yaml         # pnpm monorepo
├── apps/
│   └── api/
│       └── Dockerfile          # Hono API + BullMQ worker (jeden obraz)
├── nginx/
│   ├── nginx.conf
│   └── ssl/                    # Certbot wygeneruje certyfikaty tutaj
└── .env                        # Sekrety (NIE commitować!)
```

### 21.2 `apps/api/Dockerfile`

```dockerfile
# apps/api/Dockerfile
FROM node:20-alpine AS builder
WORKDIR /app
RUN npm install -g pnpm
COPY pnpm-lock.yaml package.json pnpm-workspace.yaml ./
COPY packages/ packages/
COPY apps/api/ apps/api/
RUN pnpm install --frozen-lockfile
RUN pnpm --filter @20hero/api build

FROM node:20-alpine AS runner
WORKDIR /app
COPY --from=builder /app/apps/api/dist ./dist
COPY --from=builder /app/node_modules ./node_modules
EXPOSE 3000
CMD ["node", "dist/index.js"]
```

### 21.3 `docker-compose.yml`

```yaml
version: "3.9"

services:

  # ── Baza danych ──────────────────────────────────────────────────────────
  postgres:
    image: postgis/postgis:16-3.4
    restart: unless-stopped
    environment:
      POSTGRES_DB:       20hero
      POSTGRES_USER:     20hero
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
    volumes:
      - postgres_data:/var/lib/postgresql/data
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U 20hero"]
      interval: 5s
      timeout: 5s
      retries: 10

  # ── Redis ────────────────────────────────────────────────────────────────
  redis:
    image: redis:7-alpine
    restart: unless-stopped
    command: redis-server --maxmemory 256mb --maxmemory-policy allkeys-lru
    volumes:
      - redis_data:/data
    healthcheck:
      test: ["CMD", "redis-cli", "ping"]
      interval: 5s

  # ── Hono API ─────────────────────────────────────────────────────────────
  api:
    build:
      context: .
      dockerfile: apps/api/Dockerfile
    restart: unless-stopped
    env_file: .env
    environment:
      - DATABASE_URL=postgresql://20hero:${POSTGRES_PASSWORD}@postgres:5432/20hero
      - REDIS_URL=redis://redis:6379
      - NODE_ENV=production
    depends_on:
      postgres: { condition: service_healthy }
      redis:    { condition: service_healthy }
    expose:
      - "3000"
    volumes:
      - ./firebase-credentials.json:/run/secrets/firebase.json:ro
    command: node dist/index.js
    healthcheck:
      test: ["CMD", "wget", "-qO-", "http://localhost:3000/health"]
      interval: 30s

  # ── BullMQ Worker ─────────────────────────────────────────────────────────
  worker:
    build:
      context: .
      dockerfile: apps/api/Dockerfile
    restart: unless-stopped
    env_file: .env
    environment:
      - DATABASE_URL=postgresql://20hero:${POSTGRES_PASSWORD}@postgres:5432/20hero
      - REDIS_URL=redis://redis:6379
      - NODE_ENV=production
    depends_on:
      postgres: { condition: service_healthy }
      redis:    { condition: service_healthy }
    volumes:
      - ./firebase-credentials.json:/run/secrets/firebase.json:ro
    command: node dist/worker.js
    # BullMQ worker obsługuje AI calls (I/O bound) — concurrency konfigurowana w kodzie

  # ── BullMQ Scheduler (cron jobs) ─────────────────────────────────────────
  scheduler:
    build:
      context: .
      dockerfile: apps/api/Dockerfile
    restart: unless-stopped
    env_file: .env
    environment:
      - DATABASE_URL=postgresql://20hero:${POSTGRES_PASSWORD}@postgres:5432/20hero
      - REDIS_URL=redis://redis:6379
      - NODE_ENV=production
    depends_on:
      - worker
    command: node dist/scheduler.js
    # Tylko jedna instancja schedulera! Dwie = podwójne cron joby

  # ── Nginx (reverse proxy + SSL termination) ──────────────────────────────
  nginx:
    image: nginx:1.25-alpine
    restart: unless-stopped
    ports:
      - "80:80"
      - "443:443"
    volumes:
      - ./nginx/nginx.conf:/etc/nginx/nginx.conf:ro
      - ./nginx/ssl:/etc/letsencrypt:ro
      - certbot_webroot:/var/www/certbot:ro
    depends_on:
      - api

  # ── Certbot (SSL — uruchamiany raz, potem cron) ──────────────────────────
  certbot:
    image: certbot/certbot
    volumes:
      - ./nginx/ssl:/etc/letsencrypt
      - certbot_webroot:/var/www/certbot
    entrypoint: /bin/sh -c 'trap exit TERM; while :; do certbot renew; sleep 12h & wait $${!}; done'

volumes:
  postgres_data:
  redis_data:
  certbot_webroot:
```

### 21.4 `nginx/nginx.conf`

```nginx
events { worker_connections 1024; }

http {
    upstream api {
        server api:3000;
    }

    # HTTP → HTTPS redirect
    server {
        listen 80;
        server_name api.20hero.pl;

        location /.well-known/acme-challenge/ {
            root /var/www/certbot;   # Certbot challenge
        }
        location / {
            return 301 https://$host$request_uri;
        }
    }

    # HTTPS
    server {
        listen 443 ssl;
        server_name api.20hero.pl;

        ssl_certificate     /etc/letsencrypt/live/api.20hero.pl/fullchain.pem;
        ssl_certificate_key /etc/letsencrypt/live/api.20hero.pl/privkey.pem;
        ssl_protocols       TLSv1.2 TLSv1.3;
        ssl_ciphers         HIGH:!aNULL:!MD5;

        # Bezpieczeństwo
        add_header Strict-Transport-Security "max-age=31536000" always;
        add_header X-Frame-Options DENY;
        add_header X-Content-Type-Options nosniff;

        client_max_body_size 1m;   # Pliki przez presigned URL, nie przez nginx

        location / {
            proxy_pass         http://api;
            proxy_set_header   Host              $host;
            proxy_set_header   X-Real-IP         $remote_addr;
            proxy_set_header   X-Forwarded-For   $proxy_add_x_forwarded_for;
            proxy_set_header   X-Forwarded-Proto $scheme;
            proxy_read_timeout 30s;
        }

        location /health {
            proxy_pass http://api/health;
            access_log off;   # Nie śmieć logów healthcheckami
        }
    }
}
```

### 21.5 `docker-compose.dev.yml` — local override

```yaml
# Użycie: docker compose -f docker-compose.yml -f docker-compose.dev.yml up
version: "3.9"

services:
  api:
    command: node --watch dist/index.js
    volumes:
      - ./apps/api:/app/apps/api    # Hot reload — kod montowany z hosta
    ports:
      - "3000:3000"       # Bezpośredni dostęp bez nginx

  worker:
    command: node --watch dist/worker.js

  # Na dev nie potrzebujemy nginx ani certbot
  nginx:
    profiles: ["prod"]
  certbot:
    profiles: ["prod"]
```

### 21.6 Pierwsze uruchomienie na serwerze

```bash
# 1. Sklonuj repo
git clone git@github.com:your/20hero.git /srv/20hero
cd /srv/20hero

# 2. Skopiuj i uzupełnij .env
cp .env.example .env
nano .env   # Ustaw POSTGRES_PASSWORD, JWT_SECRET, GEMINI_API_KEY, etc.

# 2b. Firebase service account JSON
# Pobierz z: Firebase Console → Project Settings → Service accounts → Generate new private key
# Zapisz jako: /srv/20hero/firebase-credentials.json
# UWAGA: dodaj do .gitignore (plik zawiera klucz prywatny!)
cp /path/to/downloaded-key.json firebase-credentials.json

# 3. Uruchom DB i Redis (bez api — żeby najpierw uruchomić migracje)
docker compose up -d postgres redis

# 4. Migracje i seed
docker compose run --rm api node dist/migrate.js
docker compose run --rm api node dist/seed.js

# 5. Uruchom wszystko
docker compose up -d

# 6. SSL (pierwsze uruchomienie)
docker compose run --rm certbot certonly \
    --webroot -w /var/www/certbot \
    -d api.20hero.pl \
    --email admin@20hero.pl --agree-tos --no-eff-email

# 7. Przeładuj nginx (żeby złapał certyfikat)
docker compose exec nginx nginx -s reload

# 8. Weryfikacja
curl https://api.20hero.pl/health
```

### 21.7 CI/CD — GitHub Actions (zarys)

```yaml
# .github/workflows/deploy.yml
on:
  push:
    branches: [main]

jobs:
  test:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgis/postgis:16-3.4
        env: { POSTGRES_PASSWORD: test, POSTGRES_DB: 20hero_test }
    steps:
      - uses: actions/checkout@v4
      - uses: pnpm/action-setup@v3
        with:
          version: 9
      - run: pnpm install --frozen-lockfile
      - run: pnpm test

  deploy:
    needs: test
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - name: Deploy to server
        run: |
          ssh deploy@server.20hero.pl "
            cd /srv/20hero &&
            git pull &&
            docker compose build api &&
            docker compose run --rm api node dist/migrate.js &&
            docker compose up -d --no-deps api worker scheduler
          "
```

---

## 22. Grupowe Questy — Design (Post-MVP)

> **Status:** Zdesignowane, NIE implementowane w MVP. Endpointy `/api/groups` i tabele są w schemacie, ale logika grupowych questów aktywowana przez `game_config: groups.enabled = false`.

### 22.1 Co to są grupy

Grupy to dobrowolne społeczności (znajomi, rodzina, współpracownicy) którzy:
- Obserwują nawzajem swoje proofs w dedykowanym feedzie
- Rywalizują na wewnętrznym leaderboardzie
- Opcjonalnie — mogą dostawać wspólne **grupowe questy** (wyzwania dla całej grupy)

### 22.2 Schemat DDL

> **Uwaga**: Tabele `groups` i `group_members` są już tworzone w **Migration 001** (Section 20.3).
> Kolumny kanoniczne: `owner_user_id`, `visibility` (ENUM: public/private/invite_only),
> `member_count`, `total_xp`, `quests_completed`, `require_approval`.
> Tabela `group_quests` jest tworzona w **Migration 002** (Section 24).

```sql
-- Tabela group_quests (Migration 002 — patrz Section 24)
-- groups i group_members są zdefiniowane w Migration 001 (Section 20.3)

-- Grupowe questy — wyzwanie dla wszystkich członków
CREATE TABLE group_quests (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    group_id        UUID NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
    quest_id        UUID NOT NULL REFERENCES quest_templates(id),
    title           TEXT NOT NULL,           -- Opcjonalnie inny niż quest_template
    description     TEXT,
    starts_at       TIMESTAMPTZ NOT NULL,
    ends_at         TIMESTAMPTZ NOT NULL,
    xp_bonus        INT NOT NULL DEFAULT 50, -- Dodatkowe XP za ukończenie w ramach wyzwania
    required_count  INT NOT NULL DEFAULT 1,  -- Ile razy quest musi być ukończony przez grupę
    completed_count INT NOT NULL DEFAULT 0,
    status          TEXT NOT NULL DEFAULT 'active'
);
```

### 22.3 Logika grupowych questów

```
Mechanika:
  - Admin grupy tworzy wyzwanie: "Wszyscy muszą zrobić quest X do niedzieli"
  - Każdy member który ukończy quest X w tym czasie → dostaje xp_bonus
  - Gdy completed_count >= required_count → wyzwanie ukończone, bonus XP dla całej grupy
  - Feed grupy pokazuje proofs questów wyzwania na górze (priorytetyzowane)

Typy wyzwań:
  a) Indywidualne (każdy member musi ukończyć sam) — required_count = count(members)
  b) Zbiorowe (razem N ukończeń) — required_count = N < count(members)
  c) Wyścig (kto pierwszy?) — required_count = 1, bonus tylko dla pierwszego
```

### 22.4 game_config dla grup

```json
// game_config key: "groups"
{
  "enabled":           false,
  "max_members":       20,
  "group_quest_enabled": false,
  "group_quest_xp_bonus": 50,
  "max_groups_per_user": 5
}
```

### 22.5 Aktywacja post-MVP

```sql
UPDATE game_config
SET value = value || '{"enabled": true, "group_quest_enabled": true}'::jsonb
WHERE key = 'groups';
```

Zero migracji danych. Tabele istnieją od początku, logika aktywowana przez flagę.

---

## 23. Package.json & Monitoring

### 23.1 `package.json` (monorepo root + apps/api)

```json
// package.json (root)
{
  "name": "20hero",
  "private": true,
  "scripts": {
    "dev": "turbo dev",
    "build": "turbo build",
    "test": "turbo test",
    "db:generate": "drizzle-kit generate",
    "db:migrate": "drizzle-kit migrate",
    "db:push": "drizzle-kit push",
    "db:studio": "drizzle-kit studio"
  },
  "devDependencies": {
    "drizzle-kit": "^0.30.0",
    "turbo": "^2.0.0",
    "typescript": "^5.5.0",
    "@types/node": "^20.0.0"
  }
}
```

```json
// apps/api/package.json
{
  "name": "@20hero/api",
  "scripts": {
    "dev": "tsx watch src/index.ts",
    "build": "tsc -p tsconfig.json",
    "start": "node dist/index.js",
    "test": "vitest run"
  },
  "dependencies": {
    "hono": "^4.5.0",
    "@hono/node-server": "^1.12.0",
    "drizzle-orm": "^0.36.0",
    "postgres": "^3.4.0",
    "bullmq": "^5.0.0",
    "ioredis": "^5.4.0",
    "firebase-admin": "^12.0.0",
    "@google/genai": "^1.0.0",
    "@aws-sdk/client-s3": "^3.600.0",
    "@aws-sdk/s3-request-presigner": "^3.600.0",
    "prom-client": "^15.1.0",
    "zod": "^3.23.0",
    "ky": "^1.7.0"
  },
  "devDependencies": {
    "tsx": "^4.16.0",
    "vitest": "^2.0.0",
    "@types/node": "^20.0.0"
  }
}
```

```json
// packages/db/package.json
{
  "name": "@20hero/db",
  "dependencies": {
    "drizzle-orm": "^0.36.0",
    "postgres": "^3.4.0"
  }
}
```

```
# pnpm-workspace.yaml
packages:
  - 'apps/*'
  - 'packages/*'
```

---

### 23.2 Monitoring & Alerting

#### Poziom 1 — MVP (obowiązkowe przed launch)

**Sentry** — error tracking, zero konfiguracji, darmowy tier do 5k errors/mies.:

```typescript
// apps/api/src/index.ts (lifespan setup)
import * as Sentry from '@sentry/node'
import { nodeProfilingIntegration } from '@sentry/profiling-node'

Sentry.init({
  dsn: process.env.SENTRY_DSN,          // Dodaj do .env i Settings
  integrations: [nodeProfilingIntegration()],
  tracesSampleRate: 0.1,                // 10% requestów = performance traces
  environment: process.env.NODE_ENV,    // "production" / "staging"
  sendDefaultPii: false,                // RODO: nie wysyłaj PII
})
```

Dodaj do `.env.example`:
```bash
SENTRY_DSN=                     # https://xxx@o0.ingest.sentry.io/xxx (zostaw puste dla dev)
NODE_ENV=production              # production / staging / development
```

Co Sentry automatycznie łapie:
- Nieobsłużone wyjątki (500 errors)
- BullMQ worker failures (przez wrapper)
- Slow DB queries (≥500ms)
- Performance traces dla każdego endpointu

#### Poziom 2 — Post-MVP (opcjonalne)

**Prometheus + Grafana** — metryki systemowe (jeśli user base rośnie):

```typescript
// apps/api/src/metrics.ts
import { register, Counter, Histogram } from 'prom-client'

export const questsGenerated = new Counter({
  name: 'hero_quests_generated_total',
  help: 'Total quests generated',
  labelNames: ['character_class'],
})

export const apiLatency = new Histogram({
  name: 'hero_api_duration_seconds',
  help: 'API request duration',
  labelNames: ['method', 'route', 'status'],
  buckets: [0.01, 0.05, 0.1, 0.5, 1, 5],
})

// apps/api/src/index.ts — ekspozycja metryk
app.get('/metrics', async (c) => {
  c.header('Content-Type', register.contentType)
  return c.body(await register.metrics())
})
```

Kluczowe metryki do dashboardu:
- `hero_api_duration_seconds` (p50/p95/p99 per endpoint)
- `hero_quests_generated_total` per character_class
- `bullmq_job_completed_total` / `bullmq_job_failed_total`
- `postgres_pool_size` / `redis_connected_clients`

**Uptime monitoring** — UptimeRobot (darmowy, pinguje `GET /health` co 5 min, alert na Slack/email gdy down).

#### Zasada monitoringu dla MVP

> **Start small:** Sentry + UptimeRobot = zero konfiguracji, zero kosztów, cobertura 80% potrzeb. Prometheus/Grafana gdy masz >1000 DAU i potrzebujesz capacity planning.

---

---

## 24. Migration 002 — Proof System & Quest Paths Expansion

Migracja dodaje wszystkie pola wynikające z analizy szkicu produktowego (Sekcja 24).
Uruchamiana po 001, przed pierwszym deployem lub jako osobna migracja post-MVP.

```sql
-- packages/db/migrations/0002_proof_system_expansion.sql
-- Proof system expansion + quest paths + user preferences.
-- Revision: 002
-- Revises: 001
-- Create Date: 2026-03-09

-- ── Nowe ENUM typy ────────────────────────────────────────────────────────
CREATE TYPE media_type AS ENUM
    ('photo', 'multi_photo', 'video', 'audio', 'text');
CREATE TYPE proof_visibility AS ENUM ('private', 'friends', 'public');

-- ── quest_proofs — nowe kolumny ───────────────────────────────────────────
ALTER TABLE quest_proofs
    ADD COLUMN IF NOT EXISTS visibility           proof_visibility NOT NULL DEFAULT 'public',
    ADD COLUMN IF NOT EXISTS user_text            TEXT,
    ADD COLUMN IF NOT EXISTS ai_completion_feedback TEXT,
    ADD COLUMN IF NOT EXISTS moderation_flags     JSONB NOT NULL DEFAULT '{"safe":true}'::jsonb,
    ADD COLUMN IF NOT EXISTS moderation_checked_at TIMESTAMPTZ,
    ADD COLUMN IF NOT EXISTS allowed_proof_types  TEXT[] NOT NULL
        DEFAULT '{"photo","multi_photo","video","audio","text"}';

-- Zmień CHECK constraint na media_type (do czasu ALTER TYPE)
ALTER TABLE quest_proofs
    DROP CONSTRAINT IF EXISTS quest_proofs_media_type_check;
ALTER TABLE quest_proofs
    ALTER COLUMN media_type TYPE TEXT;

CREATE INDEX idx_proofs_visibility ON quest_proofs(visibility);

-- ── proof_media — nowa tabela (dla multi_photo) ───────────────────────────
CREATE TABLE IF NOT EXISTS "proof_media" (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    proof_id        UUID NOT NULL REFERENCES quest_proofs(id) ON DELETE CASCADE,
    s3_key          TEXT NOT NULL,
    cdn_url         TEXT,
    thumbnail_url   TEXT,
    file_size_bytes BIGINT,
    sort_order      SMALLINT NOT NULL DEFAULT 0,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_proof_media_proof ON proof_media(proof_id, sort_order);

-- ── quest_attempts — nowe kolumny dla kontekstu ───────────────────────────
ALTER TABLE quest_attempts
    ADD COLUMN IF NOT EXISTS mood           TEXT,
    ADD COLUMN IF NOT EXISTS budget         TEXT NOT NULL DEFAULT 'free',
    ADD COLUMN IF NOT EXISTS companion_mode TEXT NOT NULL DEFAULT 'solo';

-- ── quest_templates — nowe kolumny ───────────────────────────────────────
ALTER TABLE quest_templates
    ADD COLUMN IF NOT EXISTS energy_required      TEXT NOT NULL DEFAULT 'medium',
    ADD COLUMN IF NOT EXISTS needed_items         TEXT[] DEFAULT '{}',
    ADD COLUMN IF NOT EXISTS companion_mode       TEXT NOT NULL DEFAULT 'solo',
    ADD COLUMN IF NOT EXISTS budget_level         TEXT NOT NULL DEFAULT 'free',
    ADD COLUMN IF NOT EXISTS suggested_proof_type TEXT NOT NULL DEFAULT 'photo',
    ADD COLUMN IF NOT EXISTS allowed_proof_types  TEXT[]
        NOT NULL DEFAULT '{"photo","multi_photo","video","audio","text"}';

-- ── users — preferencje użytkownika ──────────────────────────────────────
ALTER TABLE users
    ADD COLUMN IF NOT EXISTS preferred_proof_types TEXT[]
        NOT NULL DEFAULT '{"photo","multi_photo","video","audio","text"}',
    ADD COLUMN IF NOT EXISTS excluded_categories TEXT[] NOT NULL DEFAULT '{}',
    ADD COLUMN IF NOT EXISTS default_proof_visibility TEXT NOT NULL DEFAULT 'public';

-- ── Quest Paths (Ścieżki) — nowe tabele ──────────────────────────────────
CREATE TABLE IF NOT EXISTS "quest_paths" (
    id                   UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    slug                 TEXT UNIQUE NOT NULL,
    title                TEXT NOT NULL,
    description          TEXT NOT NULL,
    category             TEXT NOT NULL,
    difficulty           quest_difficulty NOT NULL DEFAULT 'easy',
    estimated_days       INT NOT NULL DEFAULT 7,
    quest_count          INT NOT NULL DEFAULT 7,
    min_level            INT NOT NULL DEFAULT 1,
    min_quests_completed INT NOT NULL DEFAULT 0,
    xp_bonus             INT NOT NULL DEFAULT 500,
    badge_key            TEXT,
    is_active            BOOLEAN NOT NULL DEFAULT TRUE,
    created_at           TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_paths_category ON quest_paths(category);

CREATE TABLE IF NOT EXISTS "quest_path_steps" (
    id               UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    path_id          UUID NOT NULL REFERENCES quest_paths(id) ON DELETE CASCADE,
    step_number      INT NOT NULL,
    title            TEXT NOT NULL,
    description      TEXT,
    quest_category   TEXT NOT NULL,
    quest_tags       TEXT[] NOT NULL DEFAULT '{}',
    quest_difficulty quest_difficulty NOT NULL DEFAULT 'easy',
    min_minutes      INT NOT NULL DEFAULT 10,
    max_minutes      INT NOT NULL DEFAULT 30,
    allowed_proof_types TEXT[],
    UNIQUE (path_id, step_number)
);

CREATE TABLE IF NOT EXISTS "user_path_progress" (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id         UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    path_id         UUID NOT NULL REFERENCES quest_paths(id) ON DELETE CASCADE,
    status          TEXT NOT NULL DEFAULT 'active',
    current_step    INT NOT NULL DEFAULT 1,
    steps_completed INT NOT NULL DEFAULT 0,
    started_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    completed_at    TIMESTAMPTZ,
    last_active_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (user_id, path_id)
);
CREATE INDEX idx_path_progress_user ON user_path_progress(user_id, status);

CREATE TABLE IF NOT EXISTS "path_step_quests" (
    user_path_id UUID NOT NULL REFERENCES user_path_progress(id) ON DELETE CASCADE,
    step_number  INT NOT NULL,
    quest_id     UUID NOT NULL REFERENCES quest_templates(id),
    attempt_id   UUID REFERENCES quest_attempts(id),
    completed_at TIMESTAMPTZ,
    PRIMARY KEY (user_path_id, step_number)
);

-- ── group_quests — grupowe wyzwania (disabled by game_config groups.group_quest_enabled) ──
CREATE TABLE IF NOT EXISTS "group_quests" (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    group_id        UUID NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
    quest_id        UUID NOT NULL REFERENCES quest_templates(id),
    title           TEXT NOT NULL,
    description     TEXT,
    starts_at       TIMESTAMPTZ NOT NULL,
    ends_at         TIMESTAMPTZ NOT NULL,
    xp_bonus        INT NOT NULL DEFAULT 50,
    required_count  INT NOT NULL DEFAULT 1,
    completed_count INT NOT NULL DEFAULT 0,
    status          TEXT NOT NULL DEFAULT 'active',
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_group_quests_group ON group_quests(group_id, status);
CREATE INDEX idx_group_quests_ends ON group_quests(ends_at) WHERE status = 'active';

-- ── Rename quest_proofs → proofs (canonical table name per Section 1 DDL) ─
-- All subsequent app code and Section 1 DDL use the name `proofs`.
-- FKs in proof_media, votes etc. are automatically re-pointed by PostgreSQL.
ALTER TABLE quest_proofs RENAME TO proofs;
ALTER INDEX IF EXISTS idx_proofs_attempt RENAME TO idx_proofs_attempt;
ALTER INDEX IF EXISTS idx_proofs_user RENAME TO idx_proofs_user;
ALTER INDEX IF EXISTS idx_proofs_voting RENAME TO idx_proofs_voting;
ALTER INDEX IF EXISTS idx_proofs_visibility RENAME TO idx_proofs_visibility;
```

Rollback script (additive-only, kolumny pominięte dla uproszczenia):

```sql
-- packages/db/migrations/0002_proof_system_expansion_down.sql
DROP TABLE IF EXISTS group_quests CASCADE;
DROP TABLE IF EXISTS path_step_quests CASCADE;
DROP TABLE IF EXISTS user_path_progress CASCADE;
DROP TABLE IF EXISTS quest_path_steps CASCADE;
DROP TABLE IF EXISTS quest_paths CASCADE;
DROP TABLE IF EXISTS proof_media CASCADE;
DROP TYPE IF EXISTS proof_visibility CASCADE;
DROP TYPE IF EXISTS media_type CASCADE;
-- Cofnij ALTER kolumny... (pomiń dla uproszczenia — 002 = additive only)
```

---

## 25. Mobile App Architecture — Expo (React Native)

> Stack: **Expo** (managed workflow) + **React Native** + **TypeScript**
> Uwaga: Sekcja ta opisuje architekturę klienta mobilnego. Backend (Hono) opisany w Sekcjach 1-24.

### 25.1 Stack i decyzje technologiczne

```
Expo + React Native (managed):
  ✓ iOS + Android z jednej bazy kodu
  ✓ Expo Managed = bez Xcode/Android Studio dla developerów
  ✓ EAS Build dla CI/CD (cloud builds)
  ✓ expo-notifications, expo-location, expo-camera, expo-image-picker — gotowe

TypeScript:
  ✓ Typesafe API responses (generowane ze schematów Zod backendu)
  ✓ Typy współdzielone przez monorepo packages/types

State Management:
  → Zustand (lokalny stan UI, character, auth token)
  → TanStack Query / React Query (server state: quests, proofs, feed)
    • Automatyczne cache invalidation po akcjach
    • Stale-while-revalidate dla listy questów

Navigation:
  → expo-router (file-based routing z deep linking)
  → Tab Bar: Home | Quests | Social | Profile
  → Stack navigators wewnątrz każdej zakładki

Networking:
  → ky z interceptorem JWT (auto-refresh gdy 401)
  → Timeout: 15s dla text calls, 60s dla upload-url

Storage:
  → expo-secure-store: JWT tokens (zaszyfrowany keychain)
  → expo-sqlite (via drizzle-orm): offline quest cache
  → AsyncStorage: UI preferences, proof upload queue

In-App Purchases:
  → react-native-iap (NIE expo-in-app-purchases — deprecated od Expo SDK 50+)
  → Obsługuje Apple StoreKit 2 + Google Play Billing Library v7
  → Przez bare workflow lub expo-modules API shim
```

### 25.2 Struktura Ekranów

```
Tab: Home
  ├── HomeScreen           — Daily quest suggestion, streak counter, aktywna ścieżka
  └── NotificationsScreen  — Proof verified, voting results, group invites

Tab: Quests (główny gameplay loop)
  ├── QuestDiscoveryScreen — Mapa + lista, filtr po typie/trudności, geospatial
  ├── QuestDetailScreen    — Opis questa, timer, "Zacznij misję"
  ├── ActiveQuestScreen    — Timer countdown, completion hint, proof capture CTA
  ├── ProofCaptureScreen   — Wybór proof_type → camera/audio/text editor → preview
  ├── ProofSubmitScreen    — Podgląd, caption, visibility, wyślij
  └── QuestResultScreen    — Verdict, XP animacja, completion_feedback od AI

Tab: Social
  ├── FeedScreen           — Public proofs (filtr: znajomi / globalne)
  ├── VotingScreen         — Kolejka głosowania z licznikiem czasowym
  └── GroupsScreen         — Lista grup, leaderboard

Tab: Profile
  ├── ProfileScreen        — Avatar aktualny tier, stats, achievements
  ├── AvatarGalleryScreen  — Historia tierów (1-current), animacja ewolucji
  └── SettingsScreen       — Notyfikacje, prywatność, usuń konto
```

### 25.3 Offline Handling

```
Problem: Gracz jest w piwnicy / metro bez zasięgu, właśnie skończył quest.

Strategia: Optimistic UI + upload queue

1. QUEST CACHE (SQLite via expo-sqlite):
   - Przy otwarciu app: pobierz 5 najbliższych questów, zapisz do SQLite
   - TTL cache: 1h (quest może się "przeterminować" gdy nieaktywny)
   - Gracz może przeglądać questy offline, zaczyna quest offline
   - Przy powrocie sieci: sync z API, invalidate stale cache

2. PROOF UPLOAD QUEUE (AsyncStorage):
   - Gracz robi zdjęcie → kompresja → zapisz lokalnie + dodaj do queue
   - Format queue: [{ attempt_id, proof_type, local_uri, user_caption, visibility, retries: 0 }]
   - Background upload: Expo Background Fetch (co 15min gdy offline)
   - Przy foreground + sieć: natychmiastowy retry
   - Max retries: 3 → po 3 nieudanych → alert graczowi
   - Notyfikacja push: "Twój dowód został przesłany" po udanym upload

3. AUTH TOKEN:
   - Access token (15min) w expo-secure-store
   - Refresh token (30 dni) w expo-secure-store
   - Auto-refresh przy 401: ky beforeError hook → POST /auth/refresh → retry original
   - Przy refresh failure (expired): logout → LoginScreen
```

### 25.4 Proof Capture Flow (UI)

```
ProofCaptureScreen — wybór formatu:

  [📷 Zdjęcie]  [📸 Seria]  [🎬 Wideo]  [🎙️ Audio]  [✍️ Tekst]

  Jeśli character.preferred_proof_types ustawione → highlight preferred

photo:
  → expo-camera → CameraScreen → preview → compress (expo-image-manipulator)
     max: 1920px, quality: 0.85 → upload

multi_photo (2-3):
  → expo-image-picker (multiSelect, max 3) → preview all
  → compress każde osobno → upload równolegle (Promise.all)

video:
  → expo-camera (video mode, max 60s) → preview
  → NIE kompresujemy wideo po stronie klienta (zbyt wolne) → upload raw
  → Limit egzekwowany przez S3 ContentLengthRange

audio:
  → expo-av (Audio.Recording) → max 120s → waveform preview
  → Format: m4a (iOS) / ogg (Android) → upload

text:
  → TextInput (multiline) → licznik znaków min 50/max 1000
  → Podgląd z renderowaniem markdown → submit bezpośrednio (brak S3)

COMPRESS STRATEGY:
  expo-image-manipulator:
    photo/multi_photo: resize(1920, 1080, fit), compress(0.85) → avg 1.5-3MB
    → Przed upload-url (client-side, nie marnujemy S3 PUT na ogromne pliki)
```

### 25.5 Real-Time i Push Notifications

```
Proof Status Polling:
  - Po submit: polling GET /api/proofs/{proofId}/status co 3s
  - Max 30 prób (90s) — po tym czas pokaż "Weryfikacja trwa, dostaniesz powiadomienie"
  - Przy AI_VERIFIED: animacja XP, completion_feedback
  - Przy COMMUNITY_VOTING: "Gildia weryfikuje twój dowód" + badge counter

Push Notifications (expo-notifications + FCM):
  - Przy install: zarejestruj FCM token przez POST /api/users/me/fcm-token
  - Token refresh: expo-notifications.addPushTokenListener → update API
  - Obsługiwane typy (Sekcja 8c): proof_verified, proof_rejected, voting_result,
    level_up, avatar_ready, group_invite, quest_reminder, path_step_ready

Deep Linking:
  expo-router deep links:
  - proof-verified/{proofId} → QuestResultScreen
  - level-up/{tier}          → AvatarGalleryScreen (tier)
  - group-invite/{slug}      → GroupDetailScreen
  - voting/{proofId}         → VotingScreen
```

### 25.6 Performance

```
Image Caching:
  → expo-image (nie Image z RN) — wbudowany aggressive LRU cache
  → Avatar images (512×512): cache na dysku, TTL 7 dni
  → Feed photos: cache w pamięci, TTL sesji

Bundle Size:
  → Tree-shaking via Metro bundler
  → Lazy loading ekranów (React.lazy + Suspense w expo-router)
  → Osobny chunk: mapa (react-native-maps jest ciężka)

Rendering:
  → Feed (FlatList) z getItemLayout dla stałej wysokości
  → VirtualizedList z windowSize={5} dla dużych list
  → Debounce geolocation updates (100ms) w QuestDiscoveryScreen

API:
  → Stale-While-Revalidate: list questów świeży 5min
  → Optimistic updates: reakcja (like) widoczna natychmiast, rollback jeśli error
  → prefetch /api/quests przy wejściu na HomeScreen
```

### 25.7 Uwierzytelnianie

```
Firebase Auth (Google + Apple Sign-In):
  → expo-auth-session dla OAuth flow (Safari WebView na iOS, Chrome Custom Tabs Android)
  → Po sukcesie: Firebase ID token → POST /auth/firebase/verify
  → Backend zwraca: { access_token (15min), refresh_token (30d, rotowany) }
  → Oba tokeny w expo-secure-store (keychain encrypted)

Auth State Machine (Zustand):
  States: unauthenticated | authenticating | onboarding | authenticated

  unauthenticated → (login) → authenticating → (firebase success + no character) → onboarding
  unauthenticated → (login) → authenticating → (firebase success + character exists) → authenticated
  authenticated → (401 + refresh fail) → unauthenticated

Onboarding:
  → Po pierwszym logowaniu: OnboardingScreen (chat z AI, Sekcja 4.1)
  → Po zakończeniu: character created → authenticated state
  → Blocker: gracz NIE może wejść do app bez ukończonego onboardingu
```

### 25.8 Struktura Projektu

```
apps/mobile/
├── app/                     # expo-router pages (file-based routing)
│   ├── (tabs)/
│   │   ├── index.tsx        # Home
│   │   ├── quests/
│   │   │   ├── index.tsx    # Quest Discovery
│   │   │   ├── [id].tsx     # Quest Detail
│   │   │   └── active.tsx   # Active Quest
│   │   ├── social/
│   │   └── profile/
│   ├── onboarding.tsx
│   ├── proof/
│   │   ├── capture.tsx
│   │   └── [id]/result.tsx
│   └── _layout.tsx
├── components/
│   ├── avatar/              # AvatarImage, AvatarTierBadge
│   ├── quest/               # QuestCard, QuestTimer, DifficultyBadge
│   ├── proof/               # ProofTypeSelector, ProofPreview
│   └── ui/                  # Button, Card, Badge (design system)
├── lib/
│   ├── api.ts               # ky client z JWT interceptorem
│   ├── auth.ts              # Firebase Auth hooks
│   └── store.ts             # Zustand stores (auth, character, upload-queue)
├── hooks/                   # React Query hooks
│   ├── useNearbyQuests.ts
│   └── useProofStatus.ts
├── services/
│   ├── offline-cache.ts     # SQLite quest cache
│   └── upload-queue.ts      # AsyncStorage proof queue + background retry
└── utils/
    ├── image-compress.ts
    └── deep-link.ts
```

Kluczowe pliki implementacji:

```typescript
// apps/mobile/app/(tabs)/quests/index.tsx
import { View, FlatList } from 'react-native'
import { useQuery } from '@tanstack/react-query'
import { api } from '../../../lib/api'
import type { Quest } from '@20hero/types'

export default function QuestsScreen() {
  const { data: quests, isLoading } = useQuery({
    queryKey: ['quests'],
    queryFn: () => api.get('quests').json<Quest[]>(),
  })

  return (
    <View style={{ flex: 1 }}>
      <FlatList
        data={quests}
        keyExtractor={(q) => q.id}
        renderItem={({ item }) => <QuestCard quest={item} />}
      />
    </View>
  )
}
```

```typescript
// apps/mobile/lib/api.ts
import ky from 'ky'
import { getAuth } from 'firebase/auth'

export const api = ky.create({
  prefixUrl: process.env.EXPO_PUBLIC_API_URL,
  hooks: {
    beforeRequest: [
      async (request) => {
        const token = await getAuth().currentUser?.getIdToken()
        if (token) request.headers.set('Authorization', `Bearer ${token}`)
      },
    ],
  },
})
```

```typescript
// apps/mobile/lib/store.ts
import { create } from 'zustand'
import { persist } from 'zustand/middleware'
import * as SecureStore from 'expo-secure-store'
import type { Character } from '@20hero/types'

interface AuthState {
  status: 'unauthenticated' | 'authenticating' | 'onboarding' | 'authenticated'
  accessToken: string | null
  character: Character | null
  setAuthenticated: (token: string, character: Character) => void
  logout: () => void
}

export const useAuthStore = create<AuthState>()(
  persist(
    (set) => ({
      status: 'unauthenticated',
      accessToken: null,
      character: null,
      setAuthenticated: (token, character) =>
        set({ status: 'authenticated', accessToken: token, character }),
      logout: () =>
        set({ status: 'unauthenticated', accessToken: null, character: null }),
    }),
    {
      name: 'auth-store',
      storage: {
        getItem: (key) => SecureStore.getItemAsync(key),
        setItem: (key, value) => SecureStore.setItemAsync(key, value),
        removeItem: (key) => SecureStore.deleteItemAsync(key),
      },
    }
  )
)
```

### Critical Files for Implementation

- `/srv/20hero/apps/api/src/index.ts` - Hono app structure with lifespan, CORS, and router registration pattern
- `/srv/20hero/apps/api/src/routes/` - Router pattern with Zod validation, response models, and error handling
- `/srv/20hero/packages/db/src/schema.ts` - Drizzle ORM schema with Enums and typed fields for Character, Quest, and Proof models
- `/srv/20hero/packages/db/src/index.ts` - Database initialization and connection pool (postgres-js)
- `/srv/20hero/apps/api/src/workers/` - BullMQ worker definitions for AI verification and notifications
---