Tenant database — data model

Each tenant has its own Postgres database for HIPAA isolation. The schema is identical across tenants — every customer sees the same shape — but each DB only ever holds one tenant's data.

Diagrams are grouped by Postgres schema. Each diagram shows table boxes with primary keys + foreign keys + status / type / code columns. Full column inventories follow each diagram. The ledger schema's monthly partitions (ledger_entry_2026_04, ledger_entry_2026_05, …) are folded into a single logical ledger_entry for clarity.

Table of contents

• identity — organizations, facilities, providers
• security — users, roles, dashboards
• member — members, coverage, eligibility
• service — clinical, EVV, authorizations
• billing — claims, denials, remittance, COB, fee schedules
• ingestion — feeds, batches, mappings, push API
• ledger — accounts and entries
• config — rules, scopes, payer programs
• audit + workflow

identity — organizations, facilities, providers

Customer org tree (organization → facility → site), billing entities, provider registry (subset of the master NPI cache plus per-tenant taxonomy / enrollment state).

Column inventory

identity.billing_entity

Column	Type	Null	Notes
billing_entity_id	uuid	N	PK
org_id	uuid	N	FK
name	varchar(255)	N
legal_name	varchar(255)	Y
default_facility_id	uuid	Y	FK
is_active	boolean	N
created_at	timestamptz	N
updated_at	timestamptz	N
entity_type	varchar(16)	N

identity.billing_entity_facility

Column	Type	Null	Notes
be_facility_id	uuid	N	PK
billing_entity_id	uuid	N	FK
facility_id	uuid	N	FK
is_primary	boolean	N
effective_from	date	N
effective_to	date	Y
created_at	timestamptz	N

identity.billing_entity_identifier

Column	Type	Null	Notes
billing_entity_id	uuid	N	PK, FK
identifier_id	uuid	N	PK, FK

identity.external_program

Column	Type	Null	Notes
external_program_id	uuid	N	PK
payer_id	uuid	N
state	varchar(2)	N
program_code	varchar(50)	N
name	varchar(255)	Y
canonical_program_id	uuid	Y
is_active	boolean	N
created_at	timestamptz	N
updated_at	timestamptz	N

identity.facility

Column	Type	Null	Notes
facility_id	uuid	N	PK
org_id	uuid	N	FK
site_id	uuid	Y	FK
name	varchar(255)	N
npi	varchar(10)	Y
address1	varchar(255)	Y
address2	varchar(255)	Y
city	varchar(100)	Y
state	varchar(2)	Y
zip	varchar(10)	Y
is_active	boolean	N
created_at	timestamptz	N
updated_at	timestamptz	N

identity.identifier

Column	Type	Null	Notes
identifier_id	uuid	N	PK
identifier_type	varchar(50)	N
identifier_value	varchar(100)	N
issuer	varchar(100)	Y
effective_from	date	N
effective_to	date	Y
created_at	timestamptz	N

identity.organization

Column	Type	Null	Notes
org_id	uuid	N	PK
name	varchar(255)	N
legal_name	varchar(255)	Y
is_active	boolean	N
created_at	timestamptz	N
updated_at	timestamptz	N

identity.payer_enrollment

Column	Type	Null	Notes
enrollment_id	uuid	N	PK
payer_id	uuid	N
external_program_id	uuid	Y	FK
billing_entity_id	uuid	Y	FK
provider_id	uuid	Y	FK
facility_id	uuid	Y	FK
enrollment_type	varchar(50)	N
enrollment_value	varchar(100)	N
effective_from	date	N
effective_to	date	Y
status	varchar(20)	N
created_at	timestamptz	N
updated_at	timestamptz	N
effective_range	daterange	Y

identity.program_mapping

Column	Type	Null	Notes
program_mapping_id	uuid	N	PK
external_program_id	uuid	N	FK
canonical_program_id	uuid	N
effective_from	date	N
effective_to	date	Y
created_at	timestamptz	N
effective_range	daterange	Y

identity.provider

Column	Type	Null	Notes
provider_id	uuid	N	PK
org_id	uuid	N	FK
first_name	varchar(100)	Y
last_name	varchar(100)	Y
middle_name	varchar(100)	Y
suffix	varchar(20)	Y
provider_type	varchar(50)	Y
credential	varchar(50)	Y
specialty	varchar(100)	Y
is_active	boolean	N
created_at	timestamptz	N
updated_at	timestamptz	N

identity.provider_affiliation

Column	Type	Null	Notes
affiliation_id	uuid	N	PK
provider_id	uuid	N	FK
billing_entity_id	uuid	Y	FK
facility_id	uuid	Y	FK
affiliation_type	varchar(30)	N
is_primary	boolean	N
effective_from	date	N
effective_to	date	Y
created_at	timestamptz	N
updated_at	timestamptz	N
effective_range	daterange	Y

identity.provider_claim_role

Column	Type	Null	Notes
provider_claim_role_id	uuid	N	PK
provider_id	uuid	N	FK
claim_role	varchar(30)	N
is_default	boolean	N
effective_from	date	N
effective_to	date	Y
created_at	timestamptz	N

identity.provider_identifier

Column	Type	Null	Notes
provider_id	uuid	N	PK, FK
identifier_id	uuid	N	PK, FK

identity.site

Column	Type	Null	Notes
site_id	uuid	N	PK
org_id	uuid	N	FK
name	varchar(255)	N
state	varchar(2)	Y
timezone	varchar(50)	Y
is_active	boolean	N
created_at	timestamptz	N
updated_at	timestamptz	N

security — users, roles, dashboards

Tenant users, RBAC (roles + permissions), saved dashboard views, scheduled email digests.

Column inventory

security.app_user

Column	Type	Null	Notes
user_id	uuid	N	PK
org_id	uuid	N	FK
email	text	N
password_hash	text	N
display_name	text	N
is_active	boolean	N
must_change_password	boolean	N
last_login_at	timestamptz	Y
failed_login_count	integer	N
locked_until	timestamptz	Y
created_at	timestamptz	N
updated_at	timestamptz	N

security.dashboard_email_schedule

Column	Type	Null	Notes
schedule_id	uuid	N	PK
view_id	uuid	N	FK
user_id	uuid	N	FK
name	text	N
cron_expression	text	N
recipients	jsonb	N
subject	text	N
active	boolean	N
last_fired_at	timestamptz	Y
last_status	text	Y
last_error	text	Y
created_at	timestamptz	N
updated_at	timestamptz	N

security.dashboard_view

Column	Type	Null	Notes
view_id	uuid	N	PK
user_id	uuid	N	FK
name	text	N
filters	jsonb	N
is_default	boolean	N
created_at	timestamptz	N
updated_at	timestamptz	N

security.permission

Column	Type	Null	Notes
permission_id	uuid	N	PK
permission_name	varchar(100)	N
resource	varchar(100)	N
action	varchar(20)	N
description	text	Y
created_at	timestamptz	N

