# 06 — Domain model and database schema

**Status:** Draft. Written 2026-11-07. Implements ADR-0004.

All IDs are prefixed ULIDs (`org_…`, `agt_…`, `conv_…`) for readability in logs and URLs. Every
tenant table has `org_id` and is protected by Postgres row-level security. Timestamps are
`timestamptz`. JSON columns are `jsonb` validated by Zod at the application boundary.

## 1. Entity relationship overview

```mermaid
erDiagram
  ORGANIZATION ||--o{ MEMBER : has
  ORGANIZATION ||--o{ API_KEY : issues
  ORGANIZATION ||--o{ SECRET : stores
  ORGANIZATION ||--o{ AGENT : owns
  ORGANIZATION ||--o{ PARTNER_LINK : "partners (registry)"
  PARTNER_LINK ||--|| ORGANIZATION : "links client org"
  ORGANIZATION ||--o| DEPLOYMENT : "owns (client org)"
  ORGANIZATION ||--o{ CHANNEL_IDENTITY : owns
  ORGANIZATION ||--o{ CONTACT : has
  ORGANIZATION ||--o{ WEBHOOK_DESTINATION : configures
  ORGANIZATION ||--o{ CREDIT_LEDGER : accrues
  DEPLOYMENT ||--|| AGENT : provisions
  AGENT ||--o{ AGENT_VERSION : versions
  AGENT ||--o{ TOOL : defines
  AGENT ||--o{ CAPABILITY_INSTALL : installs
  AGENT ||--o{ CHANNEL_IDENTITY : speaks_from
  AGENT ||--o{ CONVERSATION : holds
  CHANNEL_IDENTITY ||--o{ CONVERSATION : hosts
  CONTACT ||--o{ CONVERSATION : participates
  CONTACT ||--o{ CONSENT_EVENT : records
  CONVERSATION ||--o{ MESSAGE : contains
  CONVERSATION ||--o{ TURN : runs
  CONVERSATION ||--o{ HANDOFF : escalates
  CONVERSATION ||--o{ ACTION : proposes
  CONVERSATION ||--o{ PRESENTATION_INTENT : asks
  TURN ||--o{ TOOL_CALL : performs
  TURN ||--|| TRACE : records
  ACTION ||--o{ ACTION_EVENT : transitions
  AGENT ||--o{ ALERT : schedules
  AGENT ||--o{ EVAL_CASE : tests
  EVAL_RUN ||--o{ EVAL_RESULT : yields
  WEBHOOK_DESTINATION ||--o{ WEBHOOK_DELIVERY : attempts
  CONTACT ||--o{ IDENTITY_CACHE : resolved_as
```

## 2. Tables

### 2.1 Tenancy and access

**organizations** — `id`, `name`, `slug`, `region` (`us | eu | br`), `plan` (`free | starter |
pro | max | enterprise`), `country` (ISO 3166-1 alpha-2), `default_locale` (BCP-47 tag from the supported list, e.g.
`es-MX`, `es-419`, `pt-BR`, `pt-PT`, `it-IT`), `currency` (ISO 4217),
`timezone`, `billing_customer_id` (Stripe/MP, unique), `billing_subscription_id`,
`billing_status` (`active | past_due | canceled`, null before the first subscription) and
`billing_past_due_since` (first failed payment of the current dunning) — T-102: only the billing
provider's signed webhooks and the `billing-grace` sweep write them and `plan` — `settings` jsonb
(`retentionDays { messages ≥ 30 | null, attachments 7–365, traces 7–90 }` — T-141, disclosure
defaults, consent keywords, `billing.noCreditReply`,
`billing.grantRollover`, `billing.autoTopup { enabled, packId, maxPerMonth }` — T-104,
`onboarding { step, done[], agentId, template { vertical, locale }, channel, whatsapp {
connectionType: dedicated | coexistence, startedAt, identityId, isCoexistence, error },
publishedAt, updatedAt }` and `kapsoCustomerId` — T-132),
`legal_hold` boolean (default false; while true the retention jobs delete nothing of the org,
T-141),
`credit_balance` numeric(14,4) (cache of the newest
`credit_ledger.balance_after`, default 0, T-101), `created_at`.

**members** — `id`, `org_id`, `user_id` (Better Auth user, `usr_…`), `role` (`owner | admin | member |
viewer`), `invited_by`, `whatsapp_number` (optional, digits only, T-071), `joined_at`. Unique
(`org_id`, `user_id`) and (`org_id`, `whatsapp_number`) when set. Since T-130 the row mirrors a
Better Auth membership with the same id (`auth.member`, see [ADR-0009](adr/0009-dashboard-auth-better-auth.md)):
Better Auth decides who belongs and with which role, and the dashboard repairs a missing or stale
mirror on the member's next request. A removed member's row stays while history names it.

**auth schema** (T-130) — Better Auth's tables: `user`, `session`, `account`, `verification`,
`organization` (+ `country`), `member`, `invitation`, `rate_limit`. No RLS and no tenant data;
only `apps/web`'s Better Auth instance uses them. An `organization` and a `member` share their ids
with `organizations` and `members`. A Tomar press on a handoff
notification sent to the team WhatsApp number claims it for the member linked to the number that
pressed (UI in T-137).

**api_keys** (T-120) — `id` (`key_…`, kept by rotation), `org_id` (null only for platform
administration keys, created by scripts under `withSystem`, which name the org with `X-Org-Id`),
`agent_id` nullable (agent-bound key, same-tenant FK), `name`, `prefix` (`sk_live_` / `sk_test_`),
`lookup` (first 12 characters of the token body, unique index), `hash` (argon2id PHC string of the
whole token: m=19456, t=2, p=1), `last4`, `scopes` text[] (`docs/10` §1), `expires_at`,
`revoked_at`, `last_used_at` (written in the background, at most once a minute per key and
process), `created_by` (member or API key id), `created_at`, `rotated_at`. A token is the prefix
plus 32 random bytes in base62 (43 characters); it is shown once. argon2id is salted, so a token
cannot be found by hashing it: authentication reads the row by `lookup` (`withSystem`, the org is
not known yet), then verifies. Only that verification is cached (SHA-256 of the token → key id and
row hash, 60 s), so revocation, expiry and rotation apply at once in every process.

**secrets** — `id`, `org_id`, `name` (unique per org; `[A-Z0-9_]+`), `ciphertext` bytea,
`key_version`, `kind` (`developer | byok | channel | personal | webhook | payment`; `webhook` = a destination signing secret, T-083; `payment` = a payment connection's tokens, `PAYMENT_<PROVIDER>_<connection id>`, ADR-0019; `calendar` = a calendar connection's OAuth tokens, `CALENDAR_<PROVIDER>_<connection id>`, ADR-0020; proposed `person_connection` = a person's own provider tokens, `PERSON_<CONNECTOR>_<connection id>`, ADR-0022), `scope_ref` (agent id or
contact id for personal), `created_at`, `rotated_at`. Values are never returned by any API.
`ciphertext` is `0x01 ‖ nonce (12 B) ‖ tag (16 B) ‖ AES-256-GCM ciphertext`, with AAD
`(org_id, name)` so a value copied to another row does not decrypt; `key_version` 0 is the local
development key (`KMS_DATA_KEY`), 1+ are `secret_keys` versions (T-050).

**secret_keys** (platform, no `org_id`) — `version` int PK (> 0), `kms_key_id`, `wrapped_key`
bytea (the data key encrypted by the KMS master key), `created_at`. The highest version encrypts
new values; RLS lets every session read and only `withSystem` write.

**audit_log** — append-only: `id`, `org_id`, `actor_type` (`member | api_key | system |
mcp`), `actor_id`, `action` (`agent.update`, `agent.disclosure.update`, `key.create`, …), `target_type`, `target_id`,
`diff` jsonb (redacted), `ip`, `user_agent`, `created_at`. Written with `audit(tx, entry)` of
`@charla/domain` in the transaction of the change (T-145): keys named like a credential (`secret`,
`token`, `password`, `apiKey`, `authorization`, `headers`…) become `[redacted]` at any depth, pasted
keys (`sk_live_…`, `sk-ant-…`, `ghp_…`, `Bearer …`, private keys) and the values resolved in the
current secret scope are cut out of strings, and strings stop at 4 000 characters. `auditChanges`
gives `{ field: { from, to } }` for the changed top-level fields. Every agent version writes
`agent.version.create` with its config changes. A trigger (`audit_log_append_only`, migration 0039)
refuses `UPDATE` for every role and `DELETE` / `TRUNCATE` unless the table owner sets
`charla.audit_purge = 'on'` in the transaction (demo reset; org deletion after retention).

### 2.2 Agents and configuration

**agents** — `id`, `org_id`, `deployment_id` nullable, `name`, `slug`, `status` (`draft |
active | paused | archived`), `active_version_id`, `harness` (`cloud | external`),
`harness_config` jsonb (`{ mode?: 'push' | 'inbox', dispatchUrl, timeoutMs, identity?: { webhookUrl, secretRef }, actions?: { viewUrl?, webhookUrl?, secretRef? } }`; `identity` holds the identity webhook (T-054) and `actions` the lifecycle webhooks of governed actions (T-067) until `deployments` exists),
`turn_deadline_ms` (default 300000), `api_gateway_enabled`, `passthrough_enabled`,
`created_at`, `updated_at`.

