Database Schema

Auto-generated from migration SQL files. Do not edit manually. Run make schema-docs to regenerate.

Core Database

roles

Column	Type	Constraints
id	UUID	PRIMARY KEY DEFAULT uuid()
subject_type	VARCHAR(20)	NOT NULL
subject_id	VARCHAR(255)	NOT NULL
role	VARCHAR(50)	NOT NULL
merchant_id	UUID
created_at	TIMESTAMPTZ	NOT NULL DEFAULT now()
updated_at	TIMESTAMPTZ	NOT NULL DEFAULT now()

Indexes:

CREATE INDEX idx_roles_subject ON roles(subject_type, subject_id)
CREATE INDEX idx_roles_merchant ON roles(merchant_id) WHERE merchant_id IS NOT NULL

api_keys

Column	Type	Constraints
id	UUID	PRIMARY KEY DEFAULT uuid()
merchant_id	UUID	NOT NULL
prefix	VARCHAR(16)	NOT NULL
key_hash	BYTEA	NOT NULL
name	VARCHAR(255)	NOT NULL DEFAULT ”
scopes	TEXT[]	NOT NULL DEFAULT ''
key_type	VARCHAR(10)	NOT NULL
environment	VARCHAR(10)	NOT NULL
status	VARCHAR(20)	NOT NULL DEFAULT ‘active’
rate_limit_tier	VARCHAR(20)	NOT NULL DEFAULT ‘standard’
last_used_at	TIMESTAMPTZ
expires_at	TIMESTAMPTZ
revoked_at	TIMESTAMPTZ
created_at	TIMESTAMPTZ	NOT NULL DEFAULT now()
updated_at	TIMESTAMPTZ	NOT NULL DEFAULT now()

Indexes:

CREATE UNIQUE INDEX idx_api_keys_prefix_hash ON api_keys(prefix, key_hash)
CREATE INDEX idx_api_keys_merchant ON api_keys(merchant_id)
CREATE INDEX idx_api_keys_status ON api_keys(status) WHERE status = 'active'

idempotency_keys

Column	Type	Constraints
id	UUID	PRIMARY KEY DEFAULT uuid()
api_key_id	UUID	NOT NULL
idempotency_key	VARCHAR(255)	NOT NULL
request_method	VARCHAR(10)	NOT NULL
request_path	VARCHAR(500)	NOT NULL
request_hash	BYTEA	NOT NULL
response_code	INT
response_body	JSONB
locked_at	TIMESTAMPTZ
completed_at	TIMESTAMPTZ
created_at	TIMESTAMPTZ	NOT NULL DEFAULT now()

Indexes:

CREATE INDEX idx_idempotency_cleanup ON idempotency_keys(created_at)

audit_log

Column	Type	Constraints
id	UUID	PRIMARY KEY DEFAULT uuid()
actor_type	VARCHAR(20)	NOT NULL
actor_id	VARCHAR(255)	NOT NULL
action	VARCHAR(100)	NOT NULL
resource_type	VARCHAR(50)	NOT NULL
resource_id	VARCHAR(255)	NOT NULL
merchant_id	UUID
metadata	JSONB	DEFAULT ''
ip_address	INET
correlation_id	UUID
created_at	TIMESTAMPTZ	NOT NULL DEFAULT now()

Indexes:

CREATE INDEX idx_audit_log_actor ON audit_log(actor_type, actor_id)
CREATE INDEX idx_audit_log_resource ON audit_log(resource_type, resource_id)
CREATE INDEX idx_audit_log_merchant ON audit_log(merchant_id) WHERE merchant_id IS NOT NULL
CREATE INDEX idx_audit_log_created ON audit_log(created_at)

payment_intents

Column	Type	Constraints
id	UUID	PRIMARY KEY DEFAULT uuid()
merchant_id	UUID	NOT NULL
amount	BIGINT	NOT NULL
currency	VARCHAR(3)	NOT NULL DEFAULT ‘usd’
status	VARCHAR(30)	NOT NULL DEFAULT ‘requires_payment_method’
capture_method	VARCHAR(10)	NOT NULL DEFAULT ‘automatic’
gateway_token_id	UUID
connector_id	VARCHAR(50)
connector_txn_id	VARCHAR(255)
connector_status	VARCHAR(50)
error_code	VARCHAR(100)
error_message	TEXT
metadata	JSONB
test_mode	BOOLEAN	NOT NULL DEFAULT false
idempotency_key	VARCHAR(255)
created_at	TIMESTAMPTZ	NOT NULL DEFAULT now()
updated_at	TIMESTAMPTZ	NOT NULL DEFAULT now()
captured_at	TIMESTAMPTZ
canceled_at	TIMESTAMPTZ

