Database Schemas

Each service runs its own PostgreSQL database within the shared cluster. All table definitions live in numbered Goose SQL migration files under services/<name>/migrations/. Migrations run automatically on service startup via the shared github.com/olly/db package.

Migration Pattern

services/claims/migrations/
  0001_create_schema.sql     -- CREATE SCHEMA claims;
  0002_create_claims.sql     -- CREATE TABLE claims.claims (...)
  0003_create_claim_events.sql
  ...

Every file has a -- +goose Up block (applied forward) and a -- +goose Down block (rollback). Goose tracks applied migrations in a goose_db_version table within each service's schema. Adding a new table means adding a new numbered file - existing files are never edited after they are merged.

---

Claims

Database: claims

Table	Key Columns	Purpose
claims.claims	id, locator, policy_id, term_id, claimant_party_id, status, incident_date, document (JSONB)	Core claim record; document holds flexible adjudication data
claims.claim_lines	id, claim_id, line_number, element_id, coverage_term_key, amount_claimed, amount_allowed, amount_paid, reason_code	Individual service lines within a claim
claims.claim_events	id, claim_id, event_type, from_status, to_status, actor, note	Immutable audit trail of every status transition
claims.claim_documents	id, claim_id, filename, content_type, url	Supporting documents attached to a claim
claims.prior_auths	id, locator, provider_id, policy_id, member_party_id, status, procedure_code, document (JSONB)	Prior authorisation requests linked to a provider and policy
claims.outbox	id, topic, key, payload (JSONB), published_at	Transactional outbox for reliable Kafka publishing

---

Eligibility

Database: eligibility

Table	Key Columns	Purpose
eligibility.member_coverage	id, party_id, policy_id, policy_element_id, element_static_id, term_id, product_code, element_type, coverage_terms (JSONB), status, effective_from, effective_to	Active and historical coverage records per party per policy element
eligibility.coverage_accumulators	id, party_id, term_id, accumulator_type, coverage_term_key, limit_amount, consumed_amount, currency, last_claim_locator	Deductible and out-of-pocket accumulator balances per coverage term
eligibility.projection_checkpoints	consumer_group, topic, partition, offset	Kafka consumer offset checkpoints for the eligibility projection

---

Enrollment

Database: enrollment

Table	Key Columns	Purpose
enrollment.quotes	id, locator, account_id, product_version_id, status, document (JSONB), expires_at	Draft and finalised insurance quotes
enrollment.policies	id, locator, account_id, quote_id, product_version_id, status, inception_date, jurisdiction, region, document (JSONB)	Issued policies derived from accepted quotes
enrollment.policy_terms	id, locator, policy_id, term_number, effective_from, effective_to, status	Annual or mid-term policy term periods
enrollment.policy_transactions	id, locator, policy_id, term_id, category, status, effective_date, document (JSONB)	Endorsements, renewals, cancellations and other lifecycle events
enrollment.policy_elements	id, static_id, policy_id, term_id, element_type, document (JSONB)	Individual coverage elements within a policy (e.g. medical, dental)
enrollment.underwriting_flags	id, policy_id, flag_type, value, created_at	Underwriting decisions and flags attached to a policy
enrollment.quote_field_values	id, quote_id, field_def_id, value	Dynamic field answers on a quote
enrollment.policy_field_values	id, policy_id, field_def_id, value	Dynamic field answers on a policy
enrollment.transaction_field_values	id, transaction_id, field_def_id, value	Dynamic field answers on a transaction
enrollment.outbox	id, topic, key, payload (JSONB), published_at	Transactional outbox for Kafka publishing

---

Billing

Database: billing

Table	Key Columns	Purpose
billing.charges	id, locator, policy_id, term_id, claim_id, account_id, category, amount, currency, status, charge_date	Premium and claim charges to be invoiced
billing.invoices	id, locator, account_id, status, total_amount, currency, due_date, finalised_at, paid_at	Consolidated invoices grouping charges for an account
billing.invoice_line_items	id, invoice_id, charge_id, description, amount	Individual line items linking charges to an invoice
billing.payments	id, locator, invoice_id, account_id, amount, currency, method, reference, status, settled_at	Payment records against invoices
billing.adjustments	id, locator, invoice_id, account_id, reason, amount, currency	Credit and debit adjustments to invoices
billing.ledger_entries	id, locator, account_id, policy_id, entry_type, reference_id, reference_type, direction, amount, currency	Double-entry ledger for financial audit trail
billing.installments	id, locator, invoice_id, due_date, amount, status	Instalment schedules for deferred payment plans
billing.projection_checkpoints	consumer_group, topic, partition, offset	Kafka consumer offset checkpoints
billing.outbox	id, topic, key, payload (JSONB), published_at	Transactional outbox for Kafka publishing

---

Provider

Database: provider