**agent_versions** — immutable: `id`, `agent_id`, `org_id`, `version` (int, increasing),
`config` jsonb, `config_hash`, `created_by`, `created_at`, `source` (`dashboard | sync | api
| restore`). `config` shape:

```ts
type AgentConfig = {
  model: string                      // 'anthropic/claude-sonnet-5', 'openai/gpt-5.6', 'auto'
  reasoningEffort?: 'none' | 'low' | 'medium' | 'high'
  maxSteps: number                   // 1..50
  historyLimit: number               // 1..200
  systemPrompt: string               // rendered template; may reference {{org.name}} etc.
  locale: SupportedLocale            // BCP-47; GA packs: es-MX, es-AR, es-CO, es-CL, es-PE, es-419, es-ES, pt-BR, pt-PT, it-IT, en-US
  formality?: 'formal' | 'informal'  // usted/tú, você/tu, Lei/tu
  disclosure: { enabled: boolean; text?: string; onFirstContact: true }
  features: {
    memory?: { enabled: boolean; workingMemory?: boolean; semanticRecall?: boolean; observational?: boolean; categories?: string[] }
    steering?: { debounceWindowMs: number; midTurnStrategy: 'interrupt' | 'append' | 'queue'; maxBatch: number }
    media?: { audio: 'transcribe' | 'ignore'; image: 'describe' | 'ignore'; video: 'describe' | 'ignore'; document: 'extract' | 'ignore'; fallback: string } // unset = process everything; `ignore` → generic annotation (`[Nota de voz]`), no provider call; `fallback`: text shown to the model after it (sanitised), '' for none (T-033)
    escalation?: { enabled: boolean; requireConfirmation: boolean; routes: { webhook?: string; slack?: { channel: string }; email?: string; whatsapp?: { number: string } } }
    sandbox?: { enabled: boolean; persistent: boolean; maxExecTimeMs: number }
    browser?: { enabled: boolean; handoff: boolean; screenshotDelivery: boolean }
    promptCache?: boolean
    blackboard?: boolean
    payments?: { enabled: boolean; provider: 'mercadopago' | 'stripe' }
    appointments?: { enabled: boolean; connectionId: string; timeZone: string; resources: { calendarId: string; name: string }[]; services: { name: string; aliases?: string[]; durationMinutes: number; bufferMinutes?: number; resources?: string[] }[]; openingHours: { mon?: { from: string; to: string }[]; /* … */ sun?: { from: string; to: string }[] }; minNoticeMinutes?: number; horizonDays?: number; slotStepMinutes?: number } // ADR-0020 §2.3: defaults 120 min, 60 days, 30 min
    dynamicUi?: boolean
    guards?: { promptInjection: boolean; pii: 'redact' | 'off'; moderation: boolean; strictClaims?: boolean }
  }
  windowPolicy?: { outsideWindow: 'template' | 'hold' | 'drop'; defaultTemplate?: string } // default template; without defaultTemplate = drop (T-081)
  toolIds: string[]                  // references tools table at this version
  capabilityRefs: { slug: string; version: string; publisher: string }[]
  mcpConnections: McpConnection[]
  userSecrets?: UserSecretDecl[]
  personalConnections?: PersonalConnectionDecl[] // ADR-0022 §2.1 (b): OAuth connectors each contact connects
  builtinConnections?: ('google_calendar')[] // ADR-0022 §2.2: Charla's own connectors each contact connects
  workflow?: Workflow                // optional graph of stages (ADR-0013, T-161); see below
}
```

**`config.workflow`** (T-161, [ADR-0013](adr/0013-agent-workflows.md)). Optional: a version
without it is one prompt and one list of tools, and parses and hashes exactly as before. The
schema is `workflowSchema` (`packages/domain/src/schema/json/workflow.ts`); objects are strict,
so an unknown key is refused. The runtime follows workflows since T-163 (`docs/07` §14).

```ts
type Workflow = {
  nodes: WorkflowNode[]              // 1..50
  transitions: WorkflowTransition[]  // 0..200, directed
  settings: {                        // optional on write; defaults filled when stored
    maxTransitionsPerTurn: number    // 1..10, default 3 (loop guard)
    resetAfterHours: number | null   // 1..2160, default 24; null = never start over
  }
}
type WorkflowNode =
  | { id; name; position?; type: 'start' }
  | { id; name; position?; type: 'stage'
      instructions: string               // 1..8000
      promptMode: 'append' | 'replace'   // default 'append'
      inheritTools: boolean              // default true: offer the agent's own tools too
      tools: string[] }                  // ≤ 30 tool names, default []
  | { id; name; position?; type: 'end' }
  // Deterministic nodes (T-164): the runtime runs them without the model.
  | { id; name; position?; type: 'say'; text: string }       // 1..2000, {{ path }} placeholders
  | { id; name; position?; type: 'set_state'
      set: Record<variable, expression> }                    // 1..30; variable: snake_case
  | { id; name; position?; type: 'tool'
      tool: string                                           // a tool name, like stage tools
      args: Record<string, expression> }                     // ≤ 30, default {}
  | { id; name; position?; type: 'handoff'
      reason: string                                         // 1..200, {{ path }} placeholders
      summary?: string }                                     // 1..2000; default: reason
// id: ^[a-z][a-z0-9_]{0,39}$, stable across versions; name: 1..60; position: { x, y }
type WorkflowTransition =
  | { from; to; order; label?; type: 'none' }
  | { from; to; order; label?; type: 'llm'; condition: string }              // 1..500
  | { from; to; order; label?; type: 'expression'; expression: expression }  // 1..500
// from, to: node ids; order: 0..999, lower first among the transitions of `from`; label: ≤ 40
```

A stage names its tools by **name** (`tools.name`, or `<connection>__<tool>` for an MCP tool),
not by row id, so the references survive copying an agent and a template can name its own tools.
A `tool` node names its tool the same way; it calls it, it is not offered to the model there.
`end` and `handoff` are the terminal kinds: no outgoing transitions, and every path must reach
one. How the runtime runs each kind: `docs/07` §14.

**Expressions** (T-164, `packages/domain/src/workflow-expression.ts`). A small language for
`expression` transitions, `set_state` values and `tool` arguments, parsed by a hand-written
parser and evaluated by walking the tree: nothing is ever run as code.

```text
expr    := or
or      := and ("or" and)*
and     := not ("and" not)*
not     := "not" not | compare
compare := operand (("==" | "!=" | "<" | "<=" | ">" | ">=") operand)?
operand := literal | path | "exists" "(" path ")" | "(" expr ")"
literal := number | 'text' | "text" | true | false | null
path    := ("state" | "tool") ("." name | "[" index "]")*
```

- `state.<name>`: the conversation's variables. `tool.ok`, `tool.status` (`ok`, `error`, a
  refusal such as `refused_scope`, or a decided action's `declined`, `expired`…), `tool.error`
  and `tool.result.<…>`: the outcome of the last `tool` node of the chain. The contact's messages
  are not reachable, so no expression can copy what the contact typed into a variable.
- Examples: `state.verified == true`, `tool.ok and tool.result.balance > 0`,
  `exists(state.order_id) and not state.blocked`, `tool.result.items[0].status == 'open'`.
- A missing path is nothing: `exists()` is false, `==` is false, `!=` true. `==` compares scalars
  only; `<`… only two numbers or two strings. `and`/`or`/`not` treat `false`, `null`, nothing,
  `0` and `''` as false. Only own keys of plain objects and list indexes are read.
- Limits: 500 characters, 200 tokens, nesting 20, 10 path parts. Comparisons do not chain.
- A text's `{{ path }}` placeholder (`say`, `handoff`) holds one path; its value is written as
  text (`null`, nothing and objects as empty). A value is never read again as a template.
- `set_state` stores scalars only (`workflowVariablesSchema`): an object becomes `null`, a text is
  cut to 500 characters.

`validateWorkflow(config, { tools? })` (`packages/domain/src/workflow.ts`) checks the graph. It
is pure and returns every problem as `{ code, path, message, params }`. The agent editor and the
public API run it with the agent's tool names (`agentToolNames`) before saving, and
`AgentVersionsRepo.create` runs it again without them (`WorkflowGraphError`), so no writer can
store a broken graph.

