apps/backend/src/infrastructure/database/migrations/0043_sprint_u_completion.sqlSQL
-- Sprint U completion — immutable finalized invoices, generated artifacts and provider transfer.
create table if not exists accounting_generated_artifacts (
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,
source_type text not null,
source_id uuid not null,
artifact_type text not null,
file_asset_id uuid not null references file_assets(id) on delete cascade,
profile text not null default '',
checksum_sha256 text,
created_at timestamptz not null default now(),
constraint accounting_artifact_type_check check(
artifact_type in ('datev_csv','journal_csv','invoice_xml','invoice_pdf','zugferd_pdf')
),
constraint accounting_artifact_unique unique(source_type,source_id,artifact_type,profile)
);
create table if not exists einvoice_validator_connections (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete cascade,
provider text not null,
display_name text not null,
status text not null default 'active',
credential_reference text,
config_json jsonb not null default '{}'::jsonb,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
constraint einvoice_validator_provider_check check(
provider in ('mock','http-validator','command-validator')
)
);
create table if not exists invoice_render_jobs (
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,
render_type text not null,
status text not null default 'queued',
file_asset_id uuid references file_assets(id) on delete set null,
validator_result_id uuid references e_invoice_validation_results(id) on delete set null,
created_at timestamptz not null default now(),
completed_at timestamptz,
last_error text,
constraint invoice_render_type_check check(render_type in ('pdf','zugferd_pdf','xml')),
constraint invoice_render_status_check check(status in ('queued','processing','completed','failed','unsupported'))
);
create unique index if not exists idx_invoice_render_active_unique
on invoice_render_jobs(invoice_id,render_type)
where status in ('queued','processing','completed');
create or replace function prevent_finalized_invoice_mutation()
returns trigger
language plpgsql
as $$
begin
if old.status='finalized' then
if new.invoice_number is distinct from old.invoice_number
or new.organization_id is distinct from old.organization_id
or new.location_id is distinct from old.location_id
or new.invoice_customer_id is distinct from old.invoice_customer_id
or new.document_type is distinct from old.document_type
or new.currency_code is distinct from old.currency_code
or new.gross_total_minor is distinct from old.gross_total_minor
or new.net_total_minor is distinct from old.net_total_minor
or new.tax_total_minor is distinct from old.tax_total_minor
or new.issue_date is distinct from old.issue_date
or new.original_invoice_id is distinct from old.original_invoice_id
or new.source_snapshot_json is distinct from old.source_snapshot_json
then
raise exception 'finalized invoice commercial fields are immutable' using errcode='P0001';
end if;
end if;
return new;
end;
$$;
drop trigger if exists trg_prevent_finalized_invoice_mutation on invoices;
create trigger trg_prevent_finalized_invoice_mutation
before update on invoices
for each row execute function prevent_finalized_invoice_mutation();
create or replace function prevent_finalized_invoice_line_mutation()
returns trigger
language plpgsql
as $$
declare
v_invoice_id uuid;
v_status text;
begin
v_invoice_id=coalesce(new.invoice_id,old.invoice_id);
select status into v_status from invoices where id=v_invoice_id;
if v_status='finalized' then
raise exception 'finalized invoice lines are immutable' using errcode='P0001';
end if;
return coalesce(new,old);
end;
$$;
drop trigger if exists trg_prevent_finalized_invoice_line_mutation on invoice_lines;
create trigger trg_prevent_finalized_invoice_line_mutation
before insert or update or delete on invoice_lines
for each row execute function prevent_finalized_invoice_line_mutation();
create or replace function prevent_finalized_invoice_tax_mutation()
returns trigger
language plpgsql
as $$
declare
v_invoice_id uuid;
v_status text;
begin
v_invoice_id=coalesce(new.invoice_id,old.invoice_id);
select status into v_status from invoices where id=v_invoice_id;
if v_status='finalized' then
raise exception 'finalized invoice tax summary is immutable' using errcode='P0001';
end if;
return coalesce(new,old);
end;
$$;
drop trigger if exists trg_prevent_finalized_invoice_tax_mutation on invoice_tax_summaries;
create trigger trg_prevent_finalized_invoice_tax_mutation
before insert or update or delete on invoice_tax_summaries
for each row execute function prevent_finalized_invoice_tax_mutation();