apps/backend/src/infrastructure/database/migrations/0041_sprint_t_completion.sqlSQL
-- Sprint T completion — domain event routing, recovery resolution, outage receipts and export jobs.
create table if not exists fiscal_event_consumption (
outbox_event_id uuid not null references outbox_events(id) on delete cascade,
integration_connection_id uuid not null references integration_connections(id) on delete cascade,
fiscal_transaction_id uuid references fiscal_transactions(id) on delete set null,
status text not null default 'processed',
created_at timestamptz not null default now(),
primary key(outbox_event_id,integration_connection_id),
constraint fiscal_event_consumption_status_check check(status in ('processed','skipped','failed'))
);
create table if not exists fiscal_recovery_resolutions (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete cascade,
fiscal_recovery_case_id uuid not null references fiscal_recovery_cases(id) on delete cascade,
action text not null,
actor_user_id uuid references user_accounts(id) on delete set null,
note text,
result_json jsonb not null default '{}'::jsonb,
created_at timestamptz not null default now(),
constraint fiscal_recovery_resolution_action_check check(
action in ('refresh_provider','retry_finish','provider_support','ignore')
)
);
create index if not exists idx_fiscal_recovery_resolutions_case
on fiscal_recovery_resolutions(fiscal_recovery_case_id,created_at desc);
create table if not exists fiscal_export_jobs (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete cascade,
location_id uuid not null references locations(id) on delete cascade,
integration_connection_id uuid references integration_connections(id) on delete set null,
export_type text not null,
period_start date not null,
period_end date not null,
status text not null default 'queued',
provider_export_reference text,
file_asset_id uuid references file_assets(id) on delete set null,
requested_by_user_id uuid references user_accounts(id) on delete set null,
created_at timestamptz not null default now(),
started_at timestamptz,
completed_at timestamptz,
last_error text,
constraint fiscal_export_type_check check(export_type in ('tse_export','dsfinvk')),
constraint fiscal_export_status_check check(status in ('queued','processing','completed','failed','unsupported'))
);
create index if not exists idx_fiscal_export_jobs_work
on fiscal_export_jobs(status,created_at);
create table if not exists fiscal_outage_receipt_events (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete cascade,
location_id uuid not null references locations(id) on delete cascade,
fiscal_transaction_id uuid not null references fiscal_transactions(id) on delete cascade,
outage_mode text not null,
receipt_note_key text not null,
created_at timestamptz not null default now()
);
create unique index if not exists idx_fiscal_outage_receipt_once
on fiscal_outage_receipt_events(fiscal_transaction_id);
insert into permissions(permission_key,description) values
('fiscal.exports.create','Create TSE/DSFinV-K export jobs'),
('fiscal.exports.read','Read TSE/DSFinV-K export jobs'),
('fiscal.recovery.manage','Manage fiscal recovery cases')
on conflict(permission_key) do nothing;