03 — DATABASE

Depends on: 02_TECH_STACK.md · Next: 04_API_PLATFORM.md

1. Choice: MariaDB 11.4+ + Prisma

Why MariaDB for yas.sh:

You already run MariaDB in production (backups, monitoring, tuning exist)
No second SQL to operate, backup, patch, scale
MariaDB 11 handles target: 10M links, 100M+ clicks with proper schema/indexes
Prisma 6 supports MariaDB fully (migrations, relations, indexes)
Escape hatch: Repository layer isolates ORM; migration to PostgreSQL later is a migration script + Prisma provider change, not a rewrite. Documented in §8.

If you must justify PostgreSQL later: Need for pg_partman, pg_stat_statements heavy analytics, or GIS. Not needed in Phase 1.

2. Prisma Setup

// packages/database/prisma/schema.prisma
datasource db {
  provider = "mysql" // MariaDB via mysql provider
  url      = env("DATABASE_URL")
}
generator client { provider = "prisma-client-js" }

DATABASE_URL=mysql://user:pass@localhost:3306/yas
Migrations: prisma migrate dev --name <name> → SQL in prisma/migrations/
Never edit migrations after merge — add new migration
Seeds: prisma/seed.ts idempotent, requires SEED_ADMIN_PASSWORD env

3. Core Schema (Phase 1 — 5 features)

model User {
  id            String    @id @default(cuid())
  email         String    @unique
  passwordHash  String
  name          String?
  role          Role      @default(USER) // USER, ADMIN
  createdAt     DateTime  @default(now())
  updatedAt     DateTime  @updatedAt
  sessions      Session[]
  links         Link[]
  apiKeys       ApiKey[]
  @@index([createdAt])
}

model Session {
  id        String   @id @default(cuid())
  userId    String
  tokenHash String   @unique // SHA-256 of __Host-session
  expiresAt DateTime
  createdAt DateTime @default(now())
  user      User     @relation(fields: [userId], references: [id], onDelete: Cascade)
  @@index([userId])
  @@index([expiresAt])
}

model Link {
  id           String   @id @default(cuid())
  userId       String
  originalUrl  String   @db.VarChar(2048)
  shortCode    String   @unique // base62, indexed
  customAlias  String?  @unique
  title        String?  @db.VarChar(255)
  expiresAt    DateTime?
  expireClicks Int?
  passwordHash String?  // Argon2id if protected
  qrEnabled    Boolean  @default(false)
  clicks       Int      @default(0)
  createdAt    DateTime @default(now())
  updatedAt    DateTime @updatedAt
  user         User     @relation(fields: [userId], references: [id], onDelete: Cascade)
  clickEvents  ClickEvent[]
  @@index([userId, createdAt(sort: Desc)])
  @@index([shortCode])
}

model ClickEvent {
  id        String   @id @default(cuid())
  linkId    String
  ipHash    String?  @db.VarChar(64) // HMAC truncated, never raw IP
  country   String?  @db.VarChar(2)
  city      String?  @db.VarChar(100)
  device    String?  @db.VarChar(50)
  browser   String?  @db.VarChar(50)
  os        String?  @db.VarChar(50)
  referrer  String?  @db.VarChar(2048)
  bot       Boolean  @default(false)
  createdAt DateTime @default(now())
  link      Link     @relation(fields: [linkId], references: [id], onDelete: Cascade)
  @@index([linkId, createdAt(sort: Desc)])
  @@index([createdAt])
}

model ApiKey {
  id         String    @id @default(cuid())
  userId     String
  name       String
  keyHash    String    @unique // SHA-256, show once: yas_live_<32>
  scopes     Json      // ["links:write", "analytics:read"]
  lastUsedAt DateTime?
  expiresAt  DateTime?
  createdAt  DateTime  @default(now())
  user       User      @relation(fields: [userId], references: [id], onDelete: Cascade)
}

model BlogPost {
  id          String   @id @default(cuid())
  slug        String   @unique
  title       String   @db.VarChar(255)
  excerpt     String   @db.VarChar(500)
  contentMdx  String   @db.LongText
  heroImage   String?  @db.VarChar(500)
  authorId    String
  tags        Json
  publishedAt DateTime?
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt
  @@fulltext([title, excerpt]) // MariaDB full-text
  @@index([publishedAt(sort: Desc)])
}

Phase 2 adds: Team, Domain, Folder, Tag, Subscription, AuditLog, BlogAuthor.

4. Indexing Strategy

Link.shortCode UNIQUE btree — hot path for redirect
Link(userId, createdAt DESC) — dashboard list
ClickEvent(linkId, createdAt DESC) — analytics per link
ClickEvent(createdAt) — time-range aggregates, TTL
User.email UNIQUE
Full-text on BlogPost(title, excerpt) — search Phase 1 (no Meilisearch)

Explain plans for top 10 queries pasted into PROJECT_MEMORY.md (use EXPLAIN ANALYZE).

5. Scale Design (Without Over-Engineering Phase 1)

Clicks: MariaDB partitioning is optional in 11; start with indexed ClickEvent, add PARTITION BY RANGE (YEAR(createdAt)) only after 10M rows. Dashboards query SELECT COUNT(*) GROUP BY DATE(createdAt) with daily materialized view if needed (or pg-boss rollup).
Connection pooling: Prisma connection_limit=10 + pool_timeout=10; add ProxySQL only when metrics show queuing.
Slow query log: long_query_time=0.2, log_slow_verbosity=query_plan

6. Migrations & Seeds

pnpm --filter @yas/database db:generate
pnpm --filter @yas/database db:migrate   # prisma migrate deploy (prod)
pnpm --filter @yas/database db:seed      # idempotent, requires SEED_ADMIN_PASSWORD

Every migration is forward + rollback tested on a disposable DB in CI
Seed creates: admin user, 3 demo links, 5 blog posts (see 07_BLOG_SYSTEM.md)

7. Backup & Restore

Daily: mariadb-dump --single-transaction | gzip | gpg | rclone to R2/S3
Binlog: log_bin on, 7-day retention → RPO ≤15m
Restore drill: scripts/restore.sh tested monthly; record RTO in PROJECT_MEMORY.md
scripts/backup.sh wired to systemd timer or cron; alerts on failure

8. Future PostgreSQL Path (If Ever Needed)

Prisma provider change: mysql → postgresql
Dump: mariadb-dump --compatible=postgresql → transform
Data types: DATETIME → TIMESTAMPTZ, LONGTEXT → TEXT
Re-apply migrations on Postgres shadow DB
No app code change if repository layer is clean (services use Prisma client, not raw SQL)

Keep this path documented but do not implement in Phase 1.

Next: 04_API_PLATFORM.md — API-first, OpenAPI, SDKs.

03_DATABASE