tapir/docs/data-model.md

Tapir — Data Model

Current assumptions for the persistence model. Scoped to Stage 0 (single user) and Stage 1 (Future B, 1–5 trusted users). Future C concerns (sharding, cross-tenant dedup) are explicitly excluded and noted at the end.

Persistence is Postgres (ADR-002), reusing the homelab's instance with a per-tenant role when Stage 1 arrives. Secrets (OAuth tokens, BYO keys) are not stored in these tables — only opaque references to them; the secret material lives in ESO/1Password (ADR-002, ADR-006).

Design decisions baked into this model

• Per-user isolation for everything except transcripts. The earlier draft proposed a global videos/transcripts table deduped across tenants. Videos stay per-user and RLS-scoped — a shared video table reintroduces exactly the cross-domain coupling the homelab architecture review is removing. Transcripts, however, are now shared (ADR-021): keyed by (provider, provider_video_id), no user_id, not RLS-scoped. The cost avoided there is not LLM re-summarization but a rate-gated, reputation-risky caption fetch (ADR-010/014), which is paid per re-fetch regardless of user count — so persisting public caption content once and sharing it strictly beats the coupling it removes. Everything else each user owns is self-contained; rls_test.go proves transcripts is the single exception.
• Secrets by reference only. Tables hold a secret_ref (opaque string/UUID resolved via the SecretStore port), never tokens or keys.
• The brain sink is just a delivery target. No brain-specific tables. Whether a summary was also delivered to brain is recorded as sink status on the summary.

Entities

Solid entities below are persisted today (migrations 001–013). AI_CREDENTIAL and SUBSCRIPTION are planned, not yet a table — kept in the model for intent; see the notes.