security.role

Column	Type	Null	Notes
role_id	uuid	N	PK
role_name	varchar(50)	N
description	text	Y
created_at	timestamptz	N
is_system	boolean	N

security.role_permission

Column	Type	Null	Notes
role_id	uuid	N	PK, FK
permission_id	uuid	N	PK, FK

security.user_role

Column	Type	Null	Notes
user_role_id	uuid	N	PK
user_id	uuid	N	FK
role_id	uuid	N	FK
assigned_by	text	Y
assigned_at	timestamptz	N
revoked_at	timestamptz	Y

member — members, coverage, eligibility

Members and their coverage policies, COB priority history, eligibility check + sweep records, crossover cases.

Column inventory

member.cob_priority_history

Column	Type	Null	Notes
cob_history_id	uuid	N	PK
member_id	uuid	N	FK
policy_id	uuid	N	FK
priority_order	integer	N
effective_from	date	N
effective_to	date	Y
source	varchar(50)	N
changed_by	varchar(100)	Y
created_at	timestamptz	N

member.coverage_policy

Column	Type	Null	Notes
policy_id	uuid	N	PK
member_id	uuid	N	FK
payer_id	uuid	N
external_program_id	uuid	Y	FK
plan_name	varchar(255)	Y
member_number	varchar(100)	Y
group_number	varchar(100)	Y
subscriber_member_id	uuid	Y	FK
priority_order	integer	Y
effective_from	date	N
effective_to	date	Y
is_active	boolean	N
created_at	timestamptz	N
updated_at	timestamptz	N
effective_range	daterange	Y
subscriber_relationship	varchar(20)	Y
subscriber_first_name	varchar(100)	Y
subscriber_last_name	varchar(100)	Y
subscriber_dob	date	Y
subscriber_sex	varchar(10)	Y
lob	varchar(50)	Y
product_type	varchar(50)	Y
plan_id	varchar(100)	Y
coverage_status	varchar(30)	Y
coverage_status_source	varchar(50)	Y
coverage_status_verified_at	timestamptz	Y
policy_type	varchar(30)	Y
snpt_flag	boolean	N
created_by	uuid	Y
updated_by	uuid	Y
medicare_entitlement_reason	varchar(20)	Y
esrd_coordination_start_date	date	Y
employer_size	integer	Y
has_black_lung_benefit	boolean	N
member_number_encrypted	bytea	Y
member_number_hash	varchar(64)	Y
enc_kid	smallint	N

member.coverage_verification_event

Column	Type	Null	Notes
verify_id	uuid	N	PK
member_id	uuid	N	FK
payer_id	uuid	N
verified_at	timestamptz	N
source	varchar(50)	N
notes	text	Y
verified_by	uuid	Y

member.crossover_case

Column	Type	Null	Notes
crossover_id	uuid	N	PK
member_id	uuid	N	FK
primary_payer_id	uuid	N
secondary_payer_id	uuid	N
status	varchar(20)	N
created_at	timestamptz	N
updated_at	timestamptz	N

member.eligibility_check

Column	Type	Null	Notes
elig_check_id	uuid	N	PK
member_id	uuid	N	FK
policy_id	uuid	Y	FK
requested_at	timestamptz	N
source	varchar(100)	N
request_payload_pointer	text	Y

member.eligibility_result

Column	Type	Null	Notes
elig_result_id	uuid	N	PK
elig_check_id	uuid	N	FK
coverage_effective_from	date	Y
coverage_effective_to	date	Y
is_eligible	boolean	Y
raw_response_pointer	text	Y
structured_json	jsonb	Y
created_at	timestamptz	N
plan_name	varchar(255)	Y
plan_type	varchar(50)	Y
subscriber_id	varchar(100)	Y
group_number	varchar(100)	Y
group_name	varchar(255)	Y
coverage_level	varchar(50)	Y
network_status	varchar(30)	Y
copay_cents	bigint	Y
coinsurance_pct	numeric	Y
deductible_cents	bigint	Y
deductible_remaining_cents	bigint	Y
oop_max_cents	bigint	Y
oop_remaining_cents	bigint	Y
response_received_at	timestamptz	Y
subscriber_id_encrypted	bytea	Y
subscriber_id_hash	varchar(64)	Y
enc_kid	smallint	N

member.eligibility_sweep_run

Column	Type	Null	Notes
sweep_run_id	uuid	N	PK
name	varchar(255)	N
triggered_by_user_id	uuid	Y
trigger_source	varchar(20)	N
schedule_id	uuid	Y	FK
triggered_at	timestamptz	N
completed_at	timestamptz	Y
status	varchar(20)	N
scope_json	jsonb	N
total_members	integer	N
success_count	integer	N
error_count	integer	N
skipped_count	integer	N
correlation_id	varchar(100)	Y
created_at	timestamptz	N
updated_at	timestamptz	N

member.eligibility_sweep_run_member

Column	Type	Null	Notes
run_member_id	uuid	N	PK
sweep_run_id	uuid	N	FK
member_id	uuid	N	FK
service_event_id	uuid	Y	FK
elig_check_id	uuid	Y	FK
payer_id	uuid	Y
coverage_id	uuid	Y	FK
status	varchar(20)	N
failure_reason	text	Y
created_at	timestamptz	N
updated_at	timestamptz	N

member.eligibility_sweep_schedule

Column	Type	Null	Notes
sweep_schedule_id	uuid	N	PK
name	varchar(255)	N
cron_expression	text	N
scope_json	jsonb	N
created_by_user_id	uuid	N
active	boolean	N
last_fired_at	timestamptz	Y
last_run_id	uuid	Y	FK
last_status	varchar(20)	Y
last_error	text	Y
created_at	timestamptz	N
updated_at	timestamptz	N

member.member

Column	Type	Null	Notes
member_id	uuid	N	PK
org_id	uuid	N	FK
first_name	varchar(100)	Y
last_name	varchar(100)	Y
middle_name	varchar(100)	Y
suffix	varchar(20)	Y
dob	date	Y
sex	varchar(10)	Y
ssn_last_four	varchar(4)	Y
address1	varchar(255)	Y
address2	varchar(255)	Y
city	varchar(100)	Y
state	varchar(2)	Y
zip	varchar(10)	Y
phone	varchar(20)	Y
email	varchar(255)	Y
is_active	boolean	N
created_at	timestamptz	N
updated_at	timestamptz	N
member_type	varchar(30)	Y
member_status	varchar(20)	Y
created_by	uuid	Y
updated_by	uuid	Y
ssn_encrypted	bytea	Y
ssn_hash	varchar(64)	Y
dob_encrypted	bytea	Y
dob_year	smallint	Y
enc_kid	smallint	N

member.member_identifier

