apps/backend/src/infrastructure/database/migrations/0040_sprint_t_fiscal_provider_integration.sqlSQL
-- Sprint T increment 1 — production fiscal provider foundations.
alter table fiscal_transactions
add column if not exists integration_connection_id uuid references integration_connections(id) on delete set null,
add column if not exists fiscal_device_mapping_id uuid,
add column if not exists provider_transaction_id text,
add column if not exists provider_revision integer,
add column if not exists payload_hash text,
add column if not exists signature_algorithm text,
add column if not exists signature_public_key text,
add column if not exists device_serial text,
add column if not exists client_serial text,
add column if not exists qr_payload text,
add column if not exists safe_provider_receipt_json jsonb not null default '{}'::jsonb,
add column if not exists recovery_required_at timestamptz;
alter table fiscal_transactions drop constraint if exists fiscal_transaction_status_check;
alter table fiscal_transactions add constraint fiscal_transaction_status_check check(
status in ('created','starting','started','finishing','completed','failed','cancelled','uncertain','recovery_required')
);
alter table fiscal_transactions drop constraint if exists fiscal_transaction_type_check;
alter table fiscal_transactions add constraint fiscal_transaction_type_check check(
transaction_type in ('sale','refund','cancellation','cash_movement','training','other')
);
create table if not exists fiscal_device_mappings (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete cascade,
integration_connection_id uuid not null references integration_connections(id) on delete cascade,
location_id uuid not null references locations(id) on delete cascade,
fiscal_device_id uuid references fiscal_devices(id) on delete set null,
cash_register_id uuid,
provider_device_id text not null,
provider_client_id text not null,
provider_device_serial text,
provider_client_serial text,
display_name text not null,
status text not null default 'unknown',
capabilities_json jsonb not null default '{}'::jsonb,
effective_from timestamptz not null default now(),
effective_until timestamptz,
last_seen_at timestamptz,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
version bigint not null default 1,
constraint fiscal_device_mapping_status_check check(
status in ('available','degraded','offline','locked','unknown','disabled')
),
constraint fiscal_device_mapping_provider_unique
unique(integration_connection_id,provider_device_id,provider_client_id)
);
create unique index if not exists idx_fiscal_device_mapping_register_active
on fiscal_device_mappings(location_id,cash_register_id)
where cash_register_id is not null and effective_until is null and status<>'disabled';
alter table fiscal_transactions
drop constraint if exists fiscal_transactions_device_mapping_fk;
alter table fiscal_transactions
add constraint fiscal_transactions_device_mapping_fk
foreign key(fiscal_device_mapping_id) references fiscal_device_mappings(id) on delete set null;
create unique index if not exists idx_fiscal_provider_transaction_unique
on fiscal_transactions(integration_connection_id,provider_transaction_id)
where integration_connection_id is not null and provider_transaction_id is not null;
create table if not exists fiscal_recovery_cases (
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,
integration_connection_id uuid references integration_connections(id) on delete set null,
issue_type text not null,
severity text not null default 'critical',
status text not null default 'open',
next_attempt_at timestamptz not null default now(),
attempt_count integer not null default 0,
provider_reference text,
summary text not null,
details_safe_json jsonb not null default '{}'::jsonb,
last_error text,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
resolved_at timestamptz,
resolved_by_user_id uuid references user_accounts(id) on delete set null,
constraint fiscal_recovery_case_issue_check check(
issue_type in (
'fiscal_auth_failed','fiscal_device_unavailable','fiscal_unknown_status',
'fiscal_signature_missing','fiscal_payload_mismatch','fiscal_recovery_stuck',
'fiscal_mapping_missing','fiscal_provider_timeout','fiscal_rate_limited'
)
),
constraint fiscal_recovery_case_status_check check(
status in ('open','retrying','provider_support','resolved','ignored')
),
constraint fiscal_recovery_case_severity_check check(severity in ('high','critical'))
);
create unique index if not exists idx_fiscal_recovery_one_open
on fiscal_recovery_cases(fiscal_transaction_id)
where status in ('open','retrying','provider_support');
create index if not exists idx_fiscal_recovery_queue
on fiscal_recovery_cases(status,next_attempt_at,created_at);
create table if not exists fiscal_provider_health_history (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete cascade,
integration_connection_id uuid not null references integration_connections(id) on delete cascade,
ok boolean not null,
status_code integer,
code text,
latency_ms integer,
checked_at timestamptz not null default now()
);
create table if not exists fiscal_provider_attempts (
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,
integration_connection_id uuid references integration_connections(id) on delete set null,
operation text not null,
attempt_number integer not null,
idempotency_key text not null,
status text not null default 'created',
provider_status text,
provider_reference text,
safe_result_json jsonb not null default '{}'::jsonb,
error_code text,
started_at timestamptz not null default now(),
completed_at timestamptz,
constraint fiscal_provider_attempt_operation_check check(
operation in ('start','update','finish','cancel','recover','status')
),
constraint fiscal_provider_attempt_status_check check(
status in ('created','submitted','completed','failed','status_unclear')
),
constraint fiscal_provider_attempt_number_unique unique(fiscal_transaction_id,attempt_number),
constraint fiscal_provider_attempt_idempotency_unique unique(integration_connection_id,idempotency_key)
);
create table if not exists receipt_documents (
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,
check_id uuid references checks(id) on delete set null,
fiscal_transaction_id uuid references fiscal_transactions(id) on delete set null,
document_type text not null,
file_asset_id uuid references file_assets(id) on delete set null,
status text not null default 'ready',
created_at timestamptz not null default now(),
constraint receipt_document_type_check check(
document_type in ('sale_receipt','refund_receipt','cancellation_receipt')
),
constraint receipt_document_status_check check(status in ('ready','rendering','failed','archived'))
);
alter table integration_connections
add column if not exists last_successful_fiscal_at timestamptz,
add column if not exists fiscal_outage_mode text,
add column if not exists fiscal_production_enabled_at timestamptz;
alter table integration_connections drop constraint if exists integration_connection_fiscal_outage_mode_check;
alter table integration_connections add constraint integration_connection_fiscal_outage_mode_check
check(fiscal_outage_mode is null or fiscal_outage_mode in ('block','receipt_with_failure_note','provider_offline_buffer'));
insert into permissions(permission_key,description) values
('integrations.fiscal.read','Read fiscal provider configuration and health'),
('integrations.fiscal.manage','Manage fiscal provider configuration and devices'),
('fiscal.retry','Retry safe fiscal recovery operations'),
('fiscal.override','Perform privileged verified fiscal recovery overrides'),
('fiscal.receipts.read','Read fiscal receipt metadata')
on conflict(permission_key) do nothing;