apps/backend/src/infrastructure/database/migrations/0016_sprint_h_billing_payments.sqlSQL
-- Sprint H — checks, splits, tenders, payment intents and reconciliation.
create table if not exists checks (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete restrict,
location_id uuid not null references locations(id) on delete restrict,
service_session_id uuid not null references service_sessions(id) on delete restrict,
status text not null default 'open',
currency_code char(3) not null,
subtotal_minor bigint not null default 0,
discount_minor bigint not null default 0,
service_charge_minor bigint not null default 0,
tax_minor bigint not null default 0,
total_minor bigint not null default 0,
paid_minor bigint not null default 0,
balance_due_minor bigint not null default 0,
opened_at timestamptz not null default now(),
closed_at timestamptz,
version bigint not null default 1,
constraint checks_status_check check(status in ('open','partially_paid','paid','closed','voided')),
constraint checks_amounts_nonnegative check (
subtotal_minor >= 0 and discount_minor >= 0 and service_charge_minor >= 0
and tax_minor >= 0 and total_minor >= 0 and paid_minor >= 0
and balance_due_minor >= 0
)
);
create index if not exists idx_checks_session_status
on checks(service_session_id, status);
create table if not exists check_items (
id uuid primary key default gen_random_uuid(),
check_id uuid not null references checks(id) on delete cascade,
order_item_id uuid not null references order_items(id) on delete restrict,
quantity numeric(24,8) not null,
line_subtotal_minor bigint not null,
line_discount_minor bigint not null default 0,
line_tax_minor bigint not null default 0,
line_total_minor bigint not null,
created_at timestamptz not null default now(),
constraint check_items_qty_positive check(quantity > 0)
);
create unique index if not exists idx_check_item_order_item_unique
on check_items(check_id, order_item_id);
create table if not exists check_splits (
id uuid primary key default gen_random_uuid(),
source_check_id uuid not null references checks(id) on delete restrict,
target_check_id uuid not null references checks(id) on delete restrict,
split_type text not null,
metadata jsonb not null default '{}'::jsonb,
created_at timestamptz not null default now(),
constraint check_split_type_check check(split_type in ('by_item','by_seat','equal','custom'))
);
create table if not exists tenders (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete restrict,
code text not null,
display_name text not null,
tender_type text not null,
status text not null default 'active',
provider_key text,
created_at timestamptz not null default now(),
constraint tenders_org_code_unique unique(organization_id, code),
constraint tenders_type_check check(tender_type in ('cash','card','gift_card','voucher','external','other')),
constraint tenders_status_check check(status in ('active','inactive'))
);
create table if not exists payment_intents (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete restrict,
location_id uuid not null references locations(id) on delete restrict,
check_id uuid not null references checks(id) on delete restrict,
tender_id uuid not null references tenders(id) on delete restrict,
status text not null default 'created',
amount_minor bigint not null,
currency_code char(3) not null,
provider_key text,
provider_reference text,
provider_status text,
uncertain_since timestamptz,
failure_code text,
failure_message text,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
completed_at timestamptz,
version bigint not null default 1,
constraint payment_intents_amount_positive check(amount_minor > 0),
constraint payment_intents_status_check check(
status in ('created','pending','requires_action','authorized','captured','failed','cancelled','uncertain')
)
);
create index if not exists idx_payment_intents_check_status
on payment_intents(check_id, status);
create index if not exists idx_payment_intents_provider_ref
on payment_intents(provider_key, provider_reference)
where provider_reference is not null;
create table if not exists payments (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete restrict,
location_id uuid not null references locations(id) on delete restrict,
check_id uuid not null references checks(id) on delete restrict,
payment_intent_id uuid references payment_intents(id) on delete set null,
tender_id uuid not null references tenders(id) on delete restrict,
amount_minor bigint not null,
tip_minor bigint not null default 0,
currency_code char(3) not null,
status text not null default 'captured',
provider_key text,
provider_reference text,
captured_at timestamptz not null default now(),
reversed_at timestamptz,
created_by uuid references user_accounts(id) on delete set null,
constraint payments_amount_positive check(amount_minor > 0),
constraint payments_tip_nonnegative check(tip_minor >= 0),
constraint payments_status_check check(status in ('authorized','captured','reversed','refunded','failed'))
);
create index if not exists idx_payments_check
on payments(check_id, captured_at);
create table if not exists payment_reconciliation_events (
id uuid primary key default gen_random_uuid(),
payment_intent_id uuid not null references payment_intents(id) on delete cascade,
provider_key text,
provider_reference text,
observed_status text not null,
observed_amount_minor bigint,
payload jsonb not null default '{}'::jsonb,
observed_at timestamptz not null default now()
);
create index if not exists idx_payment_reconciliation_intent_time
on payment_reconciliation_events(payment_intent_id, observed_at desc);
insert into permissions(permission_key, description) values
('billing.read', 'Read checks and balances'),
('billing.manage', 'Manage checks and splits'),
('payments.read', 'Read payments and intents'),
('payments.create', 'Create payment intents/payments'),
('payments.reconcile', 'Reconcile uncertain provider states'),
('payments.refund', 'Refund payments')
on conflict(permission_key) do nothing;