Column	Type	Null	Notes
member_identifier_id	uuid	N	PK
member_id	uuid	N	FK
identifier_type	varchar(50)	N
identifier_value	varchar(100)	N
issuer	varchar(100)	Y
effective_from	date	N
effective_to	date	Y
created_at	timestamptz	N
identifier_value_encrypted	bytea	Y
identifier_value_hash	varchar(64)	Y
enc_kid	smallint	N

member.member_note

Column	Type	Null	Notes
note_id	uuid	N	PK
member_id	uuid	N	FK
note_type	varchar(50)	N
note_text	text	N
created_by	uuid	Y
created_at	timestamptz	N
updated_at	timestamptz	N

member.member_phone

Column	Type	Null	Notes
phone_id	uuid	N	PK
member_id	uuid	N	FK
phone_type	varchar(20)	N
phone_number	varchar(20)	N
can_leave_message	boolean	N
is_primary	boolean	N
created_at	timestamptz	N
updated_at	timestamptz	N

member.member_program_enrollment

Column	Type	Null	Notes
enrollment_id	uuid	N	PK
member_id	uuid	N	FK
external_program_id	uuid	N
canonical_program_id	uuid	Y
admit_date	date	N
discharge_date	date	Y
default_facility_id	uuid	Y	FK
default_provider_id	uuid	Y	FK
default_diagnosis_set_id	uuid	Y
release_of_information	boolean	N
assignment_of_benefits	boolean	N
restricting	boolean	N
flags	jsonb	Y
status	varchar(20)	N
is_active	boolean	N
created_at	timestamptz	N
updated_at	timestamptz	N

service — clinical, EVV, authorizations

Service events, group sessions, attendance, staff assignments, authorizations + procedures + responses, EVV visits + exceptions + sources.

Column inventory

service.authorization_procedure

Column	Type	Null	Notes
auth_procedure_id	uuid	N	PK
authorization_id	uuid	N	FK
procedure_code	varchar(10)	N
rate_code	varchar(20)	Y
modifier_codes	ARRAY	Y
units_allowed	numeric	Y
created_at	timestamptz	N

service.authorization_program

Column	Type	Null	Notes
authorization_id	uuid	N	PK, FK
external_program_id	uuid	N	PK

service.authorization_request

Column	Type	Null	Notes
auth_request_id	uuid	N	PK
member_id	uuid	N	FK
payer_id	uuid	N
provider_id	uuid	Y
parent_auth_request_id	uuid	Y	FK
trace_number	varchar(80)	N
request_category	varchar(4)	N
certification_type	varchar(2)	N
service_type_code	varchar(5)	Y
service_location_code	varchar(3)	Y
event_date_from	date	Y
event_date_to	date	Y
admission_date	date	Y
diagnoses	jsonb	N
status	varchar(20)	N
submitted_x12	text	Y
submitted_at	timestamptz	Y
last_response_at	timestamptz	Y
completed_at	timestamptz	Y
cancelled_at	timestamptz	Y
submission_error	text	Y
materialized_authorization_id	uuid	Y	FK
source_submission_id	uuid	Y
created_by	uuid	Y
created_at	timestamptz	N
updated_at	timestamptz	N

service.authorization_request_line

Column	Type	Null	Notes
auth_request_line_id	uuid	N	PK
auth_request_id	uuid	N	FK
sequence_no	integer	N
procedure_code	varchar(10)	N
modifiers	ARRAY	N
line_kind	varchar(15)	N
revenue_code	varchar(5)	Y
requested_units	numeric	Y
unit_qualifier	varchar(3)	Y
frequency	integer	Y
time_period_qualifier	varchar(3)	Y
number_of_periods	integer	Y
service_date_from	date	Y
service_date_to	date	Y
rendering_provider_npi	varchar(20)	Y
charge_amount_cents	bigint	Y
created_at	timestamptz	N

service.authorization_response

Column	Type	Null	Notes
auth_response_id	uuid	N	PK
auth_request_id	uuid	N	FK
response_type	varchar(20)	N
action_code	varchar(4)	Y
certification_number	varchar(80)	Y
review_reason_code	varchar(10)	Y
messages	ARRAY	N
raw_x12	text	N
received_at	timestamptz	N
source_submission_id	uuid	Y
error_reason_code	varchar(10)	Y
error_follow_up_code	varchar(10)	Y
created_at	timestamptz	N

service.authorization_response_line

Column	Type	Null	Notes
auth_response_line_id	uuid	N	PK
auth_response_id	uuid	N	FK
auth_request_line_id	uuid	Y	FK
procedure_code	varchar(10)	N
modifiers	ARRAY	N
line_kind	varchar(15)	Y
action_code	varchar(4)	Y
certification_number	varchar(80)	Y
review_reason_code	varchar(10)	Y
approved_units	numeric	Y
approved_unit_qualifier	varchar(3)	Y
approved_frequency	integer	Y
approved_time_period	varchar(3)	Y
approved_number_of_periods	integer	Y
service_date_from	date	Y
service_date_to	date	Y
messages	ARRAY	N
created_at	timestamptz	N

service.authorization_usage_event

Column	Type	Null	Notes
usage_event_id	uuid	N	PK
authorization_id	uuid	N	FK
service_event_id	uuid	Y	FK
charge_id	uuid	Y
claim_id	uuid	Y
claim_line_id	uuid	Y
units_consumed	numeric	Y
service_date	date	N
rate_code	varchar(20)	Y
procedure_code	varchar(10)	Y
status	varchar(20)	N
reversed_by_id	uuid	Y	FK
created_at	timestamptz	N
updated_at	timestamptz	N
visits_consumed	integer	Y
amount_consumed_cents	bigint	Y
days_consumed	integer	Y

service.evv_exception

Column	Type	Null	Notes
exception_id	uuid	N	PK
org_id	uuid	N	FK
visit_id	uuid	Y	FK
claim_id	uuid	Y	FK
claim_line_id	uuid	Y	FK
member_id	uuid	Y	FK
provider_id	uuid	Y	FK
service_date	date	Y
reason_code	varchar(48)	N
reason_note	text	Y
status	varchar(16)	N
requested_by	uuid	N
requested_at	timestamptz	N
approved_by	uuid	Y
approved_at	timestamptz	Y
approval_note	text	Y
expires_at	timestamptz	N
created_at	timestamptz	N
updated_at	timestamptz	N

service.evv_outbound_submission

Column	Type	Null	Notes
submission_id	uuid	N	PK
visit_id	uuid	N	FK
target_aggregator	varchar(64)	N
status	varchar(16)	N
payload	jsonb	Y
response	jsonb	Y
retry_count	integer	N
next_retry_at	timestamptz	Y
last_error	text	Y
created_at	timestamptz	N
updated_at	timestamptz	N

service.evv_source

Column	Type	Null	Notes
source_id	uuid	N	PK
org_id	uuid	N	FK
source_code	varchar(64)	N
vendor_name	varchar(128)	N
api_key_hash	varchar(256)	N
api_key_last_four	varchar(4)	N
is_active	boolean	N
created_by	uuid	Y
rotated_at	timestamptz	Y
created_at	timestamptz	N
updated_at	timestamptz	N