Table	Key Columns	Purpose
provider.providers	id, locator, npi, party_id, party_locator, name, specialty, network_status, address (JSONB), phone, email, activated_at	Provider directory; network_status drives in-/out-of-network adjudication
provider.credentialing_requests	id, locator, provider_id, status, submitted_at, reviewed_at, reviewer_notes	Credentialing workflow records; provider is not activated until approved

---

Notifications

Database: notifications

Table	Key Columns	Purpose
notifications.notification_preferences	id, party_id, party_locator, email, phone, preferred_channel, opted_out_events (JSONB)	Per-party channel preferences and opt-out lists
notifications.notification_log	id, locator, event_id, party_id, event_type, channel, status, subject, body, failure_reason, attempt_count, sent_at	Delivery audit log for every notification attempt
notifications.projection_checkpoints	consumer_group, topic, partition, offset	Kafka consumer offset checkpoints

---

Policy Admin

Database: policy_admin

Table	Key Columns	Purpose
policy_admin.parties	id, locator, type (INDIVIDUAL/ORGANISATION/PROVIDER), first_name, last_name, name, email, phone, address (JSONB), date_of_birth	Central party registry - all people and organisations in the system
policy_admin.party_roles	id, party_id, role_type, locator	Role assignments linking parties to accounts and policies
policy_admin.products	id, locator, code, name, lob, status	Insurance product definitions
policy_admin.product_versions	id, product_id, version, effective_from, effective_to, document (JSONB)	Versioned product configurations
policy_admin.rule_sets	id, product_version_id, name, rule_set_type	Groupings of underwriting and pricing rules
policy_admin.rules	id, rule_set_id, condition, action, priority	Individual rule definitions
policy_admin.rule_evaluations	id, rule_set_id, entity_id, result, evaluated_at	Stored results of rule evaluations
policy_admin.accounts	id, locator, name, billing_level (ACCOUNT/POLICY), currency, display_number	Billing accounts grouping one or more policies
policy_admin.market_profiles	id, market_code, currency, jurisdiction, document (JSONB)	Market-specific configuration (UK, US, etc.)
policy_admin.field_definitions	id, product_version_id, field_key, field_type, required, options (JSONB)	Dynamic field schema for quotes and policies

---

Consent

Database: consent

Table	Key Columns	Purpose
consent.consent_records	id, party_locator, consent_type, granted	Per-party per-consent-type grant/deny state; unique on (party_locator, consent_type)
consent.consent_audit	id, party_locator, consent_type, granted, changed_by, changed_at	Immutable audit trail of every consent state change
consent.deletion_requests	id, party_locator, delete_type, status, requested_by_type, reason, requested_at, completed_at, attempt_count	GDPR/right-to-erasure deletion requests with processing state

---

Document Service

Database: documents

Table	Key Columns	Purpose
documents.templates	id, market_code, template_type, content_html; unique on (market_code, template_type)	HTML templates for generated documents (policies, EOBs, letters)
documents.documents	id, locator, party_locator, template_type, market_code, content (BYTEA)	Generated document blobs stored in-DB (S3 in production)
documents.outbox	id, event_type, payload (JSONB), published_at	Transactional outbox for Kafka publishing

---

Triage

Database: triage

Table	Key Columns	Purpose
triage.sessions	id, locator, party_locator, status, chief_complaint, severity, recommendation (JSONB), consent_verified_at, completed_at	AI-assisted triage sessions initiated by a member
triage.turns	id, locator, session_locator, role, question_type, content	Individual conversational turns within a triage session

---

Care

Database: care (no explicit schema prefix - tables created in the default care database)

Table	Key Columns	Purpose
episodes	id, locator, party_locator, triage_session_locator, care_type, status, summary	Care episodes grouping appointments and prescriptions for a health event
provider_slots	id, locator, provider_locator, start_time, end_time, status, appointment_locator	Available provider appointment slots
appointments	id, locator, episode_locator, provider_locator, type, status, slot_locator, link_out_url, scheduled_at, notes	Scheduled appointments within a care episode
prescriptions	id, locator, episode_locator, provider_locator, medication (JSONB), status, issued_at	Prescriptions issued during a care episode
diagnostics_referrals	id, locator, episode_locator, referral_type, clinic_locator, status, appointment_locator	Diagnostic and specialist referrals

---

Group Scheme Service

Database: group_scheme

Table	Key Columns	Purpose
group_scheme.schemes	id, locator, code, name, employer_party_locator, status	Employer group schemes
group_scheme.scheme_members	id, scheme_id, member_party_locator, policy_locator, status	Members enrolled under a group scheme; unique on (scheme_id, member_party_locator)
group_scheme.bulk_enrollment_jobs	id, locator, scheme_id, status, total, processed, failed_count, errors (JSONB)	Async bulk enrollment job tracking

---

Broker API

Database: broker_api

Table	Key Columns	Purpose
broker_api.broker_configs	id, locator, broker_id, delegated_authority_limit; unique on broker_id	Broker configuration including delegated authority limits
broker_api.commissions	id, locator, broker_id, policy_locator, rate, amount, status (PENDING/PAID)	Commission records for policies placed by a broker