Indexes:

CREATE INDEX idx_pi_merchant ON payment_intents(merchant_id)
CREATE INDEX idx_pi_status ON payment_intents(status) WHERE status NOT IN ('succeeded', 'canceled', 'failed')
CREATE INDEX idx_pi_test_mode ON payment_intents(test_mode)
CREATE INDEX idx_pi_connector_txn ON payment_intents(connector_txn_id) WHERE connector_txn_id IS NOT NULL
CREATE UNIQUE INDEX idx_pi_idempotency ON payment_intents(merchant_id, idempotency_key) WHERE idempotency_key IS NOT NULL

refunds

Column	Type	Constraints
id	UUID	PRIMARY KEY DEFAULT uuid()
payment_intent_id	UUID	NOT NULL REFERENCES payment_intents(id)
amount	BIGINT	NOT NULL
status	VARCHAR(20)	NOT NULL DEFAULT ‘pending’
reason	VARCHAR(50)
connector_refund_id	VARCHAR(255)
connector_status	VARCHAR(50)
error_code	VARCHAR(100)
error_message	TEXT
test_mode	BOOLEAN	NOT NULL DEFAULT false
idempotency_key	VARCHAR(255)
created_at	TIMESTAMPTZ	NOT NULL DEFAULT now()
updated_at	TIMESTAMPTZ	NOT NULL DEFAULT now()

Indexes:

CREATE INDEX idx_refunds_pi ON refunds(payment_intent_id)
CREATE INDEX idx_refunds_test_mode ON refunds(test_mode)

Alter: payment_intents

Migration: 007_add_bin_auth_data.sql

Column	Type	Note
bin	VARCHAR(8)	Added
auth_code	VARCHAR(20)	Added
avs_result	VARCHAR(5)	Added
cvv_result	VARCHAR(5)	Added
decline_code	VARCHAR(50)	Added
response_time_ms	INTEGER	Added

Indexes:

CREATE INDEX idx_pi_bin ON payment_intents(bin) WHERE bin IS NOT NULL

merchant_routing_configs

Column	Type	Constraints
id	UUID	PRIMARY KEY DEFAULT uuid()
merchant_id	UUID	NOT NULL UNIQUE
default_connector_id	VARCHAR(50)	NOT NULL
failover_connector_id	VARCHAR(50)
created_at	TIMESTAMPTZ	NOT NULL DEFAULT now()
updated_at	TIMESTAMPTZ	NOT NULL DEFAULT now()

Indexes:

CREATE INDEX idx_mrc_merchant ON merchant_routing_configs(merchant_id)

raw_webhook_events

Column	Type	Constraints
id	UUID	PRIMARY KEY DEFAULT uuid()
connector_id	VARCHAR(50)	NOT NULL
connector_event_id	VARCHAR(255)
event_hash	VARCHAR(64)	NOT NULL
headers	JSONB	NOT NULL
body	BYTEA	NOT NULL
signature_valid	BOOLEAN	NOT NULL DEFAULT false
processed	BOOLEAN	NOT NULL DEFAULT false
received_at	TIMESTAMPTZ	NOT NULL DEFAULT now()

Indexes:

CREATE UNIQUE INDEX idx_raw_webhook_connector_event
CREATE UNIQUE INDEX idx_raw_webhook_event_hash
CREATE INDEX idx_raw_webhook_unprocessed

webhook_endpoints

Column	Type	Constraints
id	UUID	PRIMARY KEY DEFAULT uuid()
merchant_id	UUID	NOT NULL
url	VARCHAR(2048)	NOT NULL
secret	VARCHAR(255)	NOT NULL
enabled	BOOLEAN	NOT NULL DEFAULT true
event_types	TEXT[]	NOT NULL DEFAULT ''
description	TEXT
created_at	TIMESTAMPTZ	NOT NULL DEFAULT now()
updated_at	TIMESTAMPTZ	NOT NULL DEFAULT now()

Indexes:

CREATE INDEX idx_webhook_endpoints_merchant
CREATE INDEX idx_webhook_endpoints_enabled

normalized_events

Column	Type	Constraints
id	UUID	PRIMARY KEY DEFAULT uuid()
raw_webhook_event_id	UUID	NOT NULL REFERENCES raw_webhook_events(id)
event_type	VARCHAR(50)	NOT NULL
payment_intent_id	UUID
merchant_id	UUID	NOT NULL
connector_id	VARCHAR(50)	NOT NULL
data	JSONB	NOT NULL
created_at	TIMESTAMPTZ	NOT NULL DEFAULT now()

