Backend Data Architecture + Database Lifecycle

Purpose: Living reference for multi-DB topology, isolation model, data flows, and lifecycle policies across the OpenAlice ecosystem. Updated 2026-06-21 via evidence-based codebase survey. Every claim cites a file path or migration name. Unverified items are flagged [unverified].

1. DB Topology Table

The ecosystem runs 17 distinct logical databases across 13 Postgres instances (some instances host multiple logical databases on a shared PG server — the platform-db exception documented below) plus 1 Redis instance, and 1 MinIO object-store (selfhosted distribution only).

1.1 Core Dev Stack (openalice compose project)

Env vars (from openalice/.env.dev):

• A2A_DB_PASSWORD → utbQdiuPfhkd5k6ucZk6DcfR7xTXJEW
• EMBED_VISITOR_DB_PASSWORD → 88c1a1c55865c4044d7c9922d35117b70023b137a9dcadcf

1.2 External / Sibling Compose Projects (attached to openalice via shared Docker network)

These are NOT defined in openalice/docker-compose.dev.yml — they belong to sibling compose projects accessed via Docker's platform or openalice networks. The openalice-platform compose project directory is not checked out to disk on this server.

Env vars (from openalice/.env.dev):

• PLATFORM_DB_PASSWORD → platformdev123 (dev default)
• TENANTS_APP_DB_PASSWORD → openaliceappdev123 (dev default)
• RAG_DATABASE_URL → postgres://rag:aa067155791f4446160d1d87fa07082e64337080b6ebfe06@openalice-rag-db-dev:5432/rag

IMPORTANT ISOLATION EXCEPTION: openalice-platform-db-dev hosts 4 logical databases on one PG instance, violating the org convention ("no shared DB instances"). This is a known architectural debt — see Gap #4 in Section 5.

1.3 openalice-atlas Stack

Env vars (from openalice-atlas/.env.dev):

• ATLAS_DB_URL → postgres://atlas:atlas-dev-9b20b393e59b@atlas-db:5432/atlas

