anwar-pi-website/DATABASE.md

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).