# Database

PostgreSQL via Prisma 6. Schema: [prisma/schema.prisma](prisma/schema.prisma).
Migrations live in `prisma/migrations/` and are the only way schema changes
reach any environment.

## Commands

```bash
npm run db:migrate           # dev: create + apply a migration
npm run db:migrate:deploy    # prod: apply pending migrations (no generation)
npm run db:seed              # optional, removable demo data
npm run db:studio            # browse data
```

## Tenancy rules (non-negotiable)

1. Every organisation-owned table has `organisationId`; website-owned tables
   also have `websiteId`. Both are indexed.
2. No query for tenant data without first resolving the caller's membership
   (`requireOrgAccess` / `loadWebsiteForUser`).
3. `Organisation` unknown to the caller → respond **404**, never 403.
4. Cascading deletes flow from Organisation/Website so no orphaned tenant
   data survives.

## Model groups

| Group | Models |
| --- | --- |
| Identity | User, Session, Account (OAuth), AuthToken (verify/reset/invite) |
| Tenancy | Organisation, OrganisationMember, Role, Permission |
| Websites | Website, WebsiteLocation, WebsiteVerification, BrandProfile |
| Integrations | Integration, IntegrationCredential (AES-256-GCM at rest) |
| Crawling | Crawl, CrawledPage, CrawlLink, CrawlIssue |
| Auditing | Audit, AuditIssue, SeoScore |
| Keywords | Keyword, KeywordCluster, KeywordRanking |
| Competitors | Competitor, CompetitorPage |
| Content | ContentBrief, ContentDraft, ContentVersion, ContentTemplate |
| On-page | InternalLinkSuggestion, StructuredDataItem, Sitemap, RobotsVersion, Redirect |
| Workflow | Campaign, Task, Recommendation, ProposedChange, PublishedChange, Approval, Rollback |
| AI | AgentRun, AIUsage |
| Platform | Notification, Report, Subscription, UsageRecord, AuditLog, ApiKey, WebhookEndpoint, WebhookDelivery, BackgroundJob, FeatureFlag, FileUpload |

## Conventions

- IDs: cuid strings.
- Timestamps: `createdAt` default now, `updatedAt` auto where rows mutate.
- Soft delete (`deletedAt`) on User and Organisation; Websites use
  `archivedAt` (history retained).
- Statuses are Prisma enums (`CrawlStatus`, `ChangeStatus`, `TaskStatus`, …).
- **Reversibility**: `ProposedChange.originalValue` is captured before any
  modification; `PublishedChange` + `Rollback` record what actually happened.
- **No fabricated data**: metric fields (searchVolume, difficulty, rankings)
  are nullable and stay null until a real `dataSource` provides them.
- Secrets: only hashes (`Session.tokenHash`, `AuthToken.tokenHash`,
  `ApiKey.keyHash`) or AES-GCM ciphertext (`IntegrationCredential`) are stored.

## Local database options

- Docker: `docker compose up -d db` (port 5432).
- No Docker: `npx prisma dev -n northwest-seo --detach` starts a local
  Postgres (prints the URL; add `?sslmode=disable&pgbouncer=true` — the local
  server pools connections, and `pgbouncer=true` disables prepared statements
  which it cannot share).

## Backups

See DEPLOYMENT.md §Backups: nightly `pg_dump`, WAL/PITR on managed Postgres,
restore rehearsal before go-live.