# UmbraTrans Datastore Specification

> **Version:** 1.0  
> **Status:** Active  
> **Last Updated:** 2026-10-31

This document defines the complete datastore contract that every UmbraTrans utility (web API, workers, CLI tools, importers) must understand. It covers PostgreSQL schema, NFS directory layout, and the invariants each component must respect.

---

## 1. PostgreSQL Schema (Authoritative Store)

### 1.1 Core Identity Tables (Phase 1)

| Table | Purpose | Key Columns |
|-------|---------|-------------|
| `public.users` | Canonical accounts | `id` (uuid, PK), `email` (citext, unique, not null), `username` (citext, unique, nullable), `display_name`, `role`, `status`, `organization_id`, `created_at` |
| `public.user_credentials` | Auth credentials (one per user) | `id` (uuid, PK), `user_id` (uuid, unique, FK), `credential_type` (local_password/oidc), `password_hash` (argon2id), `must_change_password`, `failed_login_count`, `locked_until` |
| `public.sessions` | Server-side sessions | `id` (uuid, PK), `user_id` (FK), `token_hash`, `created_at`, `last_seen_at`, `idle_deadline_at`, `absolute_deadline_at`, `revoked_at` |
| `public.organizations` | Tenants | `id` (uuid, PK), `name`, `slug` (citext, unique), `status` (active/suspended/archived), `created_at` |
| `public.user_roles` | Role assignments | `user_id`, `role_id`, `scope` (organization/project), `organization_id`, `project_id`, `status` |
| `public.audit_events` | Immutable audit log | `id` (bigserial, PK), `occurred_at`, `action`, `outcome`, `actor_user_id`, `target_user_id`, `organization_id`, `detail` (jsonb) |

### 1.2 Project & Work Tables (Phase 2+)

| Table | Purpose | Key Columns |
|-------|---------|-------------|
| `tenant_<org>.projects` | Projects (per-tenant schema) | `id` (uuid, PK), `name`, `slug`, `status`, `created_by`, `created_at` |
| `public.jobs` | Work queue | `id` (uuid, PK), `organization_id`, `project_id`, `lang`, `kind`, `status`, `priority`, `requires` (jsonb), `lease_owner`, `lease_expires_at`, `attempt`, `max_attempts`, `payload`, `result`, `error`, `created_at`, `started_at`, `finished_at` |
| `public.job_events` | Job history | `id` (bigserial, PK), `job_id`, `node_id`, `event`, `detail` (jsonb), `at` |

### 1.3 Compute & Node Tables

| Table | Purpose | Key Columns |
|-------|---------|-------------|
| `public.compute_nodes` | Worker nodes | `id` (uuid, PK), `organization_id`, `name`, `is_controller`, `trust_domain`, `status` (provisioning/online/draining/offline/retired), `last_heartbeat_at` |
| `public.compute_devices` | GPUs/CPUs per node | `id` (uuid, PK), `compute_node_id`, `kind` (cpu/gpu_discrete/...), `label`, `vram_total_mb`, `vram_free_mb`, `throughput_factor` |
| `public.model_registry` | Known models | `id` (uuid, PK), `slug` (unique), `kind`, `vram_min_mb`, `vram_preferred_mb`, `requires_accelerators` |
| `public.node_models` | Model residency | `compute_node_id`, `model_id`, `device_id`, `state` (loaded/warm/available/loading/failed), `vram_held_mb` |
| `public.node_heartbeats` | Node health | `compute_node_id` (PK), `reported_at`, `cpu_busy_pct`, `ram_free_mb`, `vram_free_mb`, `active_jobs`, `resident_models` (jsonb) |

### 1.4 Artifact & Provenance Tables

| Table | Purpose | Key Columns |
|-------|---------|-------------|
| `public.artifacts` | Content-addressed artifacts | `id` (uuid, PK), `project_id`, `organization_id`, `lang`, `kind`, `status` (intermediate/final/superseded), `content_hash` (sha256), `byte_size`, `media_type`, `storage_key`, `storage_uri`, `storage_node_id`, `integrity` (unverified/verified), `verified_at`, `derivation` (jsonb), `parent_artifact_id`, `created_by_job_id`, `created_by_user_id`, `created_at` |
| `public.job_artifacts` | Provenance links | `job_id`, `artifact_id`, `role` (consumed/produced), `created_at` (PK: job_id, artifact_id, role) |
| `public.project_source_refs` | Audio import ledger | `project_id`, `organization_id`, `system`, `node_id`, `external_id`, `source_title`, `languages` (jsonb), `content_hash`, `imported_at` |

