# Database

## Status and authority

PostgreSQL 17 with pgvector is declared in Compose. SQLAlchemy models and an explicit Alembic
foundation migration now cover installation state, workspaces, users, memberships,
roles/permissions, device sessions, login events, reset tokens, approvals, idempotency, audit,
outbox records, durable executions, execution steps, runtime agents, model-routing profiles,
computer profiles/sessions, control leases, browser actions, artifacts, checkpoints, and durable
workspace emergency-stop state. Separate migrations add execution/agent/computer tables and
backfill runtime/settings/computer permissions, default agent browser tools, and default-role
mappings for existing installations. Upgrade,
downgrade, re-upgrade, and model-drift checks run automatically against
SQLite; PostgreSQL migration and append-only behavior CI is configured but has not yet run.

PostgreSQL is authoritative. Redis is limited to queues, locks, caching, rate limits, and ephemeral
coordination.

## Conventions

- Current identifiers use application-generated UUIDv4. A future ordered-ID change requires an
  explicit migration and compatibility review.
- Use `timestamptz` in UTC and retain an IANA timezone on the workspace/user for presentation.
- Every workspace-owned table has a non-null `workspace_id` foreign key and workspace-led indexes
  for common access paths.
- Tenant-local natural keys use compound uniqueness, for example
  `unique(workspace_id, normalized_email)`.
- Foreign keys between owned records include or validate workspace ownership; application services
  must reject cross-workspace references.
- Use explicit lifecycle/status fields and check constraints. Do not encode durable state only in
  queue payloads.
- Store money as integer minor units plus ISO currency, never floating point.
- Store secrets only as encrypted ciphertext plus key version and metadata.
- Prefer append-only records for audit and execution transitions. Soft deletion is not a substitute
  for domain-specific retention or privacy deletion.

## Foundation schema

### Identity and authorization

- `workspaces`: identity, display name, timezone, status, settings version.
- `users`: login identity and security state; not owned by one workspace.
- `workspace_memberships`: workspace/user relationship, status, invited/accepted metadata.
- `roles`: system-created or workspace-defined role, always scoped by non-null `workspace_id`.
- `permissions`: globally stable permission names such as `email.send`.
- `role_permissions`: role-to-permission mapping.
- `membership_roles`: membership-to-role mapping.
- `device_sessions`: hashed session and CSRF token identifiers, device metadata, expiry, revoked time.
- `login_events`: append-only success/failure and security metadata with retention controls.

Authorization queries begin from an authenticated user and active membership. A client-supplied
`workspace_id` never establishes membership.

### Agents and policy

- `agents` (implemented): workspace, creator, current identity/configuration, status, autonomy level,
  model identifiers, manager/escalation relationships, budget/execution limits, and version counter.
- `agent_versions` (implemented): instruction/personality and configuration snapshots for every
  create/edit/lifecycle change.
- `agent_permissions` and `agent_tools` (implemented): explicit permission grants and registered tool
  assignments. Tool constraints are reserved but currently empty.
- `agent_schedules` (implemented): timezone, working days, bounded daily window, and enable state.
- `ai_model_profiles` (implemented): workspace-owned provider/model routing metadata and declared
  capabilities. It contains no API keys or other credential material, and referenced profiles cannot
  be deleted.
- `action_policies`: risk/action/contact/hour rules and policy version.
- `approval_requests`: proposed immutable action snapshot, risk, exact content, requester, state,
  expiry, and decision metadata.
- `approval_rules`: narrowly scoped grants created by decisions such as “always allow similar.”
- `agent_delegations` (implemented initial request state): source/target, requester, instruction/hash,
  intersected permission scope, status, and timestamps. Queue/execution linkage remains later work.

Parent relationships must remain within a workspace and be cycle-checked in application logic (and
with defensive constraints where practical).

### Executions and reliability

- `executions` (implemented initial subset): workspace/actor, instruction and canonical hash,
  planning status, provider/model references, typed plan, token counts, lifecycle timestamps,
  version, error code, stop state, and selected workspace-bound agent. Budget enforcement,
  encrypted-content reference, cost, and full
  checkpoint fields remain later migrations.
- `execution_steps` (implemented initial subset): workspace-bound ordered proposed steps with trusted
  tool/permission/risk/access metadata, arguments, dependencies, status, and timestamps.
  The current runtime stores result and verification evidence in the step checkpoint; richer
  attempts, redaction, encrypted payloads, and recovery metadata remain later migrations.
