Files

T

Playwright Tests / test (push) Canceled after 0s

Details

feat: Enhance invoice file backup procedure to include JSON snapshots

- Updated the stored procedure `fds__admin_backupAndClearInvoiceFile.sql` to back up not only the invoice file but also JSON snapshots of related data (invoices, items, details, service requests) to ensure consistency and traceability.
- Modified the table structure of `fds__invoice_file_backups` to accommodate new JSON columns for storing these snapshots.
- Added an ADR (0030) document to outline the decision and rationale behind these changes, emphasizing the importance of maintaining a consistent set of data alongside the backed-up file.

2026-09-17 22:39:01 +02:00

3.4 KiB

Raw Permalink Blame History

status, date, applyTo, supersededBy

status

date

applyTo

supersededBy

Accepted

2026-09-17

Fuchs_Database/dbo/Stored Procedures/fds__admin_backupAndClearInvoiceFile.sql

Fuchs_Database/dbo/Tables/fds__invoice_file_backups.sql

0030 — Invoice-file backup carries a JSON snapshot of the invoice data

Context

ADR 0027 introduced [dbo].[fds__invoice_file_backups]: one row per admin backup-and-clear action holding the invoice's [file] bytes before they are cleared for regeneration (ADR 0025). The backup held only the rendered PDF. The data it was rendered from — the fds__invoices row, its fds__invoice_items rows, its fds__invoice_details row and its fds__invoice_servicerequests link rows — lives on in the live tables and keeps changing (payment status, modification stamps, later edits). A backed-up file could therefore no longer be matched against the data state it was produced from, which is exactly the question the backup exists to answer when a regenerated document differs from the original.

Decision

Each backup row additionally stores four JSON snapshots, written in the same statement and transaction as the file copy:
- InvoiceJson — the fds__invoices row (FOR JSON PATH, WITHOUT_ARRAY_WRAPPER), without [file], which is already stored as bytes in the row and would otherwise be base64-duplicated;
- InvoiceItemsJson — all fds__invoice_items rows for the invoice as a JSON array, ordered by SortOrder, Position, Id;
- InvoiceDetailsJson — the fds__invoice_details row, or NULL if the invoice has none;
- InvoiceServiceRequestsJson — all fds__invoice_servicerequests rows for the invoice as a JSON array, ordered by SortOrder, Id. Not part of the rendered document, but part of the invoice's data set.
The snapshots are JSON columns on the same backup row rather than four mirror tables: one row is one consistent set (file + data), the atomicity guarantee of ADR 0027 covers it without extra keys, and adding a column to an invoice table does not require touching the backup schema. The item/detail/service-request snapshots use SELECT *, so new columns appear automatically; the invoice snapshot lists its columns explicitly only to exclude [file] — a new fds__invoices column must be added to that list.
Restore is manual (OPENJSON in SSMS); no app-side reader is added. The snapshots are forensic evidence, not a rollback mechanism.

Consequences

Backup rows grow by the size of the invoice's data (small next to the PDF). No retention policy is added — same standing as ADR 0027.
INCLUDE_NULL_VALUES is used so a NULL in the snapshot is distinguishable from a column that did not exist at backup time.
Adding a column to fds__invoices requires updating the explicit column list in the proc; fds__invoice_items / fds__invoice_details / fds__invoice_servicerequests need no change.

Alternatives considered

Mirror tables (fds__invoice_backups, fds__invoice_items_backups, …) keyed by backup uid: typed and queryable, but four schemas that drift with every invoice-table change and four inserts to keep atomic. Rejected for maintenance cost; the snapshots are read rarely and by hand.
Snapshot only the invoice header: the items are what the PDF body is rendered from; leaving them out would not answer "what did this file contain and why".

3.4 KiB Raw Permalink Blame History