| Code | Rule |
|---|---|
| `workflow_start_missing`, `workflow_start_multiple` | Exactly one `start` node. |
| `workflow_start_transition` | The start node has exactly one outgoing transition, of type `none`. |
| `workflow_transition_into_start` | No transition leads to the start node. |
| `workflow_end_missing` | At least one `end` or `handoff` node. |
| `workflow_end_has_transitions` | An `end` or `handoff` node has no outgoing transitions. |
| `workflow_duplicate_node_id` | Node ids are unique. |
| `workflow_unknown_node` | `from` and `to` name existing nodes. |
| `workflow_self_transition` | No transition from a node to itself. |
| `workflow_duplicate_transition` | At most one transition per ordered pair of nodes. |
| `workflow_unreachable_node` | Every node can be reached from the start node. |
| `workflow_dead_end` | From every node some path leads to an end node. |
| `workflow_duplicate_order` | The transitions of a node have different `order` values. |
| `workflow_multiple_unconditional`, `workflow_unconditional_not_last` | A node has at most one `none` transition, and it is the last in order. |
| `workflow_duplicate_tool` | A stage lists a tool once. |
| `workflow_unknown_tool` | A stage or a `tool` node only names tools the agent has (checked when the caller knows them). |
| `workflow_invalid_expression` | An `expression` transition, a `set_state` value and a `tool` argument parse; the message names the node, the field and the character. |
| `workflow_invalid_template` | Each `{{ … }}` of a `say` or `handoff` text holds one path and closes. |
| `workflow_llm_transition_from_deterministic` | A deterministic node has no `llm` transitions: no model decides there. |
| `workflow_deterministic_without_fallback` | `say`, `set_state` and `tool` nodes have a `none` transition, taken when no expression holds. |
| `workflow_handoff_without_escalation` | A `handoff` node needs `features.escalation.enabled` (checked when the config carries `features`). |

Size limits and field formats are Zod issues (`too_big`, `invalid_format`…) with the same kind
of path.

**tools** — `id`, `org_id`, `agent_id` (nullable for deployment-level), `name`
(`^[a-z][a-z0-9_]{0,63}$`), `description`, `input_schema` jsonb (JSON Schema), `execution_type`
(`webhook | mcp | code | builtin | capability | calendar`), `config` jsonb (`webhookUrl`,
`timeoutMs`, `mcpConnection`, `mcpTool`, `code`; `calendar`: `connectionId`, `operation`, `args`,
ADR-0020 §2.2), `policy` jsonb (see [09](09-tools-identity-governance.md)),
`enabled`, `disabled_reason` (why the platform turned it off, e.g. `url_scheme` for a webhook
URL that is not public HTTPS), `source` (`dashboard | sync | deployment`), `created_at`,
`updated_at`. Unique (`agent_id`, `name`). Webhook `config` also takes `secretRef` (org secret
that signs it, default `TOOL_SIGNING_SECRET`) and `headers` (values may use `{{secret.NAME}}`).

**capability_installs** — `id`, `org_id`, `agent_id`, `capability_id`, `version`, `settings`
jsonb, `status` (`installed | error`), `last_error`, `installed_at`.

**capabilities** — `id`, `publisher_org_id`, `slug`, `version`, `manifest` jsonb, `status`
(`private | pending_review | published | rejected`), `review_notes`, `created_at`. Unique
(`publisher_org_id`, `slug`, `version`).

**mcp_connections** — `id` (`mcp_…`), `org_id`, `agent_id`, `name` (lowercase words joined by
`_`, ≤ 24 chars: it prefixes the tools as `<name>__<tool>`), `endpoint`, `kind` (`mcp |
openapi`; only `mcp` is accepted until Fase 2), `auth_mode` (`public | headers | oauth`),
`headers` jsonb (header → template; credentials only as `{{secret.NAME}}`, never a value),
`credentials` (`agent | per_sender`, default `agent`; a `per_sender` row has no `headers` and
`auth_mode: public`, ADR-0022 §2.1), `personal_connection` (nullable: the name of an agent-declared
OAuth connector whose contact's token every call carries, #677; such a row has no `headers`,
`auth_mode: public` and `credentials: agent`), `default_permission` (`allow | ask | deny`), `status` (`pending | connected | connected_no_tools
| needs_auth | error`), `last_error`, `discovered_tools` jsonb (`{ name, description?,
inputSchema, outputSchema? }[]`, ≤ 100), `discovered_at`, `created_at`, `updated_at`. Unique
(`agent_id`, `name`) and (`agent_id`, `endpoint`); at most 10 per agent (T-053).

**agent_templates** (T-153) — an organization's own agent template: `id` (`atp_…`), `org_id`,
`title` (≤ 80), `description` (≤ 280), `config` jsonb (the published version's config with
`toolIds` and `mcpConnections` empty and no escalation webhook URL; `orgTemplateConfigSchema`),
`tools` jsonb (webhook tools by definition: `{ name, description, inputSchema, policy,
secretRef?, headers? }[]`, headers only as `{{secret.NAME}}`; never a URL nor a tool id),
`required_secrets` jsonb (secret names, never values), `source_agent_id`, `source_version_id`
(for the record, no foreign key), `created_by`, `created_at`, `updated_at`. RLS per org: no
other org sees it. Saved from an agent's published version (`orgTemplateFrom`, which refuses a
config holding a pasted key or a URL with a user or password); installing it creates a draft
agent whose tools stay off (`needs_setup`) until their URL and secrets are set. No channel,
number or WhatsApp message comes with it.

**mcp_sender_credentials** — `id`, `org_id`, `agent_id`, `connection_id`, `channel`,
`sender_key` (e.g. `wa:<wa_id>`, `api:<conversation_id>`, `slack:<team>:<user>`),
`headers_ciphertext`, `created_at`.

**person_connections** (`pco_`, ADR-0022 §2.8) — a contact's own provider account for one agent's
tools: `org_id`, `agent_id`, `contact_id`, `connector` (`agent:<name>`), `status` (`pending |
connected | expired | revoked`), `external_account` (nullable; never shown to the model),
`secret_id` (the `person_connection` secret `PERSON_<CONNECTOR>_<id>`; required while
`connected`), `scopes`, `token_expires_at`, `connected_at`, `expired_at`, `revoked_at`,
`revoked_by`, `last_error`, timestamps. Unique `(org_id, agent_id, contact_id, connector)`. RLS per
org; same-tenant FKs to the agent and the contact.

**person_connection_links** (`pcl_`) — a single-use connect link: `org_id`, `connection_id`,
`conversation_id`, `turn_id` (the turn that asked; no FK), `token_hash` (unique SHA-256 of the
token), `expires_at` (15 min), `consumed_at`, `cancelled_at`, `created_at`. RLS per org.

### 2.3 Deployments and channels

Partner tenancy follows ADR-0012: each end client of a partner is its own organization, and the
deployment lives in that client org, never in the partner's.

**registry.partners** (global, [ADR-0023](adr/0023-partner-deployments-and-account-linking.md)
§2.1, Q1: default, pendiente de confirmar por el Lead) — `partner_org_id` (primary key),
`enabled_at`, `disabled_at`, `max_deployments` (100 by default). Ids, dates and limits, nothing
else. Written only by the platform operation `enablePartner` (`packages/domain/src/partners`),
under `withSystem("registry.partner_enable")`: the table forces RLS with an open read policy and a
write policy that needs `app.system`, so the app role reads it but cannot write it otherwise.

**registry.partner_links** (global, next to `registry.org_regions`, ADR-0010) — `client_org_id`
(primary key, never reused), `partner_org_id` (FK to `registry.partners`), `external_ref` (unique
per `partner_org_id` among links not `revoked`: a partial unique index), `status` (`provisioning |
active | deleting | revoked`, ADR-0023 §2.5: creation writes the link before the region and
deletion revokes it last, so a resumable job finishes either), `created_at`, `updated_at`. No RLS
(the router reads it before it knows the org); the app role may insert and update a link, never
delete it. Every state change is a guarded `UPDATE … WHERE status IN (<previous>)`
(`PartnerLinksRepo.transition`, `PARTNER_LINK_TRANSITIONS`); an invalid or lost move is an
`InvalidPartnerLinkTransitionError` and writes nothing, and a duplicate link is a
`PartnerLinkConflictError`. A partner key resolves `(partner_org_id, external_ref)` here, then
works only in `withOrg(client_org_id)`; no reads across client orgs and no `withSystem`.

**deployments** (RLS like every tenant table) — `id` (`dpl_`), `org_id` (the client org, unique:
one deployment per client org), `external_ref` (copy of the link's), `agent_id` (1:1, same-tenant
FK; `agents.deployment_id` points back with a same-tenant FK, set by `DeploymentsRepo.create`), `webhook_url`, `identity_webhook_url`,
`identity_secret_ref`, `actions_view_url`, `actions_webhook_url`, `actions_secret_ref`,
`created_at`, `updated_at`. Deleting a deployment deletes its client org.