- `action_intents`: normalized external side effect, idempotency key, authorization decision,
  approval reference, execution status, provider reference, and verification result.
- `audit_events`: append-only actor/action/resource/outcome records with request and execution IDs.
- `outbox_events`: aggregate, event type/version, payload, available time, published time, attempts.
- `consumer_receipts`: consumer/event uniqueness for idempotent event processing.

The execution and execution-step composite foreign keys prevent a workspace label from pointing at
another workspace's execution. `unique(workspace_id, idempotency_key)` is required for future action intents. Payloads containing sensitive
content should reference encrypted/redacted storage rather than duplicate plaintext into every log.

Workspace memberships and roles expose composite candidate keys. Session/reset identities and RBAC
mapping tables use composite foreign keys so a forged workspace label cannot reference another
workspace's member or role. Audit sequence/head lives on the locked workspace row; events have a
unique workspace sequence and database triggers reject update/delete. Login evidence is also
append-only. A singleton installation row is seeded by migration and locked during bootstrap so
concurrent or repeated owner setup cannot succeed.

### Persistent computers

- `computer_profiles`: workspace-owned persistent-browser identity, randomized storage key,
  viewport, locale/timezone, lifecycle, retention, and version. Storage keys are never returned by
  public APIs.
- `workspace_control_states`: one durable workspace row for emergency-stop state, reason, actor,
  generation, and timestamps. New computer activity checks this authoritative state.
- `computer_sessions`: workspace/profile/agent/execution binding, worker state, current URL/tab,
  current/upcoming action summaries, failure state, lifecycle timestamps, lease version, and version.
- `computer_control_leases`: exclusive AI/human/system ownership, actor, expiry/heartbeat, release
  reason, and monotonically increasing fencing token. The session/workspace composite foreign key
  prevents relabeling a lease across tenants.
- `computer_actions`: actor/tool, redacted parameters/result, risk/access type, canonical intent hash,
  approval/verification/error state, duration, and lifecycle timestamps.
- `computer_artifacts`: workspace/session/action metadata, isolated worker storage key, MIME, SHA-256,
  size, retention expiry, and lifecycle state. Core verifies content hash/size before delivery.
- `computer_checkpoints`: ordered session snapshots with action linkage, URL, active tab, tab list,
  redacted state, screenshot artifact reference, and checkpoint type.

Browser actions also use the existing `idempotency_records` and `approval_requests`. Computer
session recovery reuses the same profile/session identity and appends a recovery checkpoint; it does
not create a fake success record.

The current Compose foundation uses one database credential for migrations and the runtime. Before
production, introduce separate migration, runtime, and audit roles; verify that the runtime cannot
alter schema or disable append-only controls and that ordinary agents cannot mutate audit records.

### Integrations and secrets

- `integrations`: provider/type, workspace, connection state, capabilities, external account metadata,
  last health check, and error state.
- `integration_credentials`: encrypted token material, key version, expiry, scopes, rotation metadata.
- `webhook_deliveries`: provider event ID, signature result, received time, processing status.

Connection state is explicit: `unconfigured`, `pending_authorization`, `connected`, `degraded`,
`expired`, or `disconnected`. Missing credentials never imply a connected integration.

## Later-phase aggregates

Contacts/CRM, tasks/reminders, messages/email/calendar, memories/embeddings, general files, calls,
and automations are later migrations. The initial computer schema supports multiple persistent
profiles per workspace, one active runtime per profile, and a single fenced controller per session.

## Isolation and database defense

Application query scoping and authorization are mandatory. PostgreSQL row-level security should be
evaluated once connection/session context is designed; if adopted, it is defense in depth rather
than a replacement for service authorization. Integration tests must attempt cross-workspace reads,
writes, indirect foreign-key references, searches, exports, and queued jobs.

## Migration and recovery rules

- Migrations are forward-safe and compatible with rolling worker deployment where possible.
- Destructive changes use expand/backfill/contract and require a verified backup.
- Seed only stable roles, permissions, system configuration, and agent templates—never fake user data.
- Backups cover PostgreSQL plus separately managed browser profiles/files. Restore testing is a release
  gate, not merely a documented command.
- Retention jobs must be auditable and respect legal/security holds and user deletion workflows.