Database Schema¶

Verity uses two database engines: PostgreSQL 16 with TimescaleDB for operational data and ClickHouse for the immutable audit log and compliance analytics.

PostgreSQL + TimescaleDB¶

Entity-Relationship Diagram¶

erDiagram
    PEER_GROUPS ||--o{ PRINCIPALS : "has members"
    PRINCIPALS ||--o{ ACCESS_GRANTS : "owns"
    ASSETS ||--o{ ACCESS_GRANTS : "target of"
    ACCESS_GRANTS ||--o{ ACCESS_SCORES : "scored by"
    ACCESS_GRANTS ||--o{ ACCESS_EVENTS : "generates"
    ACCESS_GRANTS ||--o{ ACCESS_STATS_LATEST : "summarised in"
    ACCESS_GRANTS ||--o{ REVIEW_PACKETS : "reviewed via"
    REVIEW_PACKETS ||--o{ REVIEW_DECISIONS : "decided by"
    PRINCIPALS ||--o{ CONNECTOR_CHECKPOINTS : "tracked per connector"

    PEER_GROUPS {
        uuid id PK
        text name
        text description
        text platform
        jsonb criteria
        timestamptz created_at
        timestamptz updated_at
    }

    PRINCIPALS {
        uuid id PK
        text external_id
        text platform
        text email
        text display_name
        text principal_type
        uuid peer_group_id FK
        text status
        jsonb metadata
        timestamptz first_seen
        timestamptz last_seen
        timestamptz created_at
        timestamptz updated_at
    }

    ASSETS {
        uuid id PK
        text external_id
        text platform
        text asset_type
        text name
        text sensitivity
        text data_classification
        jsonb metadata
        timestamptz created_at
        timestamptz updated_at
    }

    ACCESS_GRANTS {
        uuid id PK
        uuid principal_id FK
        uuid asset_id FK
        text platform
        text privilege_level
        text grant_type
        text status
        timestamptz granted_at
        timestamptz expires_at
        timestamptz last_used_at
        jsonb metadata
        timestamptz created_at
        timestamptz updated_at
    }

    ACCESS_SCORES {
        uuid grant_id FK
        timestamptz scored_at
        float decay_score
        float time_factor
        float peer_factor
        float privilege_factor
        float sensitivity_factor
        float anomaly_factor
        float compliance_factor
        text model_version
    }

    ACCESS_SCORES_LATEST {
        uuid grant_id FK
        timestamptz bucket
        float avg_decay_score
        float max_decay_score
        float last_decay_score
    }

    ACCESS_EVENTS {
        uuid id PK
        uuid grant_id FK
        uuid principal_id FK
        uuid asset_id FK
        text platform
        text event_type
        text action
        text source_ip
        jsonb event_metadata
        timestamptz occurred_at
        timestamptz ingested_at
    }

    ACCESS_STATS_LATEST {
        uuid grant_id FK
        bigint total_events
        bigint events_last_30d
        bigint events_last_90d
        timestamptz last_event_at
        timestamptz computed_at
    }

    REVIEW_PACKETS {
        uuid id PK
        uuid grant_id FK
        uuid principal_id FK
        uuid asset_id FK
        text status
        float trigger_score
        text trigger_reason
        uuid assigned_to
        text workflow_run_id
        timestamptz created_at
        timestamptz updated_at
        timestamptz due_at
    }

    REVIEW_DECISIONS {
        uuid id PK
        uuid review_id FK
        text decision
        text justification
        uuid decided_by
        text decided_by_email
        timestamptz decided_at
        jsonb metadata
    }

    CONNECTOR_CHECKPOINTS {
        uuid id PK
        text connector_name
        text platform
        text checkpoint_type
        text checkpoint_value
        jsonb metadata
        timestamptz created_at
        timestamptz updated_at
    }

Table Details¶

`peer_groups`¶

Logical groupings of principals used for peer-comparison scoring.

Column	Type	Nullable	Default	Description
`id`	`uuid`	NO	`gen_random_uuid()`	Primary key.
`name`	`text`	NO	—	Human-readable group name.
`description`	`text`	YES	—	Optional description.
`platform`	`text`	NO	—	Source platform (e.g. `azure_ad`, `snowflake`).
`criteria`	`jsonb`	YES	`'{}'`	Dynamic membership criteria.
`created_at`	`timestamptz`	NO	`now()`	Row creation timestamp.
`updated_at`	`timestamptz`	NO	`now()`	Last modification timestamp.

Indexes:

Name	Columns	Type
`pk_peer_groups`	`id`	PRIMARY KEY
`ix_peer_groups_platform`	`platform`	B-tree

`principals`¶

Identity records — users, service accounts, and groups from connected platforms.

Column	Type	Nullable	Default	Description
`id`	`uuid`	NO	`gen_random_uuid()`	Primary key.
`external_id`	`text`	NO	—	Platform-native identifier.
`platform`	`text`	NO	—	Source platform.
`email`	`text`	YES	—	Email address (if available).
`display_name`	`text`	YES	—	Human-readable name.
`principal_type`	`text`	NO	`'user'`	One of: `user`, `service_account`, `group`.
`peer_group_id`	`uuid`	YES	—	FK → `peer_groups.id`.
`status`	`text`	NO	`'active'`	One of: `active`, `inactive`, `suspended`.
`metadata`	`jsonb`	YES	`'{}'`	Platform-specific attributes.
`first_seen`	`timestamptz`	YES	—	First time seen by a connector.
`last_seen`	`timestamptz`	YES	—	Most recent connector observation.
`created_at`	`timestamptz`	NO	`now()`	Row creation timestamp.
`updated_at`	`timestamptz`	NO	`now()`	Last modification timestamp.

Indexes:

Name	Columns	Type
`pk_principals`	`id`	PRIMARY KEY
`uq_principals_ext`	`(external_id, platform)`	UNIQUE
`ix_principals_email`	`email`	B-tree
`ix_principals_peer_group`	`peer_group_id`	B-tree
`ix_principals_status`	`status`	B-tree

`assets`¶

Resources whose access is being tracked — databases, workspaces, applications, etc.

Column	Type	Nullable	Default	Description
`id`	`uuid`	NO	`gen_random_uuid()`	Primary key.
`external_id`	`text`	NO	—	Platform-native identifier.
`platform`	`text`	NO	—	Source platform.
`asset_type`	`text`	NO	—	Type (e.g. `database`, `workspace`, `application`).
`name`	`text`	YES	—	Human-readable name.
`sensitivity`	`text`	YES	`'medium'`	Sensitivity level: `low`, `medium`, `high`, `critical`.
`data_classification`	`text`	YES	—	Classification label (e.g. `public`, `internal`, `confidential`, `restricted`).
`metadata`	`jsonb`	YES	`'{}'`	Platform-specific attributes.
`created_at`	`timestamptz`	NO	`now()`	Row creation timestamp.
`updated_at`	`timestamptz`	NO	`now()`	Last modification timestamp.

Indexes:

Name	Columns	Type
`pk_assets`	`id`	PRIMARY KEY
`uq_assets_ext`	`(external_id, platform)`	UNIQUE
`ix_assets_type`	`asset_type`	B-tree
`ix_assets_sensitivity`	`sensitivity`	B-tree

`access_grants`¶

The core entity — an access relationship between a principal and an asset.

Column	Type	Nullable	Default	Description
`id`	`uuid`	NO	`gen_random_uuid()`	Primary key.
`principal_id`	`uuid`	NO	—	FK → `principals.id`.
`asset_id`	`uuid`	NO	—	FK → `assets.id`.
`platform`	`text`	NO	—	Source platform.
`privilege_level`	`text`	NO	—	Privilege tier: `read`, `write`, `admin`, `owner`.
`grant_type`	`text`	NO	`'direct'`	How access was granted: `direct`, `inherited`, `group`.
`status`	`text`	NO	`'active'`	Status: `active`, `revoked`, `expired`, `under_review`.
`granted_at`	`timestamptz`	YES	—	When access was originally granted.
`expires_at`	`timestamptz`	YES	—	Expiration date (if time-boxed).
`last_used_at`	`timestamptz`	YES	—	Last time the access was exercised.
`metadata`	`jsonb`	YES	`'{}'`	Platform-specific grant attributes.
`created_at`	`timestamptz`	NO	`now()`	Row creation timestamp.
`updated_at`	`timestamptz`	NO	`now()`	Last modification timestamp.

Indexes:

Name	Columns	Type
`pk_access_grants`	`id`	PRIMARY KEY
`ix_grants_principal`	`principal_id`	B-tree
`ix_grants_asset`	`asset_id`	B-tree
`ix_grants_status`	`status`	B-tree
`ix_grants_platform`	`platform`	B-tree
`uq_grants_natural`	`(principal_id, asset_id, platform, privilege_level)`	UNIQUE

`access_scores` — TimescaleDB Hypertable¶

Time-series table storing every decay score computation. Configured as a TimescaleDB hypertable with 7-day chunks.

Column	Type	Nullable	Default	Description
`grant_id`	`uuid`	NO	—	FK → `access_grants.id`.
`scored_at`	`timestamptz`	NO	`now()`	Timestamp of scoring run (hypertable partition key).
`decay_score`	`double precision`	NO	—	Composite decay score (0.0 – 1.0).
`time_factor`	`double precision`	NO	—	Time-since-last-use factor.
`peer_factor`	`double precision`	NO	—	Peer-comparison factor.
`privilege_factor`	`double precision`	NO	—	Privilege-level weight.
`sensitivity_factor`	`double precision`	NO	—	Resource-sensitivity weight.
`anomaly_factor`	`double precision`	NO	—	Anomaly-detection signal.
`compliance_factor`	`double precision`	NO	—	Compliance/regulatory weight.
`model_version`	`text`	NO	`'v1'`	Scoring model version for reproducibility.

TimescaleDB Configuration:

Setting	Value
Hypertable dimension	`scored_at`
Chunk interval	7 days
Compression	Enabled after 30 days
Retention	1 year (`drop_chunks` policy)

Indexes:

Name	Columns	Type
`ix_scores_grant_time`	`(grant_id, scored_at DESC)`	B-tree
`ix_scores_scored_at`	`scored_at DESC`	B-tree (chunk-skipping)

`access_scores_latest` — Continuous Aggregate¶

A TimescaleDB continuous aggregate that materialises the latest score for every grant. Automatically refreshed.

Column	Type	Description
`grant_id`	`uuid`	The access grant.
`bucket`	`timestamptz`	Time bucket (1-day granularity).
`avg_decay_score`	`double precision`	Average score in the bucket.
`max_decay_score`	`double precision`	Maximum score in the bucket.
`last_decay_score`	`double precision`	Most recent score in the bucket.

Continuous Aggregate Policy:

Setting	Value
Bucket width	1 day
Refresh start offset	3 days
Refresh end offset	1 hour
Schedule interval	1 hour

`access_events` — TimescaleDB Hypertable¶

Time-series table of all access activity events. Configured with 1-day chunks and a 7-year retention policy.

Column	Type	Nullable	Default	Description
`id`	`uuid`	NO	`gen_random_uuid()`	Primary key.
`grant_id`	`uuid`	YES	—	FK → `access_grants.id` (nullable for pre-grant events).
`principal_id`	`uuid`	NO	—	FK → `principals.id`.
`asset_id`	`uuid`	NO	—	FK → `assets.id`.
`platform`	`text`	NO	—	Source platform.
`event_type`	`text`	NO	—	Category: `login`, `query`, `api_call`, `permission_change`.
`action`	`text`	NO	—	Specific action performed.
`source_ip`	`text`	YES	—	Client IP address.
`event_metadata`	`jsonb`	YES	`'{}'`	Platform-specific event details.
`occurred_at`	`timestamptz`	NO	—	When the event occurred (hypertable partition key).
`ingested_at`	`timestamptz`	NO	`now()`	When Verity ingested the event.

TimescaleDB Configuration:

Setting	Value
Hypertable dimension	`occurred_at`
Chunk interval	1 day
Compression	Enabled after 7 days
Retention	7 years (`drop_chunks` policy)

Indexes:

Name	Columns	Type
`ix_events_grant_time`	`(grant_id, occurred_at DESC)`	B-tree
`ix_events_principal_time`	`(principal_id, occurred_at DESC)`	B-tree
`ix_events_platform`	`platform`	B-tree
`ix_events_occurred_at`	`occurred_at DESC`	B-tree (chunk-skipping)

`access_stats_latest`¶

Pre-computed usage statistics for each grant, refreshed by the decay engine.

Column	Type	Nullable	Default	Description
`grant_id`	`uuid`	NO	—	FK → `access_grants.id` (PK).
`total_events`	`bigint`	NO	`0`	Lifetime event count.
`events_last_30d`	`bigint`	NO	`0`	Events in the last 30 days.
`events_last_90d`	`bigint`	NO	`0`	Events in the last 90 days.
`last_event_at`	`timestamptz`	YES	—	Timestamp of the most recent event.
`computed_at`	`timestamptz`	NO	`now()`	When stats were last refreshed.

Indexes:

Name	Columns	Type
`pk_access_stats_latest`	`grant_id`	PRIMARY KEY

`review_packets`¶

Access-review work items generated when scores exceed thresholds.

Column	Type	Nullable	Default	Description
`id`	`uuid`	NO	`gen_random_uuid()`	Primary key.
`grant_id`	`uuid`	NO	—	FK → `access_grants.id`.
`principal_id`	`uuid`	NO	—	FK → `principals.id`.
`asset_id`	`uuid`	NO	—	FK → `assets.id`.
`status`	`text`	NO	`'created'`	Workflow state (see Data Flow).
`trigger_score`	`double precision`	NO	—	Decay score that triggered the review.
`trigger_reason`	`text`	YES	—	Human-readable trigger explanation.
`assigned_to`	`uuid`	YES	—	FK → `principals.id` (the reviewer).
`workflow_run_id`	`text`	YES	—	Temporal workflow run ID.
`created_at`	`timestamptz`	NO	`now()`	When the packet was created.
`updated_at`	`timestamptz`	NO	`now()`	Last status change.
`due_at`	`timestamptz`	YES	—	SLA deadline for decision.

Indexes:

Name	Columns	Type
`pk_review_packets`	`id`	PRIMARY KEY
`ix_reviews_grant`	`grant_id`	B-tree
`ix_reviews_status`	`status`	B-tree
`ix_reviews_assigned`	`assigned_to`	B-tree
`ix_reviews_due_at`	`due_at`	B-tree

`review_decisions`¶

Immutable record of every review decision made.

Column	Type	Nullable	Default	Description
`id`	`uuid`	NO	`gen_random_uuid()`	Primary key.
`review_id`	`uuid`	NO	—	FK → `review_packets.id`.
`decision`	`text`	NO	—	One of: `revoke`, `downgrade`, `maintain`.
`justification`	`text`	YES	—	Free-text justification from the reviewer.
`decided_by`	`uuid`	NO	—	FK → `principals.id`.
`decided_by_email`	`text`	YES	—	Email of the reviewer (denormalised for audit).
`decided_at`	`timestamptz`	NO	`now()`	When the decision was submitted.
`metadata`	`jsonb`	YES	`'{}'`	Additional context (e.g. auto-decision policy).

Indexes:

Name	Columns	Type
`pk_review_decisions`	`id`	PRIMARY KEY
`ix_decisions_review`	`review_id`	B-tree
`ix_decisions_decided_by`	`decided_by`	B-tree
`ix_decisions_decided_at`	`decided_at DESC`	B-tree

`connector_checkpoints`¶

Tracks sync progress for each connector to enable incremental pulls.

Column	Type	Nullable	Default	Description
`id`	`uuid`	NO	`gen_random_uuid()`	Primary key.
`connector_name`	`text`	NO	—	Service name (e.g. `connector-azure-ad`).
`platform`	`text`	NO	—	Platform identifier.
`checkpoint_type`	`text`	NO	—	Type of checkpoint (e.g. `delta_link`, `timestamp`, `page_token`).
`checkpoint_value`	`text`	NO	—	Opaque checkpoint value.
`metadata`	`jsonb`	YES	`'{}'`	Additional checkpoint context.
`created_at`	`timestamptz`	NO	`now()`	Row creation timestamp.
`updated_at`	`timestamptz`	NO	`now()`	Last checkpoint update.

Indexes:

Name	Columns	Type
`pk_connector_checkpoints`	`id`	PRIMARY KEY
`uq_checkpoints_natural`	`(connector_name, platform, checkpoint_type)`	UNIQUE

ClickHouse — Audit Schema¶

`verity_audit`¶

Immutable, append-only audit log for all platform activity. Designed for high-throughput writes and fast analytical queries over long time ranges.

Engine: MergeTree Partition By: toYYYYMM(occurred_at) (monthly partitions) Order By: (entity_type, entity_id, occurred_at) TTL: occurred_at + INTERVAL 7 YEAR DELETE (7-year retention)

#	Column	Type	Description
1	`event_id`	`UUID`	Unique event identifier (globally unique).
2	`occurred_at`	`DateTime64(3)`	Millisecond-precision event timestamp.
3	`actor_id`	`String`	ID of the user or service that performed the action.
4	`actor_email`	`LowCardinality(Nullable(String))`	Email address of the actor (if applicable).
5	`action`	`LowCardinality(String)`	Action performed (e.g. `score.computed`, `review.decided`, `grant.revoked`).
6	`entity_type`	`LowCardinality(String)`	Type of entity affected (e.g. `grant`, `principal`, `review`).
7	`entity_id`	`String`	ID of the affected entity.
8	`entity_name`	`LowCardinality(Nullable(String))`	Human-readable name of the entity.
9	`decision`	`LowCardinality(Nullable(String))`	Review decision if applicable (`revoke`, `downgrade`, `maintain`).
10	`justification`	`Nullable(String)`	Free-text justification for the action.
11	`risk_level`	`LowCardinality(Nullable(String))`	Risk level at time of action (`low`, `medium`, `high`, `critical`).
12	`source_ip`	`Nullable(String)`	Source IP address of the actor.
13	`metadata`	`String`	JSON-encoded additional context.
14	`regulation`	`LowCardinality(Nullable(String))`	Applicable regulation (e.g. `SOX`, `GDPR`, `HIPAA`, `SOC2`).
15	`compliance_status`	`LowCardinality(Nullable(String))`	Compliance evaluation result (`compliant`, `non_compliant`, `pending`).
16	`data_classification`	`LowCardinality(Nullable(String))`	Data classification of the affected asset (`public`, `internal`, `confidential`, `restricted`).
17	`retention_years`	`UInt8`	Retention period in years (default: 7).

ClickHouse DDL¶

CREATE TABLE verity_audit
(
    event_id            UUID,
    occurred_at         DateTime64(3),
    actor_id            String,
    actor_email         LowCardinality(Nullable(String)),
    action              LowCardinality(String),
    entity_type         LowCardinality(String),
    entity_id           String,
    entity_name         LowCardinality(Nullable(String)),
    decision            LowCardinality(Nullable(String)),
    justification       Nullable(String),
    risk_level          LowCardinality(Nullable(String)),
    source_ip           Nullable(String),
    metadata            String DEFAULT '{}',
    regulation          LowCardinality(Nullable(String)),
    compliance_status   LowCardinality(Nullable(String)),
    data_classification LowCardinality(Nullable(String)),
    retention_years     UInt8 DEFAULT 7
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(occurred_at)
ORDER BY (entity_type, entity_id, occurred_at)
TTL occurred_at + INTERVAL 7 YEAR DELETE
SETTINGS index_granularity = 8192;

ClickHouse Design Notes¶

Aspect	Details
Write pattern	High-throughput batch inserts from `audit-writer` via the ClickHouse HTTP interface.
Read pattern	Analytical queries — compliance reports, audit trails, entity history.
LowCardinality	Applied to enum-like columns (`action`, `entity_type`, `risk_level`, etc.) for dictionary encoding and compression.
Partitioning	Monthly partitions (`toYYYYMM`) balance partition count with query pruning.
Ordering	`(entity_type, entity_id, occurred_at)` optimises the most common query: "show me all events for entity X".
TTL	7-year retention enforced at the engine level; ClickHouse automatically drops expired partitions.
Immutability	The `audit-writer` service only performs `INSERT` operations. No `UPDATE` or `DELETE` queries are permitted.

Database Schema¶

PostgreSQL + TimescaleDB¶

Entity-Relationship Diagram¶

Table Details¶

peer_groups¶

principals¶

assets¶

access_grants¶

access_scores — TimescaleDB Hypertable¶

access_scores_latest — Continuous Aggregate¶

access_events — TimescaleDB Hypertable¶

access_stats_latest¶

review_packets¶

review_decisions¶

connector_checkpoints¶

ClickHouse — Audit Schema¶

verity_audit¶

ClickHouse DDL¶

ClickHouse Design Notes¶

`peer_groups`¶

`principals`¶

`assets`¶

`access_grants`¶

`access_scores` — TimescaleDB Hypertable¶

`access_scores_latest` — Continuous Aggregate¶

`access_events` — TimescaleDB Hypertable¶

`access_stats_latest`¶

`review_packets`¶

`review_decisions`¶

`connector_checkpoints`¶

`verity_audit`¶