service.evv_visit

Column	Type	Null	Notes
visit_id	uuid	N	PK
org_id	uuid	N	FK
service_event_id	uuid	Y	FK
member_id	uuid	N	FK
provider_id	uuid	N	FK
facility_id	uuid	Y	FK
visit_date	date	N
check_in_ts_encrypted	bytea	Y
check_out_ts_encrypted	bytea	Y
check_in_lat_encrypted	bytea	Y
check_in_lng_encrypted	bytea	Y
check_out_lat_encrypted	bytea	Y
check_out_lng_encrypted	bytea	Y
verification_method	varchar(32)	N
verification_status	varchar(32)	N
source_id	uuid	N	FK
source_system	varchar(64)	N
external_visit_id	varchar(128)	N
external_payload_encrypted	bytea	Y
idempotency_key	varchar(256)	Y
ingested_at	timestamptz	N
verified_at	timestamptz	Y
created_at	timestamptz	N
updated_at	timestamptz	N

service.evv_visit_change_log

Column	Type	Null	Notes
change_id	uuid	N	PK
visit_id	uuid	N	FK
changed_at	timestamptz	N
changed_by_type	varchar(16)	N
changed_by_id	uuid	Y
change_type	varchar(16)	N
before_snapshot	jsonb	Y
after_snapshot	jsonb	Y
diff	jsonb	Y

service.group_session

Column	Type	Null	Notes
group_session_id	uuid	N	PK
site_id	uuid	N	FK
facility_id	uuid	Y	FK
start_ts	timestamptz	N
end_ts	timestamptz	N
group_type	varchar(50)	Y
max_participants	integer	Y
created_at	timestamptz	N
updated_at	timestamptz	N

service.service_authorization

Column	Type	Null	Notes
authorization_id	uuid	N	PK
member_id	uuid	N	FK
payer_id	uuid	N
service_type_id	uuid	Y
authorization_number	varchar(50)	N
approved_units	numeric	N
used_units	numeric	N
unit_type	varchar(20)	N
effective_from	date	N
effective_to	date	N
status	varchar(20)	N
source	varchar(20)	Y
notes	text	Y
created_at	timestamptz	N
updated_at	timestamptz	N
include_on_claim	boolean	N
provider_id	uuid	Y	FK
policy_id	uuid	Y	FK
created_by	uuid	Y
updated_by	uuid	Y
authorization_type	varchar(20)	N
parent_authorization_id	uuid	Y	FK
approved_visits	integer	Y
used_visits	integer	N
approved_amount_cents	bigint	Y
used_amount_cents	bigint	N
approved_days	integer	Y
used_days	integer	N
episode_days	integer	Y

service.service_event

Column	Type	Null	Notes
service_event_id	uuid	N	PK
member_id	uuid	N	FK
participant_id	uuid	Y	FK
site_id	uuid	N	FK
facility_id	uuid	Y	FK
place_of_service	varchar(5)	Y
modality	varchar(50)	Y
service_date	date	N
status	varchar(20)	N
external_program_id	uuid	Y	FK
notes	text	Y
created_at	timestamptz	N
updated_at	timestamptz	N
created_by	uuid	Y
updated_by	uuid	Y

service.service_participant

Column	Type	Null	Notes
participant_id	uuid	N	PK
group_session_id	uuid	N	FK
member_id	uuid	N	FK
attendance_status	varchar(20)	N
created_at	timestamptz	N
updated_at	timestamptz	N

service.service_segment

Column	Type	Null	Notes
segment_id	uuid	N	PK
service_event_id	uuid	N	FK
start_ts	timestamptz	N
end_ts	timestamptz	N
timezone	varchar(50)	N
units	numeric	Y
unit_type	varchar(20)	Y
created_at	timestamptz	N

service.staff_assignment

Column	Type	Null	Notes
staff_assignment_id	uuid	N	PK
service_event_id	uuid	N	FK
provider_id	uuid	N	FK
role	varchar(50)	N
start_ts	timestamptz	Y
end_ts	timestamptz	Y
created_at	timestamptz	N

billing — claims, denials, remittance, COB, fee schedules

The largest schema. Charges, claims (with line + modifier + institutional context), submissions, EDI transactions, remittance + adjustments, denials + appeals, auto-correction attempts, fee schedules + payer contracts, COB chains.

Column inventory

billing.appeal_record

Column	Type	Null	Notes
appeal_id	uuid	N	PK
denial_id	uuid	N	FK
original_claim_id	uuid	N	FK
appeal_claim_id	uuid	Y	FK
appeal_level	integer	N
filed_date	date	Y
deadline	date	Y
status	varchar(20)	N
submission_method	varchar(50)	Y
outcome_date	date	Y
recovered_amount_cents	bigint	Y
notes	text	Y
created_at	timestamptz	N
updated_at	timestamptz	N

billing.auto_correction_attempt

Column	Type	Null	Notes
attempt_id	uuid	N	PK
denial_id	uuid	N	FK
claim_id	uuid	N	FK
carc_code	varchar(10)	N
handler_name	varchar(80)	N
strategy	varchar(40)	N
status	varchar(20)	N
rebilled_claim_id	uuid	Y	FK
appeal_id	uuid	Y	FK
recovered_amount_cents	bigint	Y
failure_reason	text	Y
details	jsonb	N
attempted_at	timestamptz	N
created_at	timestamptz	N
updated_at	timestamptz	N

billing.charge_component

Column	Type	Null	Notes
component_id	uuid	N	PK
charge_id	uuid	N	FK
component_type	varchar(50)	N
units	numeric	Y
unit_type	varchar(20)	Y
rate_code	varchar(20)	Y
procedure_code	varchar(10)	Y
revenue_code	varchar(4)	Y
amount_cents	bigint	Y
created_at	timestamptz	N
per_diem_rate_cents	bigint	Y
per_diem_currency	varchar(3)	Y

billing.charge_item

Column	Type	Null	Notes
charge_id	uuid	N	PK
service_event_id	uuid	N	FK
segment_id	uuid	Y	FK
billing_entity_id	uuid	N	FK
facility_id	uuid	Y	FK
rendering_provider_id	uuid	Y	FK
supervising_provider_id	uuid	Y	FK
payer_id	uuid	Y
external_program_id	uuid	Y	FK
canonical_program_id	uuid	Y
place_of_service	varchar(5)	Y
from_ts	timestamptz	Y
to_ts	timestamptz	Y
service_date	date	N
units	numeric	N
unit_type	varchar(20)	N
rate_code	varchar(20)	Y
procedure_code	varchar(10)	Y
revenue_code	varchar(4)	Y
charge_status	varchar(20)	N
diagnosis_set_id	uuid	Y	FK
created_at	timestamptz	N
updated_at	timestamptz	N
amount_cents	bigint	Y
context_snapshot_id	uuid	Y	FK
created_by	uuid	Y
updated_by	uuid	Y
group_session_id	uuid	Y	FK

