Data model overview

All durable state in Soundverse 2.0 lives in one PostgreSQL instance, and exactly one service is allowed to open a connection string to it: core-database. The schemas are partitioned by domain, but they share a single database on purpose — so every cross-domain reference can be a real foreign key that Postgres enforces, not a dangling id that application code hopes is valid.

This page is the map of that database: the core entities, how the tiers split (Postgres / Redis / Azure Blob), and the one deploy caveat worth knowing. The deep dives — the money path, the queue, pricing, storage — each get their own page; this is the lay of the land.

The core entity map

erDiagram
  USERS ||--o{ WORKSPACES : owns
  WORKSPACES ||--o{ PROJECTS : contains
  PROJECTS ||--o{ MESSAGES : has
  MESSAGES ||--o| TASKS : triggers
  TOOLS ||--o{ TASKS : runs
  TOOLS ||--o{ TOOL_CONFIGS : priced_by
  TOOL_CONFIGS ||--o{ TOOL_CONFIG_LICENSES : per_license
  USERS ||--|| TOKEN_BALANCES : has
  TOKEN_BALANCES ||--o{ TOKEN_LEDGER : audited_by
  TASKS ||--o{ TOKEN_LEDGER : reserves_settles
  USERS ||--o{ TASKS : owns
  USERS ||--o{ FILES : owns
  TASKS ||--o{ FILES : produces
  FILES }o--|| BLOBS : references
  USERS {
    uuid id PK
    string email
    string username
  }
  TASKS {
    uuid id PK
    task_status status
    uuid tool_id FK
    uuid user_id FK
    bigint reserved_token_amount
    text billing_idempotency_key
    timestamptz settled_at
  }
  TOKEN_BALANCES {
    uuid user_id PK
    bigint base_tokens
    bigint extra_tokens
    timestamptz base_expires_at
  }
  TOKEN_LEDGER {
    uuid id PK
    string idempotency_key UK
    bigint amount
    string reason
  }
  BLOBS {
    string hash PK
    text blob_url
    bigint size_bytes
    string mime_type
  }
  FILES {
    uuid id PK
    uuid user_id FK
    string blob_hash FK
  }

The diagram is a map, not the territory

This ER diagram is illustrative: it collapses sibling tables (profiles, auth identities, configs, variants, metadata side-tables) and labels a few columns by intent rather than exact name. The authoritative shapes are the schema.sql files that sit next to each proto, under soundverse-proto/proto/soundverse_proto/<domain>/v1/. They are the real DDL, applied verbatim at init — when in doubt, read the SQL. The Data schema catalog lists every table.

One database, one door

The schemas are partitioned by domain — the core ones are identity, billing, generation, storage, and chat (with dna, migration, compat, notifications, collabstudio, and growth on the periphery). Because they live in one database, a task can point at a tool with tool_id UUID REFERENCES generation.tools(id), a ledger entry can point at its user with REFERENCES identity.users(id) ON DELETE CASCADE, and a produced file can point at the task that made it. Referential integrity is a database property, not an application convention.

Nothing else touches Postgres. Gateways, tool workers, and core-mcp all reach data only by calling core-database over Connect/gRPC, authenticated with an Authorization: Bearer header carrying the shared INTERNAL_RPC_SECRET. The payoff: the data layer is declared as proto annotations (sql_query, cache_key, require_internal_auth) and code-generated, so there is exactly one place that owns each query and one place that owns its auth. See The contract: proto as source of truth and How the DB codegen plugin works.

Workspaces and projects live in the chat schema

There is no separate workspace schema. chat.workspaces, chat.projects, chat.workspace_members, and chat.messages are all under chat — a chat message can carry a task_id (ON DELETE SET NULL) that ties a conversation turn to the generation it triggered.

The three storage tiers

Not everything is Postgres. State is split across three tiers by durability requirement:

Tier	What it stores	Source of truth?
PostgreSQL	Users, balances, ledger, tasks, tools + pricing, file/blob metadata	Yes — durable, the only authority
Redis	Cache-aside reads, rate-limit windows, task-event streams, task:wake signals	No — short TTLs; reconstructable from Postgres
Azure Blob	The actual file bytes (audio masters, streaming variants, images)	Bytes only — every blob has a row in storage.blobs

Redis is a cache-aside layer: readers consult Redis, fall back to core-database on a miss, and repopulate with a short TTL. Because TTLs are short and Postgres is authoritative, a cold or flushed Redis is a latency event, never a correctness event. Azure Blob follows the same rule — the bytes live in object storage, but the truth about a blob (its hash, size, MIME type, owning files) is a row in storage.blobs. See Storage & media plane for the blob lifecycle.

The entities that matter

Users & identity — JIT on (provider, provider_subject)

identity.users holds stable identity only — no passwords. A login from the external IdP (self-hosted Logto OIDC — the platform migrated off Zitadel) is mapped through identity.user_auth_identities, which is UNIQUE (provider, provider_subject). core-identity provisions a user just-in-time on first sight of that pair, converging on a verified email so one human arriving via multiple connectors links onto the existing same-email user instead of forking a duplicate. Email and username are unique-when-present and case-insensitive (via lower() partial indexes), and soft-deleted rows release them for reuse. Details: Identity & auth.

Token balances & the ledger — money as an auditable primitive

Money lives in two billing tables: billing.token_balances (the current spendable amount, split into an expiring base_tokens bucket and a never-expiring extra_tokens bucket) and billing.token_ledger (an append-only audit trail of every movement). The ledger is built to be untrustable-by-mistake — two structural guarantees:

billing/tokens/v1/schema.sql — enforced by the DB, not by convention

-- History cannot be rewritten; corrections are compensating entries.
CREATE TRIGGER trg_token_ledger_no_mutation
  BEFORE UPDATE OR DELETE ON billing.token_ledger
  FOR EACH ROW EXECUTE FUNCTION billing.reject_ledger_mutation();

-- The same idempotency key can be written at most once.
CREATE UNIQUE INDEX uq_token_ledger_idempotency
  ON billing.token_ledger (idempotency_key)
  WHERE idempotency_key IS NOT NULL;

Together with CHECK constraints and single-row FOR UPDATE locking, these make it impossible to silently lose or double-apply money. The full estimate → reserve → settle lifecycle — and why concurrent settlement can’t double-charge — is The token ledger & the money path.

Tasks — the queue is a Postgres table

There is no separate broker. generation.tasks is the work queue, and Postgres row-locking gives it exactly-one-claim semantics. A task flows through a small status enum — queued → dispatched → processing → completed | failed (dispatched is matched only by the stale-task reaper for never-claimed/legacy rows). Each row carries its own billing context: reserved_token_amount, reservation_ledger_entry_id, billing_idempotency_key, reported_usage, and a settled_at that stays NULL until the reconciler sweeps it. Ownership of a claimed task is a time-based lease (claim_expires_at), not a held lock. The claim/lease/heartbeat/reaper mechanics are Task queue on Postgres.

Tools & pricing — a three-level cascade

A “tool” is a registry row in generation.tools, unique on (environment, model, operation); its input_schema JSONB column is the Pydantic-generated JSON Schema that also drives the schema-driven AI Tools panel. Its price lives in separate rows: generation.tool_configs (scoped platform / workspace / project / user) → generation.tool_config_licenses (one row per license carrying cost_mode, cost_base, cost_increment). A registered tool with no pricing row is the single most common cause of a NotFound at chat time. See Tool pricing model.

Files & blobs — content-addressed, visibility-scoped

storage.blobs is keyed by a content hash; storage.files is the user-facing object (name, tags, owning workspace/project/task) and references its blob by blob_hash. The dedup key is not the raw file hash — it folds in the storage container so identical bytes dedup within a visibility tier but never collapse across tiers (making a private asset public can’t expose a shared one):

core-storage app/grpc_server/servicers/storage.py

db_blob_hash = hashlib.sha256(
    f"{raw_file_hash}:{container_name}".encode()
).hexdigest()

One deduped master can back many storage.files rows and many derived storage.blob_variants. Downloads are served as short-lived Azure SAS URLs minted on demand — the permanent blob_url in the row is never handed to a client. Full pipeline: Storage & media plane. New per-file metadata belongs in storage.attributes, not the metadata JSONB bag — see Add durable per-file metadata.

User-content cascade: what happens when a user is deleted Schema fixed; prod migration not applied

generation.tasks.user_id and storage.files.user_id were historically VARCHAR(255) with no FK to identity.users, so hard-deleting (or recreating) a user orphaned their content — it stayed in the DB but was unreachable, since ListFiles/ListUserTasks scope on the caller’s current uuid. Recreating your own account mints a new uuid and orphans the old library.

The schema source now fixes this: both columns are UUID NOT NULL REFERENCES identity.users(id) ON DELETE CASCADE, mirroring the existing storage.files.task_id → generation.tasks(id) precedent. A fresh init (docker-compose, new environments) gets the FK, because the load order is identity → … → generation.task → … → storage, keeping the cross-schema FK init-safe.

Existing databases still need the out-of-band migration

The inline schema change only helps a database created from scratch. Existing prod carries the old VARCHAR columns until the one-off migration scripts/db/add-user-id-fk-cascade.sql runs — it purges no-live-owner content (a prereq for adding the constraint), converts the columns to uuid, and adds the FK. It is not yet applied to prod. A few sibling tables are deliberately left as VARCHAR without a FK for now: storage.live_sessions, generation.model_credentials / model_rate_limits / tool_configs, and the billing.* tables.

Semantics once applied: a hard delete cascades away the user’s files and tasks (metadata side-tables already cascade off storage.files(id); blobs are ON DELETE RESTRICT and stay, since they are dedup-shared and garbage-collected separately). A soft delete (deleted_at) retains content during a grace window; a retention job hard-deletes long-soft-deleted users to reclaim it.

Related

• The token ledger & the money path — estimate → reserve → settle
• Task queue on Postgres — SKIP LOCKED, leases, the reaper
• Tool pricing model — the three-level pricing cascade
• Storage & media plane — content-dedup and SAS URLs
• Identity & auth — Logto OIDC + JIT provisioning
• core-database — the sole Postgres door
• Data schema catalog — every table, per domain