# 08 · Site Database Access

Read this when an extension needs persistent site data in Postgres.

Cedros extensions do not receive raw `POSTGRES_URI`, database credentials,
`PgPool`, `sqlx::Transaction`, or browser/native database access. The supported
model is manifest-declared `databaseAccess[]` plus the host-mediated wasm
`database` import for extension-owned data: `read` / `count` / `write` /
`delete` / `batch` / `compare-and-set` / `migrate`, scope-checked per call
(`batch` and `compare-and-set` reuse the read/write/delete scopes of their
conditions and inner ops). The canonical WIT shapes and capability grant live
in [`07-wasm-server-backends.md`](07-wasm-server-backends.md); this document
covers how you declare, scope, and design the data.

Database access has three layers:

1. **`databaseAccess[]` declaration**: self-serve manifest metadata for review,
   install gating, and scoped authorization.
2. **Content model and migration provisioning**: cedros-data (≥ 0.1.7)
   auto-creates every declared `surfaces.server.contentModels[]` as a
   host-managed JSONB collection at install. Explicit Wasm `migrate` calls can
   idempotently register a declared content-model id in `jsonb` or `typed` mode,
   but cannot name `surfaces.server.migrations[]`; migration-id-keyed work and
   richer schemas remain host-coordinated.
3. **Runtime database operations**: available only through wasm-backed server
   route/job execution. Admin, web, and native code must not call the database
   directly — they call your extension's server routes instead.

## What you declare

- `surfaces.server.contentModels[]` ids for extension-owned data models
- `surfaces.server.migrations[]` ids for schema changes, if needed
- `databaseAccess[]` entries for each database use case
- read/count/write/delete/migrate scopes with operator-facing summaries
- server route/job hooks that will use the database
- migration, rollback, retention, and deletion behavior
- observability tags for database operations

Manifest shape:

```json
{
  "surfaces": {
    "server": {
      "packageRef": "server",
      "required": true,
      "contentModels": ["cedros-pay:orders"],
      "migrations": ["cedros-pay:orders-v1"],
      "routes": ["cedros-pay:webhook-ingest"],
      "jobs": ["cedros-pay:reconcile-orders"]
    }
  },
  "databaseAccess": [
    {
      "id": "cedros-pay:orders-db",
      "displayName": "Orders Database",
      "description": "Reads and writes extension-owned order records.",
      "contentModels": ["cedros-pay:orders"],
      "migrations": ["cedros-pay:orders-v1"],
      "scopes": [
        {
          "verb": "read",
          "resource": "order records",
          "summary": "Reads checkout and order status."
        },
        {
          "verb": "write",
          "resource": "order records",
          "summary": "Writes order state after checkout or webhook activity."
        }
      ],
      "tags": ["commerce", "orders"]
    }
  ]
}
```

Validation rules:

- `databaseAccess[].id` must be namespaced with `extensionId`.
- `displayName` and `description` are required.
- every entry must reference at least one declared content model or migration
- `contentModels[]` must reference `surfaces.server.contentModels[]`
- `migrations[]` must reference `surfaces.server.migrations[]`
- `scopes[]` must include at least one `read`, `count`, `write`, `delete`, or
  `migrate` scope
- duplicate ids, scopes, tags, content model refs, and migration refs are
  rejected by intake validation where supported
- the server hard-enforces `{extensionId}:` namespacing on
  `surfaces.server.contentModels[]` ids — this is the security boundary on
  collection names

Generic `/entries/query` and `/entries/upsert` endpoints are not an alternative
extension database grant. Any
namespaced collection target requires full `data:admin` authority on those
operator endpoints; content editor or custom-data permissions do not authorize
extension-owned identity, payment, or other models. Extensions use the validated
host database bridge above. The `scopes[].resource` text is a review description,
not a separate model selector: the enforced targets are the access entry's
`contentModels[]` and `migrations[]`, with the declared verbs checked per operation.
Schema management and structural site import/export retain their separate
operator permissions; they do not grant access to the models' entry payloads.

## Runtime contract

Server route handlers and job executors use the host database seam. In the
Host-side Rust shape reference ([`06-runtime-sdk-reference.md`](06-runtime-sdk-reference.md)):

```rust
pub struct CedrosExtensionSiteDatabaseCallContext {
    pub extension_id: String,
    pub access_id: String,
    pub feature: Option<String>,
    pub description: Option<String>,
    pub tags: Vec<String>,
}

pub struct CedrosExtensionSiteDatabaseRequest {
    pub context: CedrosExtensionSiteDatabaseCallContext,
    pub operation: String,
    pub content_model_id: Option<String>,
    pub migration_id: Option<String>,
    pub payload: serde_json::Value,
}
```

