> ## Documentation Index > Fetch the complete documentation index at: https://docs.popsink.com/llms.txt > Use this file to discover all available pages before exploring further. # Data-plane data model > Every table the data-plane keeps about itself, what it is for, and what it is not. The data-plane keeps its own PostgreSQL database. It holds **configuration and operational records only** — which systems to read, how to shape what they produce and where to deliver it, plus who may change any of that. **Your rows never land here**: replicated data travels source → broker → target and is never written to this database. 24 tables in 5 subject areas, 37 references between them. ## How to read this * **An arrow points from the row holding the reference to the row it names.** `subscription → connector` means a subscription row stores the id of a connector row. * **A dashed arrow is a reference with no foreign key behind it.** Always deliberate, and always for the same reason: the referenced row may be deleted while the referring row has to survive it. Every one is listed, with its reason, at the end of this document. * **`PK` is the primary key, `FK` a reference, `UK` a value unique across the table.** Every table is keyed by a UUID v4 generated by the application, and carries `created_at` / `updated_at`. ## Map ```mermaid theme={null} erDiagram env env_member env_request service_account_user team team_member team_request user connector datamodel pipeline subscription worker schema schema_version consumption_metric consumption_metric_daily production_metric production_metric_daily idempotency_record license_state sync_run sync_table user_log user ||--o{ env_member : "user" env ||--o{ env_member : "env" env ||--o{ env_request : "env" user ||--o{ env_request : "from_user" user ||--o{ env_request : "to_user" user ||--o{ service_account_user : "user" env |o--o{ team : "env" user ||--o{ team_member : "user" team ||--o{ team_member : "team" team ||--o{ team_request : "team" user ||--o{ team_request : "from_user" user ||--o{ team_request : "to_user" env |o--o{ user : "active_env" team |o--o{ connector : "team" env |o--o{ connector : "env" connector |o--o{ datamodel : "source_connector" connector |o..o{ datamodel : "error_table_target (no FK)" team ||--o{ pipeline : "team" env |o--o{ pipeline : "env" connector |o--o{ pipeline : "source_connector" connector |o--o{ pipeline : "target_connector" datamodel |o--o{ pipeline : "datamodel" pipeline |o--o{ subscription : "pipeline" datamodel |o--o{ subscription : "datamodel" connector |o--o{ subscription : "target_connector" connector |o..o{ subscription : "error_table_target (no FK)" connector ||--o| worker : "connector" schema ||--o{ schema_version : "schema" subscription ||..o{ consumption_metric : "subscription (no FK)" subscription ||..o{ consumption_metric_daily : "subscription (no FK)" datamodel ||..o{ production_metric : "topic (no FK)" datamodel ||..o{ production_metric_daily : "topic (no FK)" connector ||--o{ sync_run : "connector" user |o--o{ sync_run : "triggered_by_user" sync_run ||--o{ sync_table : "sync_run" user |o--o{ user_log : "user" user |o--o{ user_log : "target_user" ``` ## Access and organisation Who may act, and on what. Environments hold teams, teams hold everything else, and membership in either is what every permission check reads. ```mermaid theme={null} erDiagram env { uuid id PK string name UK } env_member { uuid id PK uuid user_id FK uuid env_id FK } env_request { uuid id PK uuid env_id FK uuid from_user_id FK uuid to_user_id FK } service_account_user { uuid id PK uuid user_id FK } team { uuid id PK uuid env_id FK } team_member { uuid id PK uuid user_id FK uuid team_id FK } team_request { uuid id PK uuid team_id FK uuid from_user_id FK uuid to_user_id FK } user { uuid id PK string email UK uuid active_env_id FK } connector pipeline sync_run user_log user ||--o{ env_member : "user" env ||--o{ env_member : "env" env ||--o{ env_request : "env" user ||--o{ env_request : "from_user" user ||--o{ env_request : "to_user" user ||--o{ service_account_user : "user" env |o--o{ team : "env" user ||--o{ team_member : "user" team ||--o{ team_member : "team" team ||--o{ team_request : "team" user ||--o{ team_request : "from_user" user ||--o{ team_request : "to_user" env |o--o{ user : "active_env" team |o--o{ connector : "team" env |o--o{ connector : "env" team ||--o{ pipeline : "team" env |o--o{ pipeline : "env" user |o--o{ sync_run : "triggered_by_user" user |o--o{ user_log : "user" user |o--o{ user_log : "target_user" ``` ### `env` The top of the hierarchy: one environment, its teams, and the broker they share. Environments are synchronised from the control-plane by UUID, which is why two data-planes must never share a database — the names would collide. | Column | Type | Null | Description | | ------------------------- | -------------- | -------- | --------------------------------------------------------------------------- | | `id` | `UUID` | required | Primary key: a UUID v4 generated by the application, never by the database. | | `name` | `VARCHAR(100)` | optional | Env name. | | `retention_configuration` | `JSON` | required | Json retention env configuration (broker config). | | `config_hash` | `VARCHAR(64)` | optional | SHA-256 hash of deployment configuration (64 hex characters) | | `created_at` | `DATETIME` | optional | When the record was created. | | `updated_at` | `DATETIME` | optional | When the record was last updated. | ### `env_member` A user's membership of an environment, and the role it grants there. One row per (user, env): a duplicate makes every later permission check raise, which is why the write path takes an advisory lock rather than relying on a read-then-insert. | Column | Type | Null | Description | | ------------ | ------------- | -------- | --------------------------------------------------------------------------- | | `id` | `UUID` | required | Primary key: a UUID v4 generated by the application, never by the database. | | `timestamp` | `DATETIME` | required | When the membership was granted. | | `role` | `VARCHAR(16)` | required | Environment role: admin, member or reader (#3531). | | `user_id` | `UUID` | required | The member. | | `env_id` | `UUID` | required | The environment they are a member of. | | `created_at` | `DATETIME` | optional | When the record was created. | | `updated_at` | `DATETIME` | optional | When the record was last updated. | References: `user_id` → `user.id`, `ON DELETE CASCADE`; `env_id` → `env.id`, `ON DELETE CASCADE`. Indexed on: `(user_id, env_id)`. ### `env_request` A pending join or invitation to an environment, and the role acceptance would grant. The same row shape covers both directions: a user asking to join and an admin inviting differ only in who `from_user_id` is. | Column | Type | Null | Description | | -------------- | ------------- | -------- | --------------------------------------------------------------------------- | | `id` | `UUID` | required | Primary key: a UUID v4 generated by the application, never by the database. | | `role` | `VARCHAR(16)` | required | Env role granted on acceptance: admin, member or reader (#3531). | | `env_id` | `UUID` | required | Environment ID. | | `from_user_id` | `UUID` | required | Request author (user or admin). | | `to_user_id` | `UUID` | required | Target user of the request. | | `status` | `VARCHAR(32)` | required | Request status. | | `created_at` | `DATETIME` | optional | When the record was created. | | `updated_at` | `DATETIME` | optional | When the record was last updated. | References: `env_id` → `env.id`, `ON DELETE CASCADE`; `from_user_id` → `user.id`; `to_user_id` → `user.id`. Indexed on: `(env_id)`; `(from_user_id)`; `(to_user_id)`. ### `service_account_user` A named machine identity, backed by a real user row. Its token carries both ids, and every permission check reads the user — the service account only gives the token a name and a lifetime of its own. | Column | Type | Null | Description | | ------------ | -------------- | -------- | ------------------------------------------------------------------------------ | | `id` | `UUID` | required | Primary key: a UUID v4 generated by the application, never by the database. | | `name` | `VARCHAR(300)` | required | Service account user name. | | `user_id` | `UUID` | required | The user identity this service account acts as, and whose permissions it gets. | | `created_at` | `DATETIME` | optional | When the record was created. | | `updated_at` | `DATETIME` | optional | When the record was last updated. | References: `user_id` → `user.id`, `ON DELETE CASCADE`. ### `team` The unit of ownership: every pipeline and connector belongs to exactly one. Team membership is what a write is authorized against, so a team is also the smallest thing two people can be given different access to. | Column | Type | Null | Description | | ------------- | -------------- | -------- | --------------------------------------------------------------------------- | | `id` | `UUID` | required | Primary key: a UUID v4 generated by the application, never by the database. | | `name` | `VARCHAR(100)` | optional | Team name. | | `description` | `TEXT` | optional | Team description. | | `env_id` | `UUID` | optional | Environment the team lives in; connectors and pipelines mirror it (#2527). | | `created_at` | `DATETIME` | optional | When the record was created. | | `updated_at` | `DATETIME` | optional | When the record was last updated. | References: `env_id` → `env.id`, `ON DELETE SET NULL`. Unique together: (`env_id`, `name`). ### `team_member` A user's membership of a team — the row every write permission check reads. One row per (user, team), for the same reason `EnvMember` is: a duplicate makes every later check raise. An API key's memberships are derived from its grants and written here too, so authorization has one shape whoever the caller is. | Column | Type | Null | Description | | ------------ | ---------- | -------- | --------------------------------------------------------------------------- | | `id` | `UUID` | required | Primary key: a UUID v4 generated by the application, never by the database. | | `timestamp` | `DATETIME` | required | When the membership was granted. | | `admin` | `BOOLEAN` | optional | Whether the member administrates the team, rather than only working in it. | | `user_id` | `UUID` | required | The member. | | `team_id` | `UUID` | required | The team they are a member of. | | `created_at` | `DATETIME` | optional | When the record was created. | | `updated_at` | `DATETIME` | optional | When the record was last updated. | References: `user_id` → `user.id`, `ON DELETE CASCADE`; `team_id` → `team.id`, `ON DELETE CASCADE`. Indexed on: `(user_id, team_id)`. ### `team_request` A pending join or invitation to a team, and whether acceptance would grant admin. The same row shape covers both directions: a user asking to join and an admin inviting differ only in who `from_user_id` is. | Column | Type | Null | Description | | -------------- | ------------- | -------- | --------------------------------------------------------------------------- | | `id` | `UUID` | required | Primary key: a UUID v4 generated by the application, never by the database. | | `admin` | `BOOLEAN` | required | Is admin request? | | `team_id` | `UUID` | required | Team ID. | | `from_user_id` | `UUID` | required | Request author (user or admin). | | `to_user_id` | `UUID` | required | Target user of the request. | | `status` | `VARCHAR(32)` | required | Request status. | | `created_at` | `DATETIME` | optional | When the record was created. | | `updated_at` | `DATETIME` | optional | When the record was last updated. | References: `team_id` → `team.id`; `from_user_id` → `user.id`; `to_user_id` → `user.id`. Indexed on: `(from_user_id)`; `(team_id)`; `(to_user_id)`. ### `user` A person or machine identity that can act on this data-plane. Also backs every service account: a service-account token carries both ids and every permission check reads the user, so there is no second identity table. | Column | Type | Null | Description | | ----------------- | --------------- | -------- | ----------------------------------------------------------------------------------------------------------------------------------------------------- | | `id` | `UUID` | required | Primary key: a UUID v4 generated by the application, never by the database. | | `email` | `VARCHAR(320)` | required | Login and, for historical rows, the actor of record. | | `hashed_password` | `VARCHAR(1024)` | optional | Argon2 hash of the local password. NULL is the marker for an identity that has none — control-plane and service-account users authenticate elsewhere. | | `is_active` | `BOOLEAN` | required | Whether the identity may authenticate at all; deactivation is how a user is revoked without erasing the history they authored. | | `is_superuser` | `BOOLEAN` | required | Bypasses the environment and team checks. | | `is_verified` | `BOOLEAN` | required | Whether the email address was confirmed. | | `version` | `INTEGER` | required | Bumped on every change, so the control-plane membership sync can tell which side is stale without comparing every field. | | `active_env_id` | `UUID` | optional | Environment the UI last had selected. A preference, never an authorization. | | `created_at` | `DATETIME` | optional | When the record was created. | | `updated_at` | `DATETIME` | required | When the record was last updated. Unlike the inherited column this one is timezone-aware and indexed: the membership sync pages on it. | References: `active_env_id` → `env.id`, `ON DELETE SET NULL`. Indexed on: `unique (email)`; `(updated_at)`; `(version)`. ## Pipelines The configured data flow itself: a source connector reads a system, a datamodel shapes what it produced, and a subscription delivers that into a target connector. ```mermaid theme={null} erDiagram connector { uuid id PK uuid team_id FK uuid env_id FK } datamodel { uuid id PK uuid source_connector_id FK uuid error_table_target_id FK } pipeline { uuid id PK uuid team_id FK uuid env_id FK uuid source_connector_id FK uuid target_connector_id FK uuid datamodel_id FK } subscription { uuid id PK uuid pipeline_id FK uuid datamodel_id FK uuid target_connector_id FK uuid error_table_target_id FK } worker { uuid id PK uuid connector_id FK, UK } consumption_metric consumption_metric_daily env production_metric production_metric_daily sync_run team team |o--o{ connector : "team" env |o--o{ connector : "env" connector |o--o{ datamodel : "source_connector" connector |o..o{ datamodel : "error_table_target (no FK)" team ||--o{ pipeline : "team" env |o--o{ pipeline : "env" connector |o--o{ pipeline : "source_connector" connector |o--o{ pipeline : "target_connector" datamodel |o--o{ pipeline : "datamodel" pipeline |o--o{ subscription : "pipeline" datamodel |o--o{ subscription : "datamodel" connector |o--o{ subscription : "target_connector" connector |o..o{ subscription : "error_table_target (no FK)" connector ||--o| worker : "connector" subscription ||..o{ consumption_metric : "subscription (no FK)" subscription ||..o{ consumption_metric_daily : "subscription (no FK)" datamodel ||..o{ production_metric : "topic (no FK)" datamodel ||..o{ production_metric_daily : "topic (no FK)" connector ||--o{ sync_run : "connector" ``` ### `connector` An integration point with one external system, read from or written to. Whether it is a source or a target is derived from its type, never chosen; its configuration is encrypted at rest, so anything selecting `json_configuration` — even through a join — owes a decrypt (#3698). Exactly one worker runs it. | Column | Type | Null | Description | | -------------------- | -------------- | -------- | ---------------------------------------------------------------------------------------------------------------------------------------------- | | `id` | `UUID` | required | Connector ID. | | `name` | `VARCHAR(100)` | optional | Connector name. | | `connector_type` | `VARCHAR(50)` | required | Connector specific type (Snowflake target, Job SMT, PostgreSQL source...). | | `json_configuration` | `JSON` | required | Json connector definition. | | `way` | `VARCHAR(6)` | required | Is the connector a source or a target? | | `team_id` | `UUID` | optional | Team ID that owns this connector. | | `env_id` | `UUID` | optional | Env ID this connector belongs to (mirrors team.env\_id). *Mirrors team.env\_id so the (env, name) uniqueness is one table's business (#2527).* | | `created_at` | `DATETIME` | optional | When the record was created. | | `updated_at` | `DATETIME` | optional | When the record was last updated. | References: `team_id` → `team.id`; `env_id` → `env.id`. Unique together: (`env_id`, `name`). Indexed on: `(lower(name))`. ### `datamodel` The shape of what a source produced, and the topic it is republished on. Sits between the raw CDC topic and the subscriptions: one datamodel feeds as many subscriptions as there are places the data has to land. | Column | Type | Null | Description | | ----------------------- | -------------- | -------- | ------------------------------------------------------------------------------------ | | `id` | `UUID` | required | DataModel ID. | | `name` | `VARCHAR(100)` | required | DataModel name. | | `config` | `JSON` | required | JSON configuration defining the data structure/schema. | | `source_connector_id` | `UUID` | optional | Associated source connector ID. | | `source_topic` | `VARCHAR(255)` | optional | Associated source topic to read from. | | `target_topic` | `VARCHAR(255)` | optional | Associated target topic to write to. | | `consumer_id` | `VARCHAR(255)` | optional | Consumer group ID for Kafka consumption. | | `error_table_enabled` | `BOOLEAN` | optional | Associated error table enabled. | | `error_table_name` | `VARCHAR(255)` | optional | Associated error table name. | | `error_table_target_id` | `UUID` | optional | Associated error table target connector ID (None when no error table is configured). | | `enabled` | `BOOLEAN` | optional | Whether this datamodel is enabled for processing. | | `created_at` | `DATETIME` | optional | When the record was created. | | `updated_at` | `DATETIME` | optional | When the record was last updated. | References: `source_connector_id` → `connector.id`, `ON DELETE SET NULL`; `error_table_target_id` → `connector.id` (no foreign key: Optional pointer at the connector receiving rejected rows; unset on most datamodels.). Indexed on: `(lower(name))`. ### `pipeline` One configured flow from a source connector to a target connector. A pipeline has no state column of its own: what the UI shows is its worker's state, and "draft" is the computed absence of one — `is_completed` false with the worker paused. | Column | Type | Null | Description | | --------------------- | -------------- | -------- | --------------------------------------------------------------------------------------------------------------------------------------------- | | `id` | `UUID` | required | Pipeline ID. | | `name` | `VARCHAR(100)` | optional | Pipeline name. | | `json_configuration` | `JSON` | required | Json pipline definition. | | `version` | `INTEGER` | required | Pipeline version. Incremented when the pipeline is modified. | | `is_completed` | `BOOLEAN` | optional | Whether the pipeline configuration is complete (has subscription config). | | `error_table_config` | `JSON` | optional | Error table configuration for the pipeline. | | `team_id` | `UUID` | required | Attached team ID. | | `env_id` | `UUID` | optional | Env ID this pipeline belongs to (mirrors team.env\_id). *Mirrors team.env\_id so the (env, name) uniqueness is one table's business (#2527).* | | `source_connector_id` | `UUID` | optional | Source connector ID. | | `target_connector_id` | `UUID` | optional | Target connector ID. | | `datamodel_id` | `UUID` | optional | Data model ID. | | `created_at` | `DATETIME` | optional | When the record was created. | | `updated_at` | `DATETIME` | optional | When the record was last updated. | References: `team_id` → `team.id`; `env_id` → `env.id`; `source_connector_id` → `connector.id`; `target_connector_id` → `connector.id`; `datamodel_id` → `datamodel.id`. Unique together: (`env_id`, `name`). Indexed on: `(lower(name))`. ### `subscription` One delivery: how a datamodel's rows are transformed and written into one target table. Identified by (datamodel, target connector, target table), because fanning one topic out into several tables of the same target is a shape the wizard produces on purpose. | Column | Type | Null | Description | | ----------------------- | -------------- | -------- | ----------------------------------------------------------------------------------------------------------------------------------------- | | `id` | `UUID` | required | Subscription ID. | | `name` | `VARCHAR(255)` | required | Name of the subscription. | | `config` | `JSON` | required | JSON configuration for the subscription. | | `transform_config` | `JSON` | required | JSON configuration for data transformations. | | `pipeline_id` | `UUID` | optional | Associated pipeline ID. | | `datamodel_id` | `UUID` | optional | Associated datamodel ID. | | `target_connector_id` | `UUID` | optional | Associated target connector ID. | | `error_table_enabled` | `BOOLEAN` | required | Associated error table enabled. | | `error_table_name` | `VARCHAR(255)` | optional | Associated error table name. | | `error_table_target_id` | `UUID` | optional | Associated error table target connector ID (UUID). | | `target_table_name` | `VARCHAR(255)` | optional | Target table name for output data. | | `consumer_id` | `VARCHAR(255)` | optional | Consumer group ID for Kafka consumer. Defaults to subscription ID if not provided. | | `backfill` | `BOOLEAN` | optional | Reload trigger: when switched on, the subscription's consumer group is reset to earliest (#2965). Default aligned with the domain entity. | | `enabled` | `BOOLEAN` | optional | Whether this subscription is enabled for processing. | | `created_at` | `DATETIME` | optional | When the record was created. | | `updated_at` | `DATETIME` | optional | When the record was last updated. | References: `pipeline_id` → `pipeline.id`, `ON DELETE SET NULL`; `datamodel_id` → `datamodel.id`, `ON DELETE SET NULL`; `target_connector_id` → `connector.id`, `ON DELETE SET NULL`; `error_table_target_id` → `connector.id` (no foreign key: Optional pointer at the connector receiving rejected rows; unset on most subscriptions.). Unique together: (`datamodel_id`, `target_connector_id`, `target_table_name`). ### `worker` The execution state of one connector's pod — both what we want and what we observed. `state` carries the two at once: `STOPPING` is an intent, `LIVE` an observation, so every runtime write goes through `Worker.accepts_reported_state()` under a row lock. A heartbeat older than 90 s means the pod stopped reporting, not that it stopped. | Column | Type | Null | Description | | ------------------- | ------------ | -------- | ------------------------------------------------------------------------------------------------------------------------------------------------- | | `id` | `UUID` | required | Worker ID. | | `state` | `VARCHAR(8)` | required | Worker current state. | | `connector_id` | `UUID` | required | Connector ID that this worker belongs to. | | `last_heartbeat_at` | `DATETIME` | optional | Timestamp of last heartbeat from worker. | | `reason` | `VARCHAR` | optional | Optional human-readable detail for the current state (e.g. error message). | | `stop_requested_at` | `DATETIME` | optional | When an operator requested the worker to stop. Set iff state is STOPPING, cleared on every other transition; drives the stopping timeout (#3509). | | `launch_generation` | `UUID` | required | Opaque token identifying the currently launched pod generation (#3523). | | `created_at` | `DATETIME` | optional | When the record was created. | | `updated_at` | `DATETIME` | optional | When the record was last updated. | References: `connector_id` → `connector.id`, `ON DELETE CASCADE`. ## Schema registry The Avro schemas seen on the topics, de-duplicated by content hash and versioned per subject. ```mermaid theme={null} erDiagram schema { uuid id PK } schema_version { uuid id PK uuid schema_id FK } schema ||--o{ schema_version : "schema" ``` ### `schema` One schema document, stored once per distinct content. De-duplicated by `hash`: the same Avro schema registered under twenty subjects is one row here and twenty `SchemaVersion` rows pointing at it. | Column | Type | Null | Description | | ------------ | ------------ | -------- | --------------------------------------------------------------------------- | | `id` | `UUID` | required | Primary key: a UUID v4 generated by the application, never by the database. | | `type` | `VARCHAR(4)` | required | Which schema language: json or avro. | | `hash` | `VARCHAR` | required | Content hash — the identity that makes a re-registration a no-op. | | `schema` | `TEXT` | required | The schema document itself, verbatim. | | `created_at` | `DATETIME` | optional | When the record was created. | | `updated_at` | `DATETIME` | optional | When the record was last updated. | ### `schema_version` One version of one subject: what a topic's key or value looked like at a point in time. The history is what lets a consumer read a message written before the current schema. | Column | Type | Null | Description | | ------------ | ---------- | -------- | --------------------------------------------------------------------------- | | `id` | `UUID` | required | Primary key: a UUID v4 generated by the application, never by the database. | | `timestamp` | `DATETIME` | required | When this version was registered. | | `version` | `INTEGER` | required | Version number within the subject, counting from 1. | | `subject` | `VARCHAR` | required | Registry subject, conventionally '\-key' or '\-value'. | | `schema_id` | `UUID` | required | The de-duplicated schema document this version resolves to. | | `created_at` | `DATETIME` | optional | When the record was created. | | `updated_at` | `DATETIME` | optional | When the record was last updated. | References: `schema_id` → `schema.id`. ## Throughput metrics Append-only evidence of what actually moved: raw snapshots pushed by workers, and the closed-day rollups the charts read. ```mermaid theme={null} erDiagram consumption_metric { uuid id PK uuid subscription_id FK } consumption_metric_daily { uuid id PK uuid subscription_id FK } production_metric { uuid id PK string topic FK } production_metric_daily { uuid id PK string topic FK } datamodel subscription subscription ||..o{ consumption_metric : "subscription (no FK)" subscription ||..o{ consumption_metric_daily : "subscription (no FK)" datamodel ||..o{ production_metric : "topic (no FK)" datamodel ||..o{ production_metric_daily : "topic (no FK)" ``` ### `consumption_metric` Append-only table for per-subscription consumption metrics. Each row represents one consumption snapshot for a given subscription/topic over the interval \[from\_ts, to\_ts] (epoch ms). Distinct from `metric` which stores source-side production counts: two subscriptions on the same source topic produce two independent rows here, capturing their actual delivery progress (consumed/delivered/errored/lag). | Column | Type | Null | Description | | ----------------- | ---------- | -------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `id` | `UUID` | required | Primary key: a UUID v4 generated by the application, never by the database. | | `subscription_id` | `UUID` | required | Subscription this consumption snapshot belongs to. No FK by design: deleting a subscription must NOT erase its historical consumption rows — the id stays readable so past deliveries can still be traced (billing, post-mortems). | | `topic` | `VARCHAR` | required | Kafka source topic being consumed. | | `from_ts` | `BIGINT` | required | Start of the measurement interval (epoch ms). | | `to_ts` | `BIGINT` | required | End of the measurement interval (epoch ms). | | `consumed_count` | `BIGINT` | required | Number of records read from the source topic during the interval. | | `delivered_count` | `BIGINT` | required | Number of records successfully delivered to the target during the interval. | | `error_count` | `BIGINT` | required | Number of records that failed delivery during the interval. | | `consumer_lag` | `BIGINT` | optional | Consumer-group lag (records behind) at to\_ts. Null when not measurable. | | `created_at` | `DATETIME` | optional | When the record was created. | | `updated_at` | `DATETIME` | optional | When the record was last updated. | References: `subscription_id` → `subscription.id` (no foreign key: Deleting a subscription must not erase what it already delivered, and no FK action means 'keep the row and the id'.). Indexed on: `(subscription_id, created_at)`. ### `consumption_metric_daily` Pre-aggregated daily rollup of `consumption_metric`, one row per (subscription\_id, day). Populated by the metric rollup background task for closed UTC days only, mirroring `ProductionMetricDaily`. Nothing reads it yet: the dashboard summary is production-only, and the per-subscription detail path (raw rows + live lag) must keep reading raw — this table is the substrate for the detail-page aggregation follow-up flagged in #3200. `consumer_lag` is deliberately absent: it is a point-in-time gauge, not a summable counter, and its live value comes from the broker at read time. No FK on `subscription_id` — evidence must survive subscription deletion (see docs/claude/architecture-decisions.md). | Column | Type | Null | Description | | ----------------- | ---------- | -------- | ------------------------------------------------------------------------------ | | `id` | `UUID` | required | Primary key: a UUID v4 generated by the application, never by the database. | | `subscription_id` | `UUID` | required | Subscription the deliveries belong to (no FK; mirrors consumption\_metric). | | `topic` | `VARCHAR` | required | Source topic consumed (last seen within the day; informational). | | `day` | `DATE` | required | Closed UTC calendar day this row aggregates. | | `consumed_count` | `BIGINT` | required | SUM(consumed\_count) over the day. | | `delivered_count` | `BIGINT` | required | SUM(delivered\_count) over the day. | | `error_count` | `BIGINT` | required | SUM(error\_count) over the day. | | `span_ms` | `BIGINT` | required | SUM of per-row measurement spans (to\_ts - from\_ts clamped at 0), rate input. | | `min_from` | `BIGINT` | required | MIN(from\_ts) over the day (epoch ms), rate input. | | `max_to` | `BIGINT` | required | MAX(to\_ts) over the day (epoch ms), rate input. | | `row_count` | `BIGINT` | required | COUNT(\*) of raw rows aggregated, rate fallback input. | | `created_at` | `DATETIME` | optional | When the record was created. | | `updated_at` | `DATETIME` | optional | When the record was last updated. | References: `subscription_id` → `subscription.id` (no foreign key: Same rule as the raw rows it aggregates: a rollup outlives the subscription.). Indexed on: `unique (subscription_id, day)`. ### `production_metric` Append-only table for CDC event production metrics pushed by source workers. Each row represents one metric snapshot for a given topic, covering the time interval \[from\_ts, to\_ts] expressed as epoch timestamps (ms). `created_at` (inherited from Base) acts as the ingestion timestamp. Paired with `ConsumptionMetric` (delivery counts on the target side). | Column | Type | Null | Description | | ------------ | ---------- | -------- | --------------------------------------------------------------------------- | | `id` | `UUID` | required | Primary key: a UUID v4 generated by the application, never by the database. | | `from_ts` | `BIGINT` | required | Start of the measurement interval (epoch ms). | | `to_ts` | `BIGINT` | required | End of the measurement interval (epoch ms). | | `topic` | `VARCHAR` | required | Kafka topic name. | | `events` | `JSON` | required | CDC event counts: \{total, r, c, u, d}. | | `created_at` | `DATETIME` | optional | When the record was created. | | `updated_at` | `DATETIME` | optional | When the record was last updated. | References: `topic` → `datamodel.source_topic` (no foreign key: Production is counted per Kafka topic, and a topic outlives the datamodel that named it; the read path resolves the topic set, then filters on it.). Indexed on: `(topic, created_at)`. ### `production_metric_daily` Pre-aggregated daily rollup of `production_metric`, one row per (topic, day). Populated by the metric rollup background task for closed UTC days only; the monitoring read path unions this table with raw `production_metric` rows for the still-open day. Recomputed idempotently (delete + insert per day), so a crashed or duplicated rollup cycle is harmless. Like the raw metric tables, this is derived evidence keyed by a plain `topic` string — no FK, so history survives topic renames and datamodel deletions (see docs/claude/architecture-decisions.md). `span_ms` / `min_from` / `max_to` / `row_count` preserve the message-rate inputs the summary math needs (`_effective_metrics_duration_ms`); without them msg/s would silently change versus raw aggregation. | Column | Type | Null | Description | | -------------- | ---------- | -------- | ---------------------------------------------------------------------------------------------------------- | | `id` | `UUID` | required | Primary key: a UUID v4 generated by the application, never by the database. | | `topic` | `VARCHAR` | required | Kafka topic name (no FK; mirrors production\_metric.topic). | | `day` | `DATE` | required | Closed UTC calendar day this row aggregates (matches date\_trunc('day', created\_at)). | | `creates` | `BIGINT` | required | SUM(events->c) over the day. | | `updates` | `BIGINT` | required | SUM(events->u) over the day. | | `deletes` | `BIGINT` | required | SUM(events->d) over the day. | | `reads` | `BIGINT` | required | SUM(events->r) over the day. | | `total_events` | `BIGINT` | required | SUM(events->total) over the day. | | `bytes` | `BIGINT` | required | SUM(events->bytes) over the day. No worker emits it today (always 0); kept for parity with the raw reader. | | `span_ms` | `BIGINT` | required | SUM of per-row measurement spans (to\_ts - from\_ts clamped at 0), rate input. | | `min_from` | `BIGINT` | required | MIN(from\_ts) over the day (epoch ms), rate input. | | `max_to` | `BIGINT` | required | MAX(to\_ts) over the day (epoch ms), rate input. | | `row_count` | `BIGINT` | required | COUNT(\*) of raw rows aggregated, rate fallback input. | | `created_at` | `DATETIME` | optional | When the record was created. | | `updated_at` | `DATETIME` | optional | When the record was last updated. | References: `topic` → `datamodel.source_topic` (no foreign key: Same rule as the raw rows it aggregates.). Indexed on: `unique (topic, day)`. ## Operations Everything that records an operator action or a run rather than a configuration: snapshot syncs, the audit log, replay protection and the license verdict. ```mermaid theme={null} erDiagram idempotency_record { uuid id PK } license_state { uuid id PK } sync_run { uuid id PK uuid connector_id FK uuid triggered_by_user_id FK } sync_table { uuid id PK uuid sync_run_id FK } user_log { uuid id PK uuid user_id FK uuid target_user_id FK } connector user connector ||--o{ sync_run : "connector" user |o--o{ sync_run : "triggered_by_user" sync_run ||--o{ sync_table : "sync_run" user |o--o{ user_log : "user" user |o--o{ user_log : "target_user" ``` ### `idempotency_record` Cached response for an (endpoint, idempotency\_key) pair. | Column | Type | Null | Description | | ----------------- | -------------- | -------- | --------------------------------------------------------------------------- | | `id` | `UUID` | required | Primary key: a UUID v4 generated by the application, never by the database. | | `endpoint` | `VARCHAR(200)` | required | Route name (e.g. 'envs:create') the key is scoped to. | | `key` | `VARCHAR(200)` | required | Client-supplied Idempotency-Key header value. | | `request_hash` | `VARCHAR(64)` | required | SHA-256 of the serialised request payload. Mismatch on replay returns 422. | | `response_status` | `INTEGER` | required | HTTP status code that the original request produced. | | `response_body` | `JSON` | required | JSON-serialised response body returned on the original request. | | `expires_at` | `DATETIME` | required | When the record may be evicted (typically created\_at + 24h). | | `created_at` | `DATETIME` | optional | When the record was created. | | `updated_at` | `DATETIME` | optional | When the record was last updated. | Unique together: (`endpoint`, `key`). Indexed on: `(expires_at)`. ### `license_state` Single cached row holding the latest license verdict from the control-plane. | Column | Type | Null | Description | | -------------------- | ---------- | -------- | ----------------------------------------------------------------------------- | | `id` | `UUID` | required | Primary key: a UUID v4 generated by the application, never by the database. | | `license_active` | `BOOLEAN` | required | Last verdict from the control-plane. Defaults to inactive (default-deny). | | `license_expires_at` | `DATETIME` | optional | Absolute license expiry (naive UTC), for display and grace reasoning. | | `last_seen_at` | `DATETIME` | required | When the data-plane last received a verdict. Drives the grace window (#3083). | | `created_at` | `DATETIME` | optional | When the record was created. | | `updated_at` | `DATETIME` | optional | When the record was last updated. | ### `sync_run` A connector's current sync (blocking snapshot of 1..N tables, driven one at a time). | Column | Type | Null | Description | | ---------------------- | -------------- | -------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `id` | `UUID` | required | Primary key: a UUID v4 generated by the application, never by the database. | | `connector_id` | `UUID` | required | Source connector this sync belongs to. | | `status` | `VARCHAR(9)` | required | Overall sync status (syncing/done/cancelled). | | `triggered_by_user_id` | `UUID` | optional | Id of the user who started this sync, as recorded at trigger time. Convenience link only: SET NULL on user delete, and the control-plane user sync deletes/re-creates rows whenever a user churns id — read the author from triggered\_by\_email instead (#2978). | | `triggered_by_email` | `VARCHAR(320)` | optional | Author of record: email of the user who started this sync, denormalised at trigger time so authorship survives any change to the user row (same pattern as user\_log.user\_email). NULL means no author was recorded: a run triggered automatically, or one written before this column existed whose user\_id was already nulled at backfill time. *The actor of record. User ids churn on delete and re-invite, so history reads the email, never the id.* | | `stalled_since` | `DATETIME` | optional | When this run was first observed to have stopped making progress (#3597); NULL while healthy. Stamped with coalesce(existing, now) so no write can postpone it, and it is the clock the abandon ceiling counts on — which is why it is persisted rather than kept in the orchestrator's memory like the gate state: it must survive leadership moving between pods. | | `stalled_reason` | `VARCHAR(21)` | optional | Why the run is/was stalled (#3597). Kept after a run is abandoned for a stall so the History row can explain why it ended; NULL on a run that never stalled. | | `created_at` | `DATETIME` | optional | When the record was created. | | `updated_at` | `DATETIME` | optional | When the record was last updated. | References: `connector_id` → `connector.id`, `ON DELETE CASCADE`; `triggered_by_user_id` → `user.id`, `ON DELETE SET NULL`. Indexed on: `unique ((CASE WHEN status = 'SYNCING' THEN connector_id END))`; `(connector_id)`. ### `sync_table` One table's place in a sync queue plus its blocking-snapshot progress. | Column | Type | Null | Description | | ----------------------- | ------------- | -------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `id` | `UUID` | required | Primary key: a UUID v4 generated by the application, never by the database. | | `sync_run_id` | `UUID` | required | Parent sync run. | | `table_name` | `VARCHAR` | required | Fully-qualified source table name as the worker expects it (e.g. SCHEMA.TABLE). | | `position` | `INTEGER` | required | Order of the table within the queue (0-based). | | `status` | `VARCHAR(11)` | required | Per-table status (queued/in\_progress/completed/partial/failed). | | `approximate_row_count` | `BIGINT` | optional | Catalog-stat row estimate from #2910; None when the source exposes no estimate. | | `group_id` | `UUID` | optional | Shared id of the batched small-table step this table belongs to (#2979); NULL when the table is snapshotted on its own (large table or unknown estimate). | | `rows_ingested` | `BIGINT` | required | Rows snapshotted so far, as reported by the worker. | | `started_at` | `DATETIME` | optional | When this table's blocking snapshot started. | | `finished_at` | `DATETIME` | optional | When this table reached a terminal state (completed/partial/failed). | | `error_reason` | `VARCHAR` | optional | Short failure detail reported by the worker when status is 'failed' (e.g. source SQL error like SQL0666); NULL for every non-failed table. Surfaced in the Errors section. | | `created_at` | `DATETIME` | optional | When the record was created. | | `updated_at` | `DATETIME` | optional | When the record was last updated. | References: `sync_run_id` → `sync_run.id`, `ON DELETE CASCADE`. Indexed on: `(sync_run_id, group_id)`; `(sync_run_id, position)`. ### `user_log` Audit trail of the actions users took on this data-plane. Append-only, and it outlives its actors: both user references are `SET NULL` and the email is denormalised, so deleting a user redacts the link without erasing the action. | Column | Type | Null | Description | | ---------------- | -------------- | -------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `id` | `UUID` | required | Primary key: a UUID v4 generated by the application, never by the database. | | `timestamp` | `DATETIME` | required | When the action was taken. | | `user_type` | `VARCHAR(9)` | required | Which kind of identity acted (a user, a service account, the system). | | `action` | `VARCHAR(20)` | required | What was done — the verb, e.g. login, create, delete. | | `user_id` | `UUID` | optional | Who acted, while that user still exists. NULL does not mean 'system'. | | `user_email` | `VARCHAR(320)` | optional | Author of record: the actor's email as it stood at the time. *The actor of record. User ids churn on delete and re-invite, so history reads the email, never the id.* | | `target_user_id` | `UUID` | optional | Who the action was about, when it was about someone other than the actor. | | `created_at` | `DATETIME` | optional | When the record was created. | | `updated_at` | `DATETIME` | optional | When the record was last updated. | References: `user_id` → `user.id`, `ON DELETE SET NULL`; `target_user_id` → `user.id`, `ON DELETE SET NULL`. ## References that are not foreign keys Each of these points at another table without the schema saying so, and each does it for the same reason: the row it names may be deleted while this row has to outlive it. | Column | Points at | Why | | ------------------------------------------ | ------------------------ | -------------------------------------------------------------------------------------------------------------------------------------------------- | | `datamodel.error_table_target_id` | `connector.id` | Optional pointer at the connector receiving rejected rows; unset on most datamodels. | | `subscription.error_table_target_id` | `connector.id` | Optional pointer at the connector receiving rejected rows; unset on most subscriptions. | | `consumption_metric.subscription_id` | `subscription.id` | Deleting a subscription must not erase what it already delivered, and no FK action means 'keep the row and the id'. | | `consumption_metric_daily.subscription_id` | `subscription.id` | Same rule as the raw rows it aggregates: a rollup outlives the subscription. | | `production_metric.topic` | `datamodel.source_topic` | Production is counted per Kafka topic, and a topic outlives the datamodel that named it; the read path resolves the topic set, then filters on it. | | `production_metric_daily.topic` | `datamodel.source_topic` | Same rule as the raw rows it aggregates. | ## What is sensitive, and what is not | Column | What it holds | | -------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `connector.json_configuration` | Everything needed to reach one of your systems, including credentials. Encrypted at rest with the deployment's own key, and redacted again on every read of the API. | | `user.hashed_password` | A password hash, never a password. NULL for identities that authenticate elsewhere. | | `user.email, user_log.user_email, sync_run.triggered_by_email` | The only personal data the database holds. The two historical copies are deliberate: they keep an audit trail readable after the user is deleted. | | `schema.schema` | The structure of your data — column names and types — never its values. |