Database Schema

1. Goals

The V0 schema must support:

• project and environment isolation
• idempotent ingestion
• journey summaries
• entity alias search
• chronological event retrieval
• structural payload diffs
• development replay
• audit history
• retention cleanup

PostgreSQL is the only required data store.

2. General conventions

• UUID or UUID-compatible sortable identifiers
• UTC timestamps using timestamptz
• snake_case database names
• jsonb for flexible structured payloads
• explicit foreign keys
• project-scoped indexes
• append-only journey events
• migrations for every change

3. Tables

projects

Column	Type	Notes
id	uuid	Primary key
name	text	Display name
slug	text	Unique stable slug
created_at	timestamptz	Required
updated_at	timestamptz	Required

Indexes and constraints:

• primary key on id
• unique on slug

environments

Column	Type	Notes
id	uuid	Primary key
project_id	uuid	FK to projects
name	text	local, development, staging, production
retention_days	integer	Positive
capture_mode	text	metadata-only, allowlisted-fields, redacted-payload, full-payload
created_at	timestamptz	Required
updated_at	timestamptz	Required

Constraints:

• unique (project_id, name)
• check retention_days > 0
• check capture mode against allowed values

api_keys

Column	Type	Notes
id	uuid	Primary key
project_id	uuid	FK
environment_id	uuid	FK
name	text	Human-readable
key_prefix	text	Safe lookup/display prefix
key_hash	text	HMAC-SHA256 of the full key, peppered with a key derived from ENCRYPTION_KEY
key_hash_key_id	text	Nullable. Id of the ENCRYPTION_KEY the verifier was computed under; null for a key issued before ids were recorded and not used since
last_used_at	timestamptz	Nullable
revoked_at	timestamptz	Nullable
created_at	timestamptz	Required

Constraints:

• unique key_prefix
• environment must belong to the same project, enforced in application logic or composite FK design

key_hash_key_id exists for key rotation (ADR-044). A verifier cannot be recomputed without the presented key, so a key under the previous ENCRYPTION_KEY is rewritten when it next authenticates, and this column is how rotate:status knows which keys have not yet. Added by migration 012_key_rotation.js.

journeys

Column	Type	Notes
id	text	Public journey ID
project_id	uuid	FK
environment_id	uuid	FK
entity_type	text	Example: customer
primary_entity_id_hash	text	Searchable normalized hash
encrypted_primary_entity_id	text	Optional display value, in the envelope format (§5)
status	text	active, completed, failed
started_at	timestamptz	First event timestamp
completed_at	timestamptz	Nullable
last_event_at	timestamptz	Latest event timestamp
event_count	integer	Derived summary
label	text	Nullable. Public display label from the event that carried one and comes last in the timeline’s order, (timestamp, received at, event id) (migration 018_journey_browse.js)
label_at	timestamptz	Nullable. Timestamp of the event that set label, compared first when a later-arriving event carries a label. Event timestamps reach it at millisecond precision, so events less than a millisecond apart tie and label_received_at decides
label_received_at	timestamptz	Nullable. When the server received the event that set label: its journey_events.received_at, the start of the transaction that stored it. Breaks a tie between equal timestamps, as the timeline does
label_event_id	text	Nullable. Id of the event that set label; breaks a tie on both timestamp and arrival, compared with the “C” collation (byte order) whatever the database default
last_step	text	Nullable. Step name of the event that comes last in the same order, so an out-of-order event does not move it backwards and it is the timeline’s last step (migration 018_journey_browse.js)
last_step_at	timestamptz	Nullable. Timestamp of the event that set last_step, at millisecond precision like label_at
last_step_received_at	timestamptz	Nullable. When the server received the event that set last_step, like label_received_at
last_step_event_id	text	Nullable. Id of the event that set last_step; breaks a tie on both timestamp and arrival, in byte order like label_event_id
failed_step	text	Nullable. Step name of the failing event (an error, or the operation failed) that comes last in the same order among the failures applied since the journey last became failed; null whenever status is not failed, set and cleared by the statement that sets the status. Reads select it as case when status = 'failed' then failed_step end, which hides a value a previous build left on a journey it cleared or completed while the journey is out of failed; if the previous build fails the journey again, the read can name the earlier, cleared step until a failure applied by this build and stamped later replaces it, which happens only while the previous build still writes and only names a step that did fail in that journey (migration 021_journey_failed_step.js, ADR-063)
failed_step_at	timestamptz	Nullable. Timestamp of the event that set failed_step, like last_step_at
failed_step_received_at	timestamptz	Nullable. When the server received the event that set failed_step, like last_step_received_at
failed_step_event_id	text	Nullable. Id of the event that set failed_step; breaks a tie on both timestamp and arrival, in byte order like last_step_event_id
created_at	timestamptz	Required
updated_at	timestamptz	Required