### 1.5 Publisher & Byline Tables

| Table | Purpose | Key Columns |
|-------|---------|-------------|
| `public.publishers` | Imprints/publishers | `id` (uuid, PK), `parent_id`, `name`, `slug` (unique), `owner_organization_id`, `kind` (imprint/publisher) |
| `public.project_publishers` | Project→Publisher mapping | `project_id`, `publisher_id`, `lang` (PK: project_id, publisher_id, lang) |
| `public.authors` | Bylines (authors, translators, narrators) | `id` (uuid, PK), `name`, `display_name`, `slug` (unique), `user_id`, `owner_organization_id` |
| `public.project_authors` | Project→Author mapping | `project_id`, `author_id`, `credit_role`, `lang`, `position` (PK: project_id, author_id, credit_role, lang) |

---

## 2. Tenancy Model

### 2.1 Schema-per-Tenant Isolation

- **Platform tables** (`users`, `organizations`, `jobs`, `artifacts`, `compute_nodes`, etc.) live in `public` schema
- **Tenant tables** (`projects`, `chapters`, `utterances`, etc.) live in `tenant_<org_id>` schema
- **Isolation mechanism:** `SET LOCAL search_path = tenant_<org_id>, public` on every transaction
- **RLS policies** enforce organization membership on all platform tables

### 2.2 Connection Protocol

Every database operation MUST:
```python
with pool.connection() as conn:
    with conn.transaction():
        # 1. Set statement timeout
        conn.execute("SET LOCAL statement_timeout = 5000")
        
        # 2. Set caller identity for RLS
        conn.execute("SELECT set_config('app.user_id', %s, true)", (str(user_id),))
        
        # 3. Set tenant schema for isolation
        search_path = tenant_search_path(organization_id)  # "tenant_xxx, public"
        conn.execute("SELECT set_config('search_path', %s, true)", (search_path,))
        
        # 4. Execute queries - unqualified 'projects' resolves to tenant schema
```

### 2.3 Helper Functions (Database-Side)

```sql
-- Returns tenant schema name for an organization
app.tenant_schema_name(org_id uuid) RETURNS text

-- Returns current user from session GUC
app.current_user_id() RETURNS uuid

-- Returns search path for tenant
app.tenant_search_path(org_id uuid) RETURNS text

-- Policy helpers
app.is_platform_admin() RETURNS boolean
app.is_organization_admin(org_id uuid) RETURNS boolean
app.is_organization_member(org_id uuid) RETURNS boolean
app.can_access_project(project_id uuid) RETURNS boolean
app.validate_user_role_scope() -- trigger on user_roles
```

---

## 3. NFS Directory Layout (Shared Storage)

### 3.1 Root Structure

```
/mnt/umbra-data/
├── productions/          # Source productions (read/write by workers)
│   ├── <project>-<lang>/
│   │   ├── source/
│   │   │   ├── book.json           # Project metadata
│   │   │   └── covers/             # Cover images
│   │   ├── runs/
│   │   │   ├── ch-<N>/
│   │   │   │   ├── translation.json
│   │   │   │   ├── ch-<N>-c<M>.analysis.json
│   │   │   │   ├── ch-<N>-c<M>.draft.json
│   │   │   │   └── ch-<N>-c<M>.critic.json
│   │   ├── glossary/
│   │   │   └── glossary.json
│   │   └── bundle/                 # Audio import bundle (after export)
│   │       ├── bundle.json
│   │       └── manifest.jsonl
│   └── ...
├── artifacts/            # Content-addressed artifacts (read/write)
│   ├── <org_id>/
│   │   ├── <project_id>/
│   │   │   ├── <content_hash>[:<kind>][:<lang>]
│   │   │   │   ├── data          # Raw bytes (or symlink to object storage)
│   │   │   │   └── meta.json     # Artifact metadata
│   │   └── ...
│   └── ...
├── covers/               # Cover images (read/write)
│   └── <asset_id>/
│       ├── original.png
│       ├── thumbnail.jpg
│       └── meta.json
├── audio/                # Audio master files (read/write by audio workers)
│   └── <project_id>/
│       ├── <lang>/
│       │   ├── chapters/
│       │   │   └── ch-<N>/
│       │   │       ├── track-<M>.wav
│       │   │       └── track-<M>.mp3
│       │   ├── assembled/
│       │   │   └── book.mp3
│       │   └── meta.json
└── backups/              # Database backups (optional)
    └── postgresql/
        └── <timestamp>.dump
```