billing.charge_modifier

Column	Type	Null	Notes
charge_modifier_id	uuid	N	PK
charge_id	uuid	N	FK
modifier_code	varchar(5)	N
sequence_no	integer	N
source	varchar(16)	N
injection_rule_id	uuid	Y

billing.claim

Column	Type	Null	Notes
claim_id	uuid	N	PK
payer_id	uuid	N
billing_entity_id	uuid	N	FK
facility_id	uuid	Y	FK
member_id	uuid	N	FK
canonical_program_id	uuid	Y
external_program_id	uuid	Y	FK
claim_type	varchar(1)	N
service_from	date	N
service_to	date	N
grouping_key	varchar(100)	Y
status_current	varchar(30)	N
patient_control_number	varchar(50)	Y
total_charge_cents	bigint	Y
created_at	timestamptz	N
updated_at	timestamptz	N
version_number	integer	N
parent_claim_id	uuid	Y	FK
revision_reason	text	Y
context_snapshot_id	uuid	Y	FK
authorization_id	uuid	Y
authorization_number	varchar(50)	Y
root_claim_id	uuid	Y	FK

billing.claim_charge_link

Column	Type	Null	Notes
claim_line_id	uuid	N	PK, FK
charge_id	uuid	N	PK, FK
applied_units	numeric	Y
applied_charge_cents	bigint	Y

billing.claim_cob_adjustment

Column	Type	Null	Notes
cob_adjustment_id	uuid	N	PK
claim_id	uuid	N	FK
primary_claim_id	uuid	N	FK
primary_claim_line_id	uuid	Y	FK
secondary_claim_line_id	uuid	Y	FK
remittance_id	uuid	Y	FK
kind	varchar(4)	N
group_code	varchar(5)	Y
reason_code	varchar(10)	Y
quantity	numeric	Y
amt_qualifier	varchar(5)	Y
amount_cents	bigint	N
adjudication_date	date	Y
created_at	timestamptz	N

billing.claim_institutional_code

Column	Type	Null	Notes
inst_code_id	uuid	N	PK
claim_id	uuid	N	FK
code_type	varchar(20)	N
code	varchar(10)	N
code_date	date	Y
numeric_value	varchar(20)	Y

billing.claim_institutional_context

Column	Type	Null	Notes
claim_id	uuid	N	PK, FK
admit_date	date	Y
discharge_date	date	Y
patient_discharge_status	varchar(5)	Y
admit_type	varchar(5)	Y
admit_source	varchar(5)	Y
bill_type	varchar(4)	Y
drg_code	varchar(10)	Y
drg_weight	numeric	Y
point_of_origin	varchar(2)	Y
patient_status_qualifier	varchar(5)	Y
covered_days	integer	Y
non_covered_days	integer	Y

billing.claim_line

Column	Type	Null	Notes
claim_line_id	uuid	N	PK
claim_id	uuid	N	FK
line_no	integer	N
from_date	date	Y
to_date	date	Y
units	numeric	N
unit_type	varchar(20)	N
rate_code	varchar(20)	Y
procedure_code	varchar(10)	Y
revenue_code	varchar(4)	Y
charge_cents	bigint	N
diagnosis_set_id	uuid	Y	FK
rendering_provider_id	uuid	Y	FK
supervising_provider_id	uuid	Y	FK
authorization_id	uuid	Y
authorization_number	varchar(50)	Y
group_session_id	uuid	Y	FK

billing.claim_line_modifier

Column	Type	Null	Notes
claim_line_modifier_id	uuid	N	PK
claim_line_id	uuid	N	FK
modifier_code	varchar(5)	N
sequence_no	integer	N

billing.claim_relationship

Column	Type	Null	Notes
relationship_id	uuid	N	PK
parent_claim_id	uuid	N	FK
child_claim_id	uuid	N	FK
relationship_type	varchar(20)	N
notes	text	Y
created_at	timestamptz	N

billing.claim_status_history

Column	Type	Null	Notes
status_hist_id	uuid	N	PK
claim_id	uuid	N	FK
status	varchar(30)	N
status_at	timestamptz	N
source	varchar(50)	N
details	jsonb	Y
performed_by_user_id	text	N
performed_by_type	text	N

billing.claim_submission

Column	Type	Null	Notes
submission_id	uuid	N	PK
claim_id	uuid	N	FK
edi_tx_id	uuid	Y	FK
sent_at	timestamptz	N
interchange_control_number	varchar(20)	Y
group_control_number	varchar(20)	Y
transaction_set_control_number	varchar(20)	Y
ack_status	varchar(30)	Y
ack_at	timestamptz	Y
rejection_codes	jsonb	Y
payer_claim_ref	varchar(100)	Y

billing.cob_waterfall_pending

Column	Type	Null	Notes
pending_id	uuid	N	PK
primary_claim_id	uuid	N	FK
reason	varchar(30)	N
status	varchar(15)	N
paid_amount_cents	bigint	Y
remittance_id	uuid	Y	FK
notes	text	Y
created_at	timestamptz	N
resumed_at	timestamptz	Y
cancelled_at	timestamptz	Y
resumed_by_user_id	text	Y

billing.contract_expiry_alert

Column	Type	Null	Notes
alert_id	uuid	N	PK
payer_id	uuid	Y
billing_entity_id	uuid	Y
lead_days	integer	N
recipients	jsonb	N
subject	varchar(200)	N
active	boolean	N
last_fired_at	timestamptz	Y
last_status	varchar(20)	Y
last_error	text	Y
created_at	timestamptz	N
updated_at	timestamptz	N

billing.denial_category_mapping

Column	Type	Null	Notes
mapping_id	uuid	N	PK
carc_code	varchar(10)	N
rarc_code	varchar(10)	Y
denial_category	varchar(30)	N
is_correctable	boolean	N
default_correction_strategy	varchar(30)	Y
description	text	Y
created_at	timestamptz	N

billing.denial_record

Column	Type	Null	Notes
denial_id	uuid	N	PK
claim_id	uuid	N	FK
claim_line_id	uuid	Y	FK
remit_claim_id	uuid	Y	FK
denial_date	date	N
denial_type	varchar(20)	N
denial_category	varchar(30)	N
carc_codes	jsonb	N
rarc_codes	jsonb	N
denied_amount_cents	bigint	Y
original_amount_cents	bigint	Y
is_correctable	boolean	Y
correction_strategy	varchar(30)	Y
appeal_deadline	date	Y
response_claim_id	uuid	Y	FK
status	varchar(20)	N
resolved_at	timestamptz	Y
resolved_by	text	Y
resolution_notes	text	Y
created_at	timestamptz	N
updated_at	timestamptz	N

