# DATABASE — anwarpy.ai

> The schema is **Payload-first**: collections in `web/src/collections/` are the source of truth; SQL in Postgres is a generated artifact. This document maps the logical schema; never edit SQL by hand.

## 1. Adapter & migrations

- `@payloadcms/db-postgres` (Drizzle). Connection = `DATABASE_URI` (prod: composed by `docker-compose.vps.yml` from `DB_PASSWORD`; local: `web/.env` → LAN Postgres 192.168.88.36).
- Migrations live in `src/migrations/` (timestamped `YYYYMMDD_HHMMSS.ts` + paired `.json`). Created with `npx payload migrate:create`; applied **on container boot** in prod, or `npx payload migrate` locally.
- Rules: never edit an applied migration (history: the `20261003_111451` duplicate-`full_name` fix was done in place *before* prod applied it — that's the exception that proves the risk). Data backfills are standalone scripts under `web/scripts/` (`backfill-reading-minutes.ts` pattern), not migrations. `payload_migrations` is bookkeeping — if it says applied, it is.

## 2. Collections

### `posts` — guides (the core table)
| Field | Type | Notes |
|---|---|---|
| `title`, `slug` | text | slug **unique**, URL identity, 409 on conflict |
| `dek` | text | standfirst/summary, feeds metadata + JSON-LD |
| `category` | select | وكلاء / نماذج / استرجاع / سلامة / أدوات / أعمال |
| `topics` | select/array | registry in `src/lib/topics.ts` (agents, models, automation, foundations) |
| `access` | select `free` \| `vip` | **intended tier — data, not state**; never rewritten to simulate launch states |
| `content` | lexical (richtext) | serialized `{root:{children:[…]}}`; **field-level read ACL** (ARCHITECTURE §3) |
| `contentType` | select | note / tutorial / case_study / comparison — badge |
| `authorName` | text | visible byline; also JSON-LD author Person |
| `sourceId` | text | agent-pipeline idempotency; dedup before slug-409 |
| `cover` | upload → `media` | og:image source (1200×630) |
| `readingMinutes` | number | **computed at write** (`countLexicalWords` ÷ `READING_WORDS_PER_MINUTE`=160); stripped from agent payloads; backfilled honestly |
| `publishedAt` | date | feeds «حُدّث» + JSON-LD |
| `status` | select `draft` \| `published` | public visibility gate; `publish: true` agent opt-in |
| derived/rels | — | `views`, `likes` counters (see routes) |

### `users`
Payload auth: `email` (unique), `password` (hash), `fullName`, `interest`/`level` (onboarding), `emailVerified` flag + `verification_token` (written at signup, ADR-compliant), `stripeCustomerId` (Phase 3; hidden/removed when empty — never exposed to frontend). Roles: `admin` vs member (see SECURITY §3). Note: `20261003_111451` removed a duplicate `full_name` column.

### `comments`
`post` (rel → posts), `author` (rel → users, **forced server-side** — the API ignores any client-sent author, ADR-0002), `authorName` (display snapshot), `body` (textarea), `status` (moderation: pending → published / rejected). Anonymous requests are rejected at the route (PRD §3.5). `admin.defaultColumns: post, authorName, status, createdAt`.

### `launch_notifications`
`email`, `topic`, timestamps. Written by `/api/notify` on every submission (even when mail transport is down — the honest-503 still records the row). Duplicate email → no new row, `alreadySubscribed: true`. Zero rows during the 2026-10-10 prod smoke cleanup is the expected state.

### `post_likes`
`user` (rel), `post` (rel) — **one per user per post** (uniqueness enforced in the like route before insert); counter incremented on `posts`. Auth required (401 otherwise).

### `media`
Payload uploads (covers + pipeline images) → volume `media-uploads`; `sharp` for sizes. Public URL pattern `/api/media/file/...`.

### `files`
Guide attachments → volume `files-uploads`; per-file `access` mirrors its post (download gate `GET /api/files/[id]`); ingested via `agent-files.ts` (never accepts `access` from the payload).

## 3. Relationships & integrity

```
posts 1─* comments *─1 users
posts 1─* post_likes *─1 users   (unique(user,post))
posts *─1 media (cover)
posts 1─* files                  (each file carries its own access)
```

Enforcement lives in collection hooks/route handlers (Payload has no SQL-level FK constraints across its tables for these patterns) — hence the "check then insert" pattern in `like` (query `where user+post` before `create`). Keep that pattern for any new per-user-once resource.

## 4. Indexing

- Unique: `posts.slug`, `users.email`, `posts.sourceId` (dedup lookup), `verification_token`.
- Hot lookups: `comments.post` (+ `status`), `post_likes.post`, `launch_notifications.email` (dup check).
- Add indexes via migrations when a query pattern shows up in slow logs — not speculatively.

## 5. Backups & restore

- `web/scripts/backup-db.sh` → `pg_dump` gzip; prod cron/backup policy is an owner action.
- Rehearsed restore (2026-10-10): restore into a **temp DB** (`anwarpi_restore_test`), verify counts (`posts`, `users`, `payload_migrations`), then drop. Never restore over the live DB without owner approval.

## 6. Data rules that are also PRD rules

- `access` and `readingMinutes` are stripped from agent payloads (`pickFields`) — the pipeline cannot set them.
- Reading minutes are computed, never trusted from input; if a script touches them, it must recompute from `content` and report scanned/updated counts.
- Personal data in `launch_notifications`/`users` emails: never rendered into docs, videos, or evidence (the smoke test uses `@example.com` rows and deletes them).