### 3.2 Path Conventions

| Data Type | NFS Path | PostgreSQL Reference |
|-----------|----------|---------------------|
| Production source | `/mnt/umbra-data/productions/<project>-<lang>/source/book.json` | `project_source_refs.book_directory` |
| Translation runs | `/mnt/umbra-data/productions/<project>-<lang>/runs/` | `artifacts.storage_uri` (file://) |
| Audio renders | `/mnt/umbra-data/audio/<project_id>/<lang>/` | `artifacts.storage_uri` (file://) |
| Cover images | `/mnt/umbra-data/covers/<asset_id>/` | `format_metadata.cover_front_asset_id` |
| Artifact bytes | `/mnt/umbra-data/artifacts/<org>/<project>/<hash>/data` | `artifacts.storage_key` |

### 3.3 Access Patterns

| Component | Productions | Artifacts | Covers | Audio |
|-----------|-------------|-----------|--------|-------|
| Web API | Read | Read | Read/Write | Read |
| Translation Worker | Read/Write | Read | Read | Read |
| Audio Worker | Read | Read/Write | Read | Read/Write |
| Import Job | Read | Write | Write | Write |

---

## 4. Invariants & Contracts

### 4.1 Content Addressing (Artifacts)

- **Identity = SHA256 hash of bytes** (`content_hash`)
- **Storage key ≠ Identity** — `storage_key` is location, `content_hash` is identity
- **Deduplication:** Same hash = same artifact (within project scope)
- **Immutability:** `artifacts` table has `BEFORE UPDATE` trigger preventing `content_hash`/`storage_key` changes
- **Supersession:** New version = new row with `parent_artifact_id` pointing to previous

### 4.2 Job Queue Semantics

```sql
-- Claim a job (atomic, skip locked)
UPDATE public.jobs
SET status = 'running',
    lease_owner = $node_id,
    lease_expires_at = now() + interval '30 minutes',
    started_at = now(),
    attempt = attempt + 1
WHERE id = (
    SELECT id FROM public.jobs
    WHERE status = 'queued'
      AND not_before <= now()
      AND (lease_expires_at IS NULL OR lease_expires_at <= now())
      AND organization_id = $org_id
    ORDER BY priority, not_before, created_at
    FOR UPDATE SKIP LOCKED
    LIMIT 1
)
RETURNING *;
```

- **Lease expiration** returns job to `queued` (handled by controller or job timeout)
- **Idempotency key** prevents duplicate submission
- **Parent job** links for decomposition (one job → many sub-jobs)

### 4.3 Audio Import Idempotency

```python
# Deterministic IDs
project_id = uuid5(AUDIO_IMPORT_NAMESPACE, f"{org_id}:{system}:{external_id}")
import_id  = uuid5(AUDIO_IMPORT_NAMESPACE, f"{project_id}:{content_hash}")

# Import logic
existing_hash = store.last_content_hash(external_id)
if existing_hash == content_hash:
    return "no_change"  # Idempotent
# Else: DELETE old source rows, INSERT new snapshot, INSERT import ledger row
```

### 4.4 Worker Job Contract

Each worker mode polls for specific job kinds:

| Worker Mode | Job Kinds | Required Resources |
|-------------|-----------|-------------------|
| `translation` | `translate`, `analyze`, `critic`, `backtranslate` | GPU (LLM), CPU |
| `audio` | `tts`, `asr`, `assemble`, `encode`, `master` | GPU (TTS), CPU, Audio storage |

---

## 5. Component Responsibilities

### 5.1 Web API (`umbratrans-web`)

- **Reads:** Users, orgs, projects, jobs, artifacts, format metadata
- **Writes:** Users, orgs, projects, format metadata, cover uploads, job queue (enqueue)
- **NFS:** Reads productions for status, reads/writes covers, reads artifacts for download
- **DB:** Uses `PostgresAccountRepository` + direct queries for project status

### 5.2 Translation Worker (`umbratrans-worker translation`)

- **Polls:** `jobs` WHERE `kind IN ('translate','analyze','critic','backtranslate')`
- **Reads:** Source text from productions NFS, project metadata from DB
- **Writes:** Translation artifacts to NFS + DB, job results to DB
- **External:** Calls Ollama/LLM API for translation

### 5.3 Audio Worker (`umbratrans-worker audio`)

- **Polls:** `jobs` WHERE `kind LIKE 'audio%'`
- **Reads:** Translation artifacts from NFS, speaker profiles from DB
- **Writes:** Audio renders to NFS (`/audio/`) + artifact rows to DB
- **External:** Calls TTS/ASR models (local or remote)

### 5.4 Import CLI (`umbratrans-import-productions`)

- **Reads:** Productions from NFS (`/productions/`)
- **Writes:** Audio import tables (`project_source_refs`, `translation_chapters`, `translation_speakers`, `translation_utterances`, `artifacts`, `translation_imports`)
- **NFS:** Reads bundle, writes artifacts to `/artifacts/`
- **Idempotent:** Re-runnable, skips unchanged content

### 5.5 Migration CLI (`umbratrans-migrate`)

- **Applies:** All `.sql` files in `umbratrans_web/migrations/` (except rollback)
- **Order:** Lexicographic by filename (0001, 0002, ...)
- **Transaction:** Each migration in its own transaction
- **Idempotent:** Uses `IF NOT EXISTS`, `ON CONFLICT`, guarded `ALTER TABLE`

---

## 6. Environment Variables

### 6.1 Required for All Components

| Variable | Description | Example |
|----------|-------------|---------|
| `UMBRA_ENV` | Environment: development/test/staging/production | `production` |
| `UMBRATRANS_DATABASE_DSN` | PostgreSQL connection string | `postgresql://user:pass@host:5432/db?sslmode=require` |
| `UMBRATRANS_PRODUCTIONS_ROOT` | NFS productions mount | `/mnt/productions` |
| `UMBRATRANS_TOKEN_PEPPER` | 32+ byte secret for token signing | `base64-encoded` |

### 6.2 Web API Specific

| Variable | Description |
|----------|-------------|
| `UMBRATRANS_REPOSITORY` | `postgres` (production) / `memory` / `json` (dev) |
| `UMBRATRANS_ALLOWED_HOSTS` | Comma-separated host allowlist |
| `UMBRATRANS_VERIFICATION_BASE_URL` | Absolute URL for email links |
| `UMBRATRANS_COOKIE_SECURE` | `true` in production |
| `UMBRATRANS_REQUIRE_CSRF` | `true` in production |
| `UMBRATRANS_SMTP_*` | SMTP configuration |
| `UMBRATRANS_STATIC_ROOT` | Static files path (`/app/web`) |

### 6.3 Worker Specific

| Variable | Description |
|----------|-------------|
| `OLLAMA_BASE_URL` | LLM API endpoint (`http://ollama:11434/v1`) |
| `UMBRATRANS_WORKER_POLL_INTERVAL` | Queue poll seconds (default: 5) |

---

## 7. Kubernetes Deployment Contract

### 7.1 Required Resources

| Component | CPU | Memory | GPU | NFS Mounts |
|-----------|-----|--------|-----|------------|
| Web API | 250m-1000m | 256-512Mi | No | productions (ro), artifacts (rw) |
| Translation Worker | 2000m-4000m | 2-4Gi | 1x GPU | productions (rw), artifacts (rw) |
| Audio Worker | 2000m-4000m | 4-8Gi | 1x GPU | productions (ro), artifacts (rw), audio (rw) |

### 7.2 Service Discovery

| Service | DNS | Port | Purpose |
|---------|-----|------|---------|
| `umbratrans-web` | `umbratrans-web.umbratrans.svc` | 80 | Web API + UI |
| `umbratrans-db-rw` | `umbratrans-db-rw.umbratrans.svc` | 5432 | Primary DB (writes) |
| `umbratrans-db-ro` | `umbratrans-db-ro.umbratrans.svc` | 5432 | Replica DB (reads) |
| `umbratrans-db-pgbouncer` | `umbratrans-db-pgbouncer.umbratrans.svc` | 6432 | Pooled connections |
| `ollama` | `ollama.umbratrans.svc` | 11434 | LLM API |

### 7.3 Health Checks

```yaml
livenessProbe:
  httpGet:
    path: /api/health
    port: 8442
  initialDelaySeconds: 15
  periodSeconds: 30

readinessProbe:
  httpGet:
    path: /api/health
    port: 8442
  initialDelaySeconds: 5
  periodSeconds: 10
```

Health endpoint returns:
```json
{
  "status": "ok",
  "service": "umbratrans-web",
  "version": "0.2.0",
  "environment": "production",
  "repository": "postgres",
  "cookie_secure": true,
  "csrf_required": true,
  "mail_deliverable": true,
  "mail_delivery_waived": false,
  "time": "2026-10-31T12:00:00Z"
}
```

---

## 8. Backup & Recovery

### 8.1 PostgreSQL (CNPG)

- **Continuous:** WAL archiving to S3
- **Scheduled:** Daily base backup at 02:00 UTC (retention: 30 days)
- **Point-in-time recovery:** Supported via `barman-cloud-wal-archive`
- **Test:** Monthly restore to staging

### 8.2 NFS Data

- **Productions:** Rsync to offsite daily
- **Artifacts:** Content-addressed, reproducible from source
- **Covers:** Reproducible from source projects

### 8.3 Recovery Procedures

| Scenario | Procedure |
|----------|-----------|
| Web pod crash | Kubernetes restarts (liveness probe) |
| Worker pod crash | Kubernetes restarts, job lease expires, re-queued |
| DB primary failover | CNPG promotes replica (30-60s), connections retry |
| NFS server down | Workers pause (NFS hard mount), resume on recovery |
| Data corruption | Restore PG from backup, re-import productions |

---

## 9. Security Boundaries

| Boundary | Enforcement |
|----------|-------------|
| Tenant isolation | `search_path` + RLS policies on all platform tables |
| API authentication | Argon2id password hash, Secure/HttpOnly/SameSite cookies, CSRF tokens |
| Session management | Server-side sessions with token hash only, idle/absolute timeouts |
| Worker auth | Node credentials (argon2id key hash), scoped to queue operations |
| NFS access | Kubernetes `fsGroup=1000`, NFS `no_root_squash`, directory mode 0700 |
| Database credentials | Stored in Kubernetes Secrets, never in images or configmaps |
| Token pepper | 32+ bytes, in Secret, used for session/verification token HMAC |

---

## 10. Version Compatibility

| Component | Minimum Schema Version | Compatible With |
|-----------|------------------------|-----------------|
| Web API 0.2.x | Migrations 0001-0010 | Workers 0.2.x, Import 0.2.x |
| Workers 0.2.x | Migrations 0001-0010 | Web API 0.2.x |
| Import CLI 0.2.x | Migrations 0001-0010 | Web API 0.2.x |

**Breaking changes require:** New migration file, version bump, coordinated deploy.

---

## 11. Quick Reference: Key SQL Queries

```sql
-- Get project with all languages status
SELECT p.*, 
       jsonb_agg(jsonb_build_object(
         'lang', l.language_tag,
         'translation', l.translation_status,
         'audio', l.audio_status
       )) as languages
FROM tenant_<org>.projects p
LEFT JOIN tenant_<org>.project_languages l ON l.project_id = p.id
WHERE p.id = $1
GROUP BY p.id;

-- Queue depth by kind
SELECT kind, status, count(*)
FROM public.jobs
WHERE organization_id = $1
GROUP BY kind, status;

-- Artifact storage usage
SELECT organization_id, sum(byte_size) as total_bytes, count(*) as count
FROM public.artifacts
GROUP BY organization_id;

-- Node capacity
SELECT cn.id, cn.name, cn.status,
       jsonb_agg(jsonb_build_object(
         'kind', cd.kind,
         'label', cd.label,
         'vram_free', cd.vram_free_mb,
         'throughput', cd.throughput_factor
       )) as devices
FROM public.compute_nodes cn
LEFT JOIN public.compute_devices cd ON cd.compute_node_id = cn.id
WHERE cn.status = 'online'
GROUP BY cn.id;

-- Import history
SELECT pi.*, o.name as org_name
FROM public.translation_imports pi
JOIN public.organizations o ON o.id = pi.organization_id
ORDER BY pi.imported_at DESC
LIMIT 20;
```

---

## 12. Appendix: Migration Order

```
0001_phase1_identity.sql          -- Users, credentials, sessions, organizations
0002_password_reset_purpose.sql   -- Password reset tokens
0003_compute_and_queue.sql        -- Compute nodes, devices, models, jobs, artifacts, publishers, authors
0004_node_pairing.sql             -- Node pairing protocol
0005_tenant_schemas.sql           -- Tenant schema creation trigger
0006_node_removal_and_assignments.sql -- Node lifecycle, project assignments
0007_external_artifacts.sql       -- Artifact storage_uri, integrity
0008_audio_source_import.sql      -- Audio import tables (project_source_refs, etc.)
0009_translation_import_runs.sql  -- Translation import tracking
0010_utterance_translations.sql   -- Utterance translation tables
```

**Apply with:** `kubectl apply -k k8s/overlays/prod` (includes migration init container)

---

*This specification is the source of truth for all datastore interactions. Any component that reads or writes data must conform to these contracts.*