Indexes:

CREATE INDEX idx_normalized_events_pi
CREATE INDEX idx_normalized_events_merchant
CREATE INDEX idx_normalized_events_type

webhook_delivery_attempts

Column	Type	Constraints
id	UUID	PRIMARY KEY DEFAULT uuid()
normalized_event_id	UUID	NOT NULL REFERENCES normalized_events(id)
webhook_endpoint_id	UUID	NOT NULL REFERENCES webhook_endpoints(id)
attempt_number	INT	NOT NULL DEFAULT 1
status	VARCHAR(20)	NOT NULL DEFAULT ‘pending’
response_code	INT
response_body	TEXT
error_message	TEXT
dead_letter	BOOLEAN	NOT NULL DEFAULT false
next_retry_at	TIMESTAMPTZ
attempted_at	TIMESTAMPTZ	NOT NULL DEFAULT now()

Indexes:

CREATE INDEX idx_delivery_pending
CREATE INDEX idx_delivery_dlq
CREATE INDEX idx_delivery_event

iso_entities

Column	Type	Constraints
id	UUID	PRIMARY KEY DEFAULT uuid()
parent_id	UUID	REFERENCES iso_entities(id)
name	VARCHAR(255)	NOT NULL
status	VARCHAR(20)	NOT NULL DEFAULT ‘active’
created_at	TIMESTAMPTZ	NOT NULL DEFAULT now()
updated_at	TIMESTAMPTZ	NOT NULL DEFAULT now()

Indexes:

CREATE INDEX idx_iso_parent ON iso_entities(parent_id)

gateway_price_plans

Column	Type	Constraints
id	UUID	PRIMARY KEY DEFAULT uuid()
scope	VARCHAR(20)	NOT NULL
scope_id	UUID
version	INT	NOT NULL DEFAULT 1
per_txn_flat_cents	BIGINT	NOT NULL DEFAULT 0
per_txn_basis_points	BIGINT	NOT NULL DEFAULT 0
monthly_fee_cents	BIGINT	NOT NULL DEFAULT 0
effective_date	TIMESTAMPTZ	NOT NULL DEFAULT now()
created_by	VARCHAR(255)
created_at	TIMESTAMPTZ	NOT NULL DEFAULT now()

Indexes:

CREATE INDEX idx_price_plan_scope ON gateway_price_plans(scope, scope_id)
CREATE INDEX idx_price_plan_effective ON gateway_price_plans(effective_date)

usage_events

Column	Type	Constraints
id	UUID	PRIMARY KEY DEFAULT uuid()
merchant_id	UUID	NOT NULL
event_type	VARCHAR(30)	NOT NULL
period	VARCHAR(7)	NOT NULL
count	BIGINT	NOT NULL DEFAULT 1
amount_cents	BIGINT	NOT NULL DEFAULT 0
transaction_id	UUID
created_at	TIMESTAMPTZ	NOT NULL DEFAULT now()

Indexes:

CREATE INDEX idx_usage_merchant_period ON usage_events(merchant_id, period)
CREATE INDEX idx_usage_period ON usage_events(period)

financial_ledger

Column	Type	Constraints
id	UUID	PRIMARY KEY DEFAULT uuid()
merchant_id	UUID	NOT NULL
iso_id	UUID
event_type	VARCHAR(30)	NOT NULL
amount_cents	BIGINT	NOT NULL
pricing_plan_id	UUID	NOT NULL
pricing_plan_version	INT	NOT NULL
transaction_id	UUID
period	VARCHAR(7)	NOT NULL
created_at	TIMESTAMPTZ	NOT NULL DEFAULT now()

Indexes:

CREATE INDEX idx_ledger_merchant_period ON financial_ledger(merchant_id, period)
CREATE INDEX idx_ledger_iso_period ON financial_ledger(iso_id, period) WHERE iso_id IS NOT NULL
CREATE INDEX idx_ledger_transaction ON financial_ledger(transaction_id) WHERE transaction_id IS NOT NULL

pci_attestations

Column	Type	Constraints
id	UUID	PRIMARY KEY DEFAULT uuid()
merchant_id	UUID	NOT NULL
attestation_type	VARCHAR(50)	NOT NULL
effective_date	DATE	NOT NULL
expiry_date	DATE	NOT NULL
document_ref	VARCHAR(512)
reviewed_by	VARCHAR(255)
review_notes	TEXT
last_verified_at	TIMESTAMPTZ
created_at	TIMESTAMPTZ	NOT NULL DEFAULT now()
updated_at	TIMESTAMPTZ	NOT NULL DEFAULT now()

