apps/backend/src/infrastructure/database/migrations/0019_sprint_i_completion.sqlSQL
-- Sprint I completion — event linkage, reconciliation queue and export jobs.
create table if not exists fiscal_reconciliation_jobs (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete cascade,
fiscal_transaction_id uuid not null references fiscal_transactions(id) on delete cascade,
status text not null default 'pending',
attempts integer not null default 0,
next_attempt_at timestamptz not null default now(),
last_error text,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
constraint fiscal_reconciliation_job_status_check
check(status in ('pending','running','completed','failed'))
);
create unique index if not exists idx_fiscal_reconciliation_one_active
on fiscal_reconciliation_jobs(fiscal_transaction_id)
where status in ('pending','running');
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,
export_type text not null,
period_start date not null,
period_end date not null,
status text not null default 'pending',
object_storage_key text,
created_at timestamptz not null default now(),
completed_at timestamptz,
created_by uuid references user_accounts(id) on delete set null,
constraint fiscal_export_type_check check(export_type in ('dsfinvk','tse','cash_journal')),
constraint fiscal_export_status_check check(status in ('pending','running','completed','failed')),
constraint fiscal_export_period_check check(period_end >= period_start)
);
create index if not exists idx_fiscal_export_jobs_location_period
on fiscal_export_jobs(location_id, period_start, period_end);