erDiagram
    USER ||--|| USER_IDENTITY : "logs in via (Dex subject)"
    USER ||--o{ VIDEO_CONNECTION : has
    USER ||--o{ SUMMARY_ACTION : records
    USER ||--o{ AI_CREDENTIAL : "has (planned)"
    VIDEO_CONNECTION ||--o{ SUBSCRIPTION : "exposes (planned)"
    SUBSCRIPTION ||--o{ VIDEO : "produces (per user)"
    VIDEO }o--o| TRANSCRIPT : "shares one by (provider, provider_video_id) — not FK (ADR-021)"
    VIDEO ||--o| SUMMARY : "has at most one"
    SUMMARY ||--o{ SINK_DELIVERY : "delivered via"
    USER ||--o{ CHANNEL_ERROR : "reports unavailable channels"

    USER {
        uuid id PK
        text display_name
        bool auto_summarize "default true for new users (migration 011, ADR-018)"
        timestamptz created_at
    }
    USER_IDENTITY {
        text dex_subject PK
        uuid user_id FK "UNIQUE -> USER, ON DELETE CASCADE; NOT RLS-enabled"
        timestamptz created_at
    }
    VIDEO_CONNECTION {
        uuid id PK
        uuid user_id FK "-> USER, ON DELETE CASCADE"
        text provider "youtube | vimeo"
        text provider_account "nullable"
        text token_ref "-> SecretStore, never the token"
        text status "active | revoked | error"
        timestamptz connected_at
    }
    AI_CREDENTIAL {
        uuid id PK
        uuid user_id FK
        text provider "anthropic | openai | gemini"
        text key_secret_ref "-> SecretStore, never the key"
        text status
        timestamptz created_at
    }
    SUBSCRIPTION {
        uuid id PK
        uuid user_id FK
        uuid connection_id FK
        text channel_id
        text channel_title
        timestamptz websub_expires "nullable; youtube push lease"
        bool active
    }
    VIDEO {
        uuid id PK
        uuid user_id FK "-> USER, ON DELETE CASCADE"
        uuid subscription_id "nullable; no FK at Stage 0"
        text provider
        text provider_video_id
        text title
        int duration_s
        timestamptz published_at
        text url
        bool summarize_requested "default false -> manual-mode queue flag (migration 006)"
        timestamptz seen_at
        text transcript_status "none|rate_limited|fetched (migration 007)"
        timestamptz rate_limited_at "backoff clock for 429 retries (migration 007)"
    }
    TRANSCRIPT {
        text provider PK "part of shared key (ADR-021)"
        text provider_video_id PK "part of shared key — the cross-user dedup key"
        text source "captions | none"
        text language
        text content "null when source = none"
        timestamptz fetched_at
    }
    SUMMARY {
        uuid id PK
        uuid user_id FK
        uuid video_id "no FK to videos; (user_id, video_id) UNIQUE is the dedup key"
        text summary
        jsonb highlights
        jsonb takeaways
        text ai_provider "local | anthropic | openai | gemini"
        text ai_model
        bool fallback_used
        timestamptz created_at
    }
    SINK_DELIVERY {
        uuid id PK
        uuid summary_id FK "-> SUMMARY, ON DELETE CASCADE; ownership derived via this FK"
        text sink "store | brain"
        text status "pending | delivered | error"
        text detail "nullable; error message etc"
        timestamptz updated_at
    }
    SUMMARY_ACTION {
        uuid id PK
        uuid user_id FK "-> USER"
        text video_id "TEXT, not FK (mirrors summaries' standalone key)"
        text action "watched | skipped | saved"
        timestamptz acted_at
    }
    CHANNEL_ERROR {
        uuid user_id FK
        text channel_id
        text channel_name
        timestamptz first_seen
        timestamptz last_seen
    }

SUMMARY_ACTION has UNIQUE (user_id, video_id, action); VIDEO_CONNECTION has UNIQUE (user_id, provider) (one connection per provider — reconnect upserts in place). RLS (ENABLE + FORCE) is on every solid user-owned table above — users, videos, transcripts, summaries, summary_actions, video_connections, channel_errors. sink_deliveries is RLS'd via an EXISTS on its parent summary; user_identities is intentionally not RLS'd (auth plumbing). See the Isolation invariant section for the mechanism.

Notes per entity

• USER — one row per registered user (Stage 1, ADR-012; no longer single-row). The Tapir-side profile; the Dex identity is held separately in USER_IDENTITY, not on this row. auto_summarize (migration 006) is the per-user mode flag: TRUE = auto-summarize new videos published within the recency window (TAPIR_AUTO_SUMMARIZE_WINDOW, default ~7d, ADR-020); older videos are listed but summarised on demand. Default is true for new users (migration 011, ADR-018); existing rows were back-filled via migration 012 with RLS bypass.
• USER_IDENTITY (migration 004) — the dex_subject → user_id map. dex_subject is the PK, user_id a UNIQUE FK to users with ON DELETE CASCADE. This is the bridge resolved at login before a user_id is known, so it is deliberately not RLS-enabled (it holds no user data; RLS here would deadlock the lookup that yields the id used for scoping). Account deletion cascades the mapping away (ADR-013).
• VIDEO_CONNECTION (migration 005) — a connected YouTube/Vimeo account. token_ref resolves to the OAuth refresh token via SecretStore (per-user scheme youtube/<userID>/refresh_token). UNIQUE (user_id, provider): one connection per provider, reconnect upserts. Revocation/disconnect flips status, doesn't delete history. FORCE RLS'd.
• AI_CREDENTIAL — planned, no table yet. Optional, per provider, per user (ADR-004's Fallback). BYO keys are currently resolved via SecretStore refs without a dedicated table; this entity is modelled for when per-credential metadata is needed.
• SUBSCRIPTION — planned, no table yet. A watched channel; websub_expires would track the YouTube push lease. At Stage 0/1 videos.subscription_id is a nullable column with no FK (the subscriptions table is not part of the shipped store-sink slice — migration 001).
• VIDEO — one row per (user, video) — note user_id, reflecting the per-user-isolation decision. The same video seen by two users is two rows. seen_at is when Tapir detected it. summarize_requested (migration 006) is the manual-mode queue flag: the web "Summarize" button sets it TRUE; the next tapir run picks it up, summarizes, and clears it back to FALSE. transcript_status and rate_limited_at (migration 007) track caption-fetch outcomes for rate-limit backoff: NULL = not attempted; rate_limited = 429 seen, skip until NOW() - rate_limited_at > TAPIR_FETCH_BACKOFF; fetched = resolved; none = no transcript.
• TRANSCRIPT — shared public caption content, one row per (provider, provider_video_id), not RLS-scoped and carrying no user_id (ADR-021). Two users who watch the same video share the one row; the summarize path reads it before any caption fetch, so re-analysis never re-touches YouTube (ADR-010/014). source = none records "checked, no usable transcript" so no one reprocesses (ADR-007); content null in that case. A transient 429 is never stored here — it stays a per-user retry via VIDEO.transcript_status.
• SUMMARY — at most one per video. fallback_used + ai_provider/ai_model make the "is local good enough?" question queryable (the Stage 0 quality signal). highlights/ takeaways as jsonb to stay schema-flexible while the output format settles.
• SINK_DELIVERY — one row per (summary, sink) attempt. This is where "also sent to brain" lives — no brain tables, just a delivery row with sink = brain. Sinks fail independently; a failed brain delivery doesn't fail the store delivery. No own user_id; RLS ownership is derived from the parent summary via EXISTS (migration 003).
• SUMMARY_ACTION (migration 002) — records the maintainer's act on a summary (watch / skip / save) — the column that makes the Stage-0 headline metric ("acts on ≥1 summary") queryable (ui-spec.md §5, ADR-011). video_id is TEXT and not FK-constrained, mirroring summaries' standalone (user_id, video_id) key. UNIQUE (user_id, video_id, action). FORCE RLS'd.
• CHANNEL_ERRORS (migration 013) — channels that returned HTTP 404 (deleted or private) on the most recent discovery pass. Upserted per scheduler pass (last_seen refreshed each run); surfaced on the account page as a warning. Cascades on user deletion. Primary key is (user_id, channel_id). FORCE RLS'd.
• LOGIN_EVENTS (migration 010) — throttled one-row-per-(user, date) login stamp. Used by the Stage-0 gate query (VISION §Stage 0: "returned and used in ≥2 distinct weeks").

Isolation invariant (Stage 1+) — LIVE

Every user-owned table carries user_id, and isolation is enforced at the DB layer, not only in application code. ADR-011 shipped this surface single-user (one allowlisted subject, enforcement dormant); ADR-012 opened Stage 1 and turned enforcement on in the same slice.

Enforcement is Postgres Row-Level Security (migration 003_rls.up.sql):

• RLS is ENABLEd and FORCEd on every user-owned table — users, videos, transcripts, summaries, summary_actions, video_connections, channel_errors. FORCE is load-bearing: the app connects as the table owner (tapir role), and owners bypass RLS unless forced.
• Each policy keys off the per-request GUC tapir.current_user_id, set transaction-locally by the store's withUser helper via set_config('tapir.current_user_id', $1, true) — it auto-resets on commit/rollback, so it never leaks across a pooled connection.
• current_setting('tapir.current_user_id', true) uses missing_ok = true: an unset GUC yields NULL, the predicate matches no rows, and access denies by default.
• sink_deliveries has no user_id; its policy derives ownership from the parent summary via EXISTS (SELECT 1 FROM summaries …).
• user_identities (the Dex-subject → user_id map) is deliberately not RLS-enabled — it is auth plumbing read before a user_id is known; putting RLS there would deadlock. It holds no user data.

The Stage-2 isolation bar is pulled forward, not deferred: internal/adapters/store/rls_test.go runs two users against a non-superuser, non-BYPASSRLS role and asserts user A reads/writes zero of user B's rows across every table. It ships green with the multi-user features (ADR-012); no multi-user feature merges ahead of it passing.

Job / processing state

Execution state (queued / running / retrying) for the watch→resolve→summarize→deliver pipeline is owned by the worker runtime, not modelled as first-class domain tables here. SINK_DELIVERY.status and TRANSCRIPT.source capture the durable, queryable outcomes; a thin jobs table may be added when a "what's processing" view is needed (mirrors the worker queue, doesn't replace it). Deferred until there's a reason.

Explicitly out of scope (Future C)

• Global cross-tenant video dedup (rejected above). Note: cross-tenant transcript sharing is now in scope and shipped (ADR-021); only the videos half stays per-user.
• Sharding / per-tenant physical databases.
• Soft-delete + full audit trail on connections/credentials (a Stage 2 hardening item; add via ADR when Stage 2 work starts).