apps/backend/src/infrastructure/database/migrations/0042_sprint_u_accounting_einvoice.sqlSQL
-- Sprint U increment 1 — accounting / e-invoice foundations.
create table if not exists accounting_configurations (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete cascade,
location_id uuid references locations(id) on delete cascade,
currency_code text not null default 'EUR',
chart_of_accounts text not null default 'SKR03',
default_revenue_account text,
default_cash_account text,
default_card_clearing_account text,
default_tax_account text,
invoice_number_prefix text not null default 'RE',
credit_note_number_prefix text not null default 'GS',
next_invoice_number bigint not null default 1,
next_credit_note_number bigint not null default 1,
e_invoice_profile text not null default 'zugferd',
e_invoice_enabled boolean not null default false,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
version bigint not null default 1,
constraint accounting_config_location_unique unique(organization_id,location_id),
constraint accounting_chart_check check(chart_of_accounts in ('SKR03','SKR04','custom')),
constraint accounting_einvoice_profile_check check(e_invoice_profile in ('zugferd','xrechnung','both'))
);
create table if not exists accounting_rules (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete cascade,
location_id uuid references locations(id) on delete cascade,
rule_type text not null,
match_json jsonb not null default '{}'::jsonb,
debit_account text,
credit_account text,
tax_account text,
tax_key text,
priority integer not null default 100,
is_active boolean not null default true,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
constraint accounting_rule_type_check check(
rule_type in ('sale','payment','refund','cash_movement','purchase','fee','rounding')
)
);
create index if not exists idx_accounting_rules_match
on accounting_rules(organization_id,location_id,rule_type,is_active,priority);
create table if not exists invoice_customers (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete cascade,
guest_id uuid references guests(id) on delete set null,
customer_number text,
name text not null,
email text,
vat_id text,
tax_number text,
buyer_reference text,
electronic_address text,
address_json jsonb not null default '{}'::jsonb,
locale text not null default 'de-DE',
created_at timestamptz not null default now(),
updated_at timestamptz not null default now()
);
create unique index if not exists idx_invoice_customer_number
on invoice_customers(organization_id,customer_number)
where customer_number is not null;
create table if not exists invoices (
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,
invoice_customer_id uuid not null references invoice_customers(id) on delete restrict,
invoice_number text,
document_type text not null default 'invoice',
status text not null default 'draft',
issue_date date,
due_date date,
currency_code text not null,
gross_total_minor bigint not null,
net_total_minor bigint not null,
tax_total_minor bigint not null,
buyer_reference text,
payment_reference text,
payment_terms_text text,
notes text,
source_snapshot_json jsonb not null default '{}'::jsonb,
e_invoice_profile text,
e_invoice_status text,
e_invoice_validation_json jsonb not null default '{}'::jsonb,
pdf_file_asset_id uuid references file_assets(id) on delete set null,
xml_file_asset_id uuid references file_assets(id) on delete set null,
original_invoice_id uuid references invoices(id) on delete set null,
created_by_user_id uuid references user_accounts(id) on delete set null,
created_at timestamptz not null default now(),
finalized_at timestamptz,
cancelled_at timestamptz,
version bigint not null default 1,
constraint invoice_document_type_check check(document_type in ('invoice','credit_note')),
constraint invoice_status_check check(status in ('draft','validated','finalized','cancelled','failed')),
constraint invoice_e_status_check check(
e_invoice_status is null or e_invoice_status in ('not_requested','generated','valid','invalid','failed')
)
);
create unique index if not exists idx_invoice_number_unique
on invoices(organization_id,invoice_number)
where invoice_number is not null;
create table if not exists invoice_lines (
id uuid primary key default gen_random_uuid(),
invoice_id uuid not null references invoices(id) on delete cascade,
line_number integer not null,
description text not null,
quantity numeric(20,6) not null,
unit_code text,
unit_price_net_minor bigint not null,
net_amount_minor bigint not null,
tax_rate_basis_points integer not null,
tax_amount_minor bigint not null,
gross_amount_minor bigint not null,
accounting_revenue_account text,
source_entity_type text,
source_entity_id uuid,
created_at timestamptz not null default now(),
constraint invoice_line_number_unique unique(invoice_id,line_number)
);
create table if not exists invoice_tax_summaries (
invoice_id uuid not null references invoices(id) on delete cascade,
tax_rate_basis_points integer not null,
taxable_net_minor bigint not null,
tax_minor bigint not null,
gross_minor bigint not null,
primary key(invoice_id,tax_rate_basis_points)
);
create table if not exists accounting_export_jobs (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete cascade,
location_id uuid 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',
file_asset_id uuid references file_assets(id) on delete set null,
provider_export_reference text,
requested_by_user_id uuid references user_accounts(id) on delete set null,
options_json jsonb not null default '{}'::jsonb,
created_at timestamptz not null default now(),
started_at timestamptz,
completed_at timestamptz,
last_error text,
constraint accounting_export_type_check check(
export_type in ('datev_csv','journal_csv','invoice_xml','invoice_pdf','provider_transfer')
),
constraint accounting_export_status_check check(status in ('queued','processing','completed','failed','unsupported'))
);
create table if not exists accounting_export_rows (
id uuid primary key default gen_random_uuid(),
accounting_export_job_id uuid not null references accounting_export_jobs(id) on delete cascade,
row_number integer not null,
source_type text not null,
source_id uuid,
debit_account text,
credit_account text,
amount_minor bigint not null,
currency_code text not null,
tax_key text,
booking_text text,
booking_date date not null,
reference text,
payload_json jsonb not null default '{}'::jsonb,
constraint accounting_export_row_number_unique unique(accounting_export_job_id,row_number)
);
create table if not exists e_invoice_validation_results (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete cascade,
invoice_id uuid not null references invoices(id) on delete cascade,
profile text not null,
validator text not null,
valid boolean not null,
messages_json jsonb not null default '[]'::jsonb,
validated_at timestamptz not null default now()
);
create table if not exists accounting_transfer_events (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete cascade,
integration_connection_id uuid references integration_connections(id) on delete set null,
source_type text not null,
source_id uuid not null,
provider text not null,
external_id text,
status text not null default 'queued',
request_hash text,
response_safe_json jsonb not null default '{}'::jsonb,
created_at timestamptz not null default now(),
completed_at timestamptz,
last_error text,
constraint accounting_transfer_status_check check(status in ('queued','processing','completed','failed','uncertain')),
constraint accounting_transfer_unique unique(integration_connection_id,source_type,source_id)
);
insert into permissions(permission_key,description) values
('accounting.read','Read accounting data and exports'),
('accounting.manage','Manage accounting configuration and rules'),
('accounting.export','Create accounting exports'),
('invoices.read','Read invoices'),
('invoices.create','Create invoices'),
('invoices.finalize','Finalize invoices and credit notes'),
('invoices.cancel','Cancel invoices'),
('invoices.einvoice','Generate and validate e-invoices')
on conflict(permission_key) do nothing;