**channel_identities** — `id`, `org_id`, `agent_id`, `type` (`whatsapp | instagram |
messenger | telegram | sms | email | voice | web | api | slack | teams`), `ref` (optional
disambiguator), `address` (E.164, IG account id, bot username, email), `provider`
(`kapso | dialog360 | meta_cloud | twilio | telegram | resend | …`), `provider_config` jsonb
(`waba_id`, `phone_number_id`, `provider_channel_id`, `meta_billing_mode`, `page_id`, WhatsApp
from the onboarding: `is_coexistence`, `connection_type`, `provider_customer_id` (T-132),
`ig_user_id`; Telegram: `bot_id`, `bot_username`, `webhook_secret_ref`), `credentials_secret_ref`
(Telegram: the bot token secret), `status` (`connected |
needs_action | error | disconnected`), `quality` jsonb (WhatsApp: `rating`, `tier`,
`name_status`, `status`, `business_initiated_24h`, `tier_warning`, `updated_at`; refreshed hourly
by `quality-poll`, T-085), `settings` jsonb (`groups: { replyMode: mention | always, identity: group | user,
mentionAliases }` (T-112), voice settings), `created_at`. Unique (`type`, `address`) globally: one WhatsApp number belongs to
one agent.

**channel_quality_history** (T-085) — `id`, `org_id`, `channel_identity_id`, `rating`, `tier`
(integer, null = unlimited/unknown), `status` (provider health), `business_initiated_24h` (our
count of conversations opened with a template in the 24 h before the reading), `checked_at`. One
row per hourly `quality-poll` reading; the dashboard charts it (T-138).

**templates** (WhatsApp message templates) — `id`, `org_id`, `channel_identity_id`,
`name`, `language` (Meta code: `es`, `pt_BR`), `category` (`utility | marketing |
authentication`), `status` (`approved | pending | rejected | paused | disabled`), `components`
jsonb (Meta's definition: `HEADER`, `BODY`, `FOOTER`, `BUTTONS`), `meta_id`, `rejected_reason`,
`synced_at`, `created_at`, `updated_at`. Unique (`channel_identity_id`, `name`, `language`).
Mirrored from the WABA by the `template-sync` job (T-080): rows the provider no longer lists are
deleted, except a `pending` one synced in the last 15 min (Kapso lists a new template late).
Used by alerts, notices and human replies outside the 24 h window.

**template_message_requests** (T-154) — `id` (`tmr_`), `org_id`, `name`, `language` (Meta code),
`category`, `components` jsonb (the draft in Meta's shape, as the agent template's file has
it), `source` jsonb (`{ vertical, locale }` of the agent template), `status` (`pending |
submitted | failed`), `attempts`, `last_error`, `submitted_at`, `created_at`, `updated_at`.
Unique (`org_id`, `name`, `language`): the WhatsApp messages an installed agent template brings
are asked for once per org, however many agents come from it. Installing writes the rows
(`pending`); the `template-submit` job sends them to Meta's review in the WABA of the org's
connected numbers and marks them `submitted`, or `failed` when the draft breaks Meta's rules or
after 5 failed sends. Meta's decision is not kept here: it is the `templates` row.

### 2.4 Contacts, conversations, messages

**contacts** — `id`, `org_id`, `channel_type`, `external_id` (`wa_id`, BSUID, IG-scoped id,
Telegram user id, E.164, email), `display_name`, `profile` jsonb, `locale`, `opted_out`
(derived: the org-wide opt-out only, the cache of the latest org-wide `consent_events` row;
ISSUE-1169), `first_seen_at`, `last_seen_at`. Unique (`org_id`, `channel_type`, `external_id`).
A contact may have no phone number (WhatsApp usernames). `profile.username` is the channel's
handle (Telegram, Kapso) without `@`: every inbound message from an individual contact merges
the current one into `profile` (`ContactsRepo.upsertSeen`, the other keys stay and a message
without one keeps it); a shared group contact gets none. Cross-channel linking of the same
person is a Later feature (`person_id` nullable). (T-142) A DSAR erasure turns the row into a
tombstone: `erased_at` and `external_id_hash` (SHA-256 of `<channel_type>:<external id>`, never
used to link or authorise) are set together (check `contacts_tombstone`), `external_id` becomes
`erased:<id>` and `display_name`, `profile` and `locale` are cleared. The row stays because
`consent_events` point at it; the next message from the same identifier opens a new contact.

**conversations** — `id`, `org_id`, `agent_id`, `channel_identity_id`, `contact_id`,
`channel_thread_ref` (Slack thread, IG thread, Telegram chat id, `x-conversation-id`),
`kind` (`dm | group`), `status` (`open | held | closed`), `held_by_member_id`, `held_until`,
`window_expires_at` (WhatsApp 24 h customer-service window), `last_inbound_at`,
`last_outbound_at`, `mood` smallint, `mastra_thread_id`, `mastra_resource_id`,
`metadata` jsonb, `created_at`. Unique (`channel_identity_id`, `contact_id`,
`channel_thread_ref`) `NULLS NOT DISTINCT` (WhatsApp DMs have no thread ref).

**conversation_workflow_states** (T-162, [ADR-0013](adr/0013-agent-workflows.md) §3.7) — where a
conversation is in its agent's workflow: `conversation_id` (primary key, one row per
conversation, deleted with it), `org_id`, `agent_version_id` (the version whose graph `node_id`
was last checked against), `node_id` (`config.workflow.nodes[].id`), `variables` jsonb (≤ 50
snake_case names; values are a string ≤ 500, a number, a boolean or `null`), `transitions` jsonb
(the last 20, `{ turnId, from, to, at, by? }`; `by` is `model` or `runtime`, T-164), `entered_at`, `created_at`, `updated_at` (last change
of node or variables). It is its own table and not columns on `conversations`, which is written
on every inbound message and locked by takeover.

A conversation **without a row is at the workflow's entry node**, the node the `start` node
points to (`entryNodeId`); a row appears with the first transition or variable. `WorkflowStateRepo`
(`packages/domain/src/repos/workflow-state.ts`) is the only writer:

- `transition` locks the row (creating it at the entry node first, so two first transitions
  queue on it), then `applyWorkflowTransition` decides: the edge `current → to` must exist, of a
  type the caller may take, out of a node kind it may leave (`fromNodeTypes`: the model only
  leaves a `stage`); a turn makes at most `settings.maxTransitionsPerTurn` transitions decided by
  the model, at most 20 node entries in all (`WORKFLOW_MAX_NODE_ENTRIES_PER_TURN`, the runtime's
  included) and never enters a node twice (`loop_guard`); the same turn asking again for the node it is in
  is a repeat and writes nothing (`replayed`). Of two concurrent transitions the second is
  validated against the node the first left.
- `resolve` is what a turn that starts calls (`resolveWorkflowState`). It deletes the row, so the
  conversation starts over, when the version has no workflow, when the row is idle for longer
  than `settings.resetAfterHours`, when its node is gone from the version's graph, or when an
  earlier turn reached an `end` or `handoff` node. When another version still has the node it keeps the row
  and its variables under the new version.
- `get` reads the row as it is, for each model step.

The row is also deleted when the conversation is closed (`ConversationsRepo.setStatus`), by a
DSAR erasure, and by the retention job once it is idle for longer than the org's message
retention. Variables may hold personal data, and nothing redacts personal data before it is
stored (the only masking today is on what a trace shows, and nothing acts on
`features.guards.pii` yet). The only writer of variables, the `set_state` node (T-164), cannot
read the contact's messages (its expressions reach `state.*` and `tool.*` only), so no free text
typed by a contact is stored; values that come from a tool result are scalars of at most 500
characters. A future writer of contact text must add a redaction step first.

**messages** — the transcript of record: `id`, `org_id`, `conversation_id`,
`channel_identity_id` (copied from the conversation for the inbound uniqueness below), `turn_id`
nullable, `direction` (`in | out`), `role` (`contact | agent | team | system`), `kind`
(`text | interactive | template | media | reaction | location | contacts | order | flow_reply |
button_reply | list_reply | call_event`), `text`, `payload` jsonb (channel-native body as sent or
received; for `button_reply` / `list_reply` the channel-agnostic `{ id, title[, description] }`,
with the native body kept in `inbound_events`), `rendered_text` (what the contact actually saw), `attachments` jsonb
(`[{ id, mime, size, sha256, filename, storage_key, expires_at }]`), `provider_message_id`,
`reply_to_message_id`, `idempotency_key`, `status` (`received | queued | sent | delivered |
read | failed | not_delivered`), `error` jsonb (`code`, `reason`, e.g. WhatsApp `131047`; a
`queued` row with `code: window_hold` is held by `windowPolicy: hold` until the contact writes,
T-081),
`sent_at`, `delivered_at`, `read_at`, `charge_class` (`service_free | service_paid |
utility_template | marketing_template | ai_reply_charge`, outbound WhatsApp only, set when the send
goes out, T-105), `recipient_country` (ISO 3166 of the recipient's number it was priced for, null
for a BSUID), `created_at`. Unique (`channel_identity_id`,
`provider_message_id`) for inbound; unique (`conversation_id`, `idempotency_key`) for outbound
(both partial indexes filtered by `direction`).

**inbound_events** — raw provider payloads for audit and replay: `id`, `org_id`,
`channel_identity_id`, `provider`, `provider_event_id`, `payload` jsonb, `signature_ok`,
`received_at`, `processed_at`, `error`. Retention 30 days.