billing.diagnosis

Column	Type	Null	Notes
diagnosis_id	uuid	N	PK
diagnosis_set_id	uuid	N	FK
icd10_code	varchar(10)	N
sequence_no	integer	N

billing.diagnosis_set

Column	Type	Null	Notes
diagnosis_set_id	uuid	N	PK
created_at	timestamptz	N

billing.edi_control_sequence

Column	Type	Null	Notes
sequence_id	uuid	N	PK
trading_partner_id	uuid	N
sequence_type	varchar(5)	N
current_value	bigint	N
updated_at	timestamptz	N

billing.edi_transaction

Column	Type	Null	Notes
edi_tx_id	uuid	N	PK
trading_partner_id	uuid	N
tx_type	varchar(10)	N
direction	varchar(10)	N
control_numbers	jsonb	Y
payload_pointer	text	N
file_name	varchar(255)	Y
file_size_bytes	bigint	Y
checksum	varchar(64)	Y
created_at	timestamptz	N
batch_id	uuid	Y
correlation_id	uuid	Y
file_reference	varchar(500)	Y
error_details	jsonb	Y
retry_count	integer	N
x12_content_hash	varchar(128)	Y

billing.edi_transaction_claim

Column	Type	Null	Notes
edi_tx_claim_id	uuid	N	PK
edi_transaction_id	uuid	N	FK
claim_id	uuid	N	FK
claim_line_id	uuid	Y	FK
position_in_batch	integer	Y
created_at	timestamptz	N

billing.fee_schedule

Column	Type	Null	Notes
fee_schedule_id	uuid	N	PK
payer_id	uuid	N
billing_entity_id	uuid	Y	FK
name	varchar(200)	N
effective_from	date	N
effective_to	date	Y
is_active	boolean	N
created_at	timestamptz	N
updated_at	timestamptz	N
contract_id	uuid	Y	FK

billing.fee_schedule_entry

Column	Type	Null	Notes
entry_id	uuid	N	PK
fee_schedule_id	uuid	N	FK
rate_code	varchar(20)	Y
procedure_code	varchar(10)	Y
modifier_codes	ARRAY	Y
place_of_service	varchar(5)	Y
rate_amount_cents	integer	N
unit_type	varchar(20)	Y
effective_from	date	N
effective_to	date	Y
created_at	timestamptz	N

billing.payer_contract

Column	Type	Null	Notes
contract_id	uuid	N	PK
payer_id	uuid	N
billing_entity_id	uuid	Y	FK
contract_number	varchar(64)	N
effective_from	date	N
effective_to	date	Y
is_active	boolean	N
notes	text	Y
created_at	timestamptz	N
updated_at	timestamptz	N

billing.priced_charge_snapshot

Column	Type	Null	Notes
priced_snapshot_id	uuid	N	PK
charge_id	uuid	N	FK
pricing_context_id	uuid	N	FK
priced_at	timestamptz	N
expected_charge_cents	bigint	Y
expected_allowed_cents	bigint	Y
rate_source_ref	text	Y
version_hash	varchar(64)	N

billing.pricing_context

Column	Type	Null	Notes
pricing_context_id	uuid	N	PK
payer_id	uuid	N
external_program_id	uuid	Y	FK
state	varchar(2)	Y
region	varchar(50)	Y
credential_tier	varchar(50)	Y
time_of_day_bucket	varchar(50)	Y
group_size	integer	Y
place_of_service	varchar(5)	Y
created_at	timestamptz	N

billing.remittance

Column	Type	Null	Notes
remit_id	uuid	N	PK
payer_id	uuid	N
trading_partner_id	uuid	Y
received_at	timestamptz	N
trn	varchar(50)	Y
total_payment_cents	bigint	Y
edi_tx_id	uuid	Y	FK
remittance_type	varchar(30)	N
original_remit_id	uuid	Y	FK
reversal_reason	text	Y
processing_status	varchar(30)	N
billing_entity_id	uuid	Y	FK
matched_at	timestamptz	Y

billing.remittance_adjustment

Column	Type	Null	Notes
adj_id	uuid	N	PK
remit_line_id	uuid	Y	FK
group_code	varchar(5)	N
reason_code	varchar(10)	N
amount_cents	bigint	N
quantity	numeric	Y
remit_claim_id	uuid	Y	FK
adjustment_source	varchar(32)	Y

billing.remittance_claim

Column	Type	Null	Notes
remit_claim_id	uuid	N	PK
remit_id	uuid	N	FK
claim_id	uuid	Y	FK
claim_external_id	varchar(50)	Y
patient_control_number	varchar(50)	Y
total_paid_cents	bigint	Y
total_charge_cents	bigint	Y
total_patient_resp_cents	bigint	Y
claim_status	varchar(5)	Y
match_confidence	varchar(20)	Y
match_method	varchar(50)	Y

billing.remittance_line

Column	Type	Null	Notes
remit_line_id	uuid	N	PK
remit_claim_id	uuid	N	FK
matched_claim_line_id	uuid	Y	FK
service_id	varchar(20)	Y
service_from	date	Y
service_to	date	Y
units	numeric	Y
charge_cents	bigint	Y
paid_cents	bigint	Y
allowed_cents	bigint	Y
line_no	integer	Y
procedure_code	varchar(20)	Y
revenue_code	varchar(10)	Y

billing.remittance_match

Column	Type	Null	Notes
match_id	uuid	N	PK
remit_claim_id	uuid	N	FK
claim_id	uuid	Y	FK
match_method	varchar(50)	N
match_confidence	varchar(20)	N
match_score	numeric	Y
is_auto	boolean	N
matched_by	varchar(100)	Y
matched_at	timestamptz	N
superseded_by	uuid	Y	FK
notes	text	Y
created_at	timestamptz	N

billing.remittance_remark

Column	Type	Null	Notes
rarc_id	uuid	N	PK
remit_line_id	uuid	Y	FK
remark_code	varchar(10)	N
remit_claim_id	uuid	Y	FK

billing.remittance_trace

Column	Type	Null	Notes
trace_id	uuid	N	PK
remit_id	uuid	N	FK
payment_method	varchar(20)	N
eft_trace_number	varchar(50)	Y
check_number	varchar(50)	Y
deposit_date	date	Y
deposit_batch_ref	varchar(100)	Y

billing.repricing_event

Column	Type	Null	Notes
repricing_id	uuid	N	PK
org_id	uuid	N	FK
effective_date	date	N
trigger	varchar(50)	N
policy	varchar(50)	N
description	text	Y
created_at	timestamptz	N

billing.resolved_context_snapshot