`context.access_id` must match a manifest `databaseAccess[].id`. `operation`
**must equal the scope verb** — one of `read`, `count`, `write`, `delete`,
`batch`, `compare-and-set`, or `migrate` (one exception: `count` is also
granted by a `read` scope). `compare-and-set` additionally requires manifest
dependency `cedros-data:extension-storage-cas-v1`; it is not a new scope verb.
Feature-level naming belongs in `context.feature` and `tags`, not in
`operation` (note: via the wasm seam those context fields are not
transmissible — they exist only for in-process callers). Payloads must use the
published host operation contract below. If no contract covers the need,
document the operation as host-coordinated; do not invent raw SQL, ORM models,
or a private query DSL.

### Host operation contract

The validated client enforces manifest scopes, then maps `operation` onto the
content model named by `content_model_id`:

| `operation` | Required scope | `payload` | Result `output` |
|---|---|---|---|
| `read` | `read` | `{ "entryKeys"?: string[], "contains"?: object, "filters"?: Filter[], "orderBy"?: Order, "limit"?: number, "offset"?: number }` | `{ "entries": EntryRecord[] }` |
| `count` | `count` or `read` | `{ "entryKeys"?: string[], "contains"?: object, "filters"?: Filter[] }` (ordering/pagination ignored) | `{ "count": number }` |
| `write` | `write` | `{ "entryKey": string, "data": object }` | `{ "entry": EntryRecord }` |
| `delete` | `delete` | `{ "entryKey": string }` | `{ "removed": boolean }` |
| `batch` | per-op `write`/`delete` | `{ "ops": [{ "op": "write"\|"delete", "contentModelId": string, "entryKey": string, "data"?: object }] }` | `{ "applied": number }` |
| `compare-and-set` | `read` per condition; per-op `write`/`delete`; required capability | `{ "schemaVersion": 1, "conditions": Condition[], "ops": Mutation[], "idempotency"?: Idempotency }` | WIT `cas-outcome`: typed `applied(resultJson)` or `conflict(detail)` |
| `migrate` | `migrate` | `{ "mode"?: "jsonb" \| "typed" }` (defaults `jsonb`) | `{ "collectionName": string }` |

`EntryRecord` is the host row shape returned by `read`/`write`:
`{ "entry_key": string, "payload": object, "updated_at": rfc3339,
"version": string|null }` — your stored data is under `payload`, not at the top
level (source of truth: `server/crates/cedros-foundation/src/models.rs`).
Host-managed JSONB collections always return an opaque `version`; typed
collections return `null` and are outside the v1 CAS contract. Treat versions
only as equality tokens. They rotate on every committed write and are not
reused after delete/recreate.

> **Common wrong assumption:** the returned record keys match the camelCase you
> sent in. **Actually:** request inputs use camelCase (`entryKey`, `data`), but
> the returned `EntryRecord` keys are **snake_case** (`entry_key`,
> `updated_at`). A deserializer that expects camelCase output will silently
> miss these fields.

`batch` applies its `ops` in a single transaction — all commit or all roll
back — for atomic multi-record changes (e.g. register = user + membership +
audit + outbox). Each op is authorized against the access's `write`/`delete`
scope and declared content models; batching grants no authority beyond those.
The `batch` call itself takes the `access-id` and the `ops-json`, not a single
`content-model-id` (each op names its own).

### Optimistic concurrency (`cedros-data:extension-storage-cas-v1`)

Use the additive Wasm import
`database.compare-and-set(access-id, request-json)` whenever more than one
actor, tab, worker, retry, or recovery path can mutate the same logical record.
The v1 request is:

```json
{
  "schemaVersion": 1,
  "conditions": [
    {
      "contentModelId": "vinescribe:workspaces",
      "entryKey": "shared",
      "predicate": "version",
      "expectedVersion": "opaque-token-from-read"
    }
  ],
  "ops": [
    {
      "op": "write",
      "contentModelId": "vinescribe:workspaces",
      "entryKey": "shared",
      "data": { "document": {} }
    }
  ]
}
```

Conditions and transaction boundaries:

- A target is one exact `(contentModelId, entryKey)` in the current Cedros site.
  Conditions may guard mutated records or other records whose state is part of
  the same invariant.
- `predicate: "absent"` is insert-if-absent and requires no expected token.
  `predicate: "version"` requires `expectedVersion` from a prior JSONB read.
- Every condition requires declared `read` scope. Every mutation independently
  requires declared `write` or `delete` scope. All models must belong to the
  same manifest access grant; reserved Core models remain unavailable.
- The host locks every referenced target in deterministic order, including
  absent keys, evaluates every condition, and applies every mutation in one
  Postgres transaction. Limits are 32 conditions and 128 mutations.
- Success is `cas-outcome::applied(resultJson)`. The JSON contains
  `{ "outcome": "applied", "applied", "results", "entries":
  [{ "contentModelId", "entryKey", "version" }], "replayed" }`.