**ingress_outbox** (ISSUE-1065, [ADR-0006](adr/0006-ingress-outbox.md)) — Redis/BullMQ work a
new inbound message owes, written in the message's transaction: `id` (`iwk_…`), `seq` (insertion
order), `org_id`, `conversation_id`, `message_id` (cascade on delete), `kind`
(`steer|media_download|consent_reply|action_decision|handoff_reply|held_release`, unique per message), `payload` jsonb (validated by
`ingressWorkPayloadSchemas`), `status` (`pending|dispatched|abandoned`), `attempts`,
`available_at` (lease / next retry), `last_error`, `created_at`, `settled_at`. Settled rows are
deleted after 7 days.

**attachments** — `id`, `org_id`, `conversation_id`, `message_id` (nullable: an outbound file
is stored before its message), `storage_key` (`org/<orgId>/conv/<conversationId>/<attachmentId>`,
unique), `mime`, `size`, `sha256`, `filename`, `provider_media_id`, `transcript` text (for audio),
`description` text (for images), `extracted_text` text (documents, ≤ 200 kB), `processing` jsonb
(what the media pipeline did, T-033: `{ action, status: done | ignored | failed | unsupported,
provider, model, fallbackUsed, language, confidence, durationMs, pages, truncated, error,
processedAt }`; unset until processed, written once), `created_at`, `expires_at` (`created_at` + 90 days, or
`organizations.settings.retentionDays.attachments`, 7–365; indexed for the retention job).
Deleting a message sets `message_id` to null (`ON DELETE SET NULL ("message_id")`), so shorter
message retention never blocks on files; deleting a conversation is refused while it has
attachments, so that path deletes the objects and rows first. The message retention job (T-141)
marks a message's attachments expired before deleting it, so the files go with it.

### 2.5 Turns, tool calls, traces