Column	Type	Null	Notes
snapshot_id	uuid	N	PK
rendering_provider_id	uuid	Y
rendering_provider_npi	varchar(10)	Y
rendering_provider_name	varchar(200)	Y
rendering_credential	varchar(50)	Y
supervising_provider_id	uuid	Y
supervising_provider_npi	varchar(10)	Y
supervising_provider_name	varchar(200)	Y
facility_id	uuid	Y
facility_npi	varchar(10)	Y
facility_name	varchar(255)	Y
facility_address_state	varchar(2)	Y
billing_entity_id	uuid	Y
billing_entity_name	varchar(255)	Y
billing_entity_npi	varchar(10)	Y
billing_entity_tin	varchar(20)	Y
payer_id	uuid	Y
payer_name	varchar(255)	Y
external_program_id	uuid	Y
external_program_code	varchar(50)	Y
canonical_program_id	uuid	Y
canonical_program_name	varchar(100)	Y
member_id	uuid	Y
member_name	varchar(200)	Y
member_dob	date	Y
member_medicaid_id	varchar(100)	Y
snapshot_source	varchar(50)	N
snapshotted_at	timestamptz	N
created_at	timestamptz	N

billing.v_auto_correction_success_rate

Column	Type	Null	Notes
carc_code	varchar(10)	Y
attempts	bigint	Y
successes	bigint	Y
failures	bigint	Y
skipped	bigint	Y
success_rate	numeric	Y
recovered_amount_cents	bigint	Y

billing.v_member_liability

Column	Type	Null	Notes
member_id	uuid	Y
claim_id	uuid	Y
root_claim_id	uuid	Y
patient_responsibility_cents	numeric	Y
last_adjudication_date	date	Y

ingestion — feeds, batches, mappings, push API

Source systems and feeds (SFTP polling + push API), inbound batches and per-row records, mapping definitions, idempotency keys, push-API rate-usage.

Column inventory

ingestion.api_credential

Column	Type	Null	Notes
credential_id	uuid	N	PK
source_feed_id	uuid	N	FK
key_prefix	varchar(64)	N
key_hash	varchar(120)	N
key_last_four	varchar(4)	N
label	varchar(128)	Y
created_by	uuid	Y
created_at	timestamptz	N
last_used_at	timestamptz	Y
revoked_at	timestamptz	Y
revoked_by	uuid	Y

ingestion.idempotency_key

Column	Type	Null	Notes
idempotency_id	uuid	N	PK
org_id	uuid	N	FK
scope	varchar(50)	N
key_hash	varchar(64)	N
first_seen_at	timestamptz	N
resolved_entity_ref	text	Y
source_feed_id	uuid	Y
expires_at	timestamptz	Y

ingestion.import_result

Column	Type	Null	Notes
result_id	uuid	N	PK
record_id	uuid	N	FK
status	varchar(20)	N
errors	jsonb	Y
created_charge_id	uuid	Y	FK
created_member_id	uuid	Y	FK
created_service_event_id	uuid	Y	FK
created_at	timestamptz	N

ingestion.inbound_artifact

Column	Type	Null	Notes
artifact_id	uuid	N	PK
source_feed_id	uuid	N	FK
blob_pointer	text	N
file_type	varchar(20)	N
file_name	varchar(255)	Y
checksum_sha256	varchar(64)	N
received_at	timestamptz	N
size_bytes	bigint	Y
uploaded_by	uuid	Y
uploaded_at	timestamptz	Y

ingestion.inbound_batch

Column	Type	Null	Notes
batch_id	uuid	N	PK
org_id	uuid	N	FK
artifact_id	uuid	Y	FK
started_at	timestamptz	N
completed_at	timestamptz	Y
status	varchar(32)	N
summary	jsonb	Y
total_records	integer	N
processed_records	integer	N
success_records	integer	N
error_records	integer	N
skipped_records	integer	N
error_summary	text	Y
created_at	timestamptz	N
updated_at	timestamptz	N

ingestion.inbound_record

Column	Type	Null	Notes
record_id	uuid	N	PK
batch_id	uuid	N	FK
artifact_id	uuid	N	FK
row_num	integer	Y
row_hash	varchar(64)	N
raw_payload	jsonb	N
created_at	timestamptz	N
mapping_version_id	uuid	Y
status	varchar(20)	N
error_detail	jsonb	Y
transformed_data	jsonb	Y
target_entity_type	varchar(50)	Y
target_entity_id	uuid	Y
processed_at	timestamptz	Y
updated_at	timestamptz	N

ingestion.mapping_definition

Column	Type	Null	Notes
mapping_id	uuid	N	PK
source_feed_id	uuid	N	FK
version	integer	N
effective_from	date	N
effective_to	date	Y
mapping_yaml	text	N
mapping_hash	varchar(64)	N
created_at	timestamptz	N
published_at	timestamptz	Y
published_by	varchar(100)	Y
is_current	boolean	N

ingestion.push_rate_usage

Column	Type	Null	Notes
credential_id	uuid	N	PK, FK
window_start	timestamptz	N	PK
request_count	integer	N
record_count	integer	N

ingestion.reconciliation_summary

Column	Type	Null	Notes
recon_id	uuid	N	PK
batch_id	uuid	N	FK
expected_counts	jsonb	Y
imported_counts	jsonb	Y
totals	jsonb	Y
drift_flags	jsonb	Y
created_at	timestamptz	N

ingestion.source_feed

Column	Type	Null	Notes
source_feed_id	uuid	N	PK
source_system_id	uuid	N	FK
org_id	uuid	N	FK
feed_name	varchar(255)	N
feed_type	varchar(50)	Y
is_active	boolean	N
created_at	timestamptz	N
updated_at	timestamptz	N
outbound_reports_enabled	boolean	N
outbound_sftp_path	varchar(500)	Y
transport_type	varchar(20)	Y
poll_config	jsonb	Y
last_run_at	timestamptz	Y
next_run_at	timestamptz	Y
push_requests_per_minute	integer	Y
push_records_per_minute	integer	Y
parser_config	jsonb	Y

ingestion.source_system

Column	Type	Null	Notes
source_system_id	uuid	N	PK
name	varchar(255)	N
vendor	varchar(255)	Y
description	text	Y
created_at	timestamptz	N
connection_config_encrypted	bytea	Y

ledger — accounts and entries

Double-entry ledger. ledger_entry is monthly-partitioned (one child table per calendar month); shown here as a single logical table.

Column inventory

ledger.ledger_account

Column	Type	Null	Notes
account_id	uuid	N	PK
org_id	uuid	N	FK
account_type	varchar(50)	N
payer_id	uuid	Y
external_program_id	uuid	Y	FK
billing_entity_id	uuid	Y	FK
name	varchar(255)	Y
is_active	boolean	N
created_at	timestamptz	N
updated_at	timestamptz	N

ledger.ledger_entry

Column	Type	Null	Notes
entry_id	uuid	N	PK
account_id	uuid	N	FK
entry_type	text	N
amount_cents	bigint	N
currency	text	N
valid_from	date	N	PK
valid_to	date	Y
recorded_at	timestamptz	N
charge_id	uuid	Y
claim_line_id	uuid	Y
remit_line_id	uuid	Y
adj_id	uuid	Y
event_ref	text	Y
supersedes_entry_id	uuid	Y
superseded_by_entry_id	uuid	Y
is_current	boolean	N
correction_reason	text	Y