Constraints and indexes:

• composite primary key (project_id, id)
• index (project_id, environment_id, last_event_at desc)
• index (project_id, entity_type, primary_entity_id_hash)
• index (project_id, status, last_event_at, id) for recent failures across environments (migration 013_journeys_status_recent_index.js)
• index journeys_project_recent_idx (project_id, last_event_at, id) for the journey list in every environment with any status, so a page is read in order and the list stops after it (migration 019_journey_browse_indexes.js)
• check event count is nonnegative

The label and last-step columns were added by migration 018_journey_browse.js with no backfill: a journey recorded before it reads null in all six. Its last_step columns fill on its next event; its label columns stay null, which the UI shows as no label, until an event that carries a label arrives.

The failed-step columns were added by migration 021_journey_failed_step.js with no backfill and no index: a journey recorded before it reads null in all four, and a failed one keeps a null failedStep, for which a reader shows lastStep, until its next failure. A failure applied while the journey is not failed takes the step whatever the stored columns hold.

entity_aliases

Column	Type	Notes
id	uuid	Primary key
project_id	uuid	Denormalized for safe scoping
journey_id	text	Composite FK (project_id, journey_id) to journeys
alias_type	text	Developer-defined stable name
alias_value_hash	text	Normalized search hash
encrypted_display_value	text	Optional, in the envelope format (§5)
displayable	boolean	Not null, default false. True only while every event that stated the alias listed it in displayableAliases; ingestion lowers it and never raises it (ADR-053, migration 017_alias_displayable.js)
display_value	text	Nullable. Plain-text copy of the alias value, present only while displayable is true; cleared in the same statement that lowers the flag, and by key rotation’s duplicate folding when the folded flag is false, and by the trigger below for any other writer (ADR-053, migration 018_journey_browse.js). Holds the same spelling as encrypted_display_value. Null for a displayable value containing a NUL, which a text column cannot hold
created_at	timestamptz	Required

Constraints and indexes:

• unique (project_id, journey_id, alias_type, alias_value_hash)
• index (project_id, alias_type, alias_value_hash)
• index (project_id, alias_value_hash)
• partial index entity_aliases_displayable_idx (project_id, journey_id) include (display_value) where displayable, so the journey list’s text filter tests a journey’s displayable values with an index-only scan (migration 019_journey_browse_indexes.js)
• check entity_aliases_display_value_only_when_displayable: displayable or display_value is null, so a masked alias can never hold a plain value, whatever writes the row (migration 018_journey_browse.js). Added not valid with the columns and validated in a separate transaction, which takes only a SHARE UPDATE EXCLUSIVE lock and so does not block ingestion
• trigger entity_aliases_clear_masked_display_value, before insert or update ... for each row, running the plpgsql function of the same name, which sets display_value to null when displayable is false (migration 018_journey_browse.js). The build before 018 lowers the flag without knowing the copy exists; during a rollout that statement would otherwise violate the check above, fail the event, and log the row. The check stays for a write that skips triggers

display_value has no backfill either. A displayable alias stored before migration 018_journey_browse.js gets its copy the next time an event states it displayable; that statement also replaces the row’s ciphertext, so the two keep the same spelling. Until then it reads null.

An alias value may intentionally map to more than one journey over time. Do not globally force uniqueness unless the domain requires it.

journey_events

Column	Type	Notes
id	text	Client-generated public event ID
project_id	uuid	Required
environment_id	uuid	Required
journey_id	text	Required
parent_event_id	text	Nullable
protocol_version	text	Required
content_hash	text	Keyed canonical hash for duplicate-conflict detection: h1.<keyId>.<hex>, HMAC-SHA256 of the event as received (ADR-021, ADR-048). A value with no prefix is a legacy unkeyed SHA-256, still compared
operation	text	Required
name	text	Required
service	text	Required
event_timestamp	timestamptz	Client timestamp
received_at	timestamptz	Server timestamp
duration_ms	integer	Nullable
trace_id	text	Nullable
span_id	text	Nullable
message_id	text	Nullable
correlation_id	text	Nullable
input_payload	jsonb	Nullable
output_payload	jsonb	Nullable
payload_diff	jsonb	Nullable
error	jsonb	Nullable
runtime_metadata	jsonb	Nullable
deployment_metadata	jsonb	Nullable
custom_metadata	jsonb	Nullable
stated_alias_ids	uuid[]	Nullable, no default. The entity_aliases ids of the aliases this event stated, written with the insert; empty when it stated none, null for an event stored before migration 020_event_stated_aliases.js or by the build before it (F-042)
created_at	timestamptz	Required

Constraints and indexes:

• composite primary key (project_id, id), which provides idempotency
• index (project_id, journey_id, event_timestamp, received_at, id)
• index (project_id, trace_id) where trace ID is not null
• index (project_id, message_id) where message ID is not null
• index (project_id, correlation_id) where correlation ID is not null
• index (project_id, span_id) where span ID is not null, so search by span ID is an index lookup (migration 014_search_indexes.js)
• index (project_id, service, journey_id) for the recent list’s service filter (migration 013)
• optional index (project_id, operation, event_timestamp desc)
• check duration_ms >= 0

The event row is immutable after insertion. Ingestion writes it once, the alias ids included, because it stores the event’s aliases before the event. The one later write is key rotation’s to stated_alias_ids: when it deletes a stale duplicate alias row it points the ids that named that row at the row that stays, which holds the same value. What the event recorded never changes.

replay_destinations

Column	Type	Notes
id	uuid	Primary key
project_id	uuid	FK
name	text	Display name
base_url	text	Validated destination
environment_type	text	Must be development-like in V0
encrypted_headers	text	Optional, in the envelope format (§5)
enabled	boolean	Required
created_at	timestamptz	Required
updated_at	timestamptz	Required

replay_runs

Column	Type	Notes
id	uuid	Primary key
project_id	uuid	FK
journey_event_id	text	Source event
destination_id	uuid	FK
method	text	HTTP method
request_path	text	Relative path
request_payload	jsonb	Sanitized
request_headers	jsonb	Names as sent; destination header values and blocked names stored as [REDACTED]
response_status	integer	Nullable
response_payload	jsonb	Size-limited; exact occurrences of destination header values of 8 or more characters replaced with [REDACTED] (not a fragment cut at the size cap, not a shorter value)
duration_ms	integer	Nullable
status	text	queued, running, completed, failed, blocked
error	jsonb	Nullable; message scrubbed of destination header values as response_payload is
initiated_by	text	User or local actor
created_at	timestamptz	Required
completed_at	timestamptz	Nullable

Indexes:

• (project_id, created_at) for the recent replay list
• (project_id, journey_event_id), the foreign key to journey_events, so the cascade from a deleted event finds its runs without scanning the project’s (migration 016_replay_runs_event_index.js)

audit_events

Column	Type	Notes
id	uuid	Primary key
project_id	uuid	FK
actor	text	User, API key, or system
action	text	Stable action name
resource_type	text	Example: replay
resource_id	text	Nullable
metadata	jsonb	Sanitized
created_at	timestamptz	Required

Indexes:

• (project_id, created_at desc)
• (project_id, action, created_at desc)

4. Search normalization

Exact search values should be normalized before hashing.

Examples:

• trim surrounding whitespace
• normalize UUID case
• normalize email case only where domain semantics allow
• preserve meaningful punctuation for external identifiers
• include alias type in HMAC input when appropriate

Prefer an HMAC-based search token using a server-held key rather than a plain unsalted hash for sensitive low-entropy values.

5. Payload encryption

Entity identifiers, alias display values, and replay destination headers are encrypted with AES-256-GCM under a key derived from ENCRYPTION_KEY (ADR-044). Payloads are stored as plain jsonb; redaction is the payload control, by decision, not as an interim state (ADR-040).

Each encrypted value is one text column:

fr1.<keyId>.<base64(iv || authTag || ciphertext)>

• fr1. is the format version.
• <keyId> is twelve lowercase hex characters: an HKDF fingerprint of the ENCRYPTION_KEY that wrote the value. It names the key without revealing it, so a rotation can read each value under the right key and find the rows still under the old one (ADR-044).
• The payload is a fresh 12-byte IV, the 16-byte GCM tag, then the ciphertext.

A value with no fr1. prefix is the legacy format, written before values carried a key id: the same base64 payload alone. It is still read, under the current key and then the previous one, and rotate:reencrypt rewrites it into the envelope. . never appears in base64, so the two formats cannot be confused.

Search tokens (journeys.primary_entity_id_hash, entity_aliases.alias_value_hash) carry no key id. Each is rewritten in the same statement as the ciphertext beside it, so that ciphertext’s key id marks the token’s key too.

Encryption keys must not be stored in the database.

6. Retention

Retention cleanup should:

1. select expired journeys by environment policy
2. delete replay references safely
3. delete events and aliases
4. delete journey summaries
5. record cleanup metrics
6. operate in bounded batches

Do not run one unbounded deletion transaction.

7. Migration order

Recommended initial migrations:

1. projects
2. environments
3. api_keys
4. journeys
5. entity_aliases
6. journey_events
7. replay_destinations
8. replay_runs
9. audit_events
10. indexes and constraints not safely created inline
11. local development seed

8. Future storage growth

Do not introduce additional stores in V0.

Possible later split:

• PostgreSQL for configuration and summaries
• S3-compatible storage for encrypted payload blobs
• ClickHouse for high-volume event analytics

Any split must preserve the public protocol and query behavior.