**turns** — `id` (= trace id), `org_id`, `conversation_id`, `agent_version_id`, `trigger`
(`inbound | notice | steer | test | alert | resume | api`), `status` (`queued | running |
completed | failed | cancelled | held`), `held_back` bool (steer/test), `silent` bool,
`harness` (`cloud | external`), `inbound_message_ids` text[], `arrivals` jsonb (sequence →
message ids → consumed), `stop_reason` (`final_answer | max_steps | all_tools_failed |
repeated_tool_failure | interrupted | deadline | approval_requested | empty_reply | error`),
`model`, `tokens_in`, `tokens_out`, `cost_usd` numeric(12,6), `credits` numeric(12,4),
`created_at`, `started_at`, `ended_at`, `error` jsonb, `run_id` (Mastra durable-agent run), `signal_ids`
text[] (signals delivered into this run), `recovered_count` smallint (takeovers by another
consumer after the owner died, ISSUE-1067), `trace_id` (the turn's ULID in hex, `traceIdOf(id)`:
Mastra trace ids are 32 hex characters), `retried_at` (a member retried this failed turn from the
dashboard, T-135: its `inbound_message_ids` stop counting as consumed, so steer answers them in a
new turn), `followup_id` (the follow-up whose fire started a notice turn, #636; no FK).
Partial index on `started_at` where `status = 'running'` for the recovery sweep;
(`org_id`, `created_at` desc, `id` desc) for the logs page (keyset pagination).

**tool_calls** — `id`, `org_id`, `turn_id`, `step`, `tool_call_id` (the model's id, nullable),
`tool_id` (nullable for builtins), `tool_name`, `args` jsonb (redacted), `result` jsonb
(truncated 64 kB), `status` (`running | ok | error | refused_scope | approval_requested |
preflight_rejected | preview_required | cancelled`), `duration_ms`, `action_id` nullable, `created_at`, `ended_at`.
The row is opened (`running`) before the call runs, which fixes `step` (ordinal in the turn):
unique (`turn_id`, `step`) and (`turn_id`, `tool_call_id`), so a retried call reuses its step
and its webhook `Idempotency-Key` (T-052).

Spans and full trace payloads live in Mastra observability storage
(`mastra.mastra_ai_spans`), keyed by `turns.trace_id`.
Retention 14 days default (org-configurable).

### 2.6 Identity, actions, handoffs, presentations

**identity_cache** — `id`, `org_id`, `agent_id`, `sender_key` (`wa:<wa_id>`; unique per
agent), `principal` jsonb (`userId`, `role`, `scopes`, `mcpConnections`: names only, the
`mcpHeaders` themselves are sealed in `mcp_sender_credentials`), `resolved_at`, `expires_at`,
`status` (`ok | unknown`; `error` is never stored). TTL default 900 s, max 86 400, 0 = no cache.
A `reply` answer and a failed call are never cached.

**scope_grants** ([ADR-0018](adr/0018-dashboard-scope-grants.md)) — `id` (`sgr_`), `org_id`,
`agent_id`, `contact_id` (same-tenant FKs; unique per agent and contact), `scopes` text[] (only
scopes the agent's tools declare in `policy.requiredScopes` when written; intersected with the
turn's tools when read), `expires_at` (null = none), `revoked_at`, `granted_by` / `revoked_by`
(`members.id`, no FK), timestamps. Granting again reuses the row; `audit_log` keeps
`scope_grant.create | update | revoke`. Refused on an agent with an identity webhook. A DSAR
erasure deletes the contact's grants.

**actions** — `id`, `org_id`, `agent_id`, `conversation_id`, `turn_id`, `group_id`,
`tool_id`, `tool_name`, `args` jsonb (exact), `args_hash`, `description` (model walkthrough),
`preview_tool_call_id`, `preview_output` jsonb, `freshness_snapshot` jsonb, `card` jsonb
(summary blocks), `status` (`pending | executing | completed | failed | declined |
superseded | expired`), `proposer_principal` jsonb, `decider_principal` jsonb, `decided_via`
(`whatsapp_button | whatsapp_flow | chat_words | dashboard | slack | api | api_button`;
`api_button` is the API caller's decision for its user, `docs/09` §4.3), `decision_message_id`,
`result` jsonb, `error`, `expires_at` (+24 h), `created_at`, `decided_at`, `settled_at`.
(T-062) `card` is the filled `ApprovalCard` (`docs/09` §4.4), a snapshot taken at proposal;
`decision_message_id` is the inbound message that carried the decision (the button press); the
card's own outbound message is found by its idempotency key `<action id>:ask`.
(T-060) `id` is `act_…`; `args_hash` is `canonicalHash(args, descriptionArg)` (`docs/09` §4.2:
SHA-256 of the canonical JSON, the description argument left out). `decided_at` is set exactly
when the status leaves `pending`, `settled_at` exactly on a final status (check constraints).
`ActionsRepo.transition` locks the row (`FOR UPDATE`): an invalid move throws
`InvalidTransition`, a move to the current status is a no-op without event. Webhooks and the API
expose `PublicActionRecord` (no hash, preview, freshness snapshot or unknown principal keys).
(T-064) A tool with `previewTool` stores the paired `tool_calls` row, its output and the values of
its `freshnessFields` (`{ "<dot path>": value }`, a missing path as `null`). `executing →
superseded` is allowed: the preview changed at approval and nothing ran.

**action_events** — `id` (`ace_…`), `action_id`, `org_id`, `from_status` (`null` on the creation
event), `to_status`, `actor` jsonb (`{ type: "principal", principal } | { type: "member", memberId }
| { type: "system", component }`), `detail` jsonb (e.g. `{ reason: "executor_lost" }`),
`created_at`. One row per status change, creation included.

**action_webhook_attempts** (T-067) — `id` (`awa_…`), `org_id`, `action_id` (FK, cascade),
`event_id` (envelope `eventId`), `event_type` (`charla.action.<status>`), `status` (`delivered |
failed`), `http_status`, `error` (only when `failed`: `http_<status>`, `timeout`, `network_error`,
`ssrf_blocked`, `secret_missing`, `secret_too_short`, `invalid_config`…), `duration_ms`,
`created_at`. One row per lifecycle webhook sent to `actions.webhook_url`; no retries, so this is
the whole history. Holds no body, headers or URL.

**handoffs** — `id`, `org_id`, `agent_id`, `conversation_id`, `kind` (`escalation | manual |
work_item`), `status` (`waiting | taken | resolved | stale`), `reason`, `summary`,
`claimed_by_member_id`, `destination` jsonb (slack thread, whatsapp team number, email),
`opened_at`, `claimed_at`, `resolved_at`, `resolved_by` (`member | agent | system`).
(T-070) `id` is `hnd_…`; at most one open handoff (`waiting` or `taken`) per conversation
(partial unique index `handoffs_one_open`), so `escalate` updates the open one's `summary`
instead of opening another. Moves: `waiting → taken` (claim), `taken → waiting` (release, which
clears the claim), `waiting | taken → resolved`, `waiting → stale` (7-day auto-close, T-073);
`resolved` and `stale` are final and keep the last claim. Check constraints: `taken` has
`claimed_by_member_id` and `claimed_at`, `waiting` has no claimer, and `resolved_at` /
`resolved_by` are set exactly on a final status. (T-071) `destination.routes.<email | webhook |
whatsapp>` holds each escalation route's last attempt: `{ status: sent | queued | skipped |
failed, at, error?, detail? }` (`skipped`: team number outside its 24 h window without an approved
`handoff_notificacion`, or a webhook URL that is not a registered destination). (T-073) The agent
may resolve only its own `waiting` escalation (`resolve_escalation`, `resolved_by = agent`); the
daily `handoff-sweep` moves `waiting` rows with `opened_at` 7+ days old to `stale` (`resolved_by =
system`).

**presentation_intents** (T-042) — `id` (`pin_…`), `org_id`, `conversation_id`, `turn_id`,
`tool_call_id`, `tool_name`, `presentation_id` (the tool's `presentation.id`), `intent_key`
(`<tool>:<presentation id>`, unique per conversation: idempotency), `type` (`choice | confirm |
flow | url`), `options` jsonb (`[{ id, label, description? }]`; a `confirm` stores `yes` / `no`
with the labels shown), `allow_free_text`, `status` (`open | answered | superseded | expired`),
`answer` jsonb (`{ optionId, confirmed?, messageId }` or `{ freeText, messageId }`; set exactly
when `answered`), `message_id` (outbound `messages.id` promised to the `deliver` job, no FK),
`created_at`, `answered_at`, `expires_at` (+24 h). At most one `open` row per conversation
(partial unique index): opening one supersedes the rest. Only `open` moves (`answer`,
`supersede`, `expire`); the daily `maintenance` task `expire-presentations` closes rows past
`expires_at`.

### 2.7 Consent and compliance

**consent_events** — `id`, `org_id`, `contact_id`, `channel_identity_id`, `previous`
(`opted_in | opted_out | unknown`), `current`, `reason` (`keyword | provider | health |
verified_restoration | operator | import`), `keyword`, `source_message_id`, `occurred_at`.
**Consent is per line** (ISSUE-1169, [ADR-0014](adr/0014-consent-scope-per-line.md)): an opt-out is shared across the
conversations of the contact on the same channel identity, and a BAJA on one number of the org
does not block another, nor does an ALTA on another number lift it. `channel_identity_id` null is
the **org-wide scope** (BAJA TOTAL, ALTA TOTAL, an operator acting on every line, an import): it
covers every line of the org, present and future; only `keyword`, `verified_restoration`,
`operator` and `import` may act org-wide (check `consent_events_org_wide_reason`), since the
provider's stop and line health are per line. What applies on a line (`consentOnLine` in
`@charla/domain`): the line is blocked while its own latest event is `opted_out` (unless a later
org-wide opt-in could lift it, `canLiftOptOut`) or the org-wide one is (unless a later opt-in on
the line could lift it). `contacts.opted_out` without an org-wide event counts as an org-wide
opt-out of unknown origin.

**dsar_requests** — `id`, `org_id`, `contact_id`, `type` (`export | erase`), `status`,
`requested_by`, `completed_at`, `artifact_key`.
(T-142) `id` is `dsr_…`; `status` `pending | running | completed | failed`; `requested_by` is an
`audit_log` actor (`api_key:<id>`, `member:<id>`, `system`); also `attempts`, `lease_until`
(15 min while `running`), `error` (our own message, no contact data) and `created_at`. At most one
`pending | running` request per contact and type (partial unique index `dsar_requests_one_open`).
A failed attempt goes back to `pending` until 5 attempts, then `failed`. `artifact_key`
(`org/<orgId>/dsar/<id>.zip`) is the export package; the `dsar-sweep` deletes the object and clears
the key 24 h after `completed_at`.

**DSAR erasure** (T-142, `apps/runtime/src/dsar`), each step safe to repeat: (1) tombstone the
contact and, in the same transaction, delete `identity_cache` / `mcp_sender_credentials` rows of its
sender key, its `scope_grants`, its `account_links` and `account_link_requests` (ADR-0023 §3.6,
#658) and the `inbound_events` whose payload holds its external id; (2) delete the
attachment objects, then every row of its conversations (`messages`, `turns`, `tool_calls`,
`actions` with their events, `handoffs`, `presentation_intents`, `conversation_workflow_states`,
`attachments`, `ingress_outbox`,
our `webhook_deliveries` naming the conversation), the conversations, `alert_subscriptions`,
`alert_deliveries` and `followups`; `consent_events` stay with `source_message_id` null; `usage_events` and
`eval_results` keep their rows with `turn_id` null (billing records); Mastra spans of the turns'
traces and snapshots of their runs; (3) `AgentRuntime.forget` (threads and resource); (4)
`audit_log` `contact.erase`, the `person.erased` event and the request `completed`, in one commit.
The export (`contact.export` in `audit_log`) is a zip with `data.json` (contact, conversations,
messages, attachments with transcripts and extracted text, consent events, actions, handoffs,
workflow states with their variables, alert
subscriptions, follow-ups, Mastra working memory and threads) and `attachments/<id>-<filename>`.

### 2.8 Alerts, follow-ups, evals, signals

**alerts** — `id`, `org_id`, `agent_id`, `name`, `schedule` jsonb (`cron` + `timezone` |
`interval` | `webhook`), `template_id` nullable (WhatsApp template for outside-window),
`enabled`, `last_run_at`. **alert_subscriptions** — `alert_id`, `contact_id`, `preferences`
jsonb, `state` jsonb. **alert_deliveries** — `alert_id`, `contact_id`, `dedup_key`, `turn_id`,
`delivered_at`. Unique (`alert_id`, `contact_id`, `dedup_key`).

T-082 details: `alerts` also has `prompt` (the instruction the agent receives on each fire, as a
platform note), `mastra_schedule_id` and `created_at`/`updated_at`. `schedule` accepts only
`{ cron, timezone }` today (IANA zone, validated; `alertScheduleSchema`); `interval` and
`webhook` come later. The outside-window template is **`template_name`**, not an id: `templates`
holds one row per language per number and the hourly sync replaces them, so `deliver` picks the
row for the conversation as it does for `windowPolicy.defaultTemplate`. Without one, a
subscriber whose window is closed is skipped (`window_closed`), never sent free text.
`alert_subscriptions.preferences` (`alertPreferencesSchema`, loose): `timezone` (else the
alert's), `muted`, `dates` (local `YYYY-MM-DD` days to send on); `shouldSend` reads them.
Unique (`alert_id`, `contact_id`). `alert_deliveries.dedup_key` defaults to
`<alert_id>:<local date of the subscriber>`; the row is claimed before the turn starts and
deleted again when nothing was sent, so a later fire may retry. `turns.alert_id` (no FK, the
history outlives the alert) marks an alert's silent turn (`trigger = 'alert'`); `deliver`
replaces the agent's `windowPolicy` with the alert's template (or `drop`) for it.

**followups** (#636) — `id` (`fup_`), `org_id`, `agent_id`, `contact_id`, `conversation_id`,
`text`, `source_ref` nullable, `fire_at` timestamptz, `timezone` (the org's IANA zone at
scheduling), `status` (`scheduled | fired | delivered | held | cancelled | failed`),
`dedup_key`, `mastra_schedule_id`, `created_turn_id`, `turn_id`, `detail`, `fired_at`,
`delivered_at`, `cancelled_at`, `created_at`/`updated_at`. A one-off follow-up an agent
schedules for the contact of a conversation with the `schedule_followup` builtin
(`features.followups.enabled`), listed with `list_followups` and cancelled with
`cancel_followup`. **Not stored on the alert tables**: an alert is the builder's recurring job
with subscribers and a tenant-written prompt; a follow-up is one contact's, asked for in the
conversation, with its own text and lifecycle, so a separate table is simpler than bending
`alerts` (decided when planning #636).

Scheduling: `at` is a local `YYYY-MM-DDTHH:MM` read in the org's zone (or an ISO instant with
offset), to the minute; past, more than **30 days** ahead, or a **6th pending one for the same
contact and agent** is an argument error (`validateFollowup`; limits ⟦Lead L-7: default,
pendiente de confirmar por el Lead⟧). Each follow-up gets one Mastra workflow schedule on the
single scheduler (`followup-dispatch`, cron of its minute in `timezone`, `followupCron`); if the
scheduler refuses, the row is deleted and the tool answers `scheduler_unavailable`. The partial
unique index (`conversation_id`, `dedup_key`) where `status = 'scheduled'`
(`dedup_key` = `<fire_at>:<text hash>`) returns the pending row for a repeated request instead
of a second one.

Fire: `scheduled → fired` is a conditional update (the claim), so two fires produce one notice;
the notice also carries the `Idempotency-Key` `followup-fire:<id>` (`packages/jobs`). The notice
wakes a silent turn with `turns.followup_id` set (`trigger = 'notice'`, no FK); `deliver` sets
`delivered` when the turn's reply was accepted by the provider and `held` when `windowPolicy`
kept it (`window_hold`, then `delivered` once the release sends it) or the window dropped it
(`window_closed`), or a member holds the conversation. Queued is never delivered. A blocked thread
or a shutting-down replica releases the claim and the workflow's own retries run it again. A
fire for a follow-up whose scheduling turn was `cancelled` cancels it (`turn_cancelled`). The
schedule is deleted once the follow-up is no longer pending. Mastra fires a schedule whose
`next_fire_at` passed while no scheduler ran on its next tick, so a restart at the time does not
lose it. DSAR exports and erases a contact's follow-ups.

**eval_cases** — `id`, `org_id`, `agent_id`, `name`, `prompt`, `locale`, `channel`, `world`
(`mock | live`), `history` jsonb, `stubs` jsonb, `checks` jsonb, `version`, `created_from`
(`manual | generated | suggestion | failure`), `start_stage` (workflow node the held turn
starts in, T-168; `null` = the entry node), `principal` jsonb (the speaker's identity and
scopes for the held turn, #386; `null` = an unknown speaker), `chat` (`group` for a held turn
in a group chat; `null` = a direct chat), `created_at`. **eval_runs** — `id`, `agent_id`,
`kind` (`suite | sweep | external`), `candidates` text[], `status`, `summary` jsonb,
`started_at`, `ended_at`. **eval_results** — `run_id`, `case_id`, `candidate`, `passed`,
`score`, `checks` jsonb, `turn_id`.

T-090 details: `eval_cases` also has `archived_at` (`DELETE` archives, so past results keep their
case) and `updated_at`; each write freezes the content in **eval_case_versions** — `id`, `org_id`,
`case_id`, `version`, the case content columns, `created_at`; unique (`case_id`, `version`), v1
included. `eval_runs` and `eval_results` carry `org_id` like every tenant table;
`eval_results.case_version` records the content that ran and (`run_id`, `case_id`, `candidate`)
is unique, so a retried case replaces its row. `eval_runs.status`: `queued | running | completed
| failed | cancelled`. Zod: `evalCaseSchema` / `evalSuiteSchema` in
`packages/domain/src/schema/json/eval-case.ts`.

T-091 details: at most one `queued | running` run per agent (partial unique index
`eval_runs_agent_active_unique`; a second start is `409`). The runner seeds one `eval_results` row
per case × candidate when the run starts, with `status` (`queued | running | completed | failed |
cancelled`); a result also has `output` jsonb (the held turn: `replies[]`, `toolCalls[]`,
`approvals[]`, `stopReason`, `usage`, `errors[]`; `evalTurnOutputSchema`), `trace_id`, `error`
jsonb, `duration_ms`, `started_at`, `ended_at`. `passed` and `score` are null until the checks
(T-092) score the output. Eval turns write no `turns` row, so `turn_id` stays null: the link to
the run's spans is `trace_id`.

**signals** — `id`, `org_id`, `conversation_id`, `turn_id`, `message_id`, `kind` (`praise |
frustration | correction | task_failure | unsupported_claim | scheduled_error | reaction_pos |
reaction_neg | runtime_fault`), `mood` smallint (0–4), `mood_delta` numeric, `reason`,
`created_at`. Retention 90 days.

### 2.9 Webhooks and billing

**webhook_destinations** (T-083) — `id` (`whk_`), `org_id`, `url`, `description`,
`secret_ref`, `previous_secret_ref`, `previous_secret_expires_at` (both set or both null),
`events` text[] (exact types, family wildcards like `action.*`, or `*`), `agent_filter` text[]
(agent ids; empty = every agent), `enabled`, `deleted_at` (soft delete keeps the history),
`created_at`, `updated_at`. Secret refs name `secrets` rows of kind `webhook` (`WEBHOOK_<ULID>`,
value `whsec_…`, shown once on create and rotate); a rotation keeps the previous one signing for
24 h, after which the `webhook-sweep` deletes it.
**webhook_deliveries** (T-083) — `id` (`whd_`), `org_id`, `destination_id`, `event_id` (`evt_`),
`event_type`, `agent_id`, `payload` jsonb (the envelope, unchanged across retries and resends),
`status` (`pending | delivered | failed | canceled`), `attempts` jsonb (`[{ id, at, status,
duration_ms, origin, error?, next_attempt_at, destination }]`; `status` is the HTTP status or
null, `origin` `automatic | manual`, `destination` only the URL origin; never headers or
secrets), `attempt_count`, `next_attempt_at` (set iff `pending`), `next_origin`, `retry_until`
(72 h from creation or from the last resend), `lease_until`, `created_at`, `settled_at` (set iff
not `pending`). Unique `(destination_id, event_id)`, so fan-out is idempotent. A worker claims a
due row by setting `lease_until` (60 s) in one `UPDATE … WHERE lease free`, so two replicas never
send the same attempt. Terminal history 30 days.

**credit_ledger** (T-101) — `id` (`crd_`), `org_id`, `kind` (`grant | usage | purchase | adjustment |
expiry`), `amount` numeric(14,4) in credits (1 credit = 1 USD; check: `grant`/`purchase` > 0,
`usage`/`expiry` < 0, `adjustment` either), `balance_after`, `ref_type` (`turn | tool | line |
plan | invoice | checkout`) and `ref_id` (both set or both null), `description`, `created_at` (`clock_timestamp()` at
insert, so entries sort in the order their balances were computed). Append-only: the app role
has no `UPDATE`/`DELETE` (migration 0027); a correction is a new `adjustment`. Every write locks
the org row (`SELECT … FOR UPDATE`), appends with `balance_after = previous + amount` and updates
`organizations.credit_balance` in the same transaction, so concurrent charges never lose one.
Unique `(org_id, ref_type, ref_id)` where `kind = 'usage'`: a turn is charged once. Unique
`(org_id, kind, ref_type, ref_id)` for `grant`/`purchase` with a `ref_type` other than `plan`
(T-102): a paid invoice (`invoice`, its first-month bonus as `<invoice>:bonus`) or a pack checkout
(`checkout`) credits once. **billing_events** (T-102) — `id` (`bev_`), `provider` (`stripe`),
`event_id`, `type`, `org_id` (null when the event is no org's), `outcome` (`applied | ignored`),
`created_at`; unique `(provider, event_id)`, written in the transaction that applies the event,
so a resent or concurrent delivery changes nothing; no payload (it carries the customer's name,
address and tax ids). The turn
close charges its `usage_events` (each row costs `quantity × unit_cost_usd × (1 + margin)`,
margin 0.3 by default, `packages/domain/src/billing/credits.ts`; the exact sum is rounded once and
each row's `credits` is its share of it, so they add up to the entry; unpriced rows keep
`credits` null and are not charged) as one `usage` entry with `ref_type: turn`. **usage_events** —
`id`, `org_id`, `agent_id`, `turn_id`, `provider`, `feature` (`model | stt | tts | sandbox | browser |
scrape | render | message_template | vision | extract | ai_reply_charge | message_service`), `quantity`, `unit`,
`unit_cost_usd` (numeric(18,12)), `credits` (numeric(12,4)), `source_key` (idempotency key, unique per org when set), `detail` (jsonb),
`created_at`. When a turn closes (T-100), one `model` row per model step: `provider` without the AI
SDK's API suffix (`anthropic`), `unit: 1k_tokens`, `quantity` = (in + out) / 1000, `unit_cost_usd`
the blended price per 1,000 from the dated table `packages/domain/src/billing/prices.ts` (null while
a price is `TODO price`), `source_key` = `<turnId>:model:<step>`, `detail = { kind: 'model', model,
step, tokensIn, tokensOut, cachedTokensIn }` with the exact counts. Held-back turns write none. The
turn row and its usage are written in one transaction. The media pipeline (T-033) writes `stt` in seconds of audio (`unit: second`, the
provider that transcribed), `vision` per image and `extract` per document (`provider: local`),
with `turn_id` null: it runs before the turn. A sent WhatsApp message whose Meta charge class costs
(T-105) writes one row with the send: `provider: meta`, `unit: message`, `quantity` 1, `feature`
`message_template` (utility, marketing), `ai_reply_charge` or `message_service` (`service_paid`),
`unit_cost_usd` from the dated table `packages/domain/src/billing/meta-charges.ts` (null while
`TODO verify`), `source_key` = `<messageId>:meta_charge`, `detail = { kind: 'meta_charge',
messageId, chargeClass, country, ruleVersion }`. `service_free` and a utility template inside an
open window write none. `UsageEventsRepo.chargesByClass(org, 'YYYY-MM')` sums them per class for
the invoice's pass-through lines.

### 2.10 Payments by agents

[ADR-0019](adr/0019-agent-payments.md), migration 0051. The business's money, never Charla's
billing (§2.9). Money is always a `bigint` in the currency's ISO 4217 minor unit
(`packages/domain/src/payments/currency.ts` holds the exponents and each provider's exceptions:
Mercado Pago takes CLP and COP in whole units). All three tables have RLS.

**payment_connections** — `id` (`pcn_`), `org_id`, `provider` (`mercadopago | stripe`), `status`
(`pending | active | error | revoked`; only `pending` may lack an account), `external_account_id`
(unique per provider), `country`, `currency` (every request's), `secret_id` (the `payment`
secret; null for Stripe, which stores no token), `token_expires_at`, `livemode`,
`max_request_minor` and `daily_total_minor` (null = no cap of that kind; positive otherwise),
`connected_by` (member), `connected_at`, `last_error` (no token, no payer data), timestamps.
Unique `(org_id, provider)`. A new request locks the row (`FOR UPDATE`) and sums the
connection's requests created since midnight in the org's timezone, except `expired` and
`cancelled` ones, so concurrent requests never exceed the day's total.
**payment_requests** — `id` (`pay_`, sent to the provider as its reference and idempotency key),
`org_id`, `connection_id` and `provider`, `status` (`pending | paid | expired | cancelled |
refunded | partially_refunded`, forward only: `PAYMENT_TRANSITIONS`), `amount_minor` (> 0),
`currency`, `refunded_minor` (0 ≤ refunded ≤ amount), `description`, `provider_request_ref`,
`provider_payment_ref`, `checkout_url` (null until the provider answered; a retry with the same
key completes it), `expires_at`, `paid_at`, `paid_after_close` (paid after `expired` or
`cancelled`: recorded, flagged to the owner), `agent_id`, `conversation_id`, `contact_id`
(same-tenant FKs), `tool_call_id`, `idempotency_key` (unique per org), timestamps.
**payment_events** — `id` (`pev_`), `org_id`, `provider`, `provider_event_id` (unique with the
provider: a resent notification is stored once), `type`, `request_id`, `payload` (redacted by
`redactPaymentPayload`: no credentials, no payer data), `applied_status` (the state this event
moved its request to, set in the transition's transaction; null when it changed nothing),
`processed_at` (null rows are the webhook's outbox, swept by `payment-dispatch`), `created_at`.
**registry.payment_routes** (global) — `(provider, external_account_id) → region, org_id`, the
webhook router's cache (`docs/15` §9).

### 2.11 Appointments in the business's calendar

[ADR-0020](adr/0020-calendar-appointments.md), migration 0055. Both tables have RLS. The rules
that make a slot bookable live in the agent's `features.appointments` (§2.2), not here.

**calendar_connections** — `id` (`ccn_`), `org_id`, `provider` (`google`), `status` (`pending |
active | error | revoked`; only `pending` may lack an account), `external_account` (the
account's email, shown to the owner), `secret_id` (the `calendar` secret), `scopes` (what the
account granted), `token_expires_at`, `connected_by` (member), `connected_at`, `last_error` (no
token, no contact data), timestamps. Unique `(org_id, provider)`: one account per org at GA.
**calendar_appointments** — `id` (`apt_`, the id the agent gives the contact; never the
provider's), `org_id`, `connection_id`, `calendar_id` and `event_id` (unique together; the event
id is derived from the tool call, so a retry meets the event it created), `agent_id`,
`conversation_id`, `contact_id` (same-tenant FKs; a tool acts only on the turn's contact's rows),
`service`, `starts_at` < `ends_at`, `status` (`booked | cancelled`, with `cancelled_at` set
exactly when cancelled), `test` (booked from the installed agent's Probar, titled `[Prueba]`; such a row keeps no
contact nor conversation, whose ids a test turn does not store, and belongs to the agent's tests),
`tool_call_id`, `idempotency_key` (unique per org), timestamps. Erasing a contact deletes its
rows; the event stays in the business's own calendar.

### 2.12 Per-person connections (proposed)

[ADR-0022](adr/0022-per-person-connections.md) (Proposed, #632); tables and migration come with
#634. Both tables have RLS and same-tenant FKs.

**person_connections** — `id` (`pcn_`), `org_id`, `agent_id`, `contact_id`, `connector`
(`google_calendar` or `agent:<name>`), `status` (`pending | connected | expired | revoked`;
`pending` only while a link is alive), `external_account` (never shown to the model),
`secret_id` (the `person_connection` secret, deleted on expiry or revocation), `scopes`,
`token_expires_at`, `connected_at`, `expired_at`, `revoked_at`, `revoked_by` (`contact |
member:<id> | provider | dsar`), `last_error` (no token, no account data), timestamps. Unique
`(org_id, agent_id, contact_id, connector)`: one connection per contact, agent and connector.
**person_connection_links** — `id` (`pcl_`), `org_id`, `connection_id`, `conversation_id`,
`token_hash` (unique; the token itself is never stored), `expires_at` (15 min), `consumed_at`,
`cancelled_at`, `created_at`. Erasing a contact revokes its connections at the provider and
deletes these rows and their secrets.

### 2.13 Account links (proposed)

[ADR-0023](adr/0023-partner-deployments-and-account-linking.md) §3.4 (Proposed, #650); tables and
migration come with #657. Both tables have RLS and same-tenant FKs. A link gives the identity
resolver the tenant's `userId`; it is never a source of scopes.

**account_links** — `id` (`acl_`), `org_id`, `agent_id`, `contact_id`, `tenant_user_id`, `status`
(`linked | revoked | tombstoned`; a revoked or tombstoned row is never reactivated), `linked_at`,
`last_used_at` (the 180-day inactivity sweep reads it), `revoked_at`, `revoked_by` (`tenant |
member:<id> | contact | dsar | inactivity | provider`), timestamps. Unique among `linked` rows per
`(org_id, agent_id, contact_id)`.
**account_link_requests** — `id` (`alr_`), `org_id`, `agent_id`, `contact_id`, `conversation_id`,
`token_hash` (unique; the token is never stored), `expires_at` (15 min), `attempts` (5 wrong codes
cancel it), `consumed_at`, `cancelled_at`, `created_at`. Erasing a contact deletes both kinds of row.

## 3. Mastra storage mapping

| Mastra concept | Our key |
|---|---|
| `resource` | `<orgId>:<contactId>`, e.g. `org_01J…:ctc_01J…` (ids already carry their prefix) |
| `thread` | `<conversationId>`, e.g. `conv_01J…` |
| Working memory template | Per agent version (`features.memory.categories`) |
| Workflow snapshots (suspended tool approvals) | Keyed by `runId`; we store `runId` on `actions.result` / `tool_calls` for resume |
| `mastra_scorers` | Mirrored summary into `signals` and `eval_results` |
| Observability traces | `traceId = turn_id` |

## 4. Invariants

1. One running turn per conversation: one Mastra durable-agent claim per thread (Postgres
   fencing store, 15 s lease) plus the `steer` job deduplicated by conversation id.
2. A WhatsApp number belongs to exactly one agent; replies always leave from the number that
   received the message.
3. `messages.rendered_text` is what the contact saw; the model-facing history is derived from it.
4. An `actions` row in `executing` is settled by a sweeper after `turn_deadline_ms` (Keiki has
   no sweep; we add one, status `failed`, reason `executor_lost`).
5. Secrets are write-only; `GET` returns names and metadata only.
6. Deleting a conversation clears our messages, attachments and the Mastra thread
   (`forget({ orgId, conversationId })`); erasing a contact also deletes its Mastra resource
   (`forget({ orgId, contactId })`, T-142).
7. `consent_events.current = opted_out` blocks every agent-initiated send on that identity and
   contact, and only there; an org-wide opt-out (null identity, BAJA TOTAL) blocks every
   identity of the org (§2.7, ISSUE-1169). A contact-initiated message still gets an answer
   only if policy allows (default yes, with re-opt-in offer), and the keyword's confirmation is
   always sent.

## 5. Drizzle conventions

- Schema in `packages/domain/src/schema/*.ts`, one file per aggregate; migrations in
  `packages/domain/drizzle/`.
- IDs are prefixed ULIDs stored as `text`, so foreign keys (including `org_id`) are `text`.
- Every tenant table: `orgId: text('org_id').notNull().references(() => organizations.id)` plus
  RLS (`ENABLE` and `FORCE`) with policy `tenant_isolation`
  `USING (org_id = current_setting('app.org_id', true))`, and `system_access` for `withSystem`
  (see [19 §6](19-implementation-guide.md)). `organizations` is keyed by `id`. Every
  production `withSystem` caller is listed in `packages/domain/src/system-callers.ts` (T-184).
- Repositories receive the transaction from `withOrg(orgId, fn)`, which sets the GUC with
  `set_config('app.org_id', orgId, true)` (transaction-local).
- References between tenant tables are composite, `(org_id, <ref>_id) REFERENCES parent (org_id,
  id)`, because FK checks bypass RLS: a single-column FK would let a row point at another
  organization's row. Each parent has `UNIQUE (org_id, id)`. `agents.active_version_id` goes
  further: `(org_id, id, active_version_id) REFERENCES agent_versions (org_id, agent_id, id)`, so
  an agent can only run its own versions.
- Unique constraints not spelled out in §2 but added by the M0 schema: `organizations.slug`,
  (`agents.org_id`, `agents.slug`), (`agent_versions.agent_id`, `agent_versions.version`).
- Enums as Postgres enums; JSON columns typed via `$type<T>()` with Zod parsers alongside.
