Persistence — data model
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.
Chart 5 — Entity-relationship diagram
Section titled “Chart 5 — Entity-relationship diagram”Database ownership
Section titled “Database ownership”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.
Tables not in the diagram
Section titled “Tables not in the diagram”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. |
The resources namespace: change tracking
Section titled “The resources namespace: change tracking”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-textquse 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, andLIKEwildcards in values are escaped.totalcounts at most 10,001 rows, andtotalCappedsays 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 lastlagMs(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 newestchanged_atper (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.
The destinations namespace: exports
Section titled “The destinations namespace: exports”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).
Operating the resources cache
Section titled “Operating the resources cache”This is the operations runbook for resources.db — disk planning, multi-instance deployments, common symptoms.
Disk-usage planning
Section titled “Disk-usage planning”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-datavolume — 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-clearfor connections you no longer use (requiresX-Admin-Secret).- Switch to
externalmode and let your own warehouse hold the long-term data; the integration-server cache then acts as a hot-read layer.
Multi-instance deployments
Section titled “Multi-instance deployments”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:
externalmode. Each replica keeps its own diverging local cache (harmless — caches are eventually consistent per request), and persistence happens in your own DB via the outboundresource.*events. - Acceptable:
passthroughmode behind a sticky-session load balancer. No cache, no contention. - Postgres (
DATABASE_URLon 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.
Troubleshooting matrix
Section titled “Troubleshooting matrix”| 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. |
Encryption
Section titled “Encryption”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.
Unique constraints
Section titled “Unique constraints”| 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 |
See also
Section titled “See also”- services.md — which service runs each database
- auth-and-tenancy.md — what
tenant_idrepresents and where it’s set - adapter-lifecycle.md — how
adapter_settingsrows get created
opensteward.dev ·Plans and free license ·Terms ·Privacy ·support@opensteward.dev
© 2026 Falco Consulting AB. Built from open-steward/opensteward@a3961d8. This site sets no cookies, runs no analytics and loads nothing from other sites, except platform logos from Brandfetch on the Platforms page.