- A mismatch is `cas-outcome::conflict({ conditionIndex, contentModelId,
  entryKey, predicate })`. It is a domain outcome, not `cas-error`, and commits
  zero mutations. Current payloads and tokens are not disclosed.
- `cas-error` is reserved for validation, authorization, quota/rate, runtime,
  and storage failures. Branch on `code` and `category`, never message text.
- CAS uses the same Wasm invocation/host-call timeouts, metrics, standard entry
  revision audit path, and collection/storage limits as ordinary database
  writes. It adds the explicit 32-condition/128-mutation bounds; it does not
  bypass or create authority, capacity, or a separate unmetered route.

No-lost-update rules:

| Flow | Required precondition | Conflict behavior |
|---|---|---|
| create | `absent` on the new key | read the winner or choose a different stable key |
| update | `version` from the state used to build the update | re-read, reconcile/merge, and submit with the new token |
| delete | `version` from the state the actor chose to delete | re-read and require a fresh explicit delete decision |
| multi-record batch | conditions for every record whose state informed the mutations | the whole transaction is rejected; no partial audit/mirror mutation |
| reset/restore/import recovery | `version` for the current live record, or `absent` when recovery may only recreate a missing record | never auto-retry as an unconditional overwrite; surface recover/discard/merge after re-read |

Optional `idempotency: { "key", "fingerprint" }` binds retries to one exact
request. An exact replay returns the original applied result without rotating
versions again; reusing the key for different request material is rejected.
Idempotency does not turn a stale condition into success.

The older `database.batch` JSON parser continues to accept guarded
`conditions[]` for compatibility with already-published guests. New extensions
must declare `cedros-data:extension-storage-cas-v1` and use the named
`compare-and-set` import so capability discovery and WIT pinning fail closed on
older hosts.

Legacy `write`, `delete`, and unguarded `batch` remain last-writer-wins. Use
them only for genuinely single-writer state, append-safe unique keys,
idempotently reproducible projections, or an intentional operator-approved
replacement. Mixing an unconditional mutation with CAS on the same key
forfeits the no-lost-update guarantee. Models that must never accept a legacy
writer can be enrolled in the host's guarded-only write policy.

`read` filters/ordering (the relational read surface):

- `Filter` = `{ "field": string, "op": "eq"|"ne"|"lt"|"lte"|"gt"|"gte"|"like", "value": any }`.
  `field` is a **top-level** payload key. Comparisons run in JSONB space, so a
  same-typed field compares by value — numbers numerically, and **ISO-8601 UTC
  strings chronologically** (store timestamps that way for range sweeps, e.g.
  `{ "field": "expiresAt", "op": "lt", "value": "2026-05-31T00:00:00Z" }`). A
  missing field is excluded. `like` is a case-insensitive substring match and
  requires a string `value`.
- `Order` = `{ "field": string, "direction"?: "asc"|"desc" }` (default `asc`),
  ordered by the payload field with `entryKey` as a stable tiebreak. Omit for
  the default newest-first (`updatedAt`) ordering.
- Pagination bounds: `limit` defaults to **100** when omitted and may not
  exceed **1000** (a higher value is rejected); negative `limit` or `offset` is
  also rejected. Page large sweeps with `offset` rather than a single big
  `limit` — a sweep that never passes `limit` silently processes only 100 rows.

`content_model_id` is the collection name; `migrate` registers it
(idempotent). Ordinary collections never need it — the host auto-creates every
declared content model at install. From the wasm seam,
`migrate(access-id, content-model-id, mode)` **always** targets a
`contentModelId` — there is no payload `collectionName` or `migrationId`
parameter; that form exists only for in-process callers. The mode must be
`jsonb` or `typed`; any other value is rejected.

Operations are rejected unless the matching scope is declared in
`databaseAccess[]` for the `access_id`. Author route/job handlers against this
contract — do not reach for raw SQL.

### Isolation model (read before storing sensitive data)