Indexes:

CREATE INDEX idx_attestation_merchant ON pci_attestations(merchant_id)
CREATE INDEX idx_attestation_expiry ON pci_attestations(expiry_date)

merchants

Column	Type	Constraints
id	UUID	PRIMARY KEY DEFAULT uuid()
iso_id	UUID	REFERENCES iso_entities(id)
name	VARCHAR(255)	NOT NULL
status	VARCHAR(20)	NOT NULL DEFAULT ‘active’
created_at	TIMESTAMPTZ	NOT NULL DEFAULT now()
updated_at	TIMESTAMPTZ	NOT NULL DEFAULT now()

Indexes:

CREATE INDEX idx_merchant_iso ON merchants(iso_id) WHERE iso_id IS NOT NULL

card_metadata

Column	Type	Constraints
gateway_token	TEXT	PRIMARY KEY
gateway_token_id	UUID
last4	TEXT	NOT NULL
bin	TEXT	NOT NULL DEFAULT ”
card_brand	TEXT	NOT NULL DEFAULT ”
created_at	TIMESTAMPTZ	NOT NULL DEFAULT now()

Indexes:

CREATE INDEX idx_card_metadata_created_at ON card_metadata (created_at)
CREATE INDEX idx_card_metadata_gateway_token_id ON card_metadata (gateway_token_id) WHERE gateway_token_id IS NOT NULL

PCI Database (Vault)

vaulted_cards

Column	Type	Constraints
id	UUID	PRIMARY KEY DEFAULT uuid()
pan_fingerprint	BYTEA	NOT NULL
encrypted_pan	BYTEA	NOT NULL
wrapped_dek	BYTEA	NOT NULL
kms_key_version	TEXT	NOT NULL
bin	VARCHAR(8)	NOT NULL
last4	VARCHAR(4)	NOT NULL
exp_month	SMALLINT
exp_year	SMALLINT
card_brand	VARCHAR(20)
created_at	TIMESTAMPTZ	NOT NULL DEFAULT now()

Indexes:

CREATE UNIQUE INDEX idx_vaulted_cards_fingerprint ON vaulted_cards(pan_fingerprint)

gateway_tokens

Column	Type	Constraints
id	UUID	PRIMARY KEY DEFAULT uuid()
token	VARCHAR(40)	NOT NULL
vaulted_card_id	UUID	NOT NULL REFERENCES vaulted_cards(id)
merchant_id	UUID	NOT NULL
merchant_fingerprint	BYTEA	NOT NULL
environment	VARCHAR(10)	NOT NULL
created_at	TIMESTAMPTZ	NOT NULL DEFAULT now()

Indexes:

CREATE UNIQUE INDEX idx_gateway_tokens_token ON gateway_tokens(token)
CREATE UNIQUE INDEX idx_gateway_tokens_merchant_fp ON gateway_tokens(merchant_fingerprint)
CREATE INDEX idx_gateway_tokens_merchant ON gateway_tokens(merchant_id)
CREATE INDEX idx_gateway_tokens_card ON gateway_tokens(vaulted_card_id)

processor_tokens

Column	Type	Constraints
id	UUID	PRIMARY KEY DEFAULT uuid()
gateway_token_id	UUID	NOT NULL REFERENCES gateway_tokens(id)
connector_id	VARCHAR(50)	NOT NULL
processor_token	VARCHAR(255)	NOT NULL
processor_metadata	JSONB	DEFAULT ''
created_at	TIMESTAMPTZ	NOT NULL DEFAULT now()

Indexes:

CREATE UNIQUE INDEX idx_processor_tokens_gateway_connector ON processor_tokens(gateway_token_id, connector_id)

decrypt_tokens

Column	Type	Constraints
id	UUID	PRIMARY KEY DEFAULT uuid()
gateway_token_id	UUID	NOT NULL REFERENCES gateway_tokens(id)
requester_service	VARCHAR(50)	NOT NULL
expires_at	TIMESTAMPTZ	NOT NULL
used_at	TIMESTAMPTZ
created_at	TIMESTAMPTZ	NOT NULL DEFAULT now()

Indexes:

CREATE INDEX idx_decrypt_tokens_gateway ON decrypt_tokens(gateway_token_id)
CREATE INDEX idx_decrypt_tokens_expires ON decrypt_tokens(expires_at) WHERE used_at IS NULL