ledger.responsibility_allocation

Column	Type	Null	Notes
allocation_id	uuid	N	PK
charge_id	uuid	N	FK
party_type	varchar(30)	N
payer_id	uuid	Y
amount_expected_cents	bigint	Y
amount_final_cents	bigint	Y
basis	varchar(30)	N
valid_from	date	N
valid_to	date	Y
recorded_at	timestamptz	N
valid_range	daterange	Y

config — rules, scopes, payer programs

Config-driven rules engine — rule sets, versions, approvals, evaluation logs, payer-program-config rows, retention policies.

Column inventory

config.config_scope

Column	Type	Null	Notes
scope_id	uuid	N	PK
state	varchar(2)	Y
payer_id	uuid	Y
external_program_id	uuid	Y	FK
canonical_program_id	uuid	Y
billing_entity_id	uuid	Y	FK
facility_id	uuid	Y	FK
created_at	timestamptz	N
is_wildcard_scope	boolean	N
scope_label	varchar(255)	Y

config.config_version_approval

Column	Type	Null	Notes
approval_id	uuid	N	PK
version_id	uuid	N	FK
action	varchar(20)	N
performed_by	text	N
performed_at	timestamptz	N
comments	text	Y

config.payer_program_config

Column	Type	Null	Notes
payer_program_config_id	uuid	N	PK
payer_id	uuid	N
canonical_program_id	uuid	N
accepts_claims	boolean	N
requires_authorization	boolean	N
claim_type	varchar(1)	N
submission_method	varchar(16)	N
billing_npi_type	varchar(16)	N
timely_filing_days	integer	Y
max_units_per_day	integer	Y
max_units_per_week	integer	Y
max_group_size	integer	Y
allows_no_show_billing	boolean	N
no_show_procedure_code	varchar(16)	Y
no_show_attendance_statuses	ARRAY	N
effective_from	date	N
effective_to	date	Y
notes	text	Y
created_at	timestamptz	N
updated_at	timestamptz	N
auth_workflow	varchar(16)	N
auth_portal_url	text	Y
auth_turnaround_days	integer	Y
institutional_billing_period	varchar(10)	N

config.retention_policy

Column	Type	Null	Notes
policy_id	uuid	N	PK
schema_name	varchar(50)	N
table_name	varchar(100)	N
retention_days	integer	N
archive_days	integer	Y
partition_column	varchar(50)	N
is_active	boolean	N
created_at	timestamptz	N
updated_at	timestamptz	N

config.rule_artifact

Column	Type	Null	Notes
artifact_hash	varchar(64)	N	PK
schema_version	integer	N
content_yaml	text	N
compiled_blob	text	Y
created_at	timestamptz	N

config.rule_evaluation_log

Column	Type	Null	Notes
eval_id	uuid	N	PK
ruleset_type	varchar(50)	N
version_id	uuid	N	FK
evaluated_at	timestamptz	N
charge_id	uuid	Y	FK
claim_id	uuid	Y	FK
remit_id	uuid	Y	FK
outcome	varchar(20)	N
messages	jsonb	Y

config.rule_resolution_trace

Column	Type	Null	Notes
trace_id	uuid	N	PK
resolution_context	jsonb	N
ruleset_type	varchar(50)	N
resolved_version_id	uuid	Y
candidate_count	integer	N
winning_score	integer	Y
runner_up_score	integer	Y
is_ambiguous	boolean	N
resolved_at	timestamptz	N

config.rule_set

Column	Type	Null	Notes
ruleset_id	uuid	N	PK
ruleset_type	varchar(50)	N
name	varchar(255)	N
description	text	Y
created_at	timestamptz	N

config.rule_set_version

Column	Type	Null	Notes
version_id	uuid	N	PK
ruleset_id	uuid	N	FK
scope_id	uuid	N	FK
effective_from	date	N
effective_to	date	Y
precedence_rank	integer	N
artifact_hash	varchar(64)	N	FK
created_at	timestamptz	N
effective_range	daterange	Y
lifecycle_status	varchar(20)	N
reviewed_by	text	Y
reviewed_at	timestamptz	Y
published_by	text	Y
published_at	timestamptz	Y
archived_at	timestamptz	Y
archive_reason	text	Y

audit + workflow

PHI access log, document references for HIPAA accounting, legal-hold registry; workflow issue tickets and comments.

Column inventory

audit.access_log

Column	Type	Null	Notes
access_log_id	uuid	N	PK
org_id	uuid	N	FK
actor_user_id	varchar(100)	N
actor_type	varchar(50)	N
accessed_entity_type	varchar(50)	N
accessed_entity_id	uuid	N
access_action	varchar(20)	N
purpose	varchar(100)	Y
source_ip	varchar(50)	Y
user_agent	varchar(500)	Y
request_id	varchar(100)	Y
accessed_at	timestamptz	N
prev_log_hash	varchar(64)	Y
log_hash	varchar(64)	Y

audit.document_reference

Column	Type	Null	Notes
document_id	uuid	N	PK
org_id	uuid	N	FK
blob_pointer	text	N
content_type	varchar(100)	N
file_name	varchar(255)	Y
checksum_sha256	varchar(64)	Y
retention_class	varchar(50)	N
retention_until	date	Y
size_bytes	bigint	Y
related_entity_type	varchar(50)	Y
related_entity_id	uuid	Y
created_at	timestamptz	N
is_under_legal_hold	boolean	N

audit.legal_hold

Column	Type	Null	Notes
hold_id	uuid	N	PK
org_id	uuid	N	FK
hold_name	varchar(255)	N
hold_reason	text	N
entity_type	varchar(50)	N
entity_id	uuid	Y
member_id	uuid	Y
held_from	timestamptz	N
held_until	timestamptz	Y
created_by	varchar(100)	N
created_at	timestamptz	N
released_by	varchar(100)	Y
released_at	timestamptz	Y
is_active	boolean	N

workflow.issue_comment

Column	Type	Null	Notes
comment_id	uuid	N	PK
ticket_id	uuid	N	FK
comment_text	text	N
created_by	uuid	Y
created_at	timestamptz	N

workflow.issue_ticket

Column	Type	Null	Notes
ticket_id	uuid	N	PK
org_id	uuid	N
entity_type	varchar(50)	N
entity_id	uuid	N
category	varchar(50)	Y
priority	varchar(20)	N
status	varchar(20)	N
assigned_to	uuid	Y
title	varchar(200)	N
description	text	N
resolution	text	Y
created_by	uuid	Y
created_at	timestamptz	N
updated_at	timestamptz	N
resolved_at	timestamptz	Y
resolved_by	uuid	Y