Async Product Demo & Screen Recording Platform

Reelay — Async Product Demo & Screen Recording Platform #

Product Requirements Document · Final

Reelay is a screen recording and demo platform for teams. It records screen, camera and microphone from the browser or a lightweight desktop app, automatically edits the raw capture into a polished product demo — zoom-to-cursor, damped camera motion, framed backgrounds — and turns the result into a shareable, trackable, access-controlled asset with an embeddable player, viewer analytics, comments, calls-to-action and a branded library.

The product's centre of gravity is the async product demo. Auto-editing quality is the differentiator, and it is specified here to a level an engineer can implement: real thresholds, real filter coefficients, real formulas. Everything else — support replies, sales follow-ups, standups — falls out of the same capture-and-share pipeline.

This document is written to be executed cold by an AI coding agent or an engineering team, with no clarifying questions required. Every concern has exactly one canonical section; every other section references it by number rather than restating it. Where a decision was open, it has been made and recorded as settled fact — there are no deferred choices anywhere in this document.

Table of Contents #

1. Before You Start — Customization Decisions
2. Product Overview, Vision & Scope Boundaries
3. Technology Stack & System Architecture
4. Conventions, Standards & Code Organization
5. Data Model & Database Schema
6. Identity, Workspaces, Roles & Permissions
7. API Design — REST, Public API & Webhooks
8. Capture — Browser & Desktop Recording
9. Media Pipeline — Upload, Transcode, Storage & Delivery
10. AI Auto-Editing Engine — Zoom, Motion & Backgrounds
11. Timeline Editor & Edit Decision List
12. Transcription, Captions, Chapters & AI Metadata
13. Screenshot Capture & Beautification
14. Sharing, Link Security & Access Control
15. The Embeddable Player
16. Viewer Analytics
17. Engagement — Comments, Reactions, CTAs & Email Capture
18. Video Library, Folders, Collections & Brand Kit
19. Retention, Deletion & Data Lifecycle
20. Integrations — Slack, Notion, HubSpot & Webhooks
21. Billing, Plans & Usage Enforcement
22. Security, Privacy & Compliance
23. Accessibility — WCAG 2.2 AA
24. Observability, Reliability & Operations
25. Testing Strategy & Quality Assurance
26. Deployment, Environments & Configuration
27. Performance Budgets & Scale Targets
28. Milestones & Execution Plan
29. Executor Instructions

1. Before You Start — Customization Decisions #

This section lists every decision the executing team is likely to want to revisit before or during the build. Each row states the decision, the working default this document assumes everywhere else, the reasoning behind that default, and exactly what changes if a different choice is made. The rest of this document is written as if every default below has already been accepted — an executor who accepts all defaults can start building immediately with zero open questions.

1.1 How to use this document #

This is a single Markdown file, organized into 29 numbered top-level sections (## 1. through ## 29.), each owning a specific subsystem. Subsections are ### <N>.<M> and, where needed, #### <N>.<M>.<K>. Every fact — a table schema, an API error code, a naming rule, a config value — is defined in exactly ONE section (its "owning" section). Every other section that needs that fact references it by section number instead of restating it (for example: "validated per the shared Zod schema convention in Section 4.5" or "returns the error envelope defined in Section 7.6"). This means:

• If two parts of this document ever appear to disagree, the OWNING section is authoritative.
• Version numbers appear exactly once, in Section 3.1. Elsewhere the document says "the versions in Section 3" rather than repeating a number, so a version bump never requires a global find-replace.
• Database tables are defined once in Section 5. Any section that touches a table (billing touching subscriptions, sharing touching share_links) references the table name and links back to 5.
• An executing AI agent or engineer should be able to start at Section 1, read forward, and implement the product with no external clarification. Anywhere this document would normally say "TBD," it instead states a concrete default and this section explains why.
• Cross-references read as "Section 7.6" or "Section 19.3" — never as a filename, a chat log, or an external document. If the team wants to record their own deviations from this spec as they build, they should keep a DECISIONS.md in their own repository documenting what they changed and why; that file is theirs to own, not part of this spec.

1.2 Decision table #

2. Product Overview, Vision & Scope Boundaries #

2.1 The problem #

Three categories of screen-recording tool exist today, and each leaves a gap:

• Loom is fast to record and share, but the output is raw: no automatic camera movement, no cleanup of dead air or filler words, and analytics/team workflow are shallow. It optimizes for "record and send" over "record and land the message."
• Screen Studio and Tella produce polished, cinematic output — auto zoom-to-cursor, smooth camera motion, branded backgrounds — but they are single-player editing tools first. Sharing is an export step, not a first-class distribution and analytics surface, and there is no real team workflow (shared libraries, permissions, brand-kit enforcement, audit trail).
• Vidyard and Descript sit in adjacent spaces — Vidyard leans sales-video-hosting with reasonable analytics but dated, non-auto editing; Descript leans deep multi-track editing for produced content, which is more tool than most day-to-day async communication needs.

No product combines Screen Studio-grade automatic polish with Loom-grade share velocity and Vidyard-grade analytics and team control, in one capture-to-share pipeline.

2.2 The wedge #

Reelay has one deliberate center of gravity: the async product demo — the video a salesperson, founder, or product manager records to show a prospect or teammate how something works, without a live call. Automatic editing quality (Section 10) is the differentiator: a demo that looks professionally cut with zero manual editing effort is what separates Reelay from "just another Loom clone." Every other use case the product supports — support-ticket replies, sales follow-ups, async standups, bug reports — is a byproduct of the same capture → auto-edit → share → measure pipeline, not a separately engineered workflow. The product deliberately does not build vertical-specific tooling per use case (Section 2.5); the wedge is the pipeline, not the persona.

2.3 Target users and jobs-to-be-done #

2.4 Competitive positioning #

2.5 Success metrics #

2.6 User journey — install to shared demo to analytics #

1. Install: user signs up with email+password or Google OAuth (Section 6.6), verifies email (required before any public sharing, Section 6.9), and is offered browser-based recording immediately or a desktop app download for OS-audio/high-fps capture (Section 8.4).
2. Record: user starts a screen (+ optional webcam) recording. Cursor telemetry is captured throughout (Section 10.1). Recording streams to local buffer and uploads concurrently (Section 9.2) so there is no "finish recording, then wait to save" step.
3. Auto-edit: on upload completion, the auto-edit pipeline (Section 10) generates a default EditDecisionList — zoom-to-cursor segments, smooth camera path, chosen background — without any user action. This is what the user sees first when they open the video.
4. Refine (optional): user opens the timeline editor (Section 11), adjusts zoom timing, removes filler words/silence (Section 12.2, Pro+), applies a brand-kit background (Section 18.5, plan-gated per Section 21.2), and the system re-renders from the EDL, never destroying the original.
5. Share: user creates a share link (Section 14) with a visibility level, optional password/ expiry/domain restriction, and copies the URL or embeds the player (Section 15). AI-suggested title, summary, and chapters (Section 12.5) are attached automatically and editable.
6. Viewer watches: recipient opens the watch page or embedded player. No account or app install required for link/public visibility. Viewing generates analytics events (Section 16.2) from first frame.
7. Analytics come back: the sender sees, per viewer where identity is known, watch-through %, drop-off curve, and any CTA clicks/comments/reactions (Section 16, 17) — typically within seconds of a heartbeat event landing, definitely within the hourly rollup window (Section 16.3) for aggregate views.
8. Team workflow: the video lands in a shared folder per workspace permissions (Section 18.3); teammates with member+ role can view analytics on their own videos, admin/owner see workspace- wide analytics and audit log (Section 6.8).

2.7 In scope — the 15 core features #

1. Browser-based screen + webcam + microphone recording (Section 8.1–8.2)
2. Desktop capture app for OS-audio, high-fps, multi-display recording (Section 8.4)
3. Local-first capture with resumable, network-loss-proof upload (Section 9.2)
4. Automatic zoom-to-cursor, smooth camera motion, and background compositing (Section 10)
5. Non-destructive timeline editor with a re-renderable Edit Decision List (Section 11)
6. Filler-word and silence detection and removal (Section 12.2, Pro+)
7. Automatic transcription, closed captions, and chapter/summary/title generation (Section 12)
8. Server-side burned-in redaction (blur/box, static or tracked) (Sections 11.7 and 22.2, 14.8)
9. Screenshot capture and beautification (background, shadow, device frame) (Section 13)
10. Share links with visibility, password, expiry, domain restriction, and audit log (Section 14)
11. An embeddable, performance-budgeted player for web and email (Section 15)
12. Per-viewer and aggregate watch analytics with drop-off curves (Section 16)
13. Engagement: comments, reactions, CTAs, and email-gated viewing (Section 17)
14. Team libraries: folders, collections, brand kit, workspace roles and permissions (Sections 6, 18)
15. Billing and plan enforcement across Free/Pro/Business tiers (Section 21)

2.8 Out of scope — explicit boundaries #

Non-goals that follow from the wedge: Reelay does not attempt to be a general-purpose video hosting platform (no long-form/entertainment video use case), does not attempt to replace a full-featured video editor for produced marketing content, and does not attempt to be a webinar or live-event product. Every feature decision in this document should be evaluated against "does this serve the record-a-demo-and-know-it-landed loop" — features that don't are deliberately excluded even where a competitor offers them.

2.9 Glossary #

2.10 Metrics measurement methodology #

Each success metric in Section 2.5 must be computed from a specific, unambiguous data source so there is no ambiguity when the dashboard in Section 24.5 renders it.

3. Technology Stack & System Architecture #

3.1 Version lines #

The following version lines are locked for this build. Each is stated here once; every other section of this document refers back to "the versions in Section 3" rather than restating a number.

These version lines are a known-good floor, not a lockfile. At build time, install the current stable release of each dependency (npm install <pkg>@latest, or your ecosystem's equivalent), confirm the major line still matches, and let the lockfile record the exact resolved versions.

Never pin an exact patch version anywhere else in this document, in code comments, or in documentation the executing team writes from this spec. A version cited more than one major line behind the table above is stale by definition and should be treated as an error in whatever document cites it.

3.2 Monorepo layout #

Package manager: pnpm workspaces. Task orchestration and caching: Turborepo.

reelay/
├── apps/
│   ├── web/          # Next.js 16 app: marketing site, dashboard, timeline editor, watch page
│   ├── api/           # Fastify 5 REST API — internal (dashboard) + public (Section 7) surfaces
│   ├── desktop/        # Electron 43 capture app (macOS, Windows)
│   └── worker/         # BullMQ 6 worker processes — one deployable per queue group (Section 3.6)
├── packages/
│   ├── player/         # Embeddable player — vanilla TypeScript, zero framework runtime, loader ≤8KB gz, core ≤20KB gz (Section 15.2)
│   ├── db/             # Drizzle schema, migrations, seed scripts (Section 5)
│   ├── shared/          # Zod schemas, shared types, constants, provider interfaces (Section 4.5)
│   └── ui/             # React component library — dashboard/editor only, NEVER imported by packages/player
├── turbo.json
├── pnpm-workspace.yaml
└── package.json

Package responsibilities:

3.3 Request-path architecture #

                              ┌─────────────────────────────────────────┐
                              │              CDN (Section 3.8)            │
                              │  static assets · player bundle · posters  │
                              └───────────────┬───────────────────────────┘
                                              │
        ┌──────────────┐        ┌───────────▼────────────┐        ┌──────────────────┐
        │  apps/desktop │        │       apps/web           │        │   packages/player  │
        │ (Electron 43) │        │  (Next.js 16, Vercel)     │        │  (embedded, 3rd-   │
        │ capture+upload│        │ dashboard·editor·watch pg │        │  party page/email)  │
        └───────┬───────┘        └───────────┬────────────┘        └─────────┬──────────┘
                │  multipart upload           │  fetch (RSC + client)         │ fetch /v1/collect
                │  (Section 9.2)               │                               │ + signed playback URL
                ▼                              ▼                               ▼
        ┌──────────────────────────────────────────────────────────────────────────┐
        │                          apps/api (Fastify 5, Fly.io)                        │
        │   /v1/* REST — auth, videos, share-links, analytics ingest, webhooks        │
        └───────┬───────────────────────┬────────────────────────┬───────────────────┘
                │ enqueue jobs           │ read/write               │ signed URLs / webhooks
                ▼                        ▼                          ▼
        ┌───────────────┐        ┌─────────────┐          ┌─────────────────────┐
        │ Redis (BullMQ)  │        │ PostgreSQL 17 │          │  Mux (StreamingProvider) │
        │ queue topology  │        │ system of      │          │  Deepgram / Anthropic     │
        │ (Section 3.6)    │        │ record         │          │  (behind their interfaces) │
        └───────┬─────────┘        └─────────────┘          └─────────────────────┘
                │ consumes
                ▼
        ┌───────────────────────────────────────────┐
        │            apps/worker (Fly.io)              │
        │  transcode orchestration · EDL render (FFmpeg)│
        │  transcription · AI metadata · analytics rollup│
        │  deletion/retention jobs                       │
        └───────────────┬───────────────────────────────┘
                        │ reads/writes
                        ▼
        ┌───────────────────────────────────────────┐
        │   Object storage (R2/S3/B2 — Section 3.11)   │
        │   originals · renditions · exports · posters   │
        └───────────────────────────────────────────┘

Every arrow into apps/api is authenticated per Section 6 (session JWT or API key) except the analytics ingest endpoint POST /v1/collect and public webhook receivers, both of which are unauthenticated-but-verified (rate-limited and, for webhooks, signature-verified — Section 7.10).

3.4 Control plane vs. data plane #

• Control plane (PostgreSQL 17, Redis 8): every fact about what exists and what state it's in — users, workspaces, video metadata, EDLs, share-link permissions, subscription state, job queue state. Small, relational, transactional, backed up continuously (Section 24.6).
• Data plane (object storage + Mux + CDN): the actual media bytes — original recordings, rendered outputs, thumbnails, exports. Large, immutable-once-written (except deletion, Section 19), never queried relationally, served through the CDN rather than through apps/api.

Control plane and data plane are deliberately decoupled: apps/api never streams media bytes through itself. Upload goes client → object storage directly (pre-signed multipart URLs, Section 9.2); playback goes viewer → CDN/Mux directly (signed playback URLs, Section 14.6). The API's job is to issue and validate the URLs and record the resulting state transitions, not to proxy bytes.

3.5 Why PostgreSQL is the system of record — and what is deliberately not in it #

PostgreSQL 17 holds every fact the product needs to be correct and queryable relationally: entities, relationships, permissions, billing state, and rolled-up analytics. It is chosen over a document/NoSQL store because the domain is heavily relational (workspaces → members → videos → share-links → view-events, with permission checks that join across several of these on nearly every request) and because Section 5's schema needs real foreign keys, check constraints, and transactional guarantees (e.g., a share-link audit event must be written in the same transaction as the permission change it describes, Section 14.7).

Deliberately not in PostgreSQL:

• Media bytes (recordings, renditions, exports, thumbnails, screenshots) — these live in object storage (Section 3.11) and Mux; Postgres stores only pointers (media_assets.storage_key, renditions.mux_asset_id, etc., Section 5.4.4). Storing large binary blobs in Postgres would bloat the WAL, defeat point-in-time recovery efficiency, and gain nothing — object storage is purpose-built for this.
• The raw event firehose beyond its partition window — video_view_events (Section 5.4.10) is partitioned monthly and raw partitions older than 3 months are dropped after their rollups (video_view_daily, video_engagement_curve, Section 16.3) are confirmed durable. Keeping unbounded raw event history in the primary transactional database would eventually degrade every other query on the same instance; the rollup tables carry the long-term analytical value.
• Cursor telemetry blobs — raw high-frequency cursor samples (Section 10.1, up to 120 Hz) are stored as compressed blobs in object storage (referenced by cursor_telemetry_blobs.storage_key, Section 5.4.6), not as rows — at 120 samples/sec this is a data-plane volume problem, not a relational one.
• Session-level caches and rate-limit counters — these live in Redis (Section 3.7), which is the correct tool for high-churn, short-TTL, non-durable state.

3.6 Queue topology #

All async work is BullMQ 6 running against Redis 8. Every job is idempotent, keyed as job.id = <entity>:<operation>:<version> so a duplicate enqueue (from a retried webhook, for example) is a safe no-op rather than duplicate work. Every queue has a paired dead-letter queue (<queue-name>.dlq) that a failed job (after exhausting attempts) is moved to for manual inspection via the operations dashboard (Section 24.5).

This table is the concurrency/retry/backoff/dead-letter topology for each queue; it is deliberately silent on job names and on the video-render/video-export concurrency figures — Sections 9.3.1 and 9.8 own the exact job name strings and the render/export concurrency values, and restating them here would create exactly the kind of two-source-of-truth drift this document's single-owner convention (Section 1.1) exists to prevent.

All queues run in apps/worker, deployed as separate process groups per queue-family (ingest/ transcode/render/export share a "media" worker deployment; transcription/ai-metadata share an "AI" worker deployment; analytics-rollup/retention-deletion/email/webhook-delivery/screenshot share a "platform" worker deployment) so CPU-heavy FFmpeg work can be scaled independently of I/O-bound webhook/email work.

3.7 Caching layers #

3.8 CDN posture #

A CDN sits in front of two origins: the reelay-media-delivery object storage bucket (Section 3.11 — posters, thumbnails, exports, screenshots, and the player bundle) and Mux's own edge network (for HLS segment delivery — Mux operates its own CDN for adaptive streaming, so Reelay's CDN layer does not re-proxy video segments, only the surrounding static assets and non-Mux exports). The reelay-media-restricted bucket (Section 3.11) has no CDN origin configured for it at all, in any environment — this is a physical guarantee, not a cache-policy one: a misconfigured CDN rule cannot expose restricted content because the CDN has no path to that bucket to begin with.

Cache-Control policy: content-hashed static assets (player bundle, marketing site build output) are cached immutably for 1 year. Posters, thumbnails, and animated previews use the SAME immutable policy — content-hashed key, Cache-Control: public, max-age=31536000, immutable — rather than a short revalidation window, because this asset class is access-controlled by its key, not by a guessable path: the object key for a poster or thumbnail is scoped by the owning share link's playback_key_version (Section 14.1.1), so a re-render produces a new content hash and a visibility downgrade or link revocation mints a new playback_key_version — either way, the next request serves a new key rather than depending on the old cached response to expire. The old key's CDN cache entry is additionally purged outright (not left to age out) on both a re-render and a visibility downgrade/link revocation, per Section 14.1.1 — a short TTL is not a substitute for an explicit purge on those two events. API responses are never CDN-cached (all /v1/* traffic bypasses the CDN and hits apps/api directly, since responses are per-session/per-key and permission-scoped).

3.9 Why transcoding is not hand-rolled #

Adaptive-bitrate transcoding — probing source video, generating a multi-rendition ladder (multiple resolutions/bitrates), packaging HLS/DASH manifests, and serving them with per-session signed access — is a well-understood but operationally heavy problem: it requires a fleet of GPU/CPU transcode workers that must scale with upload volume, careful queue backpressure so a traffic spike doesn't starve other jobs, codec/format edge-case handling across an enormous device/browser matrix, and constant maintenance as codecs evolve (AV1 adoption, for example). None of this differentiates Reelay — the product's differentiation is the auto-editing layer (Section 10) that sits on top of a correct, fast, reliable transcode, not the transcode itself. Mux is purpose-built for exactly this problem, exposes a signed-playback-URL model that maps directly onto the share-link security model (Section 14.6), and its webhook-driven "asset ready" callback integrates cleanly into the video-transcode queue (Section 3.6). Building and operating an equivalent transcode fleet in-house would be a multi-quarter distraction from the product's actual wedge (Section 2.2) for a capability that is, from the user's perspective, table stakes rather than differentiating. FFmpeg (Section 3.1) is still used directly, but only for two narrower, product-specific tasks Mux does not do: EDL rendering (compositing zoom/pan/background per the auto-edit decision list, Section 10) and burning in redaction regions (Sections 11.7 and 22.2) — both are Reelay-specific transformations applied before the result is handed to Mux (or directly exported) for delivery.

3.10 The managed-streaming boundary — StreamingProvider interface #

No code outside packages/shared/src/streaming/ and its Mux implementation ever imports the Mux SDK directly. Every call site (API routes, worker jobs) depends only on this interface:

// packages/shared/src/streaming/provider.ts

export interface StreamingAsset {
  providerAssetId: string;
  providerPlaybackId: string;
  status: 'preparing' | 'ready' | 'errored';
  durationMs: number | null;
  maxResolutionTier: '720p' | '1080p' | '1440p' | '2160p' | null;
}

export interface SignedPlaybackToken {
  token: string;
  expiresAt: Date; // now + 6h per Section 14.8
}

export interface StreamingProvider {
  /** Create a direct-upload target the client/worker can PUT the source file to. */
  createDirectUpload(input: {
    corsOrigin: string;
    newAssetSettings: { playbackPolicy: 'signed'; maxResolutionTier: StreamingAsset['maxResolutionTier'] };
  }): Promise<{ uploadUrl: string; providerUploadId: string }>;

  /** Look up asset state — used by the poll fallback if a webhook is missed (Section 7.10). */
  getAsset(providerAssetId: string): Promise<StreamingAsset>;

  /** Issue a short-TTL signed playback token for a specific viewer session. */
  signPlaybackToken(providerPlaybackId: string, opts: { ttlSeconds: number }): Promise<SignedPlaybackToken>;

  /** Delete the underlying asset — called from the deletion pipeline (Section 19). */
  deleteAsset(providerAssetId: string): Promise<void>;

  /** Verify an inbound webhook signature and parse it into a normalized event. */
  verifyWebhook(rawBody: Buffer, headers: Record<string, string>): StreamingWebhookEvent;
}

export type StreamingWebhookEvent =
  | { type: 'asset.ready'; providerAssetId: string; playbackId: string; durationMs: number }
  | { type: 'asset.errored'; providerAssetId: string; reason: string };

The Mux implementation (packages/shared/src/streaming/mux-provider.ts) is the only file that imports @mux/mux-node. Swapping streaming vendor (Section 1.2, decision 3) means writing a new file implementing this same interface and flipping STREAMING_PROVIDER=mux|cloudflare-stream|... (Section 26.3); the renditions table (Section 5.4.4) stores provider and provider_asset_id columns generically so historical data survives a swap.

3.11 Media storage bucket layout #

Restricted media uses two physically separate buckets, not two prefixes inside one bucket, so that a single misconfigured bucket policy — a public-read ACL added by mistake, an over-broad IAM role, a CDN origin pointed at the wrong place — can expose at most one class of content and never the other. This is the canonical model (owned in full, including the exhaustive media_assets.kind → bucket mapping, by Section 22.2.2); this section states the architecture-level shape, and Section 9.6 covers the pipeline stages that write to each bucket.

A misconfigured policy on reelay-media-delivery (for example, an ACL mistake that makes a poster world-readable that shouldn't be) cannot expose anything in reelay-media-restricted, because the two are different buckets with different credentials, different IAM roles, and — critically — reelay-media-restricted has no CDN origin to misconfigure in the first place. This is the specific security property physical bucket separation buys over a single-bucket, prefix-based layout: a bucket-level public-read policy silently wins over a prefix's intended restriction if a single bucket is shared, which is exactly the failure mode that has exposed "private" content in practice elsewhere. Physical separation removes that failure mode by construction rather than depending on every future engineer getting a prefix rule right.

media_assets.kind (Section 5.4.4, canonical) determines which bucket an object lives in:

Path convention within each bucket (both buckets use {workspaceId}/{videoId}/... as the leading path segment so per-workspace lifecycle policies and cost attribution work identically in either bucket):

reelay-media-restricted/
├── originals/{workspaceId}/{videoId}/{recordingId}.{ext}              # kind = 'original' (Section 11.1)
├── unredacted/{workspaceId}/{videoId}/{mediaAssetId}.{ext}            # kind = 'redaction_unredacted_original'
└── telemetry/{workspaceId}/{videoId}/{recordingId}.cbor.gz            # cursor_telemetry_blobs (Section 5.4.6)

reelay-media-delivery/
├── renditions/{workspaceId}/{videoId}/{renditionId}/{file}            # kind = 'rendition'
├── exports/{workspaceId}/{videoId}/{exportId}.{mp4|gif|webm}          # kind = 'export' (Section 9, item list)
├── posters/{workspaceId}/{videoId}/{keyVersion}/{posterId}.jpg        # kind = 'poster', keyed by playback_key_version (Section 14.1.1)
├── thumbnails/{workspaceId}/{videoId}/{keyVersion}/{thumbId}.jpg      # kind = 'thumbnail', same keying
├── screenshots/{workspaceId}/{screenshotId}.{png|jpg}                 # kind = 'screenshot_original' | 'screenshot_edited' (Section 13)
└── exports-tmp/{jobId}/                                               # worker scratch space, lifecycle-expired after 24h, never CDN-fronted

Per-environment naming follows the same suffix convention as the rest of Section 3.12: each non-production environment gets its own bucket pair, reelay-media-restricted-<env> and reelay-media-delivery-<env> (for example reelay-media-restricted-staging and reelay-media-delivery-preview-<pr>), each with the same access-control shape as its production counterpart. The bare names reelay-media-restricted and reelay-media-delivery above are the production pair.

3.12 Environment topology #

3.13 Build-vs-buy table #

3.14 Why Fastify for apps/api #

Fastify 5 (rather than Next.js Route Handlers or Express) hosts the API for three concrete reasons that matter at this product's request shape: (1) schema-based request/response validation and serialization are first-class and fast — Fastify compiles JSON-schema-derived serializers ahead of time, which matters because every response passes through the shared Zod schemas (Section 4.5) at high frequency on hot paths like /v1/collect; (2) a plugin encapsulation model that maps cleanly onto per-resource route modules (Section 4.3's apps/api/src/routes/ layout) without the global middleware-ordering foot-guns Express has; (3) apps/api needs to run as a standalone long-lived process independent of any web framework's request lifecycle assumptions, since it is deployed separately from apps/web (Section 3.12) and must be reachable by apps/desktop and public API consumers with nothing Next.js-specific in the request path.

3.15 Autoscaling & capacity policy #

Autoscaling is a deploy-configuration concern, not application code — no app queries its own instance count. Scale-out safety is guaranteed by every job handler being idempotent (Section 3.6, 4.2), so adding workers never risks double-processing.

3.16 Workspace and pipeline configuration #

pnpm-workspace.yaml and the root Turborepo pipeline are the two files that make the monorepo buildable as a graph rather than a flat script list:

# pnpm-workspace.yaml
packages:
  - "apps/*"
  - "packages/*"

// turbo.json
{
  "$schema": "https://turbo.build/schema.json",
  "tasks": {
    "build": {
      "dependsOn": ["^build"],
      "outputs": ["dist/**", ".next/**", "!.next/cache/**"]
    },
    "dev": { "cache": false, "persistent": true },
    "lint": { "dependsOn": ["^build"] },
    "typecheck": { "dependsOn": ["^build"] },
    "test": { "dependsOn": ["^build"], "outputs": ["coverage/**"] },
    "test:e2e": { "dependsOn": ["build"] }
  }
}

^build expresses the dependency direction fixed in Section 3.2 (apps depend on packages, never the reverse): packages/shared and packages/db build before any app that imports them, and Turborepo's remote cache means an unchanged package is never rebuilt across CI runs, keeping PR checks fast regardless of monorepo size.

3.17 Local development quick reference #

Local development (Section 3.12's "Local" row) runs Postgres, Redis, and an S3-compatible store as containers so a new engineer can be productive without provisioning cloud resources:

# docker-compose.yml (repo root, local dev only — never used in staging/production)
services:
  postgres:
    image: postgres:17
    environment:
      POSTGRES_DB: reelay_dev
      POSTGRES_PASSWORD: reelay_dev
    ports: ["5432:5432"]
    volumes: ["pg-data:/var/lib/postgresql/data"]
  redis:
    image: redis:8
    ports: ["6379:6379"]
  minio:
    image: minio/minio
    command: server /data --console-address ":9001"
    environment:
      MINIO_ROOT_USER: reelay_dev
      MINIO_ROOT_PASSWORD: reelay_dev_secret
    ports: ["9000:9000", "9001:9001"]
    volumes: ["minio-data:/data"]
volumes:
  pg-data:
  minio-data:

STREAMING_PROVIDER and TRANSCRIPTION_PROVIDER (Section 26.3) still point at real vendor sandboxes in local development (Mux test mode, Deepgram's standard API) rather than being mocked, so the capture-to-share pipeline is exercised end-to-end even on a laptop; only object storage and the control-plane datastores are containerized locally.

4. Conventions, Standards & Code Organization #

4.1 Naming conventions #

4.2 Queue job naming detail #

Job names follow the <entity>.<operation>.<qualifier> pattern in dot.case (Section 4.1). The authoritative list of every queue's exact job-name strings is owned by Sections 9.3.1 and 9.8, not by Section 3.6's queue table — Section 3.6 owns concurrency, retry policy, backoff, and dead-letter topology only. The job's BullMQ id (used for idempotency, Section 3.6) is always <entity-type>:<entity-id>:<operation>:<preset-or-schema-version> — for example video:vid_2f8k...:render:compose-v3 or transcript:vid_2f8k...:transcribe:deepgram-nova-3. Re-enqueuing the same job id before the previous run completes is a safe no-op (BullMQ rejects the duplicate); re-enqueuing after completion with the same id is also a no-op unless removeOnComplete has evicted the record, in which case the handler itself re-checks entity state before doing any work (defense in depth beyond BullMQ's own dedup).

4.3 Folder structure inside each app/package #

apps/web/
├── app/                       # Next.js App Router routes
│   ├── (marketing)/            # public marketing pages, no auth
│   ├── (dashboard)/            # authenticated app shell: library, editor, settings
│   ├── (watch)/[shareSlug]/    # public watch page, no auth required
│   └── api/                    # Next.js route handlers ONLY for BFF concerns (auth callbacks,
│                                #  image optimization proxy) — never business logic; that's apps/api
├── components/                 # app-local React components not shared via packages/ui
├── lib/                        # app-local helpers (fetch client wrappers, RSC data loaders)
└── styles/

apps/api/
├── src/
│   ├── routes/                 # one file per resource, matches Section 7's endpoint groups
│   ├── plugins/                # Fastify plugins: auth, rate-limit, error-envelope, request-id
│   ├── services/                # business logic, one file per domain concern
│   ├── lib/                     # infra glue: email transport, streaming provider wiring
│   └── webhooks/                 # inbound webhook receivers (Mux, Deepgram, Stripe)
└── test/

apps/worker/
├── src/
│   ├── queues/                  # one subfolder per queue from Section 3.6, each with its processor
│   ├── jobs/                     # job handler implementations, imported by queues/
│   ├── render/                    # FFmpeg pipeline: EDL compositing (Section 10, 11), redaction burn-in (Sections 11.7, 22.2)
│   └── lib/
└── test/

apps/desktop/
├── src/
│   ├── main/                    # Electron main process: window mgmt, native capture hooks
│   ├── renderer/                 # Electron renderer: capture UI (minimal, imports packages/ui sparingly)
│   └── preload/                  # contextBridge-exposed APIs, strictly typed
└── native/                        # platform-specific native modules (ScreenCaptureKit/WASAPI bindings)

packages/player/
├── src/
│   ├── loader.ts                 # the ≤8KB embed.js entry point (Section 15.2)
│   ├── core/                      # the lazy-loaded player core (Section 15.3)
│   └── shadow-dom/                 # Shadow DOM mounting + scoped styles
└── test/

packages/db/
├── src/
│   ├── schema/                   # one file per table group (e.g. identity.ts, video.ts, billing.ts)
│   ├── client.ts                  # Drizzle client factory
│   └── migrations/                 # drizzle-kit generated SQL migrations
└── seed/

packages/shared/
├── src/
│   ├── schemas/                   # Zod schemas — the single source of truth (Section 4.5)
│   ├── types/                      # types derived from schemas via z.infer, plus pure types
│   ├── constants/                   # domains, plan limits, queue names, timing constants (Section 10)
│   ├── streaming/                    # StreamingProvider interface + implementations (Section 3.10)
│   ├── transcription/                 # TranscriptionProvider interface + implementations (Section 12.3)
│   ├── ai/                             # LlmProvider interface + prompts (Section 12.6)
│   ├── billing/                         # BillingProvider interface + implementations (Section 21.3)
│   ├── errors/                           # typed domain error classes (Section 4.4)
│   └── observability/                     # logging + Sentry init shared across apps (Section 4.6)
└── test/

packages/ui/
├── src/
│   ├── primitives/                # buttons, inputs, dialogs — design-system base layer
│   ├── composed/                   # VideoCard, ShareLinkTable, AnalyticsChart, etc.
│   └── editor/                      # timeline editor-specific components (Section 11)
└── test/

4.4 The Result/error-handling pattern #

No thrown strings, ever. Every function that can fail in an expected, domain-meaningful way returns a Result<T, E> rather than throwing; exceptions are reserved for truly unexpected/programmer-error conditions (which are caught at the process boundary and reported to Sentry, Section 24.3, never handled locally).

// packages/shared/src/errors/result.ts

export type Result<T, E extends DomainError = DomainError> =
  | { ok: true; value: T }
  | { ok: false; error: E };

export function ok<T>(value: T): Result<T, never> {
  return { ok: true, value };
}

export function err<E extends DomainError>(error: E): Result<never, E> {
  return { ok: false, error };
}

// packages/shared/src/errors/domain-error.ts

export abstract class DomainError extends Error {
  abstract readonly code: string;        // maps 1:1 to the API error envelope's `code` (Section 7.6)
  abstract readonly httpStatus: number;  // maps to the HTTP status the API route returns
  readonly details?: Array<{ field: string; issue: string }>;

  constructor(message: string, details?: Array<{ field: string; issue: string }>) {
    super(message);
    this.details = details;
  }
}

export class VideoNotFoundError extends DomainError {
  readonly code = 'video_not_found';
  readonly httpStatus = 404;
  constructor(videoId: string) {
    super(`Video ${videoId} was not found or you do not have access to it.`);
  }
}

export class PlanLimitExceededError extends DomainError {
  readonly code = 'plan_limit_exceeded';
  readonly httpStatus = 402;
  constructor(limitName: string, details?: Array<{ field: string; issue: string }>) {
    super(`This action exceeds the ${limitName} limit for your current plan.`, details);
  }
}

export class ValidationError extends DomainError {
  readonly code = 'validation_failed';
  readonly httpStatus = 422;
  constructor(details: Array<{ field: string; issue: string }>) {
    super('One or more fields failed validation.', details);
  }
}

Service functions return Promise<Result<T, SpecificDomainError>>. Route handlers in apps/api do the only translation step: unwrap the Result, and on ok: false, map error.code/error.httpStatus /error.details directly onto the error envelope owned by Section 7.6:

// apps/api/src/plugins/error-envelope.ts (sketch of the mapping, not the full plugin)
if (!result.ok) {
  return reply.status(result.error.httpStatus).send({
    error: {
      code: result.error.code,
      message: result.error.message,
      details: result.error.details ?? [],
      requestId: request.id,
    },
  });
}

Every new failure mode introduced anywhere in the codebase gets its own DomainError subclass with a stable code; reusing a generic code string across unrelated failures is not allowed — the code is the API's stable contract with public-API consumers (Section 7.6).

4.5 Shared Zod validation strategy #

One schema per domain object, defined once in packages/shared/src/schemas/, and reused in three places without redefinition: the API route's request/response validation, the client-side form validation (dashboard and editor), and the generated public API reference (Section 7.11 documents docs generation from these same schemas).

// packages/shared/src/schemas/video.ts
import { z } from 'zod';

export const createVideoRequestSchema = z.object({
  title: z.string().min(1).max(200).optional(),
  folderId: z.string().startsWith('fld_').optional(),
});
export type CreateVideoRequest = z.infer<typeof createVideoRequestSchema>;

export const videoResponseSchema = z.object({
  id: z.string().startsWith('vid_'),
  title: z.string(),
  durationMs: z.number().int().nonnegative(),
  status: z.enum(['processing', 'ready', 'failed']),
  createdAt: z.string().datetime(),
});
export type VideoResponse = z.infer<typeof videoResponseSchema>;

Rules:

• API route handlers call .parse() (or .safeParse(), converting a failure into a ValidationError per Section 4.4) on every request body/query/params object — never accept an unvalidated any.
• Client forms (dashboard, editor) import the exact same request schema for client-side validation before submit, guaranteeing the client never sends something the server would reject for a different reason than the server states.
• Response schemas are used both to type API client calls (z.infer becomes the TypeScript return type) and, in Section 7.11, to generate the public-facing API reference — so the reference can never drift from the actual validated shape.
• A schema is never duplicated by hand in a second location. If apps/web needs a slightly different shape (e.g., a subset of fields for a lightweight form), it derives it with .pick()/.omit()/ .partial() from the canonical schema, never by writing a parallel schema from scratch.

4.6 Logging conventions #

All logs are structured JSON, one object per line, written to stdout (collected by the hosting platform's log pipeline, Section 24.2). Required fields on every log line:

{
  "timestamp": "2026-08-19T14:03:22.104Z",
  "level": "info",
  "service": "apps/api",
  "requestId": "req_9fK2...",
  "workspaceId": "ws_3mQ1...",
  "userId": "usr_7bN0...",
  "message": "video.created",
  "durationMs": 42
}

Never logged, under any circumstance: raw passwords, session tokens, API key secrets (only the stored prefix, Section 6.8, may be logged), OAuth tokens, Stripe raw card data (never touches Reelay infrastructure at all — Stripe Checkout/Elements handle this, Section 21.4), full email content bodies, unredacted cursor telemetry payloads, or the contents of the reelay-media-restricted bucket (Section 3.11). A log statement that would include any of these must log a redacted placeholder ("token": "[REDACTED]") or the resource's stable ID only.

4.7 Commit and branch conventions #

• Commit messages follow Conventional Commits: <type>(<scope>): <summary>, types limited to feat, fix, chore, refactor, test, docs, perf, ci. Scope is the package/app name (feat(api): add share-link password support).
• Branch naming: <type>/<short-slug>, e.g. feat/share-link-password, fix/edl-render-race.
• No direct commits to main. All work lands via PR.

4.8 PR requirements #

Every PR must: pass CI (lint, typecheck, unit/integration tests, build — Section 25), include a test for any new behavior (Section 25.1's coverage expectations), update the relevant section of any developer-facing docs the team maintains in their own repository if the change alters a documented contract, and be reviewed and approved by at least one other engineer before merge. PRs that touch packages/player additionally require the CI size-limit check (Section 15.1's budget, enforced per Section 25.5) to pass — a budget regression blocks merge regardless of approval.

4.9 Lint/format toolchain #

ESLint (flat config) with @typescript-eslint strict rules, Prettier for formatting (invoked via an ESLint integration so there is exactly one source of truth for style, not two competing tools). Pre-commit hook (via a lightweight git-hooks manager) runs lint-staged: ESLint --fix and Prettier on staged files only. CI runs the full, unfiltered lint and format-check across the repo on every PR.

4.10 TypeScript compiler strictness #

Every package and app extends a shared root config:

// tsconfig.base.json
{
  "compilerOptions": {
    "target": "ES2023",
    "lib": ["ES2023", "DOM", "DOM.Iterable"],
    "module": "ESNext",
    "moduleResolution": "Bundler",
    "strict": true,
    "noUncheckedIndexedAccess": true,
    "noImplicitOverride": true,
    "exactOptionalPropertyTypes": true,
    "noFallthroughCasesInSwitch": true,
    "noUnusedLocals": true,
    "noUnusedParameters": true,
    "noPropertyAccessFromIndexSignature": true,
    "verbatimModuleSyntax": true,
    "isolatedModules": true,
    "skipLibCheck": true,
    "esModuleInterop": true,
    "forceConsistentCasingInFileNames": true,
    "resolveJsonModule": true
  }
}

packages/player additionally sets "types": [] in its own tsconfig.json (no ambient DOM-library globals beyond what it explicitly needs) to keep a compile-time guard against accidentally depending on a framework's global type augmentations.

4.11 Import ordering and path aliases #

Import order (enforced by an ESLint import-order rule, auto-fixed): (1) Node builtins, (2) external packages, (3) @reelay/* workspace packages, (4) absolute app-local imports via path alias, (5) relative imports, each group alphabetized and blank-line-separated. Path aliases per app, configured in each tsconfig.json's paths and mirrored in the bundler config:

4.12 Testing file convention #

Unit and integration tests (Vitest 4.x) live beside the code they test as <name>.test.ts, or in a sibling test/ directory when testing cross-module integration within a package. E2E tests (Playwright 1.62.x) live in apps/web/e2e/ and apps/api/e2e/, named <flow-name>.spec.ts. Test naming: describe(<unit under test>) → it(<behavior, stated as an assertion>), e.g. describe('createShareLink') → it('rejects an expiry date in the past'). Full testing strategy, coverage targets, and CI gating are owned by Section 25; this section only fixes the file-location and naming convention.

4.13 Do-not-do-this list #

4.14 Worked example — request handling end to end #

The following illustrates how Sections 4.4–4.6 compose in a real route, using video creation as the example (the full endpoint contract is owned by Section 7):

// apps/api/src/routes/videos.ts
import { createVideoRequestSchema, videoResponseSchema } from '@reelay/shared/schemas/video';
import { createVideo } from '@/services/video.service';

export async function registerVideoRoutes(app: FastifyInstance) {
  app.post('/v1/videos', async (request, reply) => {
    const parsed = createVideoRequestSchema.safeParse(request.body);
    if (!parsed.success) {
      const error = new ValidationError(
        parsed.error.issues.map((i) => ({ field: i.path.join('.'), issue: i.code })),
      );
      return reply.status(error.httpStatus).send(toErrorEnvelope(error, request.id));
    }

    const result = await createVideo({
      workspaceId: request.auth.workspaceId, // set by the auth plugin, Section 6
      userId: request.auth.userId,
      input: parsed.data,
    });

    if (!result.ok) {
      return reply.status(result.error.httpStatus).send(toErrorEnvelope(result.error, request.id));
    }

    request.log.info({ message: 'video.created', workspaceId: request.auth.workspaceId, videoId: result.value.id });
    return reply.status(201).send({ data: videoResponseSchema.parse(result.value), meta: null });
  });
}

// apps/api/src/services/video.service.ts
export async function createVideo(input: CreateVideoInput): Promise<Result<VideoRecord, DomainError>> {
  const usage = await getWorkspaceUsage(input.workspaceId);
  if (usage.videoCount >= usage.planLimits.libraryVideoCap) {
    return err(new PlanLimitExceededError('library_video_cap'));
  }
  const video = await db.insert(videos).values({ /* ... */ }).returning();
  return ok(video[0]);
}

