Skip to content

Persistence — data model

← back to overview

The core persistent tables across the four databases. Each service owns its own DB; tables don’t span databases. The diagram covers the tables on the hot paths — see Tables not in the diagram for the rest.

Storage is SQLite by default and Postgres when DATABASE_URL is set: every store sits behind a single DbHandle (packages/shared/src/db/) and every migration is written for both dialects as a DualMigration. The shipped docker-compose.yml sets no DATABASE_URL, so the paths below are the default.


Diagram: Chart 5 — Entity-relationship diagram Diagram: Chart 5 — Entity-relationship diagram

Each service owns its own database. Files do not share schemas or rows. The paths below are the SQLite default; when DATABASE_URL is set, resolveDbConfig selects the Postgres backend instead and the file path and volume are unused. To move an existing deployment’s data between the two, see switching databases.

Database Owned by File path inside container Volume
admin.db admin-api /app/apps/admin-api/data/admin.db admin-data
credentials.db integration-server /app/apps/integration-server/data/credentials.db integration-server-data
resources.db integration-server /app/apps/integration-server/data/resources.db integration-server-data
webhook-gateway.db webhook-gateway /app/apps/webhook-gateway/data/webhook-gateway.db (db.path in its config.yml) webhook-data

The unified credentials.db holds connections + adapter settings for every vertical in one file. The vertical column on each row keeps cross-vertical look-ups isolated; bexio in accounting and bexio in crm are distinct rows with distinct credentials.

resources.db is the opt-out-able read-through cache active when dataStore.mode != passthrough. Holds resources (canonical rows fetched from upstream), collection_sync (completeness markers), resource_tombstones (deletions), sync_jobs (background full-sync state; origin says why each was queued, and a partial unique index allows one active job per connection) and the four destination* tables (warehouse/lake exports — see below). Sibling of credentials.db on the same volume; created lazily on first boot, removed by docker compose down -v along with everything else. See dev-api.md §7 for the request-path semantics (modes, ?is_live=true, /sync, cold-start warm-up).

Since credentials v3, every connection carries its sync schedule: sync_mode (default / custom / trigger_only / once, default default) and sync_interval_minutes (custom only). connect_sessions carries the same pair since its v3, as the starting schedule of the connection a link creates (a one-time link always reads once). See dev-api.md §7.

