# Database runbook (Teleton Rifa)

How to set up the schema and seed the two raffle events. This is the offline /
operator runbook for agent H's slice: the migration is already generated and
committed; this file is how you apply it and seed a real Postgres.

## 0. Prerequisites

- A Postgres database (Supabase recommended, per architecture.md section 1.1).
- Two connection strings (Supabase gives you both in the dashboard):
  - `DATABASE_URL`: the Supavisor transaction pooler, port **6543**. This is the
    runtime URL the app uses. The driver sets `prepare: false` for it.
  - `DIRECT_URL`: the direct connection, port **5432**. Used ONLY for migrations
    (drizzle-kit). The pooler does not support the session features migrations
    need (architecture.md section 7.2).

## 1. Set the environment

Copy the example and fill the two database URLs (at minimum):

```bash
cp .env.example .env.local
```

```dotenv
# Runtime (Supavisor transaction pooler, port 6543)
DATABASE_URL="postgresql://postgres.<ref>:<password>@<region>.pooler.supabase.com:6543/postgres"
# Migrations (direct connection, port 5432)
DIRECT_URL="postgresql://postgres.<ref>:<password>@<region>.pooler.supabase.com:5432/postgres"
```

`drizzle.config.ts` prefers `DIRECT_URL` and falls back to `DATABASE_URL` if a
direct URL is not set (single-URL local dev).

For local dev against a plain Postgres you can point both at the same URL:

```dotenv
DATABASE_URL="postgresql://postgres:postgres@localhost:5432/teleton"
DIRECT_URL="postgresql://postgres:postgres@localhost:5432/teleton"
```

## 2. Generate the migration (already done, offline)

The initial migration is committed at `drizzle/0000_silent_bromley.sql`
(10 tables, 4 enums, all indexes and foreign keys). Regenerate only after a
schema change:

```bash
npm run db:generate   # drizzle-kit generate -> emits SQL into drizzle/
```

Generation reads `lib/db/schema.ts` and does NOT connect to a database. If
drizzle-kit demands a URL just to generate, any dummy value works for that step:

```bash
DATABASE_URL="postgres://user:pass@localhost:5432/db" npm run db:generate
```

## 3. Apply the schema

Two options. Use the migration in production (auditable SQL history); `push` is
fine for local iteration.

```bash
# Production / staging: apply the committed migration via the direct connection.
npm run db:push        # drizzle-kit push (DIRECT_URL, port 5432)
```

`db:push` diffs the schema against the database and applies the changes. For a
fresh database this creates all 10 tables, both unique guards
(`purchases_idem_uq`, `purchases_charge_uq`), and the `webhook_provider_event_uq`
dedup index. Confirm the table count is 10 after it finishes.

## 4. Seed the two raffle events

```bash
npm run db:seed        # tsx lib/db/seed.ts (uses DATABASE_URL)
```

The seed is **idempotent**:

- Raffles are upserted by `slug`, so re-running updates config and never
  duplicates an event.
- Prize and pricing-tier rows are replaced per raffle, but only while the raffle
  is still in `draft` and has no committed draw. Once a draw exists (and
  therefore winners can reference prizes), the seed leaves child rows alone and
  only updates the raffle config. This makes a re-seed safe to run any time
  before a draw is committed.

What it inserts:

| Raffle | slug | Prizes | Draw |
|---|---|---|---|
| Warm-up | `warmup-ago-2026` | 5 (2 confirmed examples, 3 "Premio por confirmar") | end of Aug 2026 |
| Principal | `principal-12-sep-2026` | 10 (2 confirmed examples, 8 "Premio por confirmar") | 12 Sep 2026 |

Pricing (both raffles, the recommended flat S/5 model, Framing B / Option 1),
stored as data so September can swap to a bundle or package model without a
rebuild:

- Base: `basePriceCents = 500` (S/5 per chance).
- `maxChancesPerPurchase = 4` (base + up to 3 extra).
- `feeOptInEnabled = true` ("cubre la comision").
- Upsell tiers: +1 chance / S/5, +2 chances / S/10, +3 chances / S/15.

`basesUrl` is left null until the official bases PDF exists.

## 5. Verify

In the Supabase SQL editor (or psql):

```sql
select slug, status, base_price_cents, max_chances_per_purchase from raffles;
select r.slug, count(p.id) as prizes
  from raffles r left join prizes p on p.raffle_id = r.id
  group by r.slug;
select r.slug, t.label, t.extra_chances, t.extra_price_cents
  from raffles r join pricing_tiers t on t.raffle_id = r.id
  order by r.slug, t.sort_order;
```

Expected: 2 raffles in `draft`, base 500, max 4; 5 + 10 prizes; 3 tiers each.

## Notes

- `db:seed` cannot run from the build sandbox (no live database). Run it after
  the database is provisioned and the migration is applied.
- The migration meta snapshots (`drizzle/meta/`) are gitignored per
  `.gitignore`; the `.sql` migration files are committed (auditable history for
  the charity's reviewers, per drizzle.config.ts).