This shape — parse with the shared schema, call a service that returns Result, translate exactly once at the route boundary — is the only accepted pattern for a mutating endpoint anywhere in apps/api.

4.15 Boolean and enum naming #

• Boolean columns and fields are prefixed is_/has_/can_/requires_ (is_active, has_password, can_download, requires_email_to_watch) — never a bare adjective (active is ambiguous between a boolean and a status enum).
• Enum-like columns are snake_case string enums with a fixed, documented value set (never a raw boolean pair standing in for a 3+-state concept), e.g. videos.status is 'processing' | 'ready' | 'failed', not two booleans is_processing/is_failed.
• TypeScript mirrors this with string-literal union types, never numeric/const enums (numeric enums serialize ambiguously to JSON and complicate the API contract in Section 7).

4.16 Package script conventions #

Every package/app exposes the same script names so Turborepo's pipeline (Section 3.16) and CI (Section 25) can invoke them uniformly without per-package special-casing:

4.17 Code review checklist #

Reviewers check, in addition to correctness: no thrown strings (Section 4.4), no duplicated Zod schemas (Section 4.5), no logging of sensitive fields (Section 4.6), no new vendor SDK import outside its interface file (Section 3.10, 4.13), no offset pagination introduced (Section 4.13), any new error path has a DomainError subclass with a stable code, and any new plan-gated behavior checks the limit server-side (Section 21.2's server-side-enforcement rule) rather than trusting a client flag.

4.18 Environment variable naming subcategories #

Section 26.3 owns the exhaustive environment variable reference; this section fixes the naming subcategory prefixes so a new variable is unambiguous about what it configures on sight:

A variable that configures a vendor-swappable interface (streaming, transcription, LLM, billing, email) always includes both a *_PROVIDER selector variable and the selected vendor's own prefixed credentials — never a bare credential with no accompanying selector, since that would hardcode the vendor choice outside the interface pattern in Section 3.10. The five *_PROVIDER selectors, named explicitly here so Section 26.3 can reference this list rather than re-deriving it: STREAMING_PROVIDER, TRANSCRIPTION_PROVIDER, LLM_PROVIDER, BILLING_PROVIDER, EMAIL_PROVIDER — one per vendor-swappable interface in Section 1.2's decision table, each defaulting to that table's default vendor. Object storage is deliberately not on this list: it has no *_PROVIDER selector because no code branches on which S3-compatible vendor is active (Section 1.2, decision 6) — swapping it is a credential/endpoint change, not an interface-selection change.

5. Data Model & Database Schema #

This section is the canonical source for every table, column, type, constraint, and index in the system. PostgreSQL 17 is the system of record (versions per Section 3). Every other section that mentions a table, column, or constraint is describing behavior built on top of what is defined here — if a conflict is ever perceived, this section wins.

All DDL below is written as it would appear inside Drizzle ORM migrations (managed with drizzle-kit; see Section 5.7) and is directly executable against a PostgreSQL 17 database. Table and column names follow snake_case, tables are plural, per the conventions in Section 4.

5.1 Identifier Policy #

5.1.1 Primary keys: UUIDv7, generated application-side #

Every table's primary key column is id uuid PRIMARY KEY. IDs are never generated by the database (no gen_random_uuid(), no DEFAULT). They are generated in application code using a UUIDv7 implementation before the row is constructed. This is a firm architectural rule, for three reasons:

1. Time-sortable: UUIDv7 embeds a 48-bit millisecond Unix timestamp in the high bits, so primary keys sort chronologically. B-tree indexes on id stay append-mostly (no random-page inserts the way UUIDv4 causes), which keeps write amplification and index bloat low even at the 100k-workspace scale modeled in Section 5.6.
2. No DB round-trip for FK graphs: application code that needs to construct a video, its initial recording row, and its media asset row in one transaction can generate all three IDs up front and build the full object graph in memory before touching Postgres.
3. No coordination required: multiple app server instances, worker processes, and the desktop app (which stages a recording ID before the row ever reaches the server, see Section 9) can all mint IDs independently with no collision risk and no central sequence.

packages/shared exports the generator used everywhere:

// packages/shared/src/id/uuidv7.ts
import { randomBytes } from "node:crypto";

/**
 * Generates a UUIDv7 per draft-ietf-uuidrev-rfc4122bis.
 * Layout: 48-bit unix_ts_ms | 4-bit version (0111) | 12-bit rand_a |
 *         2-bit variant (10) | 62-bit rand_b
 */
export function uuidv7(): string {
  const unixTsMs = BigInt(Date.now());
  const rand = randomBytes(10);

  const bytes = Buffer.alloc(16);
  bytes.writeUIntBE(Number(unixTsMs >> 16n), 0, 4);
  bytes.writeUIntBE(Number(unixTsMs & 0xffffn), 4, 2);

  // version (0111) in top 4 bits of byte 6, rand_a in remaining 12 bits
  bytes[6] = 0x70 | (rand[0] & 0x0f);
  bytes[7] = rand[1];

  // variant (10) in top 2 bits of byte 8, rand_b fills the remaining 62 bits
  bytes[8] = 0x80 | (rand[2] & 0x3f);
  bytes[9] = rand[3];
  rand.copy(bytes, 10, 4, 10);

  const hex = bytes.toString("hex");
  return [
    hex.slice(0, 8),
    hex.slice(8, 12),
    hex.slice(12, 16),
    hex.slice(16, 20),
    hex.slice(20, 32),
  ].join("-");
}

Every Drizzle insert helper sets id explicitly:

// packages/db/src/helpers/insert-with-id.ts
import { uuidv7 } from "@reelay/shared";

export function withId<T extends Record<string, unknown>>(row: T): T & { id: string } {
  return { id: uuidv7(), ...row };
}

5.1.2 Public prefixed-ID map #

Internal UUIDs are never exposed in API responses, URLs, or logs visible to customers. A public ID is a prefix plus a base58-encoding of the raw 16 bytes of the UUID. Base58 (Bitcoin alphabet, no 0, O, I, l) avoids visually ambiguous characters and needs no URL-escaping.

Any entity not listed here (e.g. recordings, renditions, transcript_segments) is an internal implementation detail never referenced directly by a public ID; it is always addressed through its owning videos row. upload_sessions is the one exception among pipeline-internal tables: it is public-ID-bearing because the client (browser, desktop app) holds a direct reference to it across the entire multipart upload lifecycle, including across app restarts (Section 9), so it needs a stable, URL-safe, non-guessable handle exactly like any other client-addressed resource — see 5.4.16 for why its id is minted independently rather than borrowed from the recording it belongs to.

Encode/decode lives in packages/shared:

// packages/shared/src/id/public-id.ts
import bs58 from "bs58";

const PREFIXES = {
  user: "usr_", workspace: "ws_", video: "vid_", folder: "fld_",
  shareLink: "lnk_", comment: "cmt_", viewEvent: "evt_", apiKey: "api_",
  workspaceMember: "mem_", workspaceInvite: "inv_", screenshot: "scr_",
  cta: "cta_", customDomain: "dom_", webhookEndpoint: "whe_",
  integrationConnection: "int_", uploadSession: "ups_",
} as const;

export type PublicIdKind = keyof typeof PREFIXES;

export function toPublicId(kind: PublicIdKind, uuid: string): string {
  const bytes = Buffer.from(uuid.replace(/-/g, ""), "hex");
  return PREFIXES[kind] + bs58.encode(bytes);
}

export function fromPublicId(kind: PublicIdKind, publicId: string): string {
  const prefix = PREFIXES[kind];
  if (!publicId.startsWith(prefix)) {
    throw new InvalidPublicIdError(kind, publicId);
  }
  const bytes = bs58.decode(publicId.slice(prefix.length));
  if (bytes.length !== 16) {
    throw new InvalidPublicIdError(kind, publicId);
  }
  const hex = Buffer.from(bytes).toString("hex");
  return [
    hex.slice(0, 8), hex.slice(8, 12), hex.slice(12, 16),
    hex.slice(16, 20), hex.slice(20, 32),
  ].join("-");
}

export class InvalidPublicIdError extends Error {
  constructor(kind: PublicIdKind, value: string) {
    super(`Invalid public id for ${kind}: ${value}`);
    this.name = "InvalidPublicIdError";
  }
}

fromPublicId is called at the API boundary (route param/body parsing, via the Zod transforms described in Section 7) so every handler downstream of that boundary works with raw UUIDs against Postgres. InvalidPublicIdError is caught by the shared error middleware and mapped to the validation_failed error code (Section 7.6) with HTTP 400 — a malformed public ID never reaches a database query.

5.1.3 Share slugs #

share_links.slug is NOT derived from the UUID. It is a 12-character base58 string generated from a cryptographically secure random source (crypto.randomBytes(9) → base58, truncated/padded to exactly 12 chars), independent of the row's primary key, with a unique index. Slugs are never sequential and never derived from any other identifier — a leaked slug must never let an attacker guess adjacent slugs or infer creation order.

5.2 Multi-Tenancy & Workspace Isolation #

Every workspace-scoped table carries a non-nullable workspace_id uuid column with a foreign key to workspaces.id. The isolation rule is absolute: no query against a workspace-scoped table may execute without a workspace_id predicate, and that predicate must be the actor's authorized workspace, resolved through authorize() (Section 6.9) — never taken unchecked from a client-supplied parameter.

To make forgetting this impossible rather than merely discouraged, packages/db exposes a wrapped query builder that requires a workspace scope as a first-class argument:

// packages/db/src/scoped.ts
import { and, eq, type SQL } from "drizzle-orm";
import type { PgTable } from "drizzle-orm/pg-core";
import { db } from "./client";

/**
 * The ONLY sanctioned way to read from a workspace-scoped table. Every call
 * site must supply workspaceId explicitly; there is no overload that omits
 * it. Tables without a `workspace_id` column fail to compile against this
 * helper (TypeScript structural check on `table.workspace_id`).
 */
export function scoped<T extends PgTable & { workspace_id: unknown }>(
  table: T,
  workspaceId: string,
) {
  return {
    where(extra?: SQL) {
      const base = eq(table.workspace_id as never, workspaceId);
      return extra ? and(base, extra) : base;
    },
    query: db.select().from(table),
  };
}

// Usage in a repository function — workspaceId is threaded from the
// authorize() result (Section 6.9), never from req.body/req.query directly.
export async function listVideosForWorkspace(workspaceId: string) {
  return db.select().from(videos).where(scoped(videos, workspaceId).where());
}

A repository-layer ESLint rule (no-unscoped-table-query, enforced in CI per Section 25) statically flags any db.select().from(<workspace-scoped table>) call that does not pass through scoped() or an equivalent named query function reviewed to enforce scoping. This is defense in depth on top of authorize() — the permission check decides whether an action is allowed, scoped()/the lint rule ensure the query that follows cannot silently read across tenants even if a future refactor forgets to re-check.

Tables that are NOT workspace-scoped: users, sessions, oauth_accounts, mfa_credentials, plans (global catalog), feature_flags (global, with optional per-workspace override rows), jobs_audit (system-wide worker telemetry, keyed by job not workspace, though its payload references workspace-scoped entities).

5.3 Soft-Delete Policy #

Per the canonical rule (Section 7.6 owns the API-visible effects; this section owns storage):

• Soft-delete (deleted_at timestamptz NULL, row retained): users, workspaces, videos, comments.
• Hard-delete (row physically removed): every other table.
• Media assets always hard-delete on purge. media_assets and renditions rows, and the underlying object-storage bytes, are permanently and irreversibly removed once the retention or deletion lifecycle reaches the purge stage. The purge lifecycle itself — grace periods, warning emails, the exact trigger conditions, and the worker that executes it — is owned by Section 19. This section only fixes the storage-level fact: there is no "soft-deleted media asset" state to design around; a media_assets row either exists with valid object-storage keys, or it (and its bytes) are gone.

Soft-deleted rows are excluded from all default queries via a deleted_at IS NULL predicate baked into the Drizzle repository layer's default find* helpers (a parallel convention to scoped() in 5.2 — a withoutDeleted() wrapper). Any query that needs to see soft-deleted rows (e.g. an admin-only "restore video" screen, or the deletion-lifecycle worker in Section 19) calls an explicitly named function (findVideoIncludingDeleted) so that intent is visible at the call site and greppable in review.

5.4 Full Schema DDL #

DDL is grouped by domain in dependency order (a table never references a table defined after it, except where a deferred ALTER TABLE ... ADD CONSTRAINT is called out explicitly for a circular reference).

5.4.1 Identity & Sessions #

CREATE TABLE users (
  id                 uuid PRIMARY KEY,
  email              citext NOT NULL,
  email_verified_at  timestamptz NULL,
  password_hash      text NULL,                 -- NULL if the account is OAuth-only
  display_name       text NOT NULL,
  avatar_url         text NULL,
  timezone           text NOT NULL DEFAULT 'UTC',
  locale             text NOT NULL DEFAULT 'en-US',
  last_login_at      timestamptz NULL,
  failed_login_count smallint NOT NULL DEFAULT 0,
  locked_until       timestamptz NULL,           -- brute-force lockout, Section 6.2
  created_at         timestamptz NOT NULL DEFAULT now(),
  updated_at         timestamptz NOT NULL DEFAULT now(),
  deleted_at         timestamptz NULL,

  CONSTRAINT users_display_name_len CHECK (char_length(display_name) BETWEEN 1 AND 120)
);
CREATE UNIQUE INDEX users_email_unique ON users (email) WHERE deleted_at IS NULL;
CREATE INDEX users_email_verified_idx ON users (email) WHERE email_verified_at IS NULL AND deleted_at IS NULL;
-- serves: "find unverified users older than N days" for verification reminder jobs

email uses the citext extension (CREATE EXTENSION IF NOT EXISTS citext; in the first migration) so lookups and the uniqueness constraint are case-insensitive without application-level lowercasing, while the original casing the user typed is preserved for display.

CREATE TABLE oauth_accounts (
  id                 uuid PRIMARY KEY,
  user_id            uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  provider           text NOT NULL,              -- 'google' (only provider at launch)
  provider_account_id text NOT NULL,              -- Google's stable `sub` claim
  provider_email     citext NOT NULL,
  access_token_enc   bytea NULL,                  -- AES-256-GCM encrypted, KMS-wrapped key (Section 22)
  refresh_token_enc  bytea NULL,
  token_expires_at   timestamptz NULL,
  created_at         timestamptz NOT NULL DEFAULT now(),
  updated_at         timestamptz NOT NULL DEFAULT now(),

  CONSTRAINT oauth_accounts_provider_check CHECK (provider IN ('google'))
);
CREATE UNIQUE INDEX oauth_accounts_provider_account_unique
  ON oauth_accounts (provider, provider_account_id);
-- serves: OAuth callback lookup "does this Google sub already have an account"
CREATE INDEX oauth_accounts_user_id_idx ON oauth_accounts (user_id);

ON DELETE CASCADE: an oauth_accounts row has no meaning without its users row; when a user is hard-purged at the end of the deletion lifecycle (Section 19), linked OAuth accounts disappear with it. (Recall users itself is soft-deleted for the ordinary "delete my account" flow — the cascade here only fires at final purge.)

CREATE TABLE mfa_credentials (
  id                 uuid PRIMARY KEY,
  user_id            uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  type               text NOT NULL DEFAULT 'totp',
  secret_enc         bytea NOT NULL,              -- encrypted TOTP seed, Section 22
  recovery_codes_enc bytea NOT NULL,               -- encrypted JSON array of hashed recovery codes
  enrolled_at        timestamptz NOT NULL DEFAULT now(),
  last_used_at       timestamptz NULL,

  CONSTRAINT mfa_credentials_type_check CHECK (type IN ('totp'))
);
CREATE UNIQUE INDEX mfa_credentials_user_unique ON mfa_credentials (user_id);
-- one TOTP enrollment per user at launch; unique index also serves the "is MFA enabled" lookup

CREATE TABLE sessions (
  id                 uuid PRIMARY KEY,
  user_id            uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  token_hash         text NOT NULL,               -- SHA-256 of the opaque cookie token
  user_agent         text NULL,
  ip_address         inet NULL,
  created_at         timestamptz NOT NULL DEFAULT now(),
  last_seen_at       timestamptz NOT NULL DEFAULT now(),
  expires_at         timestamptz NOT NULL,
  revoked_at         timestamptz NULL,
  revoked_reason     text NULL                   -- 'logout' | 'logout_all' | 'password_change' | 'admin'
);
CREATE UNIQUE INDEX sessions_token_hash_unique ON sessions (token_hash);
-- serves: every authenticated request's session lookup (hot path)
CREATE INDEX sessions_user_active_idx ON sessions (user_id) WHERE revoked_at IS NULL;
-- serves: "log out all devices" (bulk revoke by user_id) and the active-sessions settings screen
CREATE INDEX sessions_expires_at_idx ON sessions (expires_at) WHERE revoked_at IS NULL;
-- serves: the hourly sweep job that revokes/deletes rows past expiry

sessions hard-deletes: expired/revoked rows are pruned by a scheduled job (Section 24) after a 30-day retention window kept only for security-audit lookback; the session itself carries no product data worth soft-delete recovery semantics.

5.4.2 Workspaces, Membership & Invites #

CREATE TABLE workspaces (
  id                     uuid PRIMARY KEY,
  name                   text NOT NULL,
  slug                   text NOT NULL,           -- URL-safe, used in dashboard routing only (not public)
  owner_id               uuid NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
  plan_id                uuid NOT NULL REFERENCES plans(id) ON DELETE RESTRICT,
  plan_tier              text NOT NULL DEFAULT 'free',
  -- 'free' | 'pro' | 'business' — a denormalized, always-in-sync cache of plans.code for the
  -- workspace's CURRENT plan (joined through subscriptions -> plans). Kept alongside plan_id
  -- (the normalized FK) specifically because several hot, high-frequency code paths (the
  -- retention scan's per-video plan lookup, Section 19.1.1; storage-quota checks, Section 21)
  -- need the plan tier without a join, and those paths run per-video or per-request at a volume
  -- where the join would be a measurable cost. Updated in the same transaction as any
  -- subscription plan change (Section 21) — there is exactly one writer, the billing subsystem,
  -- and it is never computed ad hoc elsewhere.
  viewer_seats_billable  boolean NOT NULL DEFAULT false,
  storage_used_bytes     bigint NOT NULL DEFAULT 0,   -- cached rollup, reconciled nightly (Section 21)
  lifetime_view_count    bigint NOT NULL DEFAULT 0,
  -- a workspace-level, permanent counter of total historical views, incremented once per purged
  -- video's anonymized final view count (Section 19.3.1) so workspace-level trend reporting
  -- survives individual video deletions. This is the ONLY place that number continues to exist
  -- once a video's own `videos.view_count` and all its `video_view_events`/`video_view_daily`
  -- rows are purged — never decremented, never reconciled against per-video data after the fact.
  scheduled_deletion_at  timestamptz NULL,             -- grace-period deletion, Section 19
  created_at             timestamptz NOT NULL DEFAULT now(),
  updated_at             timestamptz NOT NULL DEFAULT now(),
  deleted_at             timestamptz NULL,

  CONSTRAINT workspaces_name_len CHECK (char_length(name) BETWEEN 1 AND 120),
  CONSTRAINT workspaces_viewer_seats_free CHECK (viewer_seats_billable = false),
  CONSTRAINT workspaces_plan_tier_check CHECK (plan_tier IN ('free', 'pro', 'business'))
);
CREATE UNIQUE INDEX workspaces_slug_unique ON workspaces (slug) WHERE deleted_at IS NULL;
CREATE INDEX workspaces_owner_id_idx ON workspaces (owner_id);
CREATE INDEX workspaces_scheduled_deletion_idx ON workspaces (scheduled_deletion_at)
  WHERE scheduled_deletion_at IS NOT NULL;
-- serves: Section 19's daily sweep for workspaces past their grace period
CREATE INDEX workspaces_plan_tier_idx ON workspaces (plan_tier);
-- serves: the nightly retention scan's per-tier batch query (Section 19.1.1) without a subscriptions join

owner_id ON DELETE RESTRICT: a users row can never be hard-deleted while it still owns a workspace. The account-deletion flow (Section 19) forces an explicit ownership transfer or workspace deletion first — this constraint is the database-level backstop for the one-owner rule in Section 6.7.

workspaces_viewer_seats_free is a CHECK fixed at false: per the locked plan model, viewer seats are never billable at launch. The column exists (rather than being omitted) so the billing engine (Section 21) has a single documented switch to flip if a future plan tier changes this, without a schema migration — but today it is constrained to always be false, so no application code path can accidentally start charging for viewers.

CREATE TABLE workspace_members (
  id           uuid PRIMARY KEY,
  workspace_id uuid NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
  user_id      uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  role         text NOT NULL,               -- 'owner' | 'admin' | 'member' | 'viewer'
  invited_by   uuid NULL REFERENCES users(id) ON DELETE SET NULL,
  joined_at    timestamptz NOT NULL DEFAULT now(),
  created_at   timestamptz NOT NULL DEFAULT now(),
  updated_at   timestamptz NOT NULL DEFAULT now(),

  CONSTRAINT workspace_members_role_check CHECK (role IN ('owner', 'admin', 'member', 'viewer'))
);
CREATE UNIQUE INDEX workspace_members_workspace_user_unique ON workspace_members (workspace_id, user_id);
-- serves: "is this user a member of this workspace, and with what role" (hottest query in authorize())
CREATE INDEX workspace_members_user_id_idx ON workspace_members (user_id);
-- serves: "list all workspaces this user belongs to" (workspace switcher)
CREATE UNIQUE INDEX workspace_members_one_owner_unique
  ON workspace_members (workspace_id) WHERE role = 'owner';
-- enforces the ONE-owner rule at the database level: a second 'owner' row for the
-- same workspace violates this partial unique index and the insert/update fails

The partial unique index workspace_members_one_owner_unique is the load-bearing constraint for Section 6's "exactly one owner per workspace" rule. Ownership transfer (Section 6.7) is implemented as a single transaction that updates the outgoing owner's role to admin and the incoming member's role to owner — both statements must commit together, or the transaction fails and neither role changes, because at every intermediate instant during a non-atomic sequence the index would either reject a second owner row or (if the demotion ran first) briefly leave zero owners. The application code performs both UPDATEs inside one SERIALIZABLE-isolated transaction to close that window entirely.

CREATE TABLE workspace_invites (
  id             uuid PRIMARY KEY,
  workspace_id   uuid NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
  email          citext NOT NULL,
  role           text NOT NULL,
  invited_by     uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  token_hash     text NOT NULL,             -- SHA-256 of the invite link token
  status         text NOT NULL DEFAULT 'pending',   -- 'pending' | 'accepted' | 'revoked' | 'expired'
  expires_at     timestamptz NOT NULL,
  accepted_at    timestamptz NULL,
  accepted_by    uuid NULL REFERENCES users(id) ON DELETE SET NULL,
  created_at     timestamptz NOT NULL DEFAULT now(),

  CONSTRAINT workspace_invites_role_check CHECK (role IN ('admin', 'member', 'viewer')),
  CONSTRAINT workspace_invites_status_check CHECK (status IN ('pending', 'accepted', 'revoked', 'expired'))
);
CREATE UNIQUE INDEX workspace_invites_token_hash_unique ON workspace_invites (token_hash);
CREATE UNIQUE INDEX workspace_invites_pending_unique
  ON workspace_invites (workspace_id, email) WHERE status = 'pending';
-- prevents duplicate pending invites to the same email; re-inviting first revokes the old row
CREATE INDEX workspace_invites_workspace_idx ON workspace_invites (workspace_id, status);

role on an invite excludes 'owner' by CHECK — ownership is never granted via invite, only via the explicit transfer flow in Section 6.7, which requires the invitee to already be an accepted member.

CREATE TABLE brand_kits (
  id               uuid PRIMARY KEY,
  workspace_id     uuid NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
  logo_media_id    uuid NULL REFERENCES media_assets(id) ON DELETE SET NULL,
  primary_color    text NOT NULL DEFAULT '#4F46E5',
  secondary_color  text NOT NULL DEFAULT '#0EA5E9',
  font_family      text NOT NULL DEFAULT 'Inter',
  watermark_position text NOT NULL DEFAULT 'bottom-right',
  is_enforced      boolean NOT NULL DEFAULT false,  -- Business plan: forces brand kit on all workspace videos
  created_at       timestamptz NOT NULL DEFAULT now(),
  updated_at       timestamptz NOT NULL DEFAULT now(),

  CONSTRAINT brand_kits_primary_color_hex CHECK (primary_color ~* '^#[0-9a-f]{6}$'),
  CONSTRAINT brand_kits_secondary_color_hex CHECK (secondary_color ~* '^#[0-9a-f]{6}$'),
  CONSTRAINT brand_kits_watermark_position_check
    CHECK (watermark_position IN ('bottom-right', 'bottom-left', 'top-right', 'top-left'))
);
CREATE UNIQUE INDEX brand_kits_workspace_unique ON brand_kits (workspace_id);
-- one brand kit per workspace at launch (a workspace-level singleton, not a list)

logo_media_id forward-references media_assets, defined in 5.4.4. Postgres resolves this fine within one migration file because CREATE TABLE statements in a single transaction can reference tables created earlier in the same transaction — media_assets is ordered before brand_kits in the actual migration file (this document presents domains grouped by topic for readability; the generated migration file linearizes strictly by dependency, and drizzle-kit's diff will place them correctly).

5.4.3 Folders & Permission Overrides #

CREATE TABLE folders (
  id           uuid PRIMARY KEY,
  workspace_id uuid NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
  parent_id    uuid NULL REFERENCES folders(id) ON DELETE CASCADE,
  name         text NOT NULL,
  visibility   text NOT NULL DEFAULT 'private',   -- 'private' | 'workspace'
  created_by   uuid NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
  position     integer NOT NULL DEFAULT 0,   -- manual sort order within parent
  created_at   timestamptz NOT NULL DEFAULT now(),
  updated_at   timestamptz NOT NULL DEFAULT now(),

  CONSTRAINT folders_name_len CHECK (char_length(name) BETWEEN 1 AND 200),
  CONSTRAINT folders_visibility_check CHECK (visibility IN ('private', 'workspace'))
);
CREATE INDEX folders_workspace_parent_idx ON folders (workspace_id, parent_id, position);
-- serves: rendering the folder tree for a workspace, ordered

created_by ON DELETE RESTRICT: a departing member's folders are reassigned (Section 6.7 covers what happens to a removed member's videos and folders) before the users row can ever be purged; this constraint prevents an orphaned created_by.

parent_id ON DELETE CASCADE: deleting a folder deletes its subtree. Videos inside a deleted folder are NOT deleted (see videos.folder_id ON DELETE SET NULL in 5.4.4) — they move to the workspace root, because a folder is an organizational label, not a container that owns video lifecycle.

visibility is the default-deny governor described in Section 6.8.1: workspace means every workspace member gets baseline view access with no explicit grant needed; private (the default for every newly created folder, regardless of the creator's role) means nobody but owner/admin and holders of an explicit folder_permissions grant can see the folder at all. visibility is never inherited. The column is NOT NULL DEFAULT 'private', so every folder row always carries an explicit value of its own, and that value alone governs the folder — there is no "unset" state for an ancestor's setting to fill in. Inheritance applies only to folder_permissions grants, which follow the nearest-ancestor rule in Section 6.8.1. There is deliberately no "inherit-with-no-restriction" fallback: the absence of a grant is a denial, never an implicit allow (Section 6.8.1, and the identical statement of the rule in Section 18.2.1).

CREATE TABLE folder_permissions (
  id                uuid PRIMARY KEY,
  workspace_id      uuid NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
  folder_id         uuid NOT NULL REFERENCES folders(id) ON DELETE CASCADE,
  principal_type    text NOT NULL,               -- 'user' | 'role'
  principal_user_id uuid NULL REFERENCES users(id) ON DELETE CASCADE,
  principal_role    text NULL,                   -- 'owner' | 'admin' | 'member' | 'viewer' (principal_type = 'role' only)
  permission_level  text NOT NULL,               -- 'view' | 'comment' | 'edit' | 'manage'
  granted_by        uuid NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
  created_at        timestamptz NOT NULL DEFAULT now(),

  CONSTRAINT folder_permissions_principal_type_check CHECK (principal_type IN ('user', 'role')),
  CONSTRAINT folder_permissions_principal_consistency CHECK (
    (principal_type = 'user' AND principal_user_id IS NOT NULL AND principal_role IS NULL) OR
    (principal_type = 'role' AND principal_role IS NOT NULL AND principal_user_id IS NULL)
  ),
  CONSTRAINT folder_permissions_principal_role_check CHECK (
    principal_role IS NULL OR principal_role IN ('owner', 'admin', 'member', 'viewer')
  ),
  CONSTRAINT folder_permissions_level_check CHECK (permission_level IN ('view', 'comment', 'edit', 'manage'))
);
CREATE UNIQUE INDEX folder_permissions_folder_user_unique
  ON folder_permissions (folder_id, principal_user_id) WHERE principal_type = 'user';
CREATE UNIQUE INDEX folder_permissions_folder_role_unique
  ON folder_permissions (folder_id, principal_role) WHERE principal_type = 'role';
CREATE INDEX folder_permissions_user_idx ON folder_permissions (principal_user_id) WHERE principal_user_id IS NOT NULL;
-- serves: "what folder overrides apply to this user" when computing effective access
CREATE INDEX folder_permissions_folder_idx ON folder_permissions (folder_id);
-- serves: rendering a folder's full permission list (GET /v1/folders/{id}/permissions)

A grant's principal_type is either a single named user (principal_user_id, referencing users.id directly — not workspace_members.id, because a grant is a statement about a person, and users.id is the identifier that persists across that person's membership lifecycle) or a whole role (principal_role, one of the four workspace roles) — a blanket grant to every member currently holding that role, without enumerating individual users. principal_user_id is scoped by ON DELETE CASCADE to users, but note this only fires at final account purge (Section 19); a member's removal from a specific workspace does not delete their users row, so their folder_permissions grants in that workspace must be cleaned up explicitly by the removal flow (Section 6.7.5), not left to this FK. workspace_id is carried directly on the row (rather than derived by joining through folder_id) so the removal flow's cleanup query (DELETE FROM folder_permissions WHERE workspace_id = $1 AND principal_user_id = $2) never needs a join, and so scoped() (5.2) applies uniformly.

Precedence between a folder grant, folder visibility, and the workspace role is defined in Section 6.8.1 (the permission-matrix section owns the composition rule; this table only stores the grant rows).

5.4.4 Videos, Recordings & Media #

CREATE TABLE videos (
  id                       uuid PRIMARY KEY,
  workspace_id             uuid NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
  folder_id                uuid NULL REFERENCES folders(id) ON DELETE SET NULL,
  owner_id                 uuid NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
  title                    text NOT NULL DEFAULT 'Untitled video',
  description              text NULL,
  summary                  text NULL,
  -- AI-accepted summary (Section 12.9.1), distinct from `description`; surfaced in library
  -- previews and digest emails (Sections 17/18). Written only via the ai_metadata_suggestions
  -- accept/edit_and_accept flow (5.4.7) or direct manual entry — never by generation alone.
  status                   text NOT NULL DEFAULT 'processing',
  -- 'processing' | 'ready' | 'failed' | 'archived' | 'trashed' | 'purging'
  duration_ms              integer NULL,               -- NULL until probed
  source_recording_id      uuid NULL REFERENCES recordings(id) ON DELETE SET NULL,
  active_edl_id            uuid NULL REFERENCES edit_decision_lists(id) ON DELETE SET NULL,
  auto_edit_preset_id      uuid NULL REFERENCES auto_edit_presets(id) ON DELETE SET NULL,
  auto_edit_preset_version integer NULL,               -- immutable snapshot, Section 10
  poster_media_id          uuid NULL REFERENCES media_assets(id) ON DELETE SET NULL,
  mux_asset_id             text NULL,
  visibility_default       text NOT NULL DEFAULT 'workspace',
  -- default visibility applied to new share links; does not itself gate access
  share_gate_status        text NOT NULL DEFAULT 'clear',
  -- 'clear' | 'blocked' — computed/cached, not an independent source of truth: 'blocked'
  -- whenever the video has any redaction_regions row not yet reflected in a
  -- redaction_verified = true rendition covering the video's current EDL version (Section 22.2.5).
  -- Recomputed by the render worker on every render completion and by the redaction-region
  -- create/update handler. A 'blocked' video cannot be shared, embedded, or made public
  -- through any code path (enforced at the single authorization point, Section 6.9).
  last_activity_at         timestamptz NOT NULL DEFAULT now(),
  -- bumped by trigger on any qualifying view or edit event (Section 19.1.1's definition of activity)
  activity_status          text NOT NULL DEFAULT 'active',
  -- 'active' | 'inactive' — computed nightly by the retention scan (Section 19.1.1)
  inactive_since            timestamptz NULL,           -- set the moment activity_status flips to 'inactive'
  retention_deadline       timestamptz NULL,            -- inactive_since + plan retention window; NULL on Business
  purge_at                 timestamptz NULL,            -- set on trash (status='trashed') or workspace/account deletion cascade
  view_count               bigint NOT NULL DEFAULT 0,   -- cached rollup from video_view_daily
  created_at               timestamptz NOT NULL DEFAULT now(),
  updated_at               timestamptz NOT NULL DEFAULT now(),
  deleted_at                timestamptz NULL,

  CONSTRAINT videos_status_check CHECK (status IN ('processing', 'ready', 'failed', 'archived', 'trashed', 'purging')),
  CONSTRAINT videos_visibility_check CHECK (visibility_default IN ('private', 'workspace', 'link', 'public')),
  CONSTRAINT videos_share_gate_status_check CHECK (share_gate_status IN ('clear', 'blocked')),
  CONSTRAINT videos_activity_status_check CHECK (activity_status IN ('active', 'inactive')),
  CONSTRAINT videos_title_len CHECK (char_length(title) BETWEEN 1 AND 300)
);
CREATE INDEX videos_workspace_active_idx ON videos (workspace_id, created_at DESC) WHERE deleted_at IS NULL;
-- serves: the library's default "recent videos" listing
CREATE INDEX videos_workspace_folder_idx ON videos (workspace_id, folder_id) WHERE deleted_at IS NULL;
-- serves: browsing a specific folder
CREATE INDEX videos_owner_idx ON videos (owner_id) WHERE deleted_at IS NULL;
-- serves: "member sees analytics for their OWN videos only" (Section 6.8) and "my videos" filter
CREATE INDEX videos_status_processing_idx ON videos (status) WHERE status = 'processing';
-- serves: operational dashboards / stuck-job detection (Section 24)
CREATE INDEX videos_title_trgm_idx ON videos USING gin (title gin_trgm_ops) WHERE deleted_at IS NULL;
-- serves: in-library fuzzy title search (requires `CREATE EXTENSION pg_trgm`)
CREATE INDEX videos_retention_deadline_idx ON videos (retention_deadline) WHERE retention_deadline IS NOT NULL;
-- serves: the nightly retention-notification sweep and deadline-crossing scan (Section 19.1/19.2)
CREATE INDEX videos_purge_at_idx ON videos (purge_at) WHERE status = 'trashed';
-- serves: the nightly trash-sweep that enqueues true deletion once purge_at elapses (Section 19.4)
CREATE INDEX videos_activity_scan_idx ON videos (activity_status, last_activity_at) WHERE deleted_at IS NULL;
-- serves: the nightly retention scan's "flip active videos with 30 days of silence" query (Section 19.1.1)

status's two added values reflect the trash/purge lifecycle owned by Section 19: trashed is the soft-delete state entered by an explicit user delete (deleted_at is set at the same time, per the soft-delete policy in 5.3 — trashed and deleted_at IS NOT NULL are set together, never one without the other), and purging is entered the instant the true-deletion workflow's first stage locks the video and revokes playback, before the row itself is finally removed. A video is never in both trashed and purging at once — the transition is one-directional and the row is deleted outright at the end of purging, not reset back to trashed.

owner_id ON DELETE RESTRICT: mirrors the workspaces.owner_id reasoning — a user cannot be purged while still owning videos; the member-removal flow (Section 6.7) reassigns video ownership to the workspace owner (or a designated admin) before a user is eligible for final deletion.

folder_id ON DELETE SET NULL: explained under folders above — deleting a folder never deletes its videos.

source_recording_id and active_edl_id are nullable with SET NULL because they are denormalized "current pointer" fields for fast reads; the authoritative history lives in the recordings and edit_decision_lists tables respectively (a video can have exactly one active EDL at a time but retains prior EDL rows for history/audit, per Section 11).

CREATE TABLE recordings (
  id                uuid PRIMARY KEY,
  workspace_id      uuid NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
  video_id          uuid NOT NULL REFERENCES videos(id) ON DELETE CASCADE,
  recorded_by       uuid NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
  source            text NOT NULL,               -- 'browser' | 'desktop'
  capture_mode      text NOT NULL,               -- 'screen' | 'screen_camera' | 'camera' | 'audio'
  capture_region_rect jsonb NULL,
  -- desktop-only region capture: { x, y, width, height } in physical, display-scale-corrected
  -- pixels (Section 8). NULL when the recording covers a full display/window rather than a
  -- user-dragged crop rectangle. The render worker crops to this rect during the mandatory
  -- pre-transcode normalization pass (Section 9); the raw capture itself is always full-display
  -- for encoder stability.
  telemetry_source  text NULL,                   -- 'native_120hz' | 'browser_60hz_degraded'
  -- lets downstream consumers (Section 10 auto-edit, analytics) condition on cursor-telemetry
  -- fidelity without re-deriving it from `source`/`telemetry_sample_hz`. NULL when
  -- has_cursor_telemetry = false.
  recorded_mime_type text NOT NULL,              -- chosen MediaRecorder mime, Section 8
  raw_media_id      uuid NULL REFERENCES media_assets(id) ON DELETE SET NULL,
  has_cursor_telemetry boolean NOT NULL DEFAULT false,
  telemetry_sample_hz smallint NULL,             -- 120 desktop / 60 browser fallback, Section 10.1
  telemetry_blob_id uuid NULL REFERENCES cursor_telemetry_blobs(id) ON DELETE SET NULL,
  captured_at       timestamptz NOT NULL,
  upload_completed_at timestamptz NULL,
  created_at        timestamptz NOT NULL DEFAULT now(),

  CONSTRAINT recordings_source_check CHECK (source IN ('browser', 'desktop')),
  CONSTRAINT recordings_capture_mode_check CHECK (capture_mode IN ('screen', 'screen_camera', 'camera', 'audio')),
  CONSTRAINT recordings_telemetry_source_check CHECK (telemetry_source IN ('native_120hz', 'browser_60hz_degraded'))
);
CREATE INDEX recordings_video_id_idx ON recordings (video_id);
CREATE INDEX recordings_workspace_recorded_by_idx ON recordings (workspace_id, recorded_by, created_at DESC);
-- serves: "my recent recordings" and per-seat usage accounting (Section 21)

recorded_mime_type (renamed from an earlier mime_type working name) is the exact MediaRecorder.isTypeSupported()-probed mime string recorded on capture (Section 8's codec preference order with fallbacks) — the recorded_ prefix disambiguates it from the several other mime/content-type fields elsewhere in the schema (media_assets.content_type, etc.) so a column-name grep is never ambiguous about which stage of the pipeline it describes.

recordings hard-deletes (it is not in the soft-delete list in 5.3); its video_id ON DELETE CASCADE means a recording row is meaningless without a live videos row. Note this is distinct from the video's soft-delete: while videos.deleted_at is set, the recordings row (and its media) still physically exists, because cascade only fires on a hard DELETE, which only happens at final purge (Section 19).

CREATE TABLE media_assets (
  id              uuid PRIMARY KEY,
  workspace_id    uuid NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
  kind            text NOT NULL,
  -- 'original' | 'redaction_unredacted_original' | 'poster' | 'thumbnail' | 'export' |
  -- 'brand_logo' | 'screenshot_original' | 'screenshot_edited' — see the bucket table below
  storage_bucket  text NOT NULL,
  storage_key     text NOT NULL,
  restricted      boolean NOT NULL DEFAULT false,
  -- true for 'original', 'redaction_unredacted_original', 'screenshot_original': never served to
  -- a viewer, never referenced by a share link or playback token, access audit-logged (Section 22).
  -- Enforced in the object-storage access layer (distinct IAM policy per bucket, Section 22.2.2),
  -- not just here — this column drives internal authorization checks, but the restricted bucket's
  -- own bucket policy is the actual backstop.
  content_type    text NOT NULL,
  size_bytes      bigint NOT NULL,
  checksum_sha256 text NULL,
  width_px        integer NULL,
  height_px       integer NULL,
  created_at      timestamptz NOT NULL DEFAULT now(),

  CONSTRAINT media_assets_kind_check CHECK (kind IN (
    'original', 'redaction_unredacted_original', 'poster', 'thumbnail', 'export', 'brand_logo',
    'screenshot_original', 'screenshot_edited'
  )),
  CONSTRAINT media_assets_restricted_consistency CHECK (
    (kind IN ('original', 'redaction_unredacted_original', 'screenshot_original') AND restricted = true) OR
    (kind NOT IN ('original', 'redaction_unredacted_original', 'screenshot_original') AND restricted = false)
  ),
  CONSTRAINT media_assets_bucket_consistency CHECK (
    (restricted = true AND storage_bucket = 'reelay-media-restricted') OR
    (restricted = false AND storage_bucket = 'reelay-media-delivery')
  )
);
CREATE UNIQUE INDEX media_assets_storage_key_unique ON media_assets (storage_bucket, storage_key);
CREATE INDEX media_assets_workspace_kind_idx ON media_assets (workspace_id, kind);
-- serves: storage-quota rollups by kind (Section 21) and the purge worker's sweep by kind

kind resolution and bucket placement — every value, unambiguously (Section 22.2.2 owns the security property; this table is the schema that enforces it):

The two-bucket model is physical, not a prefix convention: reelay-media-restricted and reelay-media-delivery are separate buckets with separate IAM policies (Section 22.2.2), so a misconfigured bucket policy on one can never expose the other. The application layer never computes a bucket name from a template string at write time — storage_bucket is always set from the STORAGE_BUCKET_RESTRICTED / STORAGE_BUCKET_DELIVERY environment variables (Section 4's STORAGE_* vendor-swappable naming convention) at the moment a media_assets row is created, and media_assets_bucket_consistency is the database-level backstop that a row can never claim restricted = true while pointing at the delivery bucket or vice versa.

media_assets and renditions are the only tables with NO deleted_at column at all — per Section 5.3, media hard-deletes only, so a soft-delete column would be a footgun (code could set it and believe the bytes were retained when they were not, or the purge worker could be double-guarded against a column that should never be checked). Its absence is deliberate.

CREATE TABLE renditions (
  id             uuid PRIMARY KEY,
  media_asset_id uuid NOT NULL REFERENCES media_assets(id) ON DELETE CASCADE,
  video_id       uuid NOT NULL REFERENCES videos(id) ON DELETE CASCADE,
  rendition_type text NOT NULL,              -- 'hls_abr' | 'mp4_1080p' | 'mp4_4k' | 'gif' | 'webm'
  mux_playback_id text NULL,
  resolution     text NULL,                  -- e.g. '1920x1080'
  bitrate_bps    integer NULL,
  edl_id         uuid NULL REFERENCES edit_decision_lists(id) ON DELETE SET NULL,
  -- which EDL version this rendition was rendered from; NULL = rendered from source with no edits
  render_status  text NOT NULL DEFAULT 'pending',  -- 'pending' | 'rendering' | 'ready' | 'failed'
  redaction_verified boolean NOT NULL DEFAULT false,
  -- true only once the post-render verification pass (Section 22.2.4) has confirmed every
  -- redaction_regions row covering this rendition's EDL version is actually blurred in the
  -- produced output. A rendition with one or more relevant redaction regions can never reach
  -- render_status = 'ready' while this is false (Section 22.2.4's fail-closed gate).
  servable       boolean NOT NULL DEFAULT false,
  -- the actual "safe to serve" flag checked at playback-resolution time — distinct from
  -- render_status because a previously-ready, previously-servable rendition can be flipped back
  -- to servable = false without changing its render_status: this is what happens when a NEW
  -- redaction region is added to a video that already has active share links (Section 22.2.5's
  -- immediate-unpublish rule) — the rendition stays render_status = 'ready' (it did complete) but
  -- servable flips false until a fresh redaction_verified = true rendition replaces it.
  failure_reason text NULL,
  -- set when render_status = 'failed'; e.g. 'redaction_verification_failed',
  -- 'transcode_error', 'source_unreadable'. NULL whenever render_status != 'failed'.
  created_at     timestamptz NOT NULL DEFAULT now(),

  CONSTRAINT renditions_type_check CHECK (rendition_type IN ('hls_abr', 'mp4_1080p', 'mp4_4k', 'gif', 'webm')),
  CONSTRAINT renditions_status_check CHECK (render_status IN ('pending', 'rendering', 'ready', 'failed')),
  CONSTRAINT renditions_failure_reason_consistency CHECK (
    (render_status = 'failed' AND failure_reason IS NOT NULL) OR
    (render_status != 'failed' AND failure_reason IS NULL)
  ),
  CONSTRAINT renditions_servable_requires_ready CHECK (
    servable = false OR render_status = 'ready'
  )
);
CREATE INDEX renditions_video_id_idx ON renditions (video_id, rendition_type);
-- serves: "get the ready HLS rendition for this video" (player load path, hot)
CREATE INDEX renditions_pending_idx ON renditions (render_status) WHERE render_status IN ('pending', 'rendering');
-- serves: worker queue reconciliation / stuck-render detection
CREATE INDEX renditions_servable_idx ON renditions (video_id) WHERE servable = true;
-- serves: the playback-resolution hot path — "the current servable rendition(s) for this video"

5.4.5 Edit Decision Lists & Auto-Edit Presets #

The EDL storage decision — JSONB column on a dedicated edit_decision_lists table, not a normalized set of operation tables — is deliberate:

• The EDL is read and written as a whole document by the timeline editor (Section 11) and by the render worker (Section 9). There is no query pattern that needs to filter or aggregate across individual EDL operations at the SQL level — every consumer wants "the full ordered operation list for this EDL version," which a normalized edl_operations table would only reconstruct via an ORDER BY sequence query that JSONB gives for free.
• Versioning (below) means EDLs are effectively immutable snapshots once created; a document column matches that write-once-read-many access pattern far better than a mutable row-per-op table that would need careful transaction boundaries to keep "the current state of an EDL" atomic across many rows.
• JSONB supports GIN indexing if a future need arises to search within EDL contents (e.g. "find videos with a redaction operation"), without a schema migration.

edit_decision_lists owns where the EDL lives and how it is versioned; the JSON Schema of the document itself (operation types, time-range fields, revert semantics) is defined in Section 11.

CREATE TABLE edit_decision_lists (
  id                uuid PRIMARY KEY,
  workspace_id      uuid NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
  video_id          uuid NOT NULL REFERENCES videos(id) ON DELETE CASCADE,
  version           integer NOT NULL,
  schema_version    text NOT NULL,             -- EDL JSON Schema version, e.g. 'edl.v1' (Section 11)
  document          jsonb NOT NULL,
  generated_by      text NOT NULL,              -- 'auto_edit' | 'manual' | 'system_restore'
  auto_edit_preset_version integer NULL,        -- set when generated_by = 'auto_edit'
  created_by        uuid NULL REFERENCES users(id) ON DELETE SET NULL,
  created_at        timestamptz NOT NULL DEFAULT now(),

  CONSTRAINT edl_generated_by_check CHECK (generated_by IN ('auto_edit', 'manual', 'system_restore')),
  CONSTRAINT edl_version_positive CHECK (version > 0)
);
CREATE UNIQUE INDEX edl_video_version_unique ON edit_decision_lists (video_id, version);
CREATE INDEX edl_video_id_idx ON edit_decision_lists (video_id, version DESC);
-- serves: "load the latest EDL for this video" and full edit-history listing
CREATE INDEX edl_document_gin_idx ON edit_decision_lists USING gin (document jsonb_path_ops);
-- supports future containment queries over EDL content without a migration

Every edit — including a single filler-word removal or a manual trim — creates a new edit_decision_lists row with version incremented, rather than mutating document in place. videos.active_edl_id always points at the current version; prior versions are retained indefinitely (they are cheap JSONB rows, not media) and form the complete revert history described in Section 11 ("restore all returns exactly the original timeline" — version 0, conceptually the empty EDL, is implicit: a video with no edit_decision_lists row at all plays the untouched source).

CREATE TABLE auto_edit_presets (
  id           uuid PRIMARY KEY,
  workspace_id uuid NULL REFERENCES workspaces(id) ON DELETE CASCADE,
  -- NULL = system-provided global preset (seeded, see 5.10); non-NULL = workspace-authored custom preset
  name         text NOT NULL,
  version      integer NOT NULL DEFAULT 1,
  is_system    boolean NOT NULL DEFAULT false,
  parameters   jsonb NOT NULL,          -- full zoom/motion/background parameter set, Section 10
  created_by   uuid NULL REFERENCES users(id) ON DELETE SET NULL,
  created_at   timestamptz NOT NULL DEFAULT now(),
  updated_at   timestamptz NOT NULL DEFAULT now(),

  CONSTRAINT auto_edit_presets_system_workspace_consistency CHECK (
    (is_system = true AND workspace_id IS NULL) OR (is_system = false AND workspace_id IS NOT NULL)
  )
);
CREATE INDEX auto_edit_presets_workspace_idx ON auto_edit_presets (workspace_id) WHERE workspace_id IS NOT NULL;
CREATE UNIQUE INDEX auto_edit_presets_system_name_unique ON auto_edit_presets (name) WHERE is_system = true;

Preset versions are immutable: updating a preset's parameters never mutates an existing (id, version) — it inserts a new row with the same name/lineage and version + 1. This is what makes the determinism requirement in Section 10 enforceable: videos.auto_edit_preset_version (an integer snapshot, not a foreign key to a mutable row) pins the exact parameter set used, so re-rendering later with "the same preset version" is guaranteed byte-identical even if the workspace has since edited the preset going forward. The actual immutable parameter row is addressable as auto_edit_presets filtered to (id = preset_id, version = preset_version) — no separate history table is needed because presets themselves are append-only.

5.4.6 Cursor Telemetry #

Cursor telemetry (Section 10) is high-frequency structured data — 120 samples/sec from the desktop app, each with position, screen id, click/drag/scroll/key state — for recordings that can run up to 4 hours. At 120 Hz that is up to ~51.8 million samples for a single long recording. This is never stored as Postgres rows. It is stored as a compressed binary/JSON-lines blob in object storage, with a single lightweight metadata row in Postgres pointing at it:

CREATE TABLE cursor_telemetry_blobs (
  id              uuid PRIMARY KEY,
  workspace_id    uuid NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
  recording_id    uuid NOT NULL REFERENCES recordings(id) ON DELETE CASCADE,
  storage_bucket  text NOT NULL,
  storage_key     text NOT NULL,
  format          text NOT NULL DEFAULT 'ndjson_gzip',  -- newline-delimited JSON, gzip-compressed
  sample_count    bigint NOT NULL,
  sample_rate_hz  smallint NOT NULL,
  duration_ms     integer NOT NULL,
  size_bytes      bigint NOT NULL,
  checksum_sha256 text NOT NULL,
  created_at      timestamptz NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX cursor_telemetry_blobs_recording_unique ON cursor_telemetry_blobs (recording_id);
CREATE UNIQUE INDEX cursor_telemetry_blobs_storage_key_unique ON cursor_telemetry_blobs (storage_bucket, storage_key);

Rationale, stated explicitly:

1. Row volume. Storing 51.8 million individual sample rows per long recording would make video_view_events (already the largest table, see 5.6) look small by comparison, multiplied across every recording ever made. Postgres is not the right engine for write-once, sequentially- read, never-filtered-by-SQL time series of this volume.
2. Access pattern. The auto-edit engine (Section 10) reads a telemetry blob exactly once, start to finish, to compute the zoom timeline. There is no query that needs "cursor position at timestamp X" via SQL WHERE — it is a sequential scan by a worker process, which object storage with range-read support serves at least as well as Postgres and at a fraction of the storage cost per byte.
3. Cost. Object storage is roughly an order of magnitude cheaper per GB than provisioned Postgres storage/IOPS, and telemetry blobs are large relative to everything else in the schema.
4. Lifecycle. Telemetry blobs are only needed until the auto-edit render that consumes them completes (plus a retention window for re-render-on-preset-upgrade). They are natural candidates for an object-storage lifecycle rule (Section 19), which is trivial to configure on a bucket prefix and awkward to replicate as a Postgres row-expiry job at this volume.

5.4.7 Transcription, Captions, Chapters & AI Metadata #

CREATE TABLE transcripts (
  id           uuid PRIMARY KEY,
  workspace_id uuid NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
  video_id     uuid NOT NULL REFERENCES videos(id) ON DELETE CASCADE,
  language     text NOT NULL DEFAULT 'en',
  status       text NOT NULL DEFAULT 'pending',   -- 'pending' | 'processing' | 'ready' | 'failed'
  provider     text NOT NULL,                     -- transcription vendor identifier
  full_text    text NULL,
  created_at   timestamptz NOT NULL DEFAULT now(),
  updated_at   timestamptz NOT NULL DEFAULT now(),

  CONSTRAINT transcripts_status_check CHECK (status IN ('pending', 'processing', 'ready', 'failed'))
);
CREATE UNIQUE INDEX transcripts_video_language_unique ON transcripts (video_id, language);
CREATE INDEX transcripts_full_text_search_idx ON transcripts USING gin (to_tsvector('english', coalesce(full_text, '')));
-- serves: cross-library transcript search (Section 18)

CREATE TABLE transcript_segments (
  id            uuid PRIMARY KEY,
  transcript_id uuid NOT NULL REFERENCES transcripts(id) ON DELETE CASCADE,
  sequence      integer NOT NULL,
  start_ms      integer NOT NULL,
  end_ms        integer NOT NULL,
  speaker_label text NULL,           -- e.g. 'Speaker 1', diarization best-effort
  text          text NOT NULL,
  confidence    real NULL,
  is_filler     boolean NOT NULL DEFAULT false,   -- flagged filler word/phrase, feeds Section 11 EDL suggestions

  CONSTRAINT transcript_segments_time_order CHECK (end_ms > start_ms)
);
CREATE UNIQUE INDEX transcript_segments_transcript_sequence_unique ON transcript_segments (transcript_id, sequence);
CREATE INDEX transcript_segments_transcript_time_idx ON transcript_segments (transcript_id, start_ms);
-- serves: rendering the transcript panel scrubbed to playback position

CREATE TABLE captions (
  id            uuid PRIMARY KEY,
  video_id      uuid NOT NULL REFERENCES videos(id) ON DELETE CASCADE,
  transcript_id uuid NOT NULL REFERENCES transcripts(id) ON DELETE CASCADE,
  language      text NOT NULL DEFAULT 'en',
  format        text NOT NULL DEFAULT 'vtt',
  storage_key   text NOT NULL,        -- generated VTT file in object storage
  storage_bucket text NOT NULL,
  created_at    timestamptz NOT NULL DEFAULT now(),

  CONSTRAINT captions_format_check CHECK (format IN ('vtt'))
);
CREATE UNIQUE INDEX captions_video_language_unique ON captions (video_id, language);

CREATE TABLE chapters (
  id         uuid PRIMARY KEY,
  video_id   uuid NOT NULL REFERENCES videos(id) ON DELETE CASCADE,
  sequence   integer NOT NULL,
  title      text NOT NULL,
  start_ms   integer NOT NULL,
  source     text NOT NULL DEFAULT 'ai',   -- 'ai' | 'manual'
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),

  CONSTRAINT chapters_source_check CHECK (source IN ('ai', 'manual')),
  CONSTRAINT chapters_title_len CHECK (char_length(title) BETWEEN 1 AND 200)
);
CREATE UNIQUE INDEX chapters_video_sequence_unique ON chapters (video_id, sequence);
CREATE INDEX chapters_video_start_idx ON chapters (video_id, start_ms);

CREATE TABLE ai_metadata_suggestions (
  id                   uuid PRIMARY KEY,
  workspace_id         uuid NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
  video_id             uuid NOT NULL REFERENCES videos(id) ON DELETE CASCADE,
  kind                 text NOT NULL,          -- 'chapters' | 'summary' | 'title' | 'description'
  status               text NOT NULL DEFAULT 'pending',
  -- 'pending' | 'accepted' | 'edited_and_accepted' | 'rejected' | 'superseded' | 'failed'
  content              jsonb NOT NULL,
  -- ChapterSuggestion[] | { summary } | { titles: [string,string,string] } | { description },
  -- shaped per `kind` — the exact contract is owned by Section 12.9.1; this column is immutable
  -- once written (the trigger below rejects any UPDATE that changes it after insert)
  edited_content        jsonb NULL,
  -- populated only when status = 'edited_and_accepted'; same per-kind shape as `content`
  model_provider        text NOT NULL,
  model_name            text NOT NULL,
  prompt_version         text NOT NULL,        -- e.g. 'chapters.v1'
  generation_batch_id    uuid NULL,
  -- links paired title+description generations produced by the same generation call (Section 12.8.2)
  generated_at           timestamptz NOT NULL DEFAULT now(),
  reviewed_at            timestamptz NULL,
  reviewed_by_user_id    uuid NULL REFERENCES users(id) ON DELETE SET NULL,

  CONSTRAINT ai_metadata_suggestions_kind_check
    CHECK (kind IN ('chapters', 'summary', 'title', 'description')),
  CONSTRAINT ai_metadata_suggestions_status_check CHECK (status IN (
    'pending', 'accepted', 'edited_and_accepted', 'rejected', 'superseded', 'failed'
  )),
  -- The human-acceptance invariant (Section 12.9), enforced at the database layer, not just in
  -- the API handler: a suggestion can only be marked accepted/edited_and_accepted alongside a
  -- recorded human reviewer, and edited_content exists if and only if the status says a human
  -- edited it. Both are structurally impossible to violate via a direct SQL write, not merely
  -- discouraged by application code.
  CONSTRAINT ai_metadata_suggestions_reviewed_consistency CHECK (
    (status IN ('accepted', 'edited_and_accepted')
      AND reviewed_at IS NOT NULL AND reviewed_by_user_id IS NOT NULL)
    OR (status NOT IN ('accepted', 'edited_and_accepted'))
  ),
  CONSTRAINT ai_metadata_suggestions_edited_content_consistency CHECK (
    (status = 'edited_and_accepted' AND edited_content IS NOT NULL)
    OR (status != 'edited_and_accepted' AND edited_content IS NULL)
  )
);
CREATE INDEX ai_metadata_suggestions_video_pending_idx ON ai_metadata_suggestions (video_id) WHERE status = 'pending';
CREATE INDEX ai_metadata_suggestions_video_kind_idx ON ai_metadata_suggestions (video_id, kind, generated_at DESC);
-- serves: "the current/most recent suggestion of this kind for this video" and full suggestion history

Enforcing the human-acceptance invariant at the database layer. The two CHECK constraints above are necessary but not sufficient on their own — they constrain what a single row can say about itself, but the invariant that actually matters ("nothing an LLM produces becomes public metadata without a human accepting it") is about the transition between states and about the generation fields never being silently rewritten after the fact. A BEFORE UPDATE trigger closes both gaps:

CREATE OR REPLACE FUNCTION enforce_ai_metadata_suggestion_transition()
RETURNS trigger AS $$
BEGIN
  -- The generation record itself is immutable once written. Only status, edited_content,
  -- reviewed_at, and reviewed_by_user_id may ever change after insert.
  IF NEW.content IS DISTINCT FROM OLD.content
     OR NEW.kind IS DISTINCT FROM OLD.kind
     OR NEW.video_id IS DISTINCT FROM OLD.video_id
     OR NEW.model_provider IS DISTINCT FROM OLD.model_provider
     OR NEW.model_name IS DISTINCT FROM OLD.model_name
     OR NEW.prompt_version IS DISTINCT FROM OLD.prompt_version
     OR NEW.generated_at IS DISTINCT FROM OLD.generated_at THEN
    RAISE EXCEPTION 'ai_metadata_suggestions: generation fields are immutable after insert (id=%)', OLD.id;
  END IF;

  -- Status may only move along the transitions 12.9.2 actually defines. 'pending' is the only
  -- state anything else can be reached from; every other state is terminal for this row (a
  -- regenerate creates a NEW row and marks this one 'superseded', it never reopens a resolved row).
  IF OLD.status <> NEW.status THEN
    IF OLD.status <> 'pending' THEN
      RAISE EXCEPTION 'ai_metadata_suggestions: cannot transition out of terminal status % (id=%)', OLD.status, OLD.id;
    END IF;
    IF NEW.status NOT IN ('accepted', 'edited_and_accepted', 'rejected', 'superseded', 'failed') THEN
      RAISE EXCEPTION 'ai_metadata_suggestions: illegal transition % -> % (id=%)', OLD.status, NEW.status, OLD.id;
    END IF;
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER ai_metadata_suggestions_transition_guard
  BEFORE UPDATE ON ai_metadata_suggestions
  FOR EACH ROW EXECUTE FUNCTION enforce_ai_metadata_suggestion_transition();

The accepted value for each kind lives on its owning entity, never inside the suggestion row itself — this is the other half of the invariant, and it is why content/edited_content alone being present is never sufficient to change what a viewer sees: accepted chapters become rows in the chapters table (5.4.7, source = 'ai_accepted'); accepted title and description are written to videos.title / videos.description; accepted summary is written to videos.summary (5.4.4). The copy from content/edited_content into the owning entity happens exactly once, inside the same transaction as the status → accepted/edited_and_accepted update, in the API handler for PATCH /v1/videos/{videoId}/ai-metadata-suggestions/{id} (Section 12.9.2) — the trigger above guarantees that transaction is the only place status can legally move off pending, but the copy-into-owning-entity step itself is application logic, not a second trigger, because the owning entity differs by kind (a table row for chapters, a column for the rest) in a way that is more maintainable as one handler than as per-kind trigger branches.

5.4.8 Redaction & Screenshots #

CREATE TABLE redaction_regions (
  id           uuid PRIMARY KEY,
  workspace_id uuid NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
  video_id     uuid NOT NULL REFERENCES videos(id) ON DELETE CASCADE,
  edl_id       uuid NOT NULL REFERENCES edit_decision_lists(id) ON DELETE CASCADE,
  region_type  text NOT NULL,             -- 'static' | 'tracked'
  status       text NOT NULL DEFAULT 'pending',   -- 'pending' | 'active'
  start_ms     integer NOT NULL,
  end_ms       integer NOT NULL,
  geometry     jsonb NOT NULL,            -- static rect {x,y,w,h} OR keyframe track array (Section 11)
  blur_strength smallint NOT NULL DEFAULT 25,
  created_by   uuid NULL REFERENCES users(id) ON DELETE SET NULL,
  created_at   timestamptz NOT NULL DEFAULT now(),

  CONSTRAINT redaction_regions_type_check CHECK (region_type IN ('static', 'tracked')),
  CONSTRAINT redaction_regions_status_check CHECK (status IN ('pending', 'active')),
  CONSTRAINT redaction_regions_time_order CHECK (end_ms > start_ms),
  CONSTRAINT redaction_regions_blur_range CHECK (blur_strength BETWEEN 1 AND 100)
);
CREATE INDEX redaction_regions_video_id_idx ON redaction_regions (video_id);
CREATE INDEX redaction_regions_edl_id_idx ON redaction_regions (edl_id);
-- serves: render worker loading all regions to burn in for a given EDL version
CREATE INDEX redaction_regions_status_idx ON redaction_regions (video_id, status);
-- serves: computing videos.share_gate_status ("any row not yet reflected in a verified rendition")

status tracks a region independently of any single rendition's redaction_verified flag (5.4.4): a region is pending from creation until the post-render verification pass (Section 22.2.4) confirms it is actually burned in on a produced rendition, and flips to active once confirmed. videos.share_gate_status is computed from the presence of any pending region combined with the EDL-version alignment check described in 22.2.5 — this column is what makes that computation a plain indexed query rather than a re-derivation from renditions on every request.

Redaction rows live under edit_decision_lists (an edl_id FK, ON DELETE CASCADE) because a redaction region is itself an EDL operation type conceptually — its geometry is duplicated into the EDL document JSONB at render time for the render worker's single source of truth, but is also kept as first-class relational rows here so the dashboard's redaction-management UI can list, filter, and paginate regions without parsing JSONB. Section 11 owns the exact reconciliation rule between this table and the EDL document; Section 22 owns the security properties (burn-in enforcement, restricted-bucket storage of the unredacted original via media_assets.restricted).

CREATE TABLE screenshots (
  id             uuid PRIMARY KEY,
  workspace_id   uuid NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
  video_id       uuid NULL REFERENCES videos(id) ON DELETE SET NULL,
  -- NULL when captured standalone (not extracted from a recording), Section 13
  created_by     uuid NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
  media_asset_id uuid NOT NULL REFERENCES media_assets(id) ON DELETE CASCADE,
  source_frame_ms integer NULL,          -- playback position if extracted from a video
  beautify_preset text NULL,             -- background/frame preset applied, Section 13
  created_at     timestamptz NOT NULL DEFAULT now(),
  deleted_at     timestamptz NULL
);
CREATE INDEX screenshots_workspace_idx ON screenshots (workspace_id, created_at DESC) WHERE deleted_at IS NULL;
CREATE INDEX screenshots_video_id_idx ON screenshots (video_id) WHERE video_id IS NOT NULL;

Note: screenshots is not in the canonical soft-delete list in Section 7.6/5.3 (which names videos, workspaces, users, comments); it is treated the same as media for lifecycle purposes except that the small preview row itself is retained briefly with deleted_at set to support an "undo" affordance in the UI for a short window (Section 19 specifies the exact undo window), after which a hard-delete sweep removes both the row and its media_assets bytes. This is a UX convenience layered on top of the hard-delete-for-media rule, not an exception to it — the underlying media_assets row for the image is what actually carries the bytes, and it is purged on the same schedule as any other media asset once the undo window closes.

5.4.9 Sharing & Access Control #

CREATE TABLE share_links (
  id                  uuid PRIMARY KEY,
  workspace_id        uuid NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
  video_id            uuid NOT NULL REFERENCES videos(id) ON DELETE CASCADE,
  slug                varchar(48) NOT NULL,
  is_custom_slug      boolean NOT NULL DEFAULT false,
  -- true when a Business-plan workspace supplied its own vanity slug (4-48 chars) rather than
  -- accepting the server-generated 12-char base58 random slug; does not change how playback
  -- tokens are issued/verified, purely a display/branding distinction.
  created_by          uuid NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
  visibility          text NOT NULL DEFAULT 'link',
  password_hash       text NULL,
  expires_at          timestamptz NULL,
  domain_allowlist    text[] NULL,
  disable_download    boolean NOT NULL DEFAULT false,
  disable_comments    boolean NOT NULL DEFAULT false,
  require_email       boolean NOT NULL DEFAULT false,
  playback_key_version integer NOT NULL DEFAULT 1,
  -- incremented on revoke or a visibility downgrade to link/public -> private/workspace
  -- (Section 14.8.4); every signed playback URL and poster/thumbnail key embeds the version it
  -- was minted against, so rotating this integer invalidates every previously issued signed URL
  -- and cached poster/thumbnail for this link in one write, without walking and revoking them
  -- individually.
  view_count          bigint NOT NULL DEFAULT 0,     -- cached, reconciled by rollup job
  created_at          timestamptz NOT NULL DEFAULT now(),
  updated_at          timestamptz NOT NULL DEFAULT now(),
  revoked_at          timestamptz NULL,
  -- THE sole revocation column for a share link — a link is either live (revoked_at IS NULL) or
  -- revoked (revoked_at set). There is no separate `deleted_at`/`disabled_at`; a share link is
  -- never hard-deleted while its video exists (it cascades only with the video, 5.4.4), and
  -- "disabled" and "deleted" are the same state as "revoked" from every consumer's perspective.

  CONSTRAINT share_links_visibility_check CHECK (visibility IN ('private', 'workspace', 'link', 'public')),
  CONSTRAINT share_links_slug_len CHECK (char_length(slug) BETWEEN 4 AND 48)
);
CREATE UNIQUE INDEX share_links_slug_unique ON share_links (slug);
-- global uniqueness (not per-workspace): slugs are looked up with no workspace context on the watch page
CREATE INDEX share_links_video_id_idx ON share_links (video_id) WHERE revoked_at IS NULL;
CREATE INDEX share_links_workspace_idx ON share_links (workspace_id, created_at DESC);

CREATE TABLE share_link_recipients (
  id             uuid PRIMARY KEY,
  share_link_id  uuid NOT NULL REFERENCES share_links(id) ON DELETE CASCADE,
  email          citext NOT NULL,
  recipient_token text NOT NULL,        -- embedded in personalized URL, Section 14
  first_viewed_at timestamptz NULL,
  created_at     timestamptz NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX share_link_recipients_token_unique ON share_link_recipients (recipient_token);
CREATE INDEX share_link_recipients_link_idx ON share_link_recipients (share_link_id);

CREATE TABLE share_audit_events (
  id            uuid PRIMARY KEY,
  workspace_id  uuid NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
  share_link_id uuid NOT NULL REFERENCES share_links(id) ON DELETE CASCADE,
  actor_id      uuid NULL REFERENCES users(id) ON DELETE SET NULL,
  action        text NOT NULL,          -- 'created' | 'visibility_changed' | 'password_set' | ... (Section 14)
  before_state  jsonb NULL,
  after_state   jsonb NOT NULL,
  ip_address    inet NULL,
  created_at    timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX share_audit_events_link_idx ON share_audit_events (share_link_id, created_at DESC);
CREATE INDEX share_audit_events_workspace_idx ON share_audit_events (workspace_id, created_at DESC);
-- serves: the workspace-level audit log screen (Section 6.8: admin/owner-only access)

share_audit_events hard-deletes only via the workspace's own cascade at final purge; it is never individually deleted, since it is the record of exactly the failure mode ("a video made public that was meant to be internal") this spec calls out as the named risk to guard against — mutating or pruning it would defeat its purpose.

5.4.10 Analytics: Partitioned Events & Rollups #

video_view_events is the highest-volume table in the system (heartbeats every 5 seconds of playback across every view, per Section 16) and is monthly-partitioned on received_at (the server-insert clock, not the client-reported occurred_at — see the rationale below the DDL) from day one.

CREATE TABLE video_view_events (
  id            uuid NOT NULL,
  event_id      text NOT NULL,
  -- the client-generated idempotency key (`evt_` + 22-char base58, Section 16.1) — distinct from
  -- `id`, which is the row's own internally minted UUIDv7. The client mints event_id, not the
  -- server, specifically so retried/duplicate delivery of the SAME logical event (a sendBeacon
  -- retry after a flaky network) is detectable at write time via the unique index below.
  workspace_id  uuid NOT NULL,
  video_id      uuid NOT NULL,
  share_link_id uuid NULL,
  viewer_id     uuid NULL,               -- FK enforced via trigger, not a declarative FK (see below)
  viewer_token  text NULL,               -- the raw anonymous vwr_ token carried on the request, always present pre-identification
  session_id    uuid NOT NULL,           -- generated once per player mount, stable for that playback session
  event_type    text NOT NULL,
  -- 'view_start' | 'heartbeat' | 'play' | 'pause' | 'seek' | 'quality_change' | 'complete' |
  -- 'cta_click' | 'email_submit' | 'reaction' | 'comment_open' | 'transcript_open' | 'download' |
  -- 'share' — the complete 14-value catalogue, Section 16.1
  position_ms   integer NULL,
  payload       jsonb NOT NULL DEFAULT '{}',
  is_bot        boolean NOT NULL DEFAULT false,
  is_prefetched boolean NOT NULL DEFAULT false,
  -- both set from request-time signals (Sec-Purpose/Purpose: prefetch header, maintained bot
  -- signature list, Section 16.2) — accepted and flagged, never hard-rejected, so rollups can
  -- exclude them without the collection endpoint becoming a bot-detection oracle an attacker can
  -- probe against
  country       text NULL,               -- ISO 3166-1 alpha-2, derived server-side from request IP; the IP itself is never stored here
  occurred_at   timestamptz NOT NULL,     -- client clock, clamped to received_at if skewed > 24h (Section 16.4.2)
  received_at   timestamptz NOT NULL DEFAULT now(),  -- server clock; authoritative for partition routing

  CONSTRAINT video_view_events_event_type_check CHECK (event_type IN (
    'view_start', 'heartbeat', 'play', 'pause', 'seek', 'quality_change', 'complete',
    'cta_click', 'email_submit', 'reaction', 'comment_open', 'transcript_open', 'download', 'share'
  )),
  PRIMARY KEY (id, received_at)
) PARTITION BY RANGE (received_at);

CREATE INDEX video_view_events_workspace_video_idx ON video_view_events (workspace_id, video_id, occurred_at);
-- serves: the hourly rollup job's per-video scan window
CREATE INDEX video_view_events_share_link_idx ON video_view_events (share_link_id, occurred_at) WHERE share_link_id IS NOT NULL;
-- serves: per-link analytics breakdown
CREATE UNIQUE INDEX video_view_events_video_event_unique ON video_view_events (video_id, event_id, received_at);
-- dedup constraint for retried delivery of the same client-minted event_id, Section 16.2/16.10

video_view_events deliberately has no declarative foreign keys to workspaces, videos, or viewers. The ingestion endpoint (POST /v1/collect, Section 7 and 16) is public and unauthenticated by design — a malformed or replayed video_id must never be able to throw a constraint-violation error back to an anonymous viewer's browser, and FK constraint checks on a table taking this much insert volume would add lock contention against the parent tables' own hot paths. Referential integrity is instead enforced at the application layer: the ingestion handler validates video_id and workspace_id against a short-TTL Redis-cached lookup (populated from Postgres) before accepting an event, and rejects unknown IDs with a 202 Accepted-but-discarded response (never a 4xx that would let an attacker probe for valid video IDs by observing status code differences — this mirrors the enumeration-resistance principle in Section 6.1).

The primary key is composite (id, received_at), and the table is partitioned on received_at rather than occurred_at — Postgres requires the partition key to be part of any unique constraint including the primary key on a partitioned table, and the partition key is deliberately the server-clock column, not the client-clock one: occurred_at is attacker/clock-skew influenced (a client can report any timestamp it likes), so routing partition placement off it would let a malicious or misconfigured client target an already-detached, already-dropped partition, or scatter a single burst of real-time traffic across many old partitions. received_at is set server-side at insert time and is monotonic with actual write order, which is what "always insert into the partition matching the current month" (Section 16.4.2) requires. Rollups still bucket by the (clamped) occurred_at, since that is the analytically meaningful timestamp — only partition placement uses received_at.

The dedup unique index (video_id, event_id, received_at) necessarily includes the partition key (received_at) alongside the logical dedup key (video_id, event_id), because Postgres requires every unique index on a partitioned table to include the partition key — this is a structural Postgres limitation, not a design choice, and it means uniqueness is only truly enforced for retries landing in the same partition-month as the original. In practice this is not a gap: a sendBeacon retry happens within seconds of the original send (Section 16.2's flush triggers are all sub-5-second), so a retry crossing a month boundary is not a real-world case, and Section 16.4.2's late-event handling already treats anything arriving after its own analytical window as a documented accuracy edge case rather than a correctness bug.

Partition DDL (one partition per calendar month, created ahead of need):

CREATE TABLE video_view_events_2026_08 PARTITION OF video_view_events
  FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');

CREATE TABLE video_view_events_2026_09 PARTITION OF video_view_events
  FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');

Automatic partition creation job: a BullMQ scheduled job (analytics.partition.ensure, cron 0 0 25 * * — the 25th of each month, giving a 5-6 day buffer before month-end) runs the following idempotent procedure, which creates the next 3 months of partitions if they do not already exist (a buffer beyond just "next month," so a missed run does not cause an ingestion failure):

CREATE OR REPLACE FUNCTION ensure_video_view_events_partition(target_month date)
RETURNS void AS $$
DECLARE
  partition_name text := 'video_view_events_' || to_char(target_month, 'YYYY_MM');
  start_date date := date_trunc('month', target_month);
  end_date date := start_date + interval '1 month';
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_class WHERE relname = partition_name
  ) THEN
    EXECUTE format(
      'CREATE TABLE %I PARTITION OF video_view_events FOR VALUES FROM (%L) TO (%L)',
      partition_name, start_date, end_date
    );
    EXECUTE format(
      'CREATE INDEX %I ON %I (workspace_id, video_id, occurred_at)',
      partition_name || '_workspace_video_idx', partition_name
    );
  END IF;
END;
$$ LANGUAGE plpgsql;

The job calls ensure_video_view_events_partition for now(), now() + 1 month, and now() + 2 months on every run. Job idempotency key: analytics:partition-ensure:<YYYY-MM> per the queue conventions in Section 9/24.

Detach and drop retention behaviour: raw events are retained for 13 months (one month beyond the 12-month rollup window the product exposes in the dashboard, so month-boundary queries never show a gap). A second scheduled job (analytics.partition.retire, monthly) runs:

-- Step 1: detach concurrently (non-blocking, Postgres 14+ feature) once a partition
-- is fully outside the retention window.
ALTER TABLE video_view_events DETACH PARTITION video_view_events_2025_06 CONCURRENTLY;

-- Step 2 (separate transaction, after detach completes): drop the now-standalone table.
DROP TABLE video_view_events_2025_06;

Detach is run as its own job invocation, and the drop is deferred to a subsequent job run at least 24 hours later, never in the same transaction — this gives a manual-intervention window (an operator can re-attach a detached-but-not-yet-dropped partition if a retention-window miscalculation is caught in time) before data is irrecoverably dropped. Both steps are logged to jobs_audit (Section 24) with the partition name and row-count-at-detach.

Rollup tables — derived from video_view_events, never written to directly by the ingestion path:

CREATE TABLE video_view_daily (
  id             uuid PRIMARY KEY,
  workspace_id   uuid NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
  video_id       uuid NOT NULL REFERENCES videos(id) ON DELETE CASCADE,
  day            date NOT NULL,
  view_count     integer NOT NULL DEFAULT 0,
  unique_viewer_count integer NOT NULL DEFAULT 0,
  avg_watch_ms   integer NOT NULL DEFAULT 0,
  completion_rate real NOT NULL DEFAULT 0,     -- 0..1
  cta_click_count integer NOT NULL DEFAULT 0,
  email_capture_count integer NOT NULL DEFAULT 0,
  computed_at    timestamptz NOT NULL DEFAULT now(),

  CONSTRAINT video_view_daily_completion_rate_range CHECK (completion_rate BETWEEN 0 AND 1)
);
CREATE UNIQUE INDEX video_view_daily_video_day_unique ON video_view_daily (video_id, day);
CREATE INDEX video_view_daily_workspace_day_idx ON video_view_daily (workspace_id, day DESC);
-- serves: workspace-level analytics dashboard time range queries

CREATE TABLE video_engagement_curve (
  id           uuid PRIMARY KEY,
  workspace_id uuid NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
  video_id     uuid NOT NULL REFERENCES videos(id) ON DELETE CASCADE,
  bucket_index integer NOT NULL,          -- 0-based, up to 1799 (1800-bucket cap, Section 16)
  bucket_start_ms integer NOT NULL,
  viewers_remaining integer NOT NULL DEFAULT 0,
  viewers_total integer NOT NULL DEFAULT 0,
  computed_at  timestamptz NOT NULL DEFAULT now(),

  CONSTRAINT video_engagement_curve_bucket_range CHECK (bucket_index BETWEEN 0 AND 1799)
);
CREATE UNIQUE INDEX video_engagement_curve_video_bucket_unique ON video_engagement_curve (video_id, bucket_index);
-- serves: rendering the drop-off curve chart for a single video (full-table read per video, capped at 1800 rows)

Both rollup tables are fully recomputed-and-replaced (DELETE for the video/day or video, then bulk INSERT) by their respective jobs (analytics.rollup.daily hourly, analytics.rollup.curve hourly) rather than incrementally updated — recomputation from the source partition is cheap relative to the correctness risk of incremental aggregate drift, and both jobs are naturally idempotent this way (safe to re-run after a failure with no double-counting).

CREATE TABLE viewers (
  id               uuid PRIMARY KEY,
  workspace_id     uuid NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
  viewer_token     text NOT NULL,          -- anonymous rotating token, 30-day, Section 16
  email            citext NULL,            -- set once identified
  user_id          uuid NULL REFERENCES users(id) ON DELETE SET NULL,  -- set if an authenticated workspace user
  first_seen_at    timestamptz NOT NULL DEFAULT now(),
  last_seen_at     timestamptz NOT NULL DEFAULT now(),
  identified_at    timestamptz NULL
);
CREATE UNIQUE INDEX viewers_token_unique ON viewers (viewer_token);
CREATE INDEX viewers_workspace_email_idx ON viewers (workspace_id, email) WHERE email IS NOT NULL;
-- serves: per-viewer analytics lookup by email (Pro/Business "full + per-viewer" tier, Section 21)

5.4.11 Engagement: Comments, Reactions, CTAs #

CREATE TABLE comments (
  id            uuid PRIMARY KEY,
  workspace_id  uuid NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
  video_id      uuid NOT NULL REFERENCES videos(id) ON DELETE CASCADE,
  parent_id     uuid NULL REFERENCES comments(id) ON DELETE CASCADE,
  author_user_id uuid NULL REFERENCES users(id) ON DELETE SET NULL,
  author_viewer_id uuid NULL REFERENCES viewers(id) ON DELETE SET NULL,
  timestamp_ms  integer NULL,             -- optional: comment pinned to a playback position
  body          text NOT NULL,
  created_at    timestamptz NOT NULL DEFAULT now(),
  updated_at    timestamptz NOT NULL DEFAULT now(),
  deleted_at    timestamptz NULL,

  CONSTRAINT comments_body_len CHECK (char_length(body) BETWEEN 1 AND 5000),
  CONSTRAINT comments_author_exactly_one CHECK (
    (author_user_id IS NOT NULL AND author_viewer_id IS NULL) OR
    (author_user_id IS NULL AND author_viewer_id IS NOT NULL)
  )
);
CREATE INDEX comments_video_idx ON comments (video_id, created_at) WHERE deleted_at IS NULL;
CREATE INDEX comments_parent_idx ON comments (parent_id) WHERE parent_id IS NOT NULL AND deleted_at IS NULL;

CREATE TABLE comment_reactions (
  id         uuid PRIMARY KEY,
  comment_id uuid NOT NULL REFERENCES comments(id) ON DELETE CASCADE,
  reactor_user_id uuid NULL REFERENCES users(id) ON DELETE CASCADE,
  reactor_viewer_id uuid NULL REFERENCES viewers(id) ON DELETE CASCADE,
  emoji      text NOT NULL,

  CONSTRAINT comment_reactions_reactor_exactly_one CHECK (
    (reactor_user_id IS NOT NULL AND reactor_viewer_id IS NULL) OR
    (reactor_user_id IS NULL AND reactor_viewer_id IS NOT NULL)
  )
);
CREATE UNIQUE INDEX comment_reactions_comment_user_unique
  ON comment_reactions (comment_id, reactor_user_id) WHERE reactor_user_id IS NOT NULL;
CREATE UNIQUE INDEX comment_reactions_comment_viewer_unique
  ON comment_reactions (comment_id, reactor_viewer_id) WHERE reactor_viewer_id IS NOT NULL;

CREATE TABLE ctas (
  id           uuid PRIMARY KEY,
  workspace_id uuid NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
  video_id     uuid NOT NULL REFERENCES videos(id) ON DELETE CASCADE,
  trigger_type text NOT NULL,             -- 'time' | 'end_of_video' | 'pause'
  trigger_ms   integer NULL,              -- required when trigger_type = 'time'
  style        text NOT NULL DEFAULT 'banner',   -- 'banner' | 'modal' | 'fullscreen'
  headline     text NOT NULL,
  button_label text NOT NULL,
  button_url   text NOT NULL,
  created_at   timestamptz NOT NULL DEFAULT now(),
  updated_at   timestamptz NOT NULL DEFAULT now(),

  CONSTRAINT ctas_trigger_type_check CHECK (trigger_type IN ('time', 'end_of_video', 'pause')),
  CONSTRAINT ctas_trigger_ms_required CHECK (
    (trigger_type = 'time' AND trigger_ms IS NOT NULL) OR (trigger_type != 'time')
  ),
  CONSTRAINT ctas_style_check CHECK (style IN ('banner', 'modal', 'fullscreen'))
);
CREATE INDEX ctas_video_idx ON ctas (video_id);

CREATE TABLE cta_events (
  id         uuid PRIMARY KEY,
  cta_id     uuid NOT NULL REFERENCES ctas(id) ON DELETE CASCADE,
  viewer_id  uuid NULL REFERENCES viewers(id) ON DELETE SET NULL,
  event_type text NOT NULL,              -- 'shown' | 'clicked' | 'dismissed'
  occurred_at timestamptz NOT NULL DEFAULT now(),

  CONSTRAINT cta_events_type_check CHECK (event_type IN ('shown', 'clicked', 'dismissed'))
);
CREATE INDEX cta_events_cta_idx ON cta_events (cta_id, occurred_at DESC);

CREATE TABLE email_captures (
  id           uuid PRIMARY KEY,
  workspace_id uuid NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
  video_id     uuid NOT NULL REFERENCES videos(id) ON DELETE CASCADE,
  share_link_id uuid NULL REFERENCES share_links(id) ON DELETE SET NULL,
  viewer_id    uuid NULL REFERENCES viewers(id) ON DELETE SET NULL,
  email        citext NOT NULL,
  captured_at  timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX email_captures_workspace_video_idx ON email_captures (workspace_id, video_id, captured_at DESC);

cta_events is high-volume but not partitioned like video_view_events: CTA impressions are orders of magnitude less frequent than 5-second heartbeats, and its growth profile at 100k workspaces (modeled in 5.12) stays within a single-table B-tree's comfortable range for the product's data lifetime under the retention rules in Section 19.

5.4.12 Library & Feature Extras #

Folders (5.4.3) and brand kits (5.4.2) cover most of "Video Library, Folders, Collections & Brand Kit" (Section 18); "Collections" in that section's title refers to saved filtered views composed from existing folders/tag metadata at the application layer — Section 18 owns that UI model and introduces no additional table here.

5.4.13 Billing #

CREATE TABLE plans (
  id              uuid PRIMARY KEY,
  code            text NOT NULL,             -- 'free' | 'pro' | 'business'
  name            text NOT NULL,
  stripe_price_id_monthly text NULL,
  stripe_price_id_yearly  text NULL,
  limits          jsonb NOT NULL,            -- the full limit table from Section 21, machine-readable
  is_active       boolean NOT NULL DEFAULT true,
  created_at      timestamptz NOT NULL DEFAULT now(),

  CONSTRAINT plans_code_check CHECK (code IN ('free', 'pro', 'business'))
);
CREATE UNIQUE INDEX plans_code_unique ON plans (code);

CREATE TABLE subscriptions (
  id                     uuid PRIMARY KEY,
  workspace_id           uuid NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
  plan_id                uuid NOT NULL REFERENCES plans(id) ON DELETE RESTRICT,
  stripe_customer_id     text NULL,
  stripe_subscription_id text NULL,
  status                 text NOT NULL DEFAULT 'active',
  -- 'active' | 'trialing' | 'past_due' | 'canceled' | 'incomplete'
  seat_count             integer NOT NULL DEFAULT 1,
  current_period_start   timestamptz NULL,
  current_period_end     timestamptz NULL,
  cancel_at_period_end   boolean NOT NULL DEFAULT false,
  created_at             timestamptz NOT NULL DEFAULT now(),
  updated_at             timestamptz NOT NULL DEFAULT now(),

  CONSTRAINT subscriptions_status_check CHECK (
    status IN ('active', 'trialing', 'past_due', 'canceled', 'incomplete')
  ),
  CONSTRAINT subscriptions_seat_count_positive CHECK (seat_count > 0)
);
CREATE UNIQUE INDEX subscriptions_workspace_unique ON subscriptions (workspace_id);
CREATE UNIQUE INDEX subscriptions_stripe_subscription_unique ON subscriptions (stripe_subscription_id)
  WHERE stripe_subscription_id IS NOT NULL;

CREATE TABLE usage_counters (
  workspace_id   uuid NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
  counter_type   text NOT NULL,
  -- 'storage_bytes' | 'video_count' | 'seats' | 'ai_minutes' | 'export_count' | 'api_calls' —
  -- the 6-value set consumed by Section 21.4's usage-metering table
  period_start   date NOT NULL,
  -- billing-period-aligned for period-scoped counters (ai_minutes, export_count, api_calls);
  -- all-time counters (storage_bytes, video_count, seats) use the sentinel '1970-01-01'
  value          bigint NOT NULL DEFAULT 0,
  updated_at     timestamptz NOT NULL DEFAULT now(),

  CONSTRAINT usage_counters_type_check CHECK (
    counter_type IN ('storage_bytes', 'video_count', 'seats', 'ai_minutes', 'export_count', 'api_calls')
  ),
  PRIMARY KEY (workspace_id, counter_type, period_start)
);

CREATE TABLE usage_events (
  id           uuid PRIMARY KEY,
  workspace_id uuid NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
  type         text NOT NULL,             -- 'ai_minutes' | 'export' | 'api_call'
  quantity     numeric NOT NULL,          -- minutes for ai_minutes; 1 for export/api_call
  source_id    uuid NULL,                 -- the video id, export job id, or api_keys id that caused this event
  occurred_at  timestamptz NOT NULL DEFAULT now(),

  CONSTRAINT usage_events_type_check CHECK (type IN ('ai_minutes', 'export', 'api_call'))
);
CREATE INDEX usage_events_workspace_type_idx ON usage_events (workspace_id, type, occurred_at);
-- serves: usage_counters reconciliation job (recompute from events if drift is detected, Section 21.4)

CREATE TABLE invoices (
  id                uuid PRIMARY KEY,
  workspace_id      uuid NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
  stripe_invoice_id text NOT NULL,
  status            text NOT NULL,        -- mirrors Stripe invoice status
  amount_due_cents  integer NOT NULL,
  currency          text NOT NULL DEFAULT 'usd',
  period_start      timestamptz NULL,
  period_end        timestamptz NULL,
  hosted_invoice_url text NULL,
  created_at        timestamptz NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX invoices_stripe_invoice_unique ON invoices (stripe_invoice_id);
CREATE INDEX invoices_workspace_idx ON invoices (workspace_id, created_at DESC);

usage_counters is a fast-read cache, keyed by the composite (workspace_id, counter_type, period_start) rather than a synthetic id, because every read and write against it is already addressed by that exact triple (Section 21's enforcement checkpoint looks up "this workspace's current value for this counter," never "counter row #N") — a surrogate key would only add an extra unique index with no query this table needs to serve. usage_events is the append-only ledger usage_counters is derived from and periodically reconciled against; for the two counters that are not additive-event-driven (video_count, which is recomputed from videos directly, and seats, mirrored from Stripe) usage_events is not the source of truth — only ai_minutes, export_count, and api_calls accumulate through usage_events inserts (Section 21.4's counter table states which source query is authoritative for each). Section 21 owns the reconciliation job and enforcement logic that reads these tables, including the nightly drift-correction job and the 1% drift alert threshold.

5.4.14 Public API, Webhooks & Integrations #

CREATE TABLE api_keys (
  id             uuid PRIMARY KEY,
  workspace_id   uuid NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
  created_by     uuid NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
  name           text NOT NULL,
  key_hash       text NOT NULL,           -- SHA-256 of the full secret key
  display_prefix text NOT NULL,           -- first 12 chars, e.g. 'sk_live_9K8B', shown in UI
  scopes         text[] NOT NULL DEFAULT '{}',
  rate_limit_per_minute integer NOT NULL DEFAULT 300,
  last_used_at   timestamptz NULL,
  created_at     timestamptz NOT NULL DEFAULT now(),
  revoked_at     timestamptz NULL,

  CONSTRAINT api_keys_name_len CHECK (char_length(name) BETWEEN 1 AND 100)
);
CREATE UNIQUE INDEX api_keys_key_hash_unique ON api_keys (key_hash);
CREATE INDEX api_keys_workspace_idx ON api_keys (workspace_id) WHERE revoked_at IS NULL;

CREATE TABLE webhook_endpoints (
  id             uuid PRIMARY KEY,
  workspace_id   uuid NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
  url            text NOT NULL,
  secret_enc     bytea NOT NULL,          -- signing secret, encrypted at rest (Section 22)
  event_types    text[] NOT NULL,         -- subscribed event names, Section 20
  is_active      boolean NOT NULL DEFAULT true,
  created_by     uuid NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
  created_at     timestamptz NOT NULL DEFAULT now(),
  updated_at     timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX webhook_endpoints_workspace_idx ON webhook_endpoints (workspace_id) WHERE is_active = true;

CREATE TABLE webhook_deliveries (
  id           uuid PRIMARY KEY,
  endpoint_id  uuid NOT NULL REFERENCES webhook_endpoints(id) ON DELETE CASCADE,
  event_type   text NOT NULL,
  payload      jsonb NOT NULL,
  status       text NOT NULL DEFAULT 'pending',  -- 'pending' | 'delivered' | 'failed' | 'exhausted'
  attempt_count smallint NOT NULL DEFAULT 0,
  last_attempt_at timestamptz NULL,
  last_response_status smallint NULL,
  next_retry_at timestamptz NULL,
  created_at   timestamptz NOT NULL DEFAULT now(),

  CONSTRAINT webhook_deliveries_status_check CHECK (status IN ('pending', 'delivered', 'failed', 'exhausted'))
);
CREATE INDEX webhook_deliveries_endpoint_idx ON webhook_deliveries (endpoint_id, created_at DESC);
CREATE INDEX webhook_deliveries_retry_idx ON webhook_deliveries (next_retry_at) WHERE status = 'pending';

CREATE TABLE integration_connections (
  id             uuid PRIMARY KEY,
  workspace_id   uuid NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
  provider       text NOT NULL,           -- 'slack' | 'notion' | 'hubspot'
  connected_by   uuid NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
  access_token_enc bytea NOT NULL,
  refresh_token_enc bytea NULL,
  external_workspace_id text NULL,        -- provider-side team/workspace id
  config         jsonb NOT NULL DEFAULT '{}',
  status         text NOT NULL DEFAULT 'active',  -- 'active' | 'error' | 'disconnected'
  created_at     timestamptz NOT NULL DEFAULT now(),
  updated_at     timestamptz NOT NULL DEFAULT now(),

  CONSTRAINT integration_connections_provider_check CHECK (provider IN ('slack', 'notion', 'hubspot')),
  CONSTRAINT integration_connections_status_check CHECK (status IN ('active', 'error', 'disconnected'))
);
CREATE UNIQUE INDEX integration_connections_workspace_provider_unique
  ON integration_connections (workspace_id, provider);

5.4.15 System, Audit & Operations #

CREATE TABLE jobs_audit (
  id           uuid PRIMARY KEY,
  job_id       text NOT NULL,             -- BullMQ job.id, e.g. 'video:transcode:3'
  queue_name   text NOT NULL,
  workspace_id uuid NULL,                 -- no FK: must survive workspace hard-delete for audit trail
  status       text NOT NULL,             -- 'started' | 'completed' | 'failed' | 'dlq'
  attempt      smallint NOT NULL DEFAULT 1,
  error_message text NULL,
  started_at   timestamptz NOT NULL DEFAULT now(),
  finished_at  timestamptz NULL,

  CONSTRAINT jobs_audit_status_check CHECK (status IN ('started', 'completed', 'failed', 'dlq'))
);
CREATE INDEX jobs_audit_job_id_idx ON jobs_audit (job_id, started_at DESC);
CREATE INDEX jobs_audit_queue_status_idx ON jobs_audit (queue_name, status, started_at DESC);
-- serves: operational dashboards (Section 24), dead-letter triage

CREATE TABLE audit_events (
  id           uuid PRIMARY KEY,
  workspace_id uuid NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
  actor_id     uuid NULL REFERENCES users(id) ON DELETE SET NULL,
  action       text NOT NULL,             -- e.g. 'member.role_changed', 'brand_kit.updated'
  resource_type text NOT NULL,
  resource_id  uuid NULL,
  before_state jsonb NULL,
  after_state  jsonb NULL,
  ip_address   inet NULL,
  created_at   timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX audit_events_workspace_idx ON audit_events (workspace_id, created_at DESC);
CREATE INDEX audit_events_resource_idx ON audit_events (resource_type, resource_id);

CREATE TABLE retention_policies (
  id           uuid PRIMARY KEY,
  workspace_id uuid NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
  applies_to   text NOT NULL,             -- 'inactive_videos' | 'deleted_workspace' | 'deleted_account'
  retain_days  integer NOT NULL,
  created_at   timestamptz NOT NULL DEFAULT now(),

  CONSTRAINT retention_policies_applies_to_check CHECK (
    applies_to IN ('inactive_videos', 'deleted_workspace', 'deleted_account')
  ),
  CONSTRAINT retention_policies_retain_days_positive CHECK (retain_days > 0)
);
CREATE UNIQUE INDEX retention_policies_workspace_applies_to_unique ON retention_policies (workspace_id, applies_to);

CREATE TABLE deletion_requests (
  id                   uuid PRIMARY KEY,
  workspace_id         uuid NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
  target_type          text NOT NULL,
  -- 'video' | 'workspace' | 'account' | 'gdpr_erasure'
  target_id            uuid NOT NULL,
  -- the id of the video/workspace/user this request targets, per target_type; not a declarative
  -- FK (it points at different tables depending on target_type, and the row must survive even
  -- after its target is gone — this is itself the audit trail of a deletion)
  reason               text NOT NULL,
  -- 'retention_policy' | 'user_trash_purge' | 'workspace_deletion' | 'account_deletion' | 'gdpr_erasure'
  status               text NOT NULL DEFAULT 'pending',
  -- 'pending' | 'notified' | 'scheduled' | 'in_progress' | 'verifying' | 'completed' |
  -- 'verification_failed' | 'cancelled' — the 8-value lifecycle status, Section 19.3.2
  requested_by         uuid NULL REFERENCES users(id) ON DELETE SET NULL,
  -- NULL for system-initiated requests (retention-driven deletion has no human requester)
  scheduled_for        timestamptz NOT NULL,
  notice_30d_sent_at   timestamptz NULL,
  notice_7d_sent_at    timestamptz NULL,
  notice_1d_sent_at    timestamptz NULL,
  purge_progress       integer NOT NULL DEFAULT 0,
  -- index into the ordered ten-stage purge workflow, 0..10 (Section 19.3.2)
  verification_report  jsonb NULL,
  -- the structured per-artifact-category pass/fail result written by the final verify stage (19.3.3)
  started_at           timestamptz NULL,
  completed_at         timestamptz NULL,
  created_at           timestamptz NOT NULL DEFAULT now(),
  updated_at           timestamptz NOT NULL DEFAULT now(),

  CONSTRAINT deletion_requests_target_type_check
    CHECK (target_type IN ('video', 'workspace', 'account', 'gdpr_erasure')),
  CONSTRAINT deletion_requests_reason_check CHECK (reason IN (
    'retention_policy', 'user_trash_purge', 'workspace_deletion', 'account_deletion', 'gdpr_erasure'
  )),
  CONSTRAINT deletion_requests_status_check CHECK (status IN (
    'pending', 'notified', 'scheduled', 'in_progress', 'verifying',
    'completed', 'verification_failed', 'cancelled'
  ))
);
CREATE INDEX deletion_requests_due_idx ON deletion_requests (scheduled_for)
  WHERE status IN ('pending', 'notified', 'scheduled');
-- serves: Section 19's purge-execution sweep
CREATE INDEX deletion_requests_target_idx ON deletion_requests (target_type, target_id);
-- serves: "is there already an open deletion request for this target" idempotency checks

CREATE TABLE custom_domains (
  id             uuid PRIMARY KEY,
  workspace_id   uuid NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
  hostname       text NOT NULL,
  verification_token text NOT NULL,
  status         text NOT NULL DEFAULT 'pending_verification',
  -- 'pending_verification' | 'verified' | 'failed'
  tls_status     text NOT NULL DEFAULT 'pending',   -- 'pending' | 'issued' | 'failed'
  created_at     timestamptz NOT NULL DEFAULT now(),
  verified_at    timestamptz NULL,

  CONSTRAINT custom_domains_status_check CHECK (status IN ('pending_verification', 'verified', 'failed')),
  CONSTRAINT custom_domains_tls_status_check CHECK (tls_status IN ('pending', 'issued', 'failed'))
);
CREATE UNIQUE INDEX custom_domains_hostname_unique ON custom_domains (hostname);

CREATE TABLE feature_flags (
  id           uuid PRIMARY KEY,
  key          text NOT NULL,
  description  text NOT NULL,
  default_enabled boolean NOT NULL DEFAULT false,
  workspace_overrides jsonb NOT NULL DEFAULT '{}',  -- { "<workspace_id>": true|false }
  created_at   timestamptz NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX feature_flags_key_unique ON feature_flags (key);

CREATE TABLE notification_preferences (
  id           uuid PRIMARY KEY,
  user_id      uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  workspace_id uuid NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
  channel      text NOT NULL,             -- 'comment_reply' | 'video_ready' | 'share_activity' | 'billing' | 'mention'
  email_enabled boolean NOT NULL DEFAULT true,
  in_app_enabled boolean NOT NULL DEFAULT true,

  CONSTRAINT notification_preferences_channel_check CHECK (
    channel IN ('comment_reply', 'video_ready', 'share_activity', 'billing', 'mention')
  )
);
CREATE UNIQUE INDEX notification_preferences_user_workspace_channel_unique
  ON notification_preferences (user_id, workspace_id, channel);

CREATE TABLE email_log (
  id           uuid PRIMARY KEY,
  workspace_id uuid NULL REFERENCES workspaces(id) ON DELETE SET NULL,
  user_id      uuid NULL REFERENCES users(id) ON DELETE SET NULL,
  to_email     citext NOT NULL,
  template     text NOT NULL,
  status       text NOT NULL DEFAULT 'sent',   -- 'sent' | 'bounced' | 'failed'
  provider_message_id text NULL,
  sent_at      timestamptz NOT NULL DEFAULT now(),

  CONSTRAINT email_log_status_check CHECK (status IN ('sent', 'bounced', 'failed'))
);
CREATE INDEX email_log_to_email_idx ON email_log (to_email, sent_at DESC);

5.4.16 Upload Sessions (Resumable Multipart Upload State) #

upload_sessions and upload_parts track the client-resumable S3-compatible multipart upload described in Section 9 — a recording chunked client-side, uploaded in 8 MB parts across possibly many app restarts and network outages, is not durable until the server has independently confirmed CompleteMultipartUpload. These are implementation-internal pipeline tables (not part of the core domain-entity list in the front matter), but they are still owned here, not narrated ad hoc in Section 9, because the same rule applies to every table in this document: one owner, no duplicate column lists.

CREATE TABLE upload_sessions (
  id                    uuid PRIMARY KEY,
  -- its OWN identity, minted independently at session creation — deliberately NOT the recording
  -- id it will eventually be attached to. An upload session can exist, accept parts, and even be
  -- abandoned before any recording row is ever created (Section 7.11.9's flow allows
  -- `purpose = "screenshot_original"` sessions with no recording at all), so borrowing the
  -- recording's id would make the FK direction backwards for that case and would also mean the
  -- client has to mint (or be handed) a recording id before it has anything to attach one to.
  -- Public ID prefix `ups_` (5.1.2).
  workspace_id          uuid NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
  recording_id          uuid NULL REFERENCES recordings(id) ON DELETE SET NULL,
  -- set once the client links this session to a recording (Section 7.11.8's finalize call);
  -- NULL for the screenshot-upload purpose, which never has a recording
  purpose               text NOT NULL,
  -- 'recording' | 'screenshot_original' — what the assembled object becomes; drives which
  -- media_assets.kind the finalize step writes (5.4.4)
  media_kind            text NOT NULL,
  -- 'video_screen' | 'video_camera' | 'telemetry' | 'image' — the finer-grained content type used
  -- to pick bucket/content-type validation independent of `purpose`
  s3_upload_id          text NOT NULL,
  bucket_key            text NOT NULL,
  status                text NOT NULL DEFAULT 'open',
  -- 'open' | 'uploading' | 'stalled' | 'assembling' | 'completed' | 'aborted'
  total_parts_expected  integer NULL,             -- NULL until the client's finalize call reports the true count
  parts_completed       integer NOT NULL DEFAULT 0,
  created_at            timestamptz NOT NULL DEFAULT now(),
  updated_at            timestamptz NOT NULL DEFAULT now(),
  completed_at          timestamptz NULL,

  CONSTRAINT upload_sessions_purpose_check CHECK (purpose IN ('recording', 'screenshot_original')),
  CONSTRAINT upload_sessions_media_kind_check
    CHECK (media_kind IN ('video_screen', 'video_camera', 'telemetry', 'image')),
  CONSTRAINT upload_sessions_status_check CHECK (status IN (
    'open', 'uploading', 'stalled', 'assembling', 'completed', 'aborted'
  ))
);
CREATE INDEX upload_sessions_workspace_idx ON upload_sessions (workspace_id, created_at DESC);
CREATE INDEX upload_sessions_recording_idx ON upload_sessions (recording_id) WHERE recording_id IS NOT NULL;
CREATE INDEX upload_sessions_stalled_idx ON upload_sessions (status, updated_at) WHERE status IN ('open', 'uploading', 'stalled');
-- serves: the 7-day idle abort sweep (Section 9.6) and app-relaunch "resume this upload" lookup

CREATE TABLE upload_parts (
  upload_session_id uuid NOT NULL REFERENCES upload_sessions(id) ON DELETE CASCADE,
  part_number        integer NOT NULL,
  etag               text NULL,
  size_bytes         integer NOT NULL,
  status             text NOT NULL DEFAULT 'pending',   -- 'pending' | 'uploaded' | 'failed'
  attempts           integer NOT NULL DEFAULT 0,
  updated_at         timestamptz NOT NULL DEFAULT now(),

  CONSTRAINT upload_parts_status_check CHECK (status IN ('pending', 'uploaded', 'failed')),
  PRIMARY KEY (upload_session_id, part_number)
);

State machine (unchanged from the pipeline behavior in Section 9, restated here as the canonical column-level contract):

Local chunks are deleted client-side only after the server's .../complete response reports status: "completed" — never on a successful-looking part PUT alone — which is what backs the stated product invariant that a recording is never lost to a network failure (Section 9.1/9.6): local data persists through any number of failed attempts or restarts, and is removed only once the server has independently verified durable assembly.

5.5 Entity-Relationship Overview #

erDiagram
  USERS ||--o{ WORKSPACE_MEMBERS : "has"
  WORKSPACES ||--o{ WORKSPACE_MEMBERS : "has"
  WORKSPACES ||--o{ WORKSPACE_INVITES : "issues"
  WORKSPACES ||--|| BRAND_KITS : "has one"
  WORKSPACES ||--o{ FOLDERS : "contains"
  FOLDERS ||--o{ FOLDER_PERMISSIONS : "grants"
  USERS ||--o{ FOLDER_PERMISSIONS : "may be principal of"
  WORKSPACES ||--o{ VIDEOS : "owns"
  FOLDERS ||--o{ VIDEOS : "organizes"
  VIDEOS ||--o{ RECORDINGS : "sourced from"
  RECORDINGS ||--o| CURSOR_TELEMETRY_BLOBS : "captured with"
  RECORDINGS ||--o| UPLOAD_SESSIONS : "assembled from"
  VIDEOS ||--o{ EDIT_DECISION_LISTS : "versioned edits"
  EDIT_DECISION_LISTS ||--o{ REDACTION_REGIONS : "burns in"
  VIDEOS ||--o{ RENDITIONS : "delivered as"
  VIDEOS ||--o{ MEDIA_ASSETS : "stores"
  VIDEOS ||--o| TRANSCRIPTS : "transcribed"
  TRANSCRIPTS ||--o{ TRANSCRIPT_SEGMENTS : "contains"
  VIDEOS ||--o{ CHAPTERS : "chaptered"
  VIDEOS ||--o{ SHARE_LINKS : "shared via"
  SHARE_LINKS ||--o{ SHARE_LINK_RECIPIENTS : "personalized for"
  SHARE_LINKS ||--o{ SHARE_AUDIT_EVENTS : "audited"
  VIDEOS ||--o{ VIDEO_VIEW_EVENTS : "viewed"
  VIDEO_VIEW_EVENTS }o--|| VIDEO_VIEW_DAILY : "rolls up to"
  VIDEOS ||--o{ COMMENTS : "discussed"
  VIDEOS ||--o{ CTAS : "prompts"
  WORKSPACES ||--|| SUBSCRIPTIONS : "subscribes"
  SUBSCRIPTIONS }o--|| PLANS : "on plan"
  WORKSPACES ||--o{ API_KEYS : "issues"
  WORKSPACES ||--o{ WEBHOOK_ENDPOINTS : "configures"

5.6 Growth Estimates & Scale Implications #

Modeled assumptions: 2 workspaces average per user account; average workspace records 15 videos/month after month 1; each video averages 40 views over its lifetime; each view generates ~24 heartbeat events (a 2-minute average watch time at 5s intervals) plus ~3 discrete events.

| Table | @ 1k workspaces | @ 10k workspaces | @ 100k workspaces | Implication | |---|---|---|---| | workspaces | 1,000 | 10,000 | 100,000 | trivial; b-tree on owner_id/slug stays shallow | | workspace_members | ~4,500 | ~45,000 | ~450,000 | trivial | | videos | ~180,000 | ~1.8M | ~18M | videos_workspace_active_idx keeps per-workspace listing O(log n); partitioning NOT needed at this scale | | recordings + media_assets | ~200,000 / ~600,000 | ~2M / ~6M | ~20M / ~60M | object storage cost dominates, not Postgres row count; media_assets_storage_key_unique index sized ~9 GB at 100k tier, fits in shared_buffers on a mid-size instance | | edit_decision_lists | ~360,000 | ~3.6M | ~36M | JSONB documents average ~5 KB; table ~180 GB at 100k tier — largest non-partitioned table; candidate for time-based archival to cold storage past Section 19's retention window if it becomes an operational concern | | cursor_telemetry_blobs | 200,000 rows (metadata only) | 2M | 20M | rows are tiny (metadata only); actual bytes live in object storage, unbounded by Postgres capacity | | transcript_segments | ~7.2M | ~72M | ~720M | first table requiring partitioning consideration beyond video_view_events at the 100k tier; plan is to range-partition by created_at (inherited from parent transcript) if p99 query latency on transcript_segments_transcript_time_idx degrades past the Section 27 budget — not needed at 1k/10k | | video_view_events | ~4.3M/month | ~43M/month | ~430M/month | already partitioned monthly from day one (5.4.10); at 100k tier each monthly partition is ~430M rows / ~90 GB — within a single partition's comfortable operating range for the indexes defined, but this is the ceiling at which sub-partitioning by workspace_id hash would be evaluated | | video_view_daily | ~54,000/month | ~540,000/month | ~5.4M/month | trivial, rollup keeps this small permanently | | video_engagement_curve | ≤180,000 rows total (capped at 1800/video) | ≤1.8M | ≤18M | bounded by design (Section 16's 1800-bucket cap) — this table's size is a function of active video count, not view count, so it never grows unboundedly | | comments | ~90,000 | ~900,000 | ~9M | trivial | | api_keys | ~150 (Business only) | ~1,500 | ~15,000 | trivial |

Index footprint implication: at the 100k-workspace tier, the two largest index sets are on edit_decision_lists (GIN index on document) and video_view_events partitions. The GIN index on edit_decision_lists.document is created with jsonb_path_ops specifically (not the default jsonb_ops) because it produces a substantially smaller index (roughly 3-4x smaller in practice) at the cost of only supporting @> containment queries — which is the only query shape this index needs to serve (5.4.5).

Partition footprint implication: at the 100k tier, video_view_events carries roughly 12-13 live monthly partitions at any time (13-month retention, 5.4.10) at ~90 GB each — a working set of roughly 1.1-1.2 TB for this table alone, which is the primary driver of the storage-tier sizing guidance in Section 27.

5.7 Migration Strategy (drizzle-kit) #

File naming: drizzle-kit generates timestamped migration files (drizzle/migrations/0001_initial_schema.sql, 0002_add_redaction_regions.sql, ...) via drizzle-kit generate. The numeric prefix is drizzle-kit's own strictly-increasing sequence number — migrations are never renumbered or reordered after merge, and a migration file, once merged to the main branch, is never edited (a mistake is corrected by a new forward migration, not by rewriting history).

Ordering: migrations apply in filename order via drizzle-kit migrate, tracked in drizzle-kit's own __drizzle_migrations bookkeeping table. CI (Section 25/26) runs drizzle-kit migrate against a fresh database as part of every deploy pipeline; a migration that fails to apply blocks the deploy.

Expand/contract pattern for zero-downtime changes: because apps/api and apps/worker are deployed as multiple replicas with rolling restarts, a schema change and the code that depends on it are never deployed in the same instant across the fleet — for any window of a rolling deploy, old code and new code run against the same database. Every migration that changes a column's meaning follows three phases, each a separate migration/deploy cycle:

1. Expand: add the new column/table alongside the old one, nullable or defaulted, written by both old and new code paths (dual-write). Old code is untouched and keeps working.
2. Migrate: backfill the new column from the old one in batches (a one-off script, never a long-running transaction that locks the table), then deploy application code that reads from the new column exclusively while still dual-writing.
3. Contract: once all replicas run code that no longer reads the old column, and a full retention-window's worth of time has passed with no rollback need, drop the old column in a final migration.

Example: renaming videos.title to videos.display_title would never be a single ALTER TABLE ... RENAME COLUMN deployed with the code that reads the new name — it would be ADD COLUMN display_title, a backfill, a deploy that dual-writes both, a deploy that reads only display_title, then a final migration dropping title.

Destructive migration gate: any migration containing DROP TABLE, DROP COLUMN, or a NOT NULL addition without a DEFAULT on a non-empty table is tagged -- DESTRUCTIVE in a header comment (a CI lint step, scripts/check-destructive-migrations.ts, greps for DROP COLUMN/DROP TABLE/ALTER COLUMN ... SET NOT NULL and fails the PR unless that header comment is present) and requires a second named approver on the pull request in addition to normal review, per Section 25's review policy. DROP TABLE/DROP COLUMN migrations may only ship as the "contract" phase of the expand/contract pattern above — never as a same-PR companion to the code change that stops using the column.

5.8 Seed Data for Local Development #

packages/db/src/seed.ts, run via pnpm --filter @reelay/db seed against a local database, inserts:

Plans (mirrors Section 21's limit table into plans.limits JSONB):

await db.insert(plans).values([
  {
    id: uuidv7(), code: "free", name: "Free",
    stripePriceIdMonthly: null, stripePriceIdYearly: null,
    limits: {
      seats: 1, maxRecordingLengthMs: 5 * 60 * 1000, libraryVideoCap: 25,
      storageQuotaBytes: 2 * 1024 ** 3, watermark: true, aiAutoEditTier: "basic",
      aiChaptersAndSummaries: false, fillerWordRemoval: false,
      viewerAnalyticsTier: "aggregate", customDomain: false,
      retentionDays: 90, transcodePriority: "standard", apiAccess: false,
      exportMaxResolution: "720p",
    },
  },
  {
    id: uuidv7(), code: "pro", name: "Pro",
    stripePriceIdMonthly: "price_pro_monthly_placeholder", stripePriceIdYearly: "price_pro_yearly_placeholder",
    limits: {
      seats: "per_seat", maxRecordingLengthMs: null, softCapMs: 4 * 60 * 60 * 1000,
      libraryVideoCap: null, storageQuotaBytesPerSeat: 250 * 1024 ** 3, watermark: false,
      aiAutoEditTier: "full", aiChaptersAndSummaries: true, fillerWordRemoval: true,
      viewerAnalyticsTier: "full_per_viewer", customDomain: false,
      retentionDays: 730, transcodePriority: "standard", apiAccess: false,
      exportMaxResolution: "4k",
    },
  },
  {
    id: uuidv7(), code: "business", name: "Business",
    stripePriceIdMonthly: "price_business_monthly_placeholder", stripePriceIdYearly: "price_business_yearly_placeholder",
    limits: {
      seats: "per_seat", maxRecordingLengthMs: null, softCapMs: 4 * 60 * 60 * 1000,
      libraryVideoCap: null, storageQuotaBytesPerSeat: 1024 ** 4, watermark: false,
      aiAutoEditTier: "full", aiChaptersAndSummaries: true, fillerWordRemoval: true,
      viewerAnalyticsTier: "full_per_viewer", customDomain: true,
      retentionDays: null, transcodePriority: "priority", apiAccess: true,
      exportMaxResolution: "4k",
    },
  },
]);

Default auto-edit presets (auto_edit_presets with is_system = true, workspace_id = NULL, parameters matching the defaults specified in Section 10). The three seed presets differ only in zoom.defaultLevel and zoom.maxLevel — every other zoom-timing field, and the entire motion/framing parameter set, is identical across all three and pinned to the engine-wide constants Section 10 fixes globally (omega0 9.0, zeta 1.0, oneEuroMinCutoff 1.0, oneEuroBeta 0.007, deadzonePx 2, safeRectPct 60, hysteresisBandPct 8). These are physics/framing constants that describe how the camera moves and stays composed — they are calibrated once for the engine as a whole, not a per-preset "personality" knob; only the zoom aggressiveness itself (how far in, how far the ceiling goes) is what actually differs between "Subtle," "Balanced," and "Energetic":

const SHARED_MOTION = { omega0: 9.0, zeta: 1.0, oneEuroBeta: 0.007, oneEuroMinCutoff: 1.0, deadzonePx: 2 };
const SHARED_FRAMING = { safeRectPct: 60, hysteresisBandPct: 8 };
const SHARED_ZOOM_TIMING = { minHoldMs: 1200, minGapMs: 800, leadInMs: 400, easeOutMs: 600, coalesceWindowMs: 900 };

await db.insert(autoEditPresets).values([
  {
    id: uuidv7(), workspaceId: null, name: "Subtle", version: 1, isSystem: true,
    parameters: {
      zoom: { defaultLevel: 1.3, maxLevel: 1.8, ...SHARED_ZOOM_TIMING },
      motion: SHARED_MOTION,
      framing: SHARED_FRAMING,
    },
  },
  {
    id: uuidv7(), workspaceId: null, name: "Balanced (Default)", version: 1, isSystem: true,
    parameters: {
      zoom: { defaultLevel: 1.6, maxLevel: 2.5, ...SHARED_ZOOM_TIMING },
      motion: SHARED_MOTION,
      framing: SHARED_FRAMING,
    },
  },
  {
    id: uuidv7(), workspaceId: null, name: "Energetic", version: 1, isSystem: true,
    parameters: {
      zoom: { defaultLevel: 1.9, maxLevel: 2.5, ...SHARED_ZOOM_TIMING },
      motion: SHARED_MOTION,
      framing: SHARED_FRAMING,
    },
  },
]);

Default background presets (stored as a feature_flags-adjacent static catalog seeded into a JSONB row consumed by packages/ui; not a dedicated table, since backgrounds are a closed, versioned set shipped with the app rather than user-editable rows — Section 10 owns their specification):

await db.insert(featureFlags).values({
  id: uuidv7(), key: "background_preset_catalog", description: "Seeded catalog of auto-edit background presets",
  defaultEnabled: true,
  workspaceOverrides: {},
});

A local dev workspace for manual testing: one seed users row (dev@reelay.local, password devpassword123! hashed with the Section 6.2 Argon2id parameters), one workspaces row owned by that user on the business plan (so every gated feature is exercisable locally), and a workspace_members row with role = 'owner'.

5.9 Summary #

This section is the single source of truth for storage. Every other section that persists data does so through the tables, constraints, and indexes defined above — a drafter or implementer who needs a new persisted field extends a table here (via the migration process in 5.7), never invents a parallel, undocumented store.

6. Identity, Workspaces, Roles & Permissions #

This section owns authentication, session management, workspace membership, and the complete permission model. The underlying tables (users, sessions, oauth_accounts, mfa_credentials, workspaces, workspace_members, workspace_invites, folder_permissions, api_keys) are defined in Section 5; this section defines the behavior built on top of them.

6.1 Email + Password Authentication #

6.1.1 Password hashing #

Passwords are hashed with Argon2id, parameters m=19456 KiB, t=2, p=1 (per the locked decision) — the OWASP-recommended baseline for Argon2id in 2025+, balancing brute-force resistance against acceptable request latency (~150-250 ms per hash on typical API server hardware). The node-argon2 (or equivalent WASM-backed) library is used server-side only; a password never reaches the database, logs, or any telemetry system in plaintext or as a weaker hash.

users.password_hash stores the full Argon2id encoded string (algorithm, version, parameters, salt, and hash all embedded per the standard $argon2id$v=19$m=19456,t=2,p=1$<salt>$<hash> format), so a future parameter upgrade can be detected and the hash transparently re-computed on next successful login (a "rehash on login if parameters are stale" check, comparing the stored string's embedded parameters against the current server-side constants).

6.1.2 Password rules #

Breach-list check: the password's SHA-1 hash is computed client-side-adjacent (server-side, never transmitting the plaintext further than the TLS-terminated request body) and the first 5 hex characters are sent to the Have I Been Pwned k-anonymity range API (GET https://api.pwnedpasswords.com/range/{first5}); the full hash suffix is matched against the returned list locally. If the check fails to complete (network error) within a 2-second timeout, the signup/change proceeds — breach-checking degrades open, because blocking account creation on a third-party dependency's availability is a worse failure mode than occasionally allowing a breached-but-otherwise-valid password through. A password found in the breach list is rejected with error code password_breach_detected (Section 7.6 error envelope) and the message instructs the user to choose a different password; the specific breach source is never disclosed.

6.1.3 Signup flow #

POST /v1/auth/signup — { email, password, displayName }. Signup does not create a workspace, and this endpoint takes no workspaceName field or any other workspace-shaped input. It creates exactly one thing: a users row. Workspace creation is a separate, subsequent call — POST /v1/workspaces (6.7.1) — made by the client only after the account exists (and, in the common case, after the user has verified they can log in). This separation is deliberate: it keeps the enumeration-resistant 202 contract below simple to reason about (a signup response never has to carry or omit workspace-shaped data depending on whether the account was new or pre-existing), and it means the same signup endpoint serves both "sign up and create your first workspace" and "sign up because you were invited to an existing workspace" (6.7.4) without a conditional body shape — the invite-acceptance flow never touches this endpoint's request contract at all. Server steps:

1. Validate shape via the shared Zod schema (Section 7).
2. Normalize email (lowercase handled transparently by citext; trim whitespace).
3. Check breach list (6.1.2); reject password_breach_detected if hit.
4. Check for an existing users row with this email (including soft-deleted — a soft-deleted account's email is NOT immediately reusable; see 6.1.7 for the account-deletion email-reuse rule).
5. If an account already exists, respond with the SAME 202 Accepted shape and generic message as a successful signup ("Check your email to continue") — this endpoint is enumeration-resistant by design: an attacker cannot distinguish "new account created" from "email already registered" by response shape, status code, or timing (a constant minimum response latency of 300 ms is enforced via a floor on the handler, so the presence/absence of the Argon2id hashing step does not leak through timing). In the already-registered case, an email is sent to the existing address stating that a signup attempt was made and offering a password-reset link instead — this is the account owner's notification path, not the requester's.
6. Otherwise, hash the password, create the users row (email_verified_at = NULL), send a verification email with a single-use time-limited token (6.4.1), and respond 202 Accepted with the same generic body.

6.1.4 Login flow #

POST /v1/auth/login — { email, password }.

1. Look up users by email. If not found, or deleted_at IS NOT NULL, or the password does not match: respond 401 with error code invalid_credentials — the same code and message in every one of these cases, so an attacker cannot distinguish "no such account" from "wrong password" from "deleted account."
2. If locked_until is in the future: respond 401 invalid_credentials as well (never a distinct "account locked" message pre-authentication — that would itself be an enumeration signal that this account exists and has recently failed logins). A separate, authenticated-context surface (the login page can show a soft "too many attempts, try again later" hint keyed only off client-side rate-limit response headers, never off account-specific state).
3. On password mismatch: increment failed_login_count. At 10 consecutive failures within a rolling 15-minute window, set locked_until = now() + 15 minutes and reset the counter.
4. On success: reset failed_login_count to 0, clear locked_until, update last_login_at, and:
   • If mfa_credentials exists for this user, respond 200 with a short-lived (5-minute) mfaChallengeToken and no session yet — the client must complete 6.3.3 before a session is issued.
   • Otherwise, create a sessions row (6.3) and respond 200 with the session cookie set.
5. Rate limiting: POST /v1/auth/login is limited to 10 requests/minute per IP AND 5 requests/minute per email, whichever is hit first. This section is the sole source for every /v1/auth/* rate limit in the product — these thresholds are keyed by IP and by email, never by plan: an unauthenticated caller has no plan to key against, and a plan-keyed limit on an auth endpoint would itself be an enumeration signal (a response that varies by which workspace's plan a given email happens to belong to leaks account existence). Section 7.9's general rate-limit mechanism (token buckets, X-RateLimit-* headers, 429 + Retry-After) supplies the transport; the numbers themselves live only here, are never restated as a plan-tier row in Section 7.9's per-plan limit table, and every other section that touches auth rate limiting references this subsection by number rather than repeating the figures.

6.1.5 Password reset flow #

POST /v1/auth/password-reset/request — { email }. Always responds 202 Accepted with an identical generic body regardless of whether the email is registered (enumeration-resistant, same principle as 6.1.3). If registered and not soft-deleted, a reset token is generated: a cryptographically random 32-byte value, SHA-256 hashed for storage (reusing the sessions-style pattern but as a one-off row — reset tokens are stored inline on a lightweight internal table not listed among the core entities because it is purely transient bookkeeping, implemented as a Redis key password-reset:<sha256(token)> = userId with a 1-hour TTL rather than a Postgres row, since it needs no durability beyond that window and benefits from Redis's native expiry).

POST /v1/auth/password-reset/confirm — { token, newPassword }.

1. Look up the Redis key; if missing or expired, respond 400 invalid_or_expired_token.
2. Validate newPassword against 6.1.2 rules including breach check.
3. Update users.password_hash, delete the Redis key immediately (single-use — a second confirm with the same token always fails, even within the TTL window).
4. Revoke every existing session for this user (UPDATE sessions SET revoked_at = now(), revoked_reason = 'password_change' WHERE user_id = $1 AND revoked_at IS NULL) — a password reset is a security event that must invalidate any session an attacker may have established.
5. Send a confirmation email to the account's address noting the password was changed, with a "this wasn't me" link that starts a support-mediated recovery flow (out of scope for this document beyond noting its existence).

6.1.6 Enumeration resistance summary #

6.1.7 Account deletion and email reuse #

When a users row is soft-deleted (Section 19 owns the request/grace-period/purge lifecycle), its email is NOT released for immediate re-signup — the unique index users_email_unique is scoped WHERE deleted_at IS NULL, meaning a soft-deleted row does not block a new signup with the same email even before final purge. This is intentional: it allows a genuinely departed user's address to be reclaimed by a new signup (e.g. a company email being reassigned) without waiting for the full purge cycle, while the soft-deleted row itself remains for the audit and grace-period-restore flow in Section 19.

6.2 Google OAuth 2.0 (PKCE) #

6.2.1 Flow #

1. GET /v1/auth/google/start — server generates a PKCE code_verifier (43-128 char random string) and code_challenge (BASE64URL(SHA256(code_verifier))), and a random state value. Both code_verifier and state are stored server-side in a short-lived (10-minute) Redis key keyed by a session-scoped nonce cookie (oauth_flow_id, httpOnly, Secure, SameSite=Lax, 10-minute expiry) — never in a client-readable cookie, and never round-tripped through the client beyond the opaque oauth_flow_id. The server redirects to Google's authorization endpoint with code_challenge, code_challenge_method=S256, state, scope=openid email profile, and the registered redirect_uri.
2. Google redirects back to GET /v1/auth/google/callback?code=...&state=....
3. The server reads oauth_flow_id from the cookie, looks up the stored state/code_verifier in Redis, and rejects the callback outright (400 invalid_oauth_state) if: the cookie is missing, the Redis key has expired, or the returned state does not exactly match the stored value. This closes CSRF on the OAuth callback — an attacker cannot trick a victim into linking the attacker's Google account to the victim's session, because the flow can only complete with the exact code_verifier this specific server-side flow generated.
4. The server exchanges code + code_verifier for tokens at Google's token endpoint, validates the returned ID token's signature, issuer, audience, and expiry, and extracts sub, email, email_verified, name, picture.
5. The Redis key and oauth_flow_id cookie are deleted immediately (single-use, mirroring 6.1.5).

6.2.2 Account linking rules #

Auto-linking by verified email is a deliberate, narrow exception to "never derive access from implicit matching" (6.10): it is scoped to exactly one email, proven by a live OAuth assertion from the specific provider at the specific moment of login, not a standing domain-wide policy — it is categorically different from SSO/SCIM's domain-based auto-provisioning, which this product explicitly defers.

6.3 Session Model #

6.3.1 Cookie and token #

• sessions.token_hash stores SHA-256(opaque_token); the opaque token itself (256 bits of crypto.randomBytes, base64url-encoded) is set in a cookie named reelay_session.
• Cookie attributes: httpOnly; Secure; SameSite=Lax; Path=/; Max-Age=2592000 (30 days). SameSite=Lax (not Strict) because top-level navigation into a shared video link or an OAuth-callback redirect must carry the cookie for the dashboard's own domain; Strict would break the "click a link in Slack, land logged-in" experience. The cookie is never readable by JavaScript (httpOnly), so it is not a viable target for XSS token theft even if a script injection vulnerability existed elsewhere.
• Sliding expiry: every authenticated request that hits the API updates sessions.last_seen_at and, if more than 24 hours have elapsed since expires_at was last extended, re-issues expires_at = now() + 30 days (both server-side row and cookie Max-Age refreshed). This bounds the write amplification of updating the session row on every single request while still keeping active users perpetually logged in and inactive sessions expiring on schedule.

6.3.2 Server-side session table, revocation, "log out all devices" #

Every session is a durable sessions row (Section 5.4.1), not a stateless signed token — this is what makes server-side revocation possible at all. DELETE /v1/auth/logout sets revoked_at = now(), revoked_reason = 'logout' on the current session only. POST /v1/auth/logout-all-devices revokes every session for the user (revoked_reason = 'logout_all') except, optionally, the requesting session if the client passed keepCurrent: true in the request body. A revoked or expired session fails auth on its very next use (401 session_expired) — there is no grace window, because the whole point of revocation is immediate effect.

The dashboard's "active sessions" settings screen lists sessions WHERE user_id = $1 AND revoked_at IS NULL ORDER BY last_seen_at DESC, showing user_agent, a coarse location derived from ip_address (city-level geo lookup, never stored beyond the IP itself), and last_seen_at, with a per-row "revoke this device" action.

6.3.3 MFA step-up during login #

When POST /v1/auth/login succeeds password-wise but the user has an mfa_credentials row (6.5), no sessions row is created yet. The client receives mfaChallengeToken (JWT, 5-minute expiry, sub = user_id, purpose = mfa_challenge, signed with the API's session-signing key) and must call POST /v1/auth/mfa/verify with { mfaChallengeToken, code } (a 6-digit TOTP code, or a recovery code — see 6.5). Only on success is the sessions row created and the cookie set. This token is never a valid Authorization: Bearer credential for any other endpoint — the API's auth middleware rejects any token whose purpose claim is not appropriate for the route it is presented to.

6.3.4 Short-lived JWT for the editor client #

The timeline editor (Section 11) and other latency-sensitive client-side code paths (the desktop app, Section 8) do not want to hit the session table on every action. The API issues a short-lived JWT (POST /v1/auth/editor-token, requires a valid session cookie) with a 10-minute expiry, sub = user_id, workspaceId (the currently active workspace), and the resolved role for that workspace at issuance time. This JWT is used as a Bearer token for high-frequency editor/desktop calls (e.g. autosave, telemetry upload progress) where the marginal cost of a session-table lookup per request would add latency; it carries no more authority than the session it was derived from, expires quickly enough that a stale cached role is never a meaningful exposure window (Section 27's performance budget assumes it is refreshed silently by the client every 8 minutes), and is re-verified against the live session and membership on any action with billing, deletion, or permission-management side effects — the JWT is a fast path for low-stakes, high-frequency actions, never the sole authority for a consequential one.

6.4 Email Verification Gate #

6.4.1 Verification token #

Sent at signup (6.1.3) and resendable via POST /v1/auth/verify-email/resend (enumeration-resistant per 6.1.6). The token is a single-use, time-limited value: 32 random bytes, SHA-256 hashed and stored as a Redis key email-verify:<sha256(token)> = userId with a 24-hour TTL. GET /v1/auth/verify-email?token=... looks up the key, sets users.email_verified_at = now(), deletes the key, and redirects to the dashboard with a success toast. An expired or already-used token yields 400 invalid_or_expired_token and a "resend" affordance.

6.4.2 What an unverified user can do #

Email verification exists specifically as an anti-spam gate on public sharing — it is deliberately not a gate on product exploration, because forcing verification before any use increases signup friction for no security benefit (an unverified account cannot spam anyone until it can create a public artifact).

The block is enforced server-side in the share-link creation/visibility-change handler (Section 14 owns the endpoint), not merely hidden in the UI — a direct API call from an unverified account attempting link/public visibility receives the same 403 email_unverified. email_unverified is the one and only error code for this condition anywhere in the product — Section 7.6 owns the error catalogue and lists no email_verification_required variant; that name never existed as a second code, only as this one, spelled consistently everywhere it is returned.

6.5 Multi-Factor Authentication (TOTP) #

• Enrolment: POST /v1/auth/mfa/enroll generates a random 160-bit TOTP secret, returns it as both a otpauth:// URI (for QR-code rendering client-side) and the raw base32 secret (manual entry fallback). The secret is held in a pending state (not yet written to mfa_credentials) until confirmed: POST /v1/auth/mfa/enroll/confirm with a valid current code writes the encrypted secret and 10 freshly generated recovery codes (10 random 10-character alphanumeric codes, each individually hashed with SHA-256 and stored as the recovery_codes_enc JSON array, encrypted at rest per Section 22) to mfa_credentials, and returns the recovery codes to the client exactly once — they are never retrievable again after this response; a "regenerate recovery codes" action invalidates and replaces the full set.
• Verification: TOTP codes use the standard 30-second window, 6 digits, with a ±1 step tolerance (accepts the previous and next 30-second window to absorb clock drift) — no wider, since a larger tolerance meaningfully weakens the brute-force resistance of a 6-digit code.
• Recovery codes: each is single-use; using one immediately invalidates it (removed from the stored array) and triggers an email notification ("a recovery code was used to sign in") since this is a signal worth the account owner's attention.
• Step-up requirements: beyond initial login (6.3.3), a fresh MFA code (or recovery code) is required — even for an already-authenticated session — before: disabling MFA, changing the account password from within settings (as opposed to the reset-token flow, which is its own proof of control), generating an API key, transferring workspace ownership, and deleting a workspace. This "step-up" check is a requireStepUp(action) middleware that looks for a recent (last 5 minutes) successful MFA verification recorded in the session's Redis-cached state; if absent, it returns 403 step_up_required with a stepUpChallenge: true flag the client uses to prompt for a fresh code without a full re-login.
• MFA is available to all plans and both roles-with-billing-access and not — it is a security feature, never plan-gated.

6.6 API Keys (Public API, Business Plan Only) #

API keys are created and managed only by owner and admin roles (permission matrix, 6.8).

Effective role is fixed at creation, not dynamically re-derived. An API key's authority is admin-equivalent, narrowed only by its scopes array, computed once when the key is created and never recomputed against the creator's current membership afterward. Concretely: authorize() (6.9) treats every api_key-typed Actor as carrying an implicit admin role ceiling — the same ceiling regardless of which role the creating user happened to hold at creation time, and regardless of any role change, demotion, or removal that user later experiences — with the actual permitted action set narrowed down from that ceiling by apiKeyScopeCovers(actor.apiKeyScopes, action). This is a deliberate, explicit design choice, not an oversight: keys are workspace-level credentials used by external systems (Section 20 integrations, customer-built automation against the public API), and those systems have no way to observe or react to "the human who created this key was later demoted to member." Re-deriving a key's authority from its creator's live membership would mean an external integration's behavior could silently change (or break) as a side effect of unrelated HR churn inside the workspace — a surprising and hard-to-debug failure mode. The security backstop is instead scope minimalism at creation and prompt revocation on departure: an owner/admin creating a key should grant only the scopes the integration actually needs (6.6's scopes row), and removing a member (6.7.5) does not auto-revoke keys they created — an owner/admin reviewing the keys list (which shows created_by) is expected to revoke or reassign a departed creator's keys as part of offboarding, exactly as they would rotate any other credential a departing employee had access to. A key's authority is never silently upgraded either: if the creator was later promoted, the key's ceiling was already admin-equivalent (narrowed by scopes) from the moment of creation, so there is nothing further to grant.

6.7 Workspaces: Lifecycle #

6.7.1 Creation #

Any authenticated, email-or-not-verified user may create a workspace (POST /v1/workspaces). The creator becomes owner (a workspace_members row with role = 'owner' is created in the same transaction as the workspaces row — a workspace never exists, even momentarily, without an owner). New workspaces start on the free plan (a subscriptions row is created pointing at the free plan's plans.id) unless created via an in-flow "start on Pro" checkout, in which case the Stripe checkout session must complete before the workspaces row commits (Section 21 owns the checkout flow).

6.7.2 The one-owner rule #

Exactly one owner per workspace at all times, enforced by the partial unique index workspace_members_one_owner_unique (Section 5.4.2). There is no code path that creates a workspace, or leaves a workspace in a settled state, with zero owners or more than one.

6.7.3 Ownership transfer #

POST /v1/workspaces/{workspaceId}/transfer-ownership — { toMemberId }. Requirements:

1. Caller must be the current owner (only the owner can initiate transfer — an admin cannot transfer ownership to themselves or anyone else).
2. Caller must pass MFA step-up (6.5) if MFA is enrolled.
3. toMemberId must reference an existing, accepted workspace_members row in this workspace with role IN ('admin', 'member') — a viewer cannot receive ownership directly (they must first be promoted to at least member, a separate, ordinary role-change action).
4. Executed as a single SERIALIZABLE transaction (5.4.2 explains why): UPDATE workspace_members SET role = 'admin' WHERE workspace_id = $1 AND role = 'owner' followed by UPDATE workspace_members SET role = 'owner' WHERE id = $toMemberId. If either statement would violate workspace_members_one_owner_unique, the whole transaction rolls back and the endpoint returns 409 ownership_transfer_conflict.
5. An audit_events row (action = 'workspace.ownership_transferred') and notification emails to both parties are written in the same transaction.

6.7.4 Invites #

POST /v1/workspaces/{workspaceId}/invites — { email, role }, role IN ('admin', 'member', 'viewer') (never 'owner', per the workspace_invites_role_check constraint). Behavior:

• If a pending invite already exists for this (workspace_id, email), it is revoked (status = 'revoked') and a new one created — re-inviting always supersedes rather than erroring, since the caller's intent ("get this person into the workspace") is served either way.
• The invite token (32 random bytes, SHA-256 hashed, stored in token_hash) is embedded in an emailed link (https://app.reelay.app/invites/accept?token=...) AND made available as a shareable "invite link" the inviter can copy directly — both are the same token/row; there is no behavioral difference between "clicked from email" and "opened the copied link."
• expires_at = now() + 7 days.
• If the invited email already has a users account: accepting the invite (after login, or as part of a login-then-accept redirect chain if not currently authenticated) creates the workspace_members row with the preselected role and sets status = 'accepted'.
• If the invited email has no account: the accept link routes through signup first (pre-filling the email, which becomes read-only on that signup form to prevent a bait-and-switch where the invite is accepted under a different identity), then completes the same membership-creation step.
• An expired invite (now() > expires_at at accept-time) yields 410 invite_expired; the inviter can re-invite (which supersedes as above).

6.7.5 Member removal #

DELETE /v1/workspaces/{workspaceId}/members/{memberId} (admin/owner only, permission matrix 6.8). The owner cannot be removed via this endpoint (must transfer ownership first, 403 cannot_remove_owner). On removal:

1. The workspace_members row is hard-deleted (membership is not in the soft-delete list).
2. Every videos row where owner_id was the removed user, within this workspace, has owner_id reassigned to the workspace's current owner. This is a deliberate default: a removed member's work product stays with the workspace (it was recorded as work for that workspace) rather than becoming orphaned or deleted; the new owner (the workspace owner) can subsequently reassign or delete individual videos through ordinary video-management actions.
3. Every share_links row created_by the removed user is untouched and continues to function (share links are never invalidated by their creator's departure — this follows directly from the iron rule in Section 21/19 that creation-time state never revokes existing playback).
4. Every folder_permissions row granting this specific user access in this workspace is deleted explicitly by the removal handler (DELETE FROM folder_permissions WHERE workspace_id = $1 AND principal_type = 'user' AND principal_user_id = $2, Section 5.4.3) — this is not an automatic FK cascade, because folder_permissions.principal_user_id references users.id directly (a grant is a statement about a person, addressable across that person's whole account lifetime), and users.id survives a single workspace's membership removal. Role-typed grants (principal_type = 'role') are untouched by a member removal, since they apply to whichever users currently hold that role, not to any specific departed individual.
5. An audit_events row records the removal, actor, and the video-reassignment count.
6. The removed user retains their users account and any OTHER workspace memberships untouched; only this workspace's membership is affected.

6.7.6 Workspace deletion #

DELETE /v1/workspaces/{workspaceId} — owner only, MFA step-up required. This does not hard-delete immediately: it sets workspaces.deleted_at = now() and scheduled_deletion_at = now() + 30 days (the grace period), creates a deletion_requests row (target_type = 'workspace', status = 'grace_period'), and sends a confirmation email with an "undo" link valid for the full grace window. During the grace period the workspace is fully inaccessible to members (dashboard access 404s) but existing share links continue to serve playback — per the iron rule, deletion in progress is not yet purge, and purge is what stops playback, not the deletion request itself. Section 19 owns the exact purge execution (what happens at day 30, the media hard-delete order, and the final deletion_requests.status = 'purged' transition).

6.8 Roles & the Complete Permission Matrix #

Four workspace roles, exactly as locked: owner, admin, member, viewer. The table below is the exhaustive, authoritative list of gated actions in the product. Y = allowed, N = not allowed. Where a cell says own, the action is allowed only on resources the actor themselves created/owns (see the column note).

This matrix governs workspace members only — every row above marked own follows the exact same ownership-resolution rule (resolved against videos.owner_id at authorize()-check time, never cached). It does not govern anonymous share-link viewers: an anonymous visitor watching a link/public video, entering a password, or posting a comment via a share link is never a workspace_members row and is never evaluated against this table or against ROLE_CAPABILITIES['viewer'] — that population is authorized through the separate share_viewer allow-list defined in 6.9. Conflating the two is exactly the bug 6.9 exists to make structurally impossible.

Notes:

• "Own" columns are resolved against videos.owner_id / share_links.created_by at authorize()-check time (6.9), never cached — an owner reassignment (6.7.5) immediately changes who "own" applies to.
• Analytics visibility is the rule called out explicitly in the assignment: member sees analytics scoped to videos WHERE owner_id = actor.userId; owner/admin see workspace-wide analytics with no owner_id filter. This is enforced in the analytics query layer (Section 16) by the same authorize() call, not by a separate ad hoc check — see 6.9.
• viewer never appears in a workspace_members row with billable-seat consequences (6.10) and never gains recording capability regardless of any folder-level grant (a folder grant can only ever raise a folder's visibility to a viewer, never hand them a capability the role does not structurally have — recording is never expressible as a folder permission level, see 6.8.1).

6.8.1 Folder-level access: default deny #

Every folder is deny-by-default. Access to a folder's contents is governed by exactly two things: the folder's own visibility (5.4.3: private or workspace) and any explicit folder_permissions grant — there is no third state where "no grant exists, so the member's plain workspace role applies with no restriction." That fallback existed in an earlier draft of this model and was a real vulnerability: it meant every private folder became readable by every workspace member the instant it was created, before anyone had explicitly decided who should see it. It is deleted. The rule now:

1. If the requesting user's workspace role is owner or admin: always allow, at manage level, regardless of the folder's visibility or any folder_permissions row. Owner/admin have workspace-wide "all videos" access as a role capability (permission matrix, 6.8); folder grants exist to scope down member/viewer access for collaboration-control purposes (e.g. a client-facing folder only certain members should see), never to fence off content from the people whose job is to administer the whole workspace.
2. Else if the folder's visibility = 'workspace': allow at view level (or higher, if a folder_permissions row grants more) — a workspace-visible folder is readable by every member by design, that is what visibility = 'workspace' means.
3. Else if an explicit folder_permissions grant applies to this user — either a user-typed grant naming them directly, or a role-typed grant naming the role they currently hold — allow at the granted permission_level (view | comment | edit | manage, Section 5.4.3's single enum, used identically here and in Section 18.2.1's folder/collection browsing rule; there is no second, differently-shaped access enum anywhere in the product).
4. Else: deny. No role, no visibility match, no grant — the folder (and everything under it) is invisible to this user, full stop. This is what makes "a private folder is not visible to everyone in the workspace by default" actually true, including at the instant of creation, with no window where a missing grant defaults to allow.
5. visibility is never inherited; folder_permissions grants are. These are two separate mechanisms and conflating them is a security bug. A folder's visibility column is NOT NULL DEFAULT 'private' (Section 5.4.3), so every folder always has an explicit value of its own and that value alone governs it — a folder never "falls back" to an ancestor's visibility, because there is no unset state for it to fall back from. Grants are different: for folder_permissions, the closest ancestor in the chain carrying an explicit grant for the actor governs. If no folder in the chain carries a grant, nothing is granted — which, per rule 4, means deny for anyone who is not owner/admin. Section 18.2.1 states this same rule from the library side; the two are identical by construction and neither may be changed without the other.
6. A folder grant can only ever raise access relative to the deny-by-default baseline, never grant a capability the role does not structurally have. A viewer with a manage grant on a folder still cannot record new videos into it — recording is a role capability the viewer role does not have, full stop, and no folder-level permission level is capable of expressing "grant the recording capability." What a folder grant on a viewer DOES do is let them see (view), comment on (comment), or — if a workspace explicitly chooses to — edit (edit) the EDLs of videos already inside that folder, which are the specific capabilities those permission levels denote.
7. A member always retains access to videos they personally own regardless of the containing folder's grant state — ownership is a separate access path from folder browsing (permission matrix, 6.8's "own" columns), matching the member role's baseline capability to manage their own videos everywhere in the workspace.

Computing effective folder access is a single function, not scattered checks — and it now takes the folder's visibility as an explicit input rather than assuming an implicit allow:

// packages/shared/src/authz/folder-access.ts
export type FolderPermissionLevel = "view" | "comment" | "edit" | "manage";
export type FolderAccess = FolderPermissionLevel | "deny";

export interface FolderAccessInput {
  actorRole: "owner" | "admin" | "member" | "viewer";
  actorUserId: string;
  /** Visibility + grant state walked from the target folder up through ancestors —
   * index 0 is the target folder itself; the first entry with an explicit setting wins. */
  folderChain: Array<{
    visibility: "private" | "workspace" | null; // null = no explicit setting on this folder
    grants: Array<{ principalType: "user" | "role"; principalId: string; level: FolderPermissionLevel }>;
  }>;
}

export function resolveFolderAccess(input: FolderAccessInput): FolderAccess {
  if (input.actorRole === "owner" || input.actorRole === "admin") {
    return "manage"; // rule 1: never restricted
  }

  for (const folder of input.folderChain) {
    const hasExplicitSetting = folder.visibility !== null || folder.grants.length > 0;
    if (!hasExplicitSetting) continue; // rule 5: keep walking up to the nearest explicit ancestor

    const grant = folder.grants.find(
      (g) =>
        (g.principalType === "user" && g.principalId === input.actorUserId) ||
        (g.principalType === "role" && g.principalId === input.actorRole),
    );
    if (grant) return grant.level; // rule 3

    if (folder.visibility === "workspace") return "view"; // rule 2

    return "deny"; // rule 4: explicit private setting, no matching grant
  }

  return "deny"; // rule 4/5 fallback: no explicit setting anywhere in the chain => private => deny
}

6.9 Authorization Enforcement Pattern #

This subsection is the sole definition of authorize() in the entire document. Every other section that touches permission checking (Section 22.4.1's security-property narration included) describes behavior built on top of what is defined here and cites this subsection by number; none of them redefines the interface, the Actor shape, or the Action enum. Every route handler that touches workspace-scoped data, and every route that serves anonymous share-link traffic, calls this one function — there is no second way to check permissions, and no route is permitted to query scoped data without passing through it first (enforced by the same no-unscoped-table-query ESLint rule referenced in Section 5.2, extended to also require a preceding authorize() call in the same handler, detected via static call-graph analysis in the lint rule).

// packages/shared/src/authz/authorize.ts
export type Role = "owner" | "admin" | "member" | "viewer";

export type Action =
  | "video.record" | "video.edit" | "video.delete" | "video.view"
  | "screenshot.capture" | "screenshot.manage"
  | "transcript.edit" | "caption.manage" | "chapter.manage"
  | "ai_suggestion.resolve"
  | "cta.manage"
  | "comment.moderate"
  | "share_link.create" | "share_link.update_visibility" | "share_link.view_audit_log"
  | "share_link.view"
  | "analytics.view_own" | "analytics.view_all"
  | "brand_kit.manage" | "custom_domain.manage" | "retention.manage"
  | "folder.manage" | "folder.set_overrides"
  | "member.invite" | "member.change_role" | "member.remove"
  | "workspace.transfer_ownership" | "workspace.delete"
  | "billing.manage" | "api_key.manage" | "audit_log.view"
  | "integration.manage" | "webhook.manage";

Every Action uses one vocabulary, resource.verb form, and only this form — video.edit, never video.write; there is no second spelling convention anywhere this enum is consumed (Section 22.4.1 narrates the security property this enforces but never introduces a competing name). This list covers every permission-gated verb enumerated across the full endpoint reference (Section 7.11): recording is video.record; screenshots are screenshot.capture/screenshot.manage; transcript corrections are transcript.edit; caption and chapter management are caption.manage and chapter.manage; AI metadata suggestion accept/reject is ai_suggestion.resolve; CTA create/update/delete is cta.manage; deleting another author's comment is comment.moderate; and anonymous share-link playback resolution is share_link.view (see the third Actor variant below).