Chart 5 covers the hot paths. These tables also exist; find their DDL by grepping for handle.migrate( in the owning package.

Table Database Purpose
analytics_principals credentials.db (Postgres only) Metadata for read-only SQL logins: role name, label, PII flag, scope, connection limit, expiry, revocation and rotation times. Never a password or verifier: the login itself is a Postgres role. Migration namespace analytics. The views, roles and scope tables it describes live in the os_analytics* schemas — see deployment/postgres-analytics.md.
connection_external_accounts credentials.db Maps a platform’s external account id (Stripe acct_…, a Shopify shop domain, a Xero tenant id) to exactly one connection, so an inbound webhook resolves to one row instead of fanning out across every connection on that platform. PK (platform, external_account_id); that PK is the multi-tenant isolation guard, and a second connection claiming the same account raises DuplicateExternalAccountError.
end_user_tokens credentials.db eut_* tokens minted for one end-user’s AI-agent installs. Same prefix-then-scrypt-verify shape as developer_api_keys; default scopes ["mcp:read","eut:read"].
inbound_events credentials.db Durable, ordered log of verified inbound platform webhooks. Doubles as the idempotency ledger — UNIQUE(dedup_key) means a replayed delivery appends once — and as the cursor source for GET /v1/events?after_seq=.
license_state credentials.db The license’s clock-rollback guard: one row, id='singleton', holding max_seen_at, the latest wall-clock time integration-server has seen (epoch ms; BIGINT on Postgres). The license is evaluated at max(now, max_seen_at − 24 h). Migration namespace license_state; in the credentials db-copy profile. After a forward clock jump, fix the clock, then delete the row (DELETE FROM license_state) and restart. See deployment/license.md.
oauth_issuers, oauth_clients, oauth_scope_rules credentials.db The OIDC bearer-token trust store: trusted external IdPs, registered service accounts (unique (issuer_id, subject) → tenant + scopes), and priority-ordered scope-mapping rules for bearer auth on /v1/*. All three migrate under the single oauth_issuers namespace.
sandbox_resources credentials.db Per-tenant /test/v1 fixture rows keyed (tenant_id, vertical, resource_type, resource_id). Sandbox sync jobs are ordinary rows under the reserved _sync_jobs resource type; DELETE /test/v1/reset wipes the tenant’s rows.
settings, runtime_settings admin.db Operator-tunable values (including the live rate-limit budgets siblings poll for over X-Service-Secret). settings is a plain key/value table; today its only rows are the Status page’s cleared tasks — dashboard.dismissed:<taskKey> → {fp, by, at} — written by POST /api/dashboard-tasks/dismissals, honoured by the health poller, and pruned by it after 90 days once the condition is gone (a clear on a still-live condition is kept; wizard:* skips are never pruned; sync:failed:* and refresh:failing:* rows are age-only, since the poller sees neither sync jobs nor refresh attempts).
scope_groups, connect_link_templates admin.db Reusable API-key scope presets and pinned Connect Link definitions.
tenants, tenant_domains admin.db Deployment tenants and the custom domains mapped to them (TENANT_MISMATCH resolution).
users (email, email_source), user_avatars admin.db Console-user profile (admin_store v7). users.email is optional, and email_source says who owns it: manual (an admin; SSO never overwrites it) or idp (refreshed at every SSO sign-in or SCIM update). user_avatars holds one picture per user, apart from the users row the access resolver reads on every request: the bytes exactly as uploaded or fetched (PNG, JPEG or WebP by magic bytes, ≤ 512 KB), their sha256 (the ETag and ?v= version), source upload/idp, and the sha256 of the IdP picture URL so an unchanged sign-in does not refetch. No foreign key: deleteUser removes the row in its transaction. See auth-and-tenancy.md.
dashboards admin.db Operator-made native dashboards (admin_store v4): name, private/shared visibility, owner, and the widget list as JSON (metric ids and chart choices — never a query). Since v6, audience_tenants_json says who a shared dashboard is shared with — NULL is everyone, otherwise a JSON array of tenants (anyone whose scope holds any of them) — and pinned_tenants_json limits its data to the viewer’s scope ∩ those tenants. v6 narrowed a restricted owner’s existing shared dashboards to that owner’s tenants (private when there were none). Rules in core/dashboards/audience.ts. Built-in dashboards live in code. See data-access.md.
superset_dashboards admin.db Registry of embedded Apache Superset dashboards (admin_store v5; optional, Postgres deployments): title, Superset’s own dashboard uuid, the uuid of its embedded configuration (what the guest token and iframe name), verticals and an optional required permission. Written by the superset-bootstrap job through PUT /api/internal/superset/dashboards, read by the admin UI to list and embed them. v6 added the same audience_tenants_json / pinned_tenants_json pair, set in the admin UI; the bootstrap job never writes them. See deployment/superset.md.
identity_providers, identity_links, role_mapping_rules, scim_tokens, scim_groups, sso_transactions, sso_handoff, saml_replay, saml_request_cache admin.db The SSO/SCIM surface — see sso-and-identity-providers.md. Migrated under their own identity_store namespace so they version independently of admin_store.

ResourceStore (packages/shared/src/data-store/resource-store.ts) migrates the resources namespace forward. Five versions exist so far:

Version Adds Why
1 resources + idx_resources_lookup (vertical, platform, tenant_id, resource_type) The cache itself.
2 collection_sync Records that a whole collection was written. The fact that rows exist doesn’t prove the collection is complete: one cachedGet stores one record. Only an unfiltered fetch that reached the last page, or a completed sync, may set a marker. Every mutation clears it.
3 norm_version on both tables Rows are stored normalized. A deploy that changes a transform invalidates exactly the rows the old normalizer wrote, because reads filter on the version.
4 changed_at, data_hash; idx_resources_browse (tenant_id, resource_type, updated_at, pk), idx_resources_changes (vertical, resource_type, changed_at, pk) A change watermark (see below).
5 resource_tombstones + index on deleted_at Deletions a change feed can see.

updated_at vs changed_at. upsert and upsertMany stamp updated_at on every write, including a re-write of unchanged data. sweepCollection depends on that: after a full sync, anything older than the sync’s start is a row the platform no longer returns. That also means updated_at can’t say what changed. changed_at moves only when the sha256 of the stored JSON (data_hash) differs from the previous write. A pre-v4 row with a NULL hash is compared by its JSON instead. Every read treats NULL changed_at as updated_at (COALESCE), and backfillChangedAt() fills pre-v4 rows 5,000 at a time. integration-server starts the backfill at boot and doesn’t wait for it.

Tombstones. delete, sweepCollection, deleteConnection and deleteAll write a row to resource_tombstones in the same transaction as the delete, and only when something was actually deleted. NULL columns widen the scope: (vertical, platform, connection, NULL, NULL) means the whole connection, (vertical, NULL, …) means a whole vertical, and all NULL means everything. tombstonesSince(afterId) reads them in order.

Read APIs for browsing and destinations.

  • page() returns one page for a set of connections. It uses keyset pagination on (updated_at | resource_id, pk) with an opaque base64url cursor, so it stays O(page) at any depth. Filters (eq, contains, gt, lt, is_null) and free-text q use dialect-aware JSON paths: json_extract(data, '$.a.b') on SQLite, data::jsonb #>> '{a,b}' on Postgres. Paths are validated against ^[A-Za-z0-9_]+(\.[A-Za-z0-9_]+)*$ and bound as parameters, and LIKE wildcards in values are escaped. total counts at most 10,001 rows, and totalCapped says when that cap was hit. Which paths a reader may filter on is the caller’s decision; see data classification.
  • changesSince() returns rows whose data changed after a (changed_at, pk) watermark, oldest first. Rows changed within the last lagMs (default 120 s) are held back, because a write stamped at T can commit after a reader has already moved past T.
  • countsByConnection() returns the row count and newest changed_at per (connection, vertical, platform, type), which backs /admin/data/summary.
  • getRecord() returns one row with its metadata.

stripRawPayload applies to every one of these reads, as it does to get and list.

DestinationStore (packages/destinations/src/store.ts) migrates the destinations namespace on the data-store handle, next to resources — the export worker reads the change feed and writes its cursors in the same database. See integrate/destinations.md for the operator view.

Table Purpose
destinations One row per configured destination: type, non-secret config_json, encrypted_secrets (AES-256-GCM, salt opensteward-destinations-v1), include_pii + pii_approved_by, scope_json (owner tenants, verticals/resource types, maskFinancial), interval_sec, enabled, last run outcome, and the run lease (lease_owner, lease_expires_at).
destination_cursors PK (destination_id, vertical, resource_type). The (changed_at, pk) watermark of the last row written per pair; the row keyed ('*', '*') holds the tombstone watermark tombstone_id. A cursor moves only after its file (or Postgres batch) was written. Backfill deletes the row watermarks and keeps the tombstone one.
destination_runs One row per run: trigger (schedule / manual / backfill), status, counts, files_json, error. A run left running by a crashed worker is marked failed once its lease has expired.
destination_connections PK (destination_id, connection_id): the connections a destination has exported, with their owner tenant, so a tombstone for a connection deleted since can still be attributed to a tenant-scoped destination.

Lease. A worker takes a destination with UPDATE destinations SET lease_owner = ?, lease_expires_at = now + lease WHERE id = ? AND (lease_expires_at IS NULL OR lease_expires_at < now) and runs it only if that updated one row, so across replicas exactly one worker runs a destination. The lease is renewed at every flush and released at the end.

Rotation. destinations.encrypted_secrets is registered in DATA_STORE_BLOBS (packages/shared/src/sqlite/encrypted-blobs.ts); pass resources.db to pnpm rotate-store-secret alongside credentials.db (on Postgres the single --database-url covers it).

This is the operations runbook for resources.db — disk planning, multi-instance deployments, common symptoms.

Rough back-of-envelope:

  • One row per resource per connection = ~1–10 KB JSON depending on vertical (invoices are larger than tracking events).
  • 100 connections × 10K invoices × 5 KB ≈ 5 GB. SQLite + WAL handles this fine, but it lives in the integration-server-data volume — size that volume accordingly.

The integration-server runs an hourly size monitor and logs WARN at 100 MB and ERROR at 1 GB by default (tune via DATA_STORE_MAX_SIZE_MB). To shrink an over-sized cache:

  • POST /admin/connections/:id/cache-clear for connections you no longer use (requires X-Admin-Secret).
  • Switch to external mode and let your own warehouse hold the long-term data; the integration-server cache then acts as a hot-read layer.

Local SQLite (modes database and external) is single-writer safe on local disk and unsafe on NFS or any networked filesystem. dataStoreMaintenance logs a one-shot WARN at boot if the resources DB path (DATA_STORE_PATH) looks NFS-mounted.

For HA setups (multiple replicas behind a load balancer):

  • Recommended: external mode. Each replica keeps its own diverging local cache (harmless — caches are eventually consistent per request), and persistence happens in your own DB via the outbound resource.* events.
  • Acceptable: passthrough mode behind a sticky-session load balancer. No cache, no contention.
  • Postgres (DATABASE_URL on every replica): the replicas share one store, cache included, so the local-disk limits above do not apply. Keep replica clocks synchronised (see dev-api.md §7).

docker compose --profile scale up -d brings up Redis only for shared rate-limit budgets — it does not make the SQLite cache HA-safe.

The sync scheduler’s “at most two waiting per vertical” is a per-replica limit: each integration-server replica checks it on its own, against a pending count it reads once per tick, so with N replicas up to 2N jobs can be waiting in one vertical. The one-active-job index still keeps each connection to one queued sync across all of them.

Symptom Look here
Stale data in responses Append ?is_live=true to bypass the cache. If the data is correct then, invalidate via /admin/connections/:id/cache-clear or trigger a sync.
Sync job stuck in running Search the integration-server’s stdout (docker compose logs integration-server) for [sync:<vertical>] lines. Crash recovery resets running → pending on next boot.
Disk usage climbing The hourly size monitor logs WARN at 100 MB. Use the admin cache-clear endpoint or raise DATA_STORE_MAX_SIZE_MB once the volume is sized for it.
resource.* events not firing Confirm DATA_STORE_MODE=external, the subscription’s events includes resource.* (or specific types), and the request carries a Bearer key (admin-secret requests don’t have a tenant — events skip emit).
/sync returns 404 in production Mode is passthrough. Flip to database or external and restart.

These tables hold encrypted or hashed data. The salts are literal constants — they are inputs to key derivation, so the exact string (lower-case, including the -v1 suffix where present) is what decrypts existing rows:

Table Plaintext Encryption Key derivation salt
credentials.data OAuth Credentials JSON AES-256-GCM opensteward-v1 (DEFAULT_SALT in auth/crypto.ts)
adapter_settings.encrypted_config Provider config JSON (clientId, clientSecret, scopes, …) AES-256-GCM opensteward-adapter-settings (passed explicitly at boot, overriding the store’s own -v1 default)
end_users.pii_data PII JSON (firstName / lastName / phone / address / personalIdNumber) AES-256-GCM opensteward-end-users-v1
webhook_subscriptions.secret_encrypted HMAC shared secret (hex) AES-256-GCM opensteward-webhooks-v1
webhook_subscriptions.headers_encrypted Custom delivery headers (JSON map) — a receiver credential AES-256-GCM opensteward-webhooks-v1
destinations.encrypted_secrets (resources.db) Destination credentials JSON (S3 keys, GCS service-account JSON, Azure key/SAS, Postgres password) AES-256-GCM opensteward-destinations-v1
developer_api_keys.hash one-way hash of the plaintext key scrypt (NOT decryptable) opensteward-api-keys-v1

end_user_tokens.hash follows developer_api_keys with its own salt, opensteward-end-user-tokens-v1.

All derive their key from STORE_SECRET (the same env var) via scrypt. The distinct salts mean a leaked key for one table doesn’t decrypt the others.

The admin-api users.password_hash column uses bcrypt (different scheme — passwords need a cost factor and don’t need decryption).

The admin-api company_info.logo_data_url is not encrypted — logos are public branding, not secrets.

Table Unique on Why
users username Login uniqueness
credentials (COALESCE(vertical,''), platform, COALESCE(tenant_id,''), COALESCE(label,'')) One row per labelled connection within a vertical + platform + tenant. The COALESCEs make the index usable on rows with a NULL vertical or no label
adapter_settings (vertical, slug) (composite PK) Colliding slugs across verticals get distinct rows (e.g. bexio in accounting vs bexio in crm)
company_info id = 1 (PK + CHECK) Single-row guarantee
end_users (tenant_id, lower(email)) One customer record per email per tenant; idempotent upsert
developer_api_keys indexed on prefix + on (tenant_id, created_at DESC) Verify-time lookup by prefix; admin list per tenant
webhook_deliveries indexed on (status, next_attempt_at) Worker drains pending rows whose backoff has elapsed
resources composite PK (vertical, platform, tenant_id, resource_type, resource_id) derived as pk text column; indexes on (vertical, platform, tenant_id, resource_type), (tenant_id, resource_type, updated_at, pk) and (vertical, resource_type, changed_at, pk) Cache write-through is keyed by tuple; list queries hit the lookup index, page() the browse index, changesSince() the change index
resource_tombstones id (autoincrement / BIGSERIAL); indexed on deleted_at Monotonic cursor for the deletion feed; deleted_at for retention
destination_cursors composite PK (destination_id, vertical, resource_type) One watermark per exported (vertical, type); ('*', '*') is the tombstone watermark
destination_runs indexed on (destination_id, started_at) Run history per destination, newest first
destination_connections composite PK (destination_id, connection_id) Each exported connection recorded once
sync_jobs partial unique idx_sync_jobs_one_active (connection_id) WHERE status IN ('pending','running'); indexed on (connection_id, status, created_at), (vertical, connection_id, status) and (vertical, status, created_at) At most one active job per connection, across replicas — every enqueue goes through SyncStore.createIfIdle, which reads a violation as “already queued”. Workers claim with a conditional update (claimNext) and skip connections they are already running. (connection_id, status, created_at) serves the schedule’s history reads and the retention sweep. v2 superseded pre-existing duplicate active jobs (kept the oldest).
connection_external_accounts composite PK (platform, external_account_id) One external account maps to exactly one connection — the isolation guard that stops an inbound webhook fanning out across tenants
inbound_events dedup_key Idempotency ledger: a replayed platform delivery appends once and returns inserted: false thereafter