Data

Postgres is the source of truth. Redis holds cache, rate limits and the job queue, and is never authoritative.

Identity and access

erDiagram
    accTitle: Identity and access tables
    accDescr: A user registers many devices. A device is bound to many sessions. A user has exactly one preferences row. Users have id, phone hash, locale and deleted at. Devices have id, user id, install id, model and last seen at. Sessions have id, device id, token hash and expires at. Preferences have user id, language, speech rate and wake word.
    users ||--o{ devices : registers
    devices ||--o{ sessions : "bound to"
    users ||--|| user_preferences : has

    users {
        uuid id PK
        text phone_hash UK
        text locale
        timestamptz deleted_at
    }
    devices {
        uuid id PK
        uuid user_id FK
        text install_id UK
        text model
        timestamptz last_seen_at
    }
    sessions {
        uuid id PK
        uuid device_id FK
        text token_hash UK
        timestamptz expires_at
    }
    user_preferences {
        uuid user_id PK
        text language
        integer speech_rate
        text wake_word
    }

Activity and consent

erDiagram
    accTitle: Activity and consent tables
    accDescr: A user gives many consent grants, generates many command events and pays many subscriptions. Consent grants have id, user id, scope, granted and created at. Command events have id, user id, outcome, duration in milliseconds, confidence and created at. Subscriptions have id, user id, status and renews at.
    users ||--o{ consent_grants : gives
    users ||--o{ command_events : generates
    users ||--o{ subscriptions : pays

    consent_grants {
        uuid id PK
        uuid user_id FK
        text scope
        boolean granted
        timestamptz created_at
    }
    command_events {
        uuid id PK
        uuid user_id FK
        text outcome
        integer duration_ms
        real confidence
        timestamptz created_at
    }
    subscriptions {
        uuid id PK
        uuid user_id FK
        text status
        timestamptz renews_at
    }

There is no transcripts table and no audio table. That is the design, not an omission.

Audio exists in memory for the duration of one request. Transcripts exist long enough to reach the planner and are discarded with the request.

command_events in detail

The only table with meaningful volume.

Column	Type	Null	Notes
id	uuid	no	Also the request id, propagated to every log line
user_id	uuid	no	FK, cascade on user deletion
outcome	text	no	done · failed · rejected · blocked · cancelled
duration_ms	integer	no	End of speech to terminal state
confidence	real	yes	Null when the command never reached the gate
stage_timings	jsonb	no	Per-stage milliseconds for latency analysis
created_at	timestamptz	no	Partition key

No transcript column, no audio reference, no app name. The row records that a command happened and how it went, never what was said.

Index	Columns	Serves
command_events_user_created_idx	(user_id, created_at desc)	Per-user history
command_events_created_idx	created_at	Aggregation jobs
command_events_outcome_idx	(outcome, created_at)	Failure-rate dashboards

Growth and retention

At ten thousand users the table takes roughly nine million rows a month. Two decisions follow, both cheaper now than later:

• Partition monthly from the first migration. Retro-partitioning a live table is a migration with downtime.
• Keep 90 days of raw events, then roll up to daily per-user aggregates and drop the partition. Detaching a partition is instant; deleting 27 million rows is not.

Consent is append-only

consent_grants is never updated. Revocation writes a new row with granted = false. Current state is the latest row per (user_id, scope).

That costs one index and buys an auditable history of exactly what a user agreed to, and when.

Deletion

DELETE users
  → devices, sessions, consent_grants,
    user_preferences, subscriptions, command_events   (CASCADE)
  → retained audio in object storage                  (job, not a cascade)

Object storage sits outside the transaction. Deletion enqueues a job, the job retries until the objects are gone, and the user is told deletion is complete only when it succeeds.

In the repository

The schema is defined with Drizzle in apps/api/src/shared/database/schema.ts. Migrations live in apps/api/drizzle/, each with a matching rollback in drizzle/down/.

• npm run db:migrate applies pending migrations.
• npm run db:rollback reverts the latest one.
• create_command_events_partition(date) creates a month’s partition, and is safe to call repeatedly. The first migration creates the current month and the next two, plus a default partition that catches rows if a month is ever missing.

Deletion is a durable job in deletion_jobs (Postgres is authoritative; Redis only wakes the worker). The request sets users.deleted_at, which stops every credential at once. The worker clears the user’s storage prefix through StorageService, then deletes the user row, which cascades through every table, and only then marks the job completed. Failures retry with backoff; after DELETION_JOB_MAX_ATTEMPTS the job is failed and an alert is logged. Clients poll GET /v1/consent/user-data/jobs/{taskId}. Audio retention is not built, so the default storage driver (none) refuses writes and deletion truthfully removes zero objects.

Other tables added for production: admin_users, admin_permissions, admin_sessions, audit_logs (append-only; target_user_id deliberately has no foreign key so the record outlives the user), vocabulary_terms, and users.status / users.suspended_at. users.phone_hash is now nullable: only a verified number may link installs, and no verification exists yet.