The `database` seam exposes only *your* extension's declared content models.
Understand what that isolation actually is: per-call **declared-name scoping**
(the content model must be in *that* manifest's declared list) plus a reserved
first-party collection blocklist. Collections share one global keyspace with no
owner column, and the storage layer trusts declared names — manifest
validation enforces `{extensionId}:` namespacing, but the storage layer does
not re-check ownership. Always namespace content-model ids
`{extensionId}:...`; operators review declared names at install. There is no
cross-extension DB access — to consume another extension's data, call its
published HTTP route
([`19-composability-and-relying-party-contracts.md`](19-composability-and-relying-party-contracts.md)).

The host selects the current Cedros site before constructing the extension
database client, and CAS stays inside that same site store. Project/team/actor
are not separate row-level columns in this generic extension keyspace: when an
extension exposes project- or team-scoped records, it must derive that scope
from the verified route/job context, include it in its stable key/payload
policy, and reject mismatches before the database call. CAS preserves the
existing extension/access/site boundary; it does not turn a guest-supplied
project/team id into authority.

## Content model definition status

- content model ids in `surfaces.server.contentModels[]` are ownership metadata
  **and** provisioning input: cedros-data (≥ 0.1.7) auto-creates each declared
  id as a host-managed JSONB collection at install — no `migrate` call needed
- adding an id is idempotent; removing an id from a later manifest revokes the
  extension's runtime access but does not delete the collection or its records
  (only an explicit remove-data operation may do that)
- bespoke typed schemas remain host-coordinated unless Cedros publishes a
  concrete typed-schema format

When host-coordinated, provide this handoff:

| Content model id | Logical records | Fields | Indexes | Retention | Owner | Migration ids |
|---|---|---|---|---|---|---|
| `acme-stats:daily-summary` | daily aggregate metric rows owned by the extension | `site_id`, `day`, `metric_key`, `metric_value`, `source_run_id`, `created_at` | unique `(site_id, day, metric_key)`, index `source_run_id` | keep 400 days, delete on explicit remove-data action | Cedros backend + extension author review | `acme-stats:daily-summary-v1` |

Schema details, SQL, ORM models, and migration files remain reviewed handoff
material until Cedros publishes a content-model schema format or migration
runner. Do not place raw SQL inside the manifest.

## Migration definition status

- migration ids in `surfaces.server.migrations[]` are ownership metadata
- ordinary JSONB collections are auto-created at install from
  `surfaces.server.contentModels[]`; the explicit Wasm `migrate` operation
  registers a content-model id and never consumes these migration ids
- migration-id-keyed work and bespoke SQL-level migrations require host
  coordination
- SQL source, if any, is host-reviewed handoff material and is not
  automatically executed from an uploaded ZIP

For each migration, document:

- migration id, purpose, and target content model
- additive, reversible, or forward-only classification
- schema changes and data changes
- old-version compatibility
- rollback or forward-fix plan

Forward-only migrations require release notes and operator handoff. Do not
claim automatic rollback if the schema cannot safely downgrade.

## Access boundaries

Allowed:

- extension-owned records declared through content model ids
- host-approved migrations declared through migration ids
- route/job database work with extension attribution
- compare-and-set for shared JSONB records and recovery/reset writes
- read-only dashboard summaries through extension server APIs
- redacted, aggregate observability records

Not allowed:

- raw SQL unless the host migration handoff explicitly asks for reviewed SQL
- raw SQL from browser, native, or public web components
- direct credentials or raw pool access in extension packages
- direct reads of Cedros core tables
- direct reads of another extension's private tables
- logging raw row payloads that contain secrets, payment details, auth tokens,
  or unnecessary PII

PII and retention plan:

| Data class | Allowed? | Notes |
|---|---:|---|
| secrets/tokens | no | store in host secret storage only |
| payment details | no | reference Cedros Pay ids instead |
| emails/phones | minimize | store only with purpose and retention |
| aggregate metrics | yes | prefer for dashboard summaries |
| owner-authored content | yes | preserve on disable/remove unless approved |

## Server hook usage

Database work should live in server route handlers or job executors. Admin,
web, and native UI should call extension server APIs or host services rather
than touching the database directly.

Examples:

- read/count: dashboard route loads aggregate order counts with
  `operation: "count"` plus filters
- write: webhook route stores a validated extension-owned order record
- batch: registration route atomically writes user + membership + audit rows
- aggregate: nightly job computes redacted summary rows for dashboard cards

## Acceptance checklist

- All database access is declared in `databaseAccess[]`.
- Every referenced content model or migration exists in `surfaces.server`.
- No docs, code, or package plan requires raw Postgres credentials.
- Route/job hooks identify which database access ids they use.
- Sweeps paginate (`limit`/`offset`) instead of relying on the 100-row default.
- Shared mutable JSONB records declare
  `cedros-data:extension-storage-cas-v1` and use `compare-and-set`.
- Conflict handling re-reads and reconciles; it never silently retries an
  unconditional overwrite.
- Migration, retention, disable, remove, and rollback behavior are documented.

## When not to use direct database access

If the data the extension wants to persist is really a **label on a customer
profile** that operators should see and filter by, use the customer-tag host
capability ([`20-crm-tags-and-source-events.md`](20-crm-tags-and-source-events.md))
instead of building your own CRM-shaped tables. The customer-tag bridge handles
attribution, lifecycle, and admin UI integration for free.

Direct database access is the right call for extension-private state (order
rows, event attendance, billing snapshots). Customer-tag access is the right
call when "this customer matches condition X" needs to appear as a chip on the
admin's CRM view.

Next: [`09-settings-secrets-and-state.md`](09-settings-secrets-and-state.md).