Zone isolation state: atlas-db is the one intentional exception to the per-instance rule — it stores BOTH zone='openalice' and zone='external' rows in the same instance. Physical split into atlas-ext-db is planned (Phase 3; see Section 5, Gap #1).

1.4 Service-Specific Postgres Instances

Each of the following services owns a dedicated, isolated Postgres instance on its own Docker network — conforming to the org convention.

Additionally, openalice-vault, openalice-passport, openalice-clone-lab, openalice-auth, openalice-social-api, openalice-live-control, and openalice-chat-bridge each manage their own DB but share the `openalice-platform-db-dev` PG instance in dev (see 1.2). Each has isolated logical databases on that shared instance.

1.5 Test-Only Instance

1.6 Redis

1.7 Object Storage (selfhosted distribution only)

1.8 Complete Logical Database Registry

2. Isolation Model

2.1 Per-Instance Convention (Org Rule)

The org convention is "no shared DB instances" — each service owns its own Postgres, its own Docker network, and no service reaches directly into another's DB. This holds for most of the stack (a2a, embed-visitor, rag, atlas, persona, radio, bluegenie, vault, passport, clone-lab each isolated).

Exception: openalice-platform-db-dev hosts 4 logical databases on one PG instance: openalice_platform, openalice_tenants, openalice_live_control, openalice_chat_bridge. These are logically isolated (separate schemas/databases) but physically co-located. This is the primary isolation debt (Gap #4).

2.2 Postgres Row-Level Security (RLS)

Three distinct RLS key patterns are used:

#### 2.2.1 app.tenant_id (openalice-tenants)

Applied to all tenant-scoped tables in openalice_tenants. Session variable set per-request via db::set_tenant_context / db::with_tenant_context in openalice-tenants/src/.

Policy pattern:

USING (tenant_id::text = current_setting('app.tenant_id', true))

FORCE RLS rollout (migrations 0038_rls_force_pilot.sql through ~0056):

• 0038 — pilot on tenant_consent (gated behind TENANTS_RLS_ENFORCE env flag)
• 0039 — coverage added to sso_login_states, tenant_voices, tenant_vrm_assets, widget_audit, widget_experiments, widget_kb_uploads, widget_webhooks
• 0040–0049 — FORCE applied one table at a time: agent_pairings, tenant_quotas, tenant_vrm_assets, tenant_voices, widget_audit, data_export_jobs, audit_log, invites/members, widgets, widget sub-resources, SSO/billing tables, balance_transactions, feature_flags

Known caveat — CORRECTED 2026-06-21 (verified against live containers): This survey claim was WRONG. openalice_app is in fact NOSUPERUSER NOBYPASSRLS (verified pg_roles: rolsuper=f, rolbypassrls=f), and `openalice-tenants-dev` connects as `openalice_app` with `TENANTS_RLS_ENFORCE=true` → tenant RLS IS enforced live. Only openalice-auth-dev still connects as the bootstrap superuser platform. See Gap #3 (corrected).

Additionally, migration 0038 found doubly-dead policies in dev: db::set_tenant_context had zero callers at the time of writing, so app.tenant_id was never being set. Status of callers as of survey: with_tenant_context callers now exist in openalice-tenants/src/.

Widget-level privacy controls (migration 0025_widget_privacy_controls.sql):

• cookieless BOOLEAN DEFAULT false — skips durable visitor ID
• pii_redaction BOOLEAN DEFAULT true — scrubs emails, phone numbers, card-like digit runs from transcript content before persistence

#### 2.2.2 app.user_id (openalice-auth)

Applied to all user-scoped tables in openalice_platform. Migration 014_user_id_rls.sql enables + FORCE on all user-scoped tables. Policy allows empty-string branch for unauthenticated paths (signup, login, password reset). Same SUPERUSER caveat applies.

#### 2.2.3 app.owner_id (openalice-vault, openalice-passport, openalice-clone-lab)

Asymmetric in passport: SELECT is public (USING (true)), INSERT/UPDATE owner-scoped. Vault and clone-lab: symmetric owner isolation. FORCE toggled at runtime via env flags (VAULT_RLS_ENFORCE, PASSPORT_RLS_ENFORCE, CLONELAB_RLS_ENFORCE).

#### 2.2.4 Query-level tenant filter (openalice-rag)

rag_chunks table has NO RLS. Isolation is done entirely by WHERE tenant_id = $1 at the application query level. This is intentional (Mistral embedding table with IVFFlat ANN index — RLS overhead on vector scans was avoided) but is a security risk if any query path omits the filter.

2.3 Atlas Zone Isolation (the exception)

atlas-db physically co-locates zone='openalice' and zone='external' rows. Isolation is logical only — a WHERE zone= filter in api/src/flow.rs and api/src/routes.rs. API surface: ?zone=openalice (default), ?zone=external, ?zone=all. Unknown values fall back to openalice.

Current external projects indexed: osthud and exposefy/immoclip (bind-mounted read-only into ingestor containers, scanned every 30 min). Isolation tested by api/tests/zone_isolation.rs.

Zone evolution (migrations):

• 0035_zones.sql — 3-tier: openalice / lab / external
• 0040_drop_lab_add_product_group.sql — collapsed to 2-tier: openalice / external (no row was ever in lab); also added product_group TEXT to repos for soft grouping
• 0041_scan_enabled.sql — scan_enabled BOOLEAN + scan_root TEXT per-repo opt-in gate

Planned Phase 3 (not yet implemented): physical split into atlas-ext-db (separate pgvector/pg17 instance) — zone becomes a pool selector, not a WHERE clause. See Section 5, Gap #1.

3. Data Flows

3.1 Inter-Service Communication Patterns

Authentication header: X-Internal-Secret (value: INTERNAL_BROADCAST_SECRET). Used by all service-to-service HTTP calls for internal endpoint authentication. Defined in crates/modules-connectors/src/connectors/a2a/delivery_adapter.rs.

Shared JWT: SHARED_JWT_SECRET — used by gateway JWT middleware, voice, rt, a2a, persona, radio for token verification. The atlas stack uses a DIFFERENT SHARED_JWT_SECRET value (see openalice-atlas/.env.dev).

Rule verified: No service reaches into another service's database directly. All cross-service data access goes through HTTP internal APIs.

3.2 Data Flow Table

3.3 RAG Shared-Pool Note

openalice-rag-db is the only DB accessed by two distinct services (tenants writes, agent-lite reads). Both use the same RAG_DATABASE_URL connection string and connect as the rag PG user. There is no row-level ownership (see 2.2.4). This is a design choice to avoid a cross-service API hop for vector retrieval; the trade-off is that agent-lite can theoretically read any tenant's chunks if it omits the WHERE tenant_id= filter.

4. Lifecycle Per Database

4.1 Schema Migrations

All databases use sqlx forward-only migrations (migrations/NNNN_*.sql). No migration reversals — rollbacks are handled by forward-porting fixup migrations.

4.2 Backups

Existing backup coverage:

⚠️ CORRECTION (2026-06-21, post-survey verification against the live server crontab). This survey checked the repos and missed the server-level backup infra — the original "only atlas / all others none" finding below was WRONG. The live crontab runs: - `/home/blal/projects/_shared/scripts/backup-postgres.sh` nightly at 03:00 — discovers ALL running Postgres containers (by env signature), pg_dumpall + gzip each, AES-256-encrypts at rest (BACKUP_KEY_FILE/BACKUP_PASSPHRASE), optional off-site copy (BACKUP_OFFSITE_DEST), 7-daily retention, with a tested restore (test-restore.sh + README-DR.md). Built in the 2026-06-14 Wave-B audit. This already covers every OpenAlice DB. - `/home/blal/projects/_shared/scripts/pg-pitr-backup.sh` — PITR for `openalice-platform`: WAL archiving every 15 min + nightly base backup + prune. So the most critical instance (auth/sessions/billing-adjacent) already has point-in-time recovery. So Gap #2 below is largely CLOSED and Gap #4 (PITR) is partially closed (platform). Remaining real items: extend PITR to openalice_tenants + atlas-db, and confirm BACKUP_OFFSITE_DEST + off-server key storage are set (else single-host SPOF). The redundant db-backup-all.sh I briefly added has been removed — backup-postgres.sh is the canonical, stronger tool.

The atlas-specific openalice/scripts/atlas-db-backup.sh (custom-format -Fc dump, run before each deploy per the runbook) remains as a deploy-time complement to the nightly backup-postgres.sh.

4.3 Retention Policies

#### 4.3.1 Widget Transcript Retention (openalice-agent-lite)

• embed_conversations.retention_days is copied from widgets.transcript_retention_days at conversation creation time.
• Migration source: openalice-tenants/migrations/0015_widget_transcript_retention.sql — transcript_retention_days INT NOT NULL DEFAULT 90, range 0–3650.
• Sweep: PostgresTranscriptStore::purge_now() (openalice-agent-lite/src/transcript/postgres.rs) runs hourly (3600s interval, spawn_purge_task Tokio background task).
• SQL: DELETE FROM embed_conversations WHERE retention_days > 0 AND started_at < now() - make_interval(days => retention_days)
• Also sweeps: embed_knowledge_gaps and embed_leads (same retention_days pattern).
• embed_messages cascade-deletes via FK ON DELETE CASCADE.
• Idle conversation finalizer: runs every 5 minutes (spawn_idle_end_task, 300s interval).

#### 4.3.2 Atlas Data Retention

• scan_runs: 30-day retention enforced at scan start via runtime DELETE. Index: scan_runs_started_idx ON scan_runs(started_at DESC). Source: migration 0021_scan_runs_retention.sql.
• health_snapshots: 30-day retention. Index: health_snapshots_ts_idx ON health_snapshots(ts DESC).
• repo_deps: 90-day retention. Index: repo_deps_created_at_idx ON repo_deps(created_at DESC).
• All enforced in ingestor runtime (not pg_cron). pg_cron noted in migration comments as an alternative but not activated.

#### 4.3.3 Other Tables Without Explicit Retention Policies

The following tables have no TTL or retention column and accumulate indefinitely:

• visitor_memory — durable visitor profiles in embed-visitor-db [unverified: no TTL column found]
• cost_events — LLM cost tracking in embed-visitor-db [unverified]
• tool_audit_events — tool call audit log in embed-visitor-db [unverified]
• audit_log in openalice_tenants — GDPR-relevant; anonymize path NULLs actor_id but row is kept
• A2A message history — no TTL found in 6 a2a migrations

4.4 GDPR / Art. 17 Deletion

#### 4.4.1 Tenant Deletion (openalice-tenants)

File: openalice-tenants/src/anonymize.rs

Lifecycle: active → suspended → scheduled_delete → deleted_anonymized

Background sweep: Tokio task, 60-second interval, FOR UPDATE SKIP LOCKED (one row per tick). Triggered when status = 'scheduled_delete' AND delete_after < now() (partial index tenants_anonymize_ready_idx).

Per-tenant deletion cascade (single transaction):

1. Replaces owner_email with deleted-<sha256-8hex>@anonymized.local
2. Replaces display_name with Deleted User <hash>
3. Sets status = 'deleted_anonymized', deleted_at = now()
4. NULLs audit_log.actor_id for the tenant
5. Revokes all active agent_pairings

Post-transaction (best-effort, not transactional — potential GDPR breach if these fail):

• Deletes VRM files from disk at $TENANT_VRM_DIR/tenant/<uuid>/
• Deletes tenant_vrm_assets rows
• Calls RagRouter::purge_tenant() — DELETEs all rag_chunks WHERE tenant_id = <uuid> from rag-db
• Logs at ERROR level on failure (breach risk flagged in code comments)

Philosophy: anonymize-not-delete (preserves analytics row integrity, strips PII linkability).

#### 4.4.2 Visitor Erasure (openalice-agent-lite)

• forget_visitor_conversations() and forget_visitor_leads() in PostgresTranscriptStore — hard DELETE (not anonymize).
• Called from widget-level GDPR erasure endpoint.
• Cascade deletes embed_messages via FK ON DELETE CASCADE.

#### 4.4.3 Auth User Deletion [unverified]

Auth manages users and sessions in openalice_platform. No explicit GDPR anonymizer found in the auth crate survey — deletion likely cascades from the tenants GDPR flow via the tenants → auth internal API call during export fan-out.

#### 4.4.4 Cross-Service GDPR Export

tenants fans out GDPR data export requests to social-api via SOCIAL_API_INTERNAL_URL=http://openalice-social-api-dev:3951 and to auth via AUTH_INTERNAL_URL=http://openalice-auth-dev:3960.

4.5 Staging → Production

Current state: No staging tier exists. The server (blal.de) runs dev containers only. Per memory feedback-no-prod-yet-dev-only-2026-05-22.md: blal.de = dev only. No production environment has been provisioned.

The atlas deploy runbook (openalice/docs/atlas-deploy-runbook.md) documents a restore drill and migration procedure but notes the absence of a staging environment as a gap. All schema migrations run directly against the dev DB.

5. Gaps → Roadmap to Ideal Multi-DB Orchestration

Gap #1 — PRIORITY: Atlas zone physical isolation (atlas-ext-db split)

Current: zone='external' rows in atlas-db alongside zone='openalice' data. Isolation is a WHERE zone= filter — a missing filter on any new API endpoint = cross-zone data leak. Verified risk: zone_isolation.rs only tests existing endpoints; new endpoints can silently regress.

Target: Separate atlas-ext-db pgvector/pg17 instance. Zone becomes an sqlx pool selector (atlas_pool vs ext_pool), making leaks structurally impossible. Data migration plan documented in openalice/docs/atlas-deploy-runbook.md (Part 3).

Action:

1. Add atlas-ext-db service to openalice-atlas/docker-compose.yml
2. Add ext_pool: PgPool to atlas-api and atlas-ingestor
3. Route by pool in flow.rs / routes.rs
4. Migrate external rows via \copy + verify + DELETE WHERE zone='external' in atlas-db
5. Drop zone column (no longer needed as filter)

Gap #2 — ~~PRIORITY: Per-DB nightly backup cron~~ → LARGELY CLOSED (corrected 2026-06-21)

CORRECTION: This gap was a survey error — see the ⚠️ note in §4.2. _shared/scripts/backup-postgres.sh already backs up every Postgres container nightly at 03:00 (discover → pg_dumpall → gzip → AES-256 encrypt → optional off-site → 7-day retention + tested restore), and pg-pitr-backup.sh gives openalice-platform PITR. Per-DB nightly backup is DONE. Residual hardening only: (a) extend PITR to openalice_tenants + atlas-db; (b) verify BACKUP_OFFSITE_DEST is set AND the encryption key is stored off-server (else single-host SPOF — backup-postgres.sh logs this loudly); (c) add a backup-freshness check to Grafana.

~~Original (incorrect) finding: Only atlas-db has a backup script; the 12 other databases have zero backup coverage.~~

Action:

1. Wire scripts/atlas-db-backup.sh into a nightly cron (crontab on host or a backup-cron container in the atlas stack)
2. Create scripts/per-db-backup.sh covering every DB: - visitor_memory (agent-lite data — GDPR-sensitive) - openalice_tenants (billing, quotas, widget config) - rag (embedded brand KB) - openalice_platform / auth + social-api - a2a - persona
3. Add backup health check to Grafana dashboard
4. Run and verify first backup for every DB before declaring done

Gap #3 — ~~PRIORITY: Complete the RLS FORCE rollout~~ → MOSTLY DONE (corrected 2026-06-21, verified live)

CORRECTION: The original claim ("ALL RLS FORCE inert because the app role is a superuser") was WRONG — verified against the running containers + pg_roles:

• `openalice-tenants-dev` → connects as `openalice_app` (`NOSUPERUSER NOBYPASSRLS`, confirmed rolsuper=f/rolbypassrls=f) + `TENANTS_RLS_ENFORCE=true`. Tenant RLS is ENFORCED live — the dedicated non-superuser role already exists and is wired (code comment: "Now BITES (app runs as the non-superuser openalice_app)"). The platform-superuser DATABASE_URLs the survey saw are only in stale git worktrees (openalice-wt-*), not the live deploy.
• `openalice-auth-dev` → connects as `platform` (bootstrap superuser, BYPASSRLS). This is the ONE live service still on the superuser role. But auth is the cross-tenant identity store (users/sessions/oauth) — verify whether RLS even applies to its model before treating this as a gap.
• vault/passport/clone-lab: not running in dev (N/A).

Residual (narrow) action: IF auth's data model warrants row isolation, migrate openalice-auth to an auth_app NOSUPERUSER NOBYPASSRLS role (mirror the tenants pattern that already works) + grants + the auth RLS integration tests. Optionally add a CI gate asserting the live app role is not BYPASSRLS. Not the fleet-wide emergency the original survey implied — the multi-tenant surface (tenants) is already protected at both the app layer AND RLS.

Gap #4 — DEBT: openalice-platform-db-dev hosts 4 logical databases (violates convention)

Current: openalice_platform, openalice_tenants, openalice_live_control, openalice_chat_bridge share one PG instance. This means a compromised platform role can read all four databases.

Target: Each logical database on its own PG instance (matching the convention followed by a2a, atlas, persona, radio, etc.).

Action: Split into individual PG containers in a openalice-platform compose project (which is currently not even checked out to disk). This is a significant migration effort — schedule when prod provisioning begins.

Gap #5 — PITR / WAL archiving

Current: No Point-In-Time Recovery is configured on any instance. A pg_dump backup taken at T-24h loses up to 24 hours of data on disk failure.

Target: Enable WAL archiving + continuous basebackup (e.g., pgBackRest or WAL-G) for at minimum openalice_tenants and atlas-db.

Action: Evaluate pgBackRest (simpler self-hosted) vs WAL-G (lighter weight). Configure S3/Hetzner Object Storage destination for WAL archives. Pilot on atlas-db first.

Gap #6 — No staging environment / restore drill

Current: No staging tier. All dev == blal.de. No documented restore drill has ever been executed.

Target: A staging environment that shadows prod structure (ideally a second Hetzner box or a provisioned namespace). Per the atlas deploy runbook: staging migration tests before prod apply.

Action: Provision a staging VPS or Docker namespace. Run pg_restore --clean against a backup copy before every prod deploy to validate restore path.

Gap #7 — Missing retention policies on accumulating tables

Current: visitor_memory profiles, cost_events, tool_audit_events (all in embed-visitor-db) and A2A message history have no TTL or sweep.

Action: Add last_seen_at + a configurable TTL to visitor_memory; sweep inactive visitors after N days. Add retention column to cost_events and tool_audit_events. Evaluate A2A message archival (cold storage after 90 days).

Gap #8 — RAG tenant isolation relies solely on application-level filter

Current: rag_chunks has no RLS. Any query path that omits WHERE tenant_id = $1 exposes all tenants' brand knowledge. Two services share the same pool.

Action (option A — low cost): Add a CREATE POLICY rag_tenant_isolation USING (tenant_id = current_setting('app.tenant_id', true)) + FORCE, with a dedicated NOSUPERUSER rag_app role. Requires setting app.tenant_id in both tenants and agent-lite connections.

Action (option B — alternative): Route all RAG reads through a single internal API (not shared DB pool). agent-lite calls tenants/internal/rag/search instead of hitting the DB directly. Eliminates the shared-pool dual-writer issue.

Roadmap Priority Order

Appendix A: Key File Index

Appendix B: Docker Network Map

proxy         ← Traefik (all public services)
openalice     ← alice, a2a, rt, voice, gateway, auth, social-api, agent-lite, tenants,
                 live-control, chat-bridge, persona-server, radio-server, ml, ml-gateway, etc.
platform      ← auth, social-api, tenants (access to openalice-platform-db-dev)
atlas         ← atlas-db, atlas-api, atlas-ingestor, atlas-ingestor-cron
persona       ← persona-postgres, persona-server, persona-web
radio         ← radio-postgres, radio-server
bluegenie     ← openalice-bluegenie-db, bluegenie service
openalice-platform_platform ← openalice-platform-db-dev, openalice-platform-redis-dev (sibling project)