apps/backend/src/infrastructure/database/migrations/0018_sprint_i_cash_fiscal_foundation.sqlSQL
-- Sprint I — cash management and German fiscal/TSE foundation.
create table if not exists tills (
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,
code text not null,
display_name text not null,
status text not null default 'active',
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
version bigint not null default 1,
constraint tills_location_code_unique unique(location_id, code),
constraint tills_status_check check(status in ('active','inactive','archived'))
);
create table if not exists cash_shifts (
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,
till_id uuid not null references tills(id) on delete restrict,
business_date date not null,
status text not null default 'open',
opened_by uuid references user_accounts(id) on delete set null,
closed_by uuid references user_accounts(id) on delete set null,
opened_at timestamptz not null default now(),
closed_at timestamptz,
opening_float_minor bigint not null default 0,
expected_cash_minor bigint not null default 0,
counted_cash_minor bigint,
variance_minor bigint,
currency_code char(3) not null,
notes text,
version bigint not null default 1,
constraint cash_shift_status_check check(status in ('open','counting','closed','cancelled')),
constraint cash_shift_opening_nonnegative check(opening_float_minor >= 0)
);
create unique index if not exists idx_one_open_cash_shift_per_till
on cash_shifts(till_id)
where status in ('open','counting');
create table if not exists cash_movements (
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,
cash_shift_id uuid not null references cash_shifts(id) on delete restrict,
movement_type text not null,
amount_minor bigint not null,
currency_code char(3) not null,
reason text,
source_type text,
source_id uuid,
created_by uuid references user_accounts(id) on delete set null,
occurred_at timestamptz not null default now(),
constraint cash_movement_amount_positive check(amount_minor > 0),
constraint cash_movement_type_check check(
movement_type in ('opening_float','cash_sale','cash_refund','pay_in','pay_out','safe_drop','correction')
)
);
create index if not exists idx_cash_movements_shift_time
on cash_movements(cash_shift_id, occurred_at);
create table if not exists cash_counts (
id uuid primary key default gen_random_uuid(),
cash_shift_id uuid not null references cash_shifts(id) on delete cascade,
counted_by uuid references user_accounts(id) on delete set null,
denomination_counts jsonb not null default '{}'::jsonb,
total_minor bigint not null,
currency_code char(3) not null,
counted_at timestamptz not null default now(),
constraint cash_count_total_nonnegative check(total_minor >= 0)
);
create table if not exists fiscal_devices (
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,
system_serial_number text not null,
tse_serial_number text,
provider_key text,
status text not null default 'configured',
metadata jsonb not null default '{}'::jsonb,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
version bigint not null default 1,
constraint fiscal_devices_location_system_serial_unique unique(location_id, system_serial_number),
constraint fiscal_devices_status_check check(
status in ('configured','active','degraded','offline','retired')
)
);
create table if not exists fiscal_transactions (
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,
fiscal_device_id uuid not null references fiscal_devices(id) on delete restrict,
check_id uuid references checks(id) on delete set null,
payment_id uuid references payments(id) on delete set null,
transaction_type text not null,
status text not null default 'created',
client_transaction_ref text not null,
tse_transaction_number bigint,
tse_signature_counter bigint,
tse_start_at timestamptz,
tse_end_at timestamptz,
verification_value text,
process_data jsonb not null default '{}'::jsonb,
provider_reference text,
provider_status text,
uncertain_since timestamptz,
failure_code text,
failure_message text,
created_at timestamptz not null default now(),
completed_at timestamptz,
version bigint not null default 1,
constraint fiscal_transaction_client_ref_unique unique(fiscal_device_id, client_transaction_ref),
constraint fiscal_transaction_type_check check(
transaction_type in ('sale','refund','cash_movement','training','other')
),
constraint fiscal_transaction_status_check check(
status in ('created','started','completed','failed','cancelled','uncertain')
)
);
create index if not exists idx_fiscal_transactions_device_time
on fiscal_transactions(fiscal_device_id, created_at);
create table if not exists fiscal_receipt_snapshots (
id uuid primary key default gen_random_uuid(),
fiscal_transaction_id uuid not null references fiscal_transactions(id) on delete restrict,
check_id uuid references checks(id) on delete set null,
receipt_number text not null,
issuer_name text not null,
issuer_address text not null,
issued_at timestamptz not null,
transaction_start_at timestamptz,
transaction_end_at timestamptz,
tse_transaction_number bigint,
system_serial_number text not null,
tse_serial_number text,
signature_counter bigint,
verification_value text,
qr_payload text,
receipt_payload jsonb not null,
locale text not null,
created_at timestamptz not null default now()
);
create unique index if not exists idx_fiscal_receipt_number
on fiscal_receipt_snapshots(fiscal_transaction_id, receipt_number);
insert into permissions(permission_key, description) values
('cash.read', 'Read cash shifts and movements'),
('cash.open_shift', 'Open a cash shift'),
('cash.move', 'Create pay-in/pay-out/safe-drop cash movements'),
('cash.count', 'Count cash'),
('cash.close_shift', 'Close cash shift'),
('fiscal.read', 'Read fiscal device/transaction state'),
('fiscal.manage', 'Manage fiscal device configuration'),
('fiscal.reconcile', 'Reconcile uncertain fiscal transactions')
on conflict(permission_key) do nothing;