jakob
d1af440966
The transactions table keeps 9 of the 16 source fields; the dropped list also includes Card type (equals Card number in all samples, per readme). Header said 10 and omitted Card type.
185 lines
9.9 KiB
SQL
185 lines
9.9 KiB
SQL
-- Domain schema for rusty_petroleum (v1: fuel domain)
|
|
-- Optimized for MariaDB / InnoDB
|
|
--
|
|
-- Scope: the six tables below plus invoice_items cover the v1 "in
|
|
-- development" features (epsilon ingest, invoices). Auth (users,
|
|
-- sessions, roles) and the tsdrms / subfranchise / voucher / car
|
|
-- registry domains arrive as later embedded migrations, per the
|
|
-- decisions recorded 2026-09. In the application this file becomes
|
|
-- the first embedded migration, applied by `db setup` behind the
|
|
-- schema_migrations table (see readme.md); the DROP block below is
|
|
-- the reset path (`db reset` drops the whole database).
|
|
--
|
|
-- Design decisions (agreed with maintainer, 2026-09):
|
|
-- - cards.pin: NULL-able; created without pin on import. Stored
|
|
-- cleartext on purpose: customers retrieve it in the portal, and
|
|
-- the cards are only used on-site at the station, so the risk is
|
|
-- accepted.
|
|
-- - transactions is a SLIM PROJECTION (9 of 16 source fields):
|
|
-- QualityName, Card type, Station, Terminal, Pump, Card report
|
|
-- group number and Control number are dropped. The files table (full
|
|
-- byte content) is the canonical archive; the ledger keeps only what
|
|
-- application features query.
|
|
-- - Customer business key is the register customer number, stored as
|
|
-- a STRING in both customers.external_id and
|
|
-- transactions.customer_number (arrives as quoted text in the CSV;
|
|
-- always 4 digits today, but bookkeeping/Fortnox features cannot
|
|
-- guarantee numeric-only). NULL = retail/unknown (replaces the 0
|
|
-- sentinel).
|
|
-- - Money is DECIMAL(10,2) SEK everywhere (VAT-inclusive as
|
|
-- delivered); the BIGINT-cents representation is dropped.
|
|
-- - Invoice status values are exactly cli.md's: draft / sent. Card
|
|
-- statuses: active / suspended / cancelled. (cli.md is the spec.)
|
|
-- - Invoices carry full source traceability: batch_id or date range,
|
|
-- per-transaction line items, and credit invoices reference the
|
|
-- invoice they correct.
|
|
|
|
SET FOREIGN_KEY_CHECKS = 0;
|
|
DROP TABLE IF EXISTS invoice_items;
|
|
DROP TABLE IF EXISTS invoices;
|
|
DROP TABLE IF EXISTS transactions;
|
|
DROP TABLE IF EXISTS cards;
|
|
DROP TABLE IF EXISTS batches;
|
|
DROP TABLE IF EXISTS customers;
|
|
DROP TABLE IF EXISTS files;
|
|
SET FOREIGN_KEY_CHECKS = 1;
|
|
|
|
-- 1. Imported source files
|
|
-- filename is the natural key (readme.md: filenames carry metadata and
|
|
-- make re-ingestion idempotent); checksum catches byte-identical
|
|
-- re-imports. content holds the full byte-faithful source so
|
|
-- `file export --format raw` works and the DB is the archive of
|
|
-- record. One file may span many batches (cumulative exports);
|
|
-- batch_number is set only for single-batch files.
|
|
CREATE TABLE files (
|
|
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
filename VARCHAR(255) NOT NULL,
|
|
format VARCHAR(20) NOT NULL, -- 'epsilon-tsv' (v1); tsdrms / subfranchise later
|
|
checksum VARCHAR(64) NOT NULL, -- SHA-256, dedup of byte-identical re-imports
|
|
content LONGTEXT NOT NULL, -- raw source, byte-faithful
|
|
row_count INT UNSIGNED NOT NULL, -- data rows parsed from the file
|
|
batch_number INT UNSIGNED, -- single-batch files only; NULL for cumulative
|
|
imported_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE INDEX idx_filename (filename),
|
|
UNIQUE INDEX idx_checksum (checksum)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- 2. Customers (contract fuel customers)
|
|
-- Identity is the register customer number (business key). VARCHAR:
|
|
-- the number arrives as quoted text, is 4 digits in all samples, and
|
|
-- must not be assumed numeric-only when bookkeeping features arrive.
|
|
CREATE TABLE customers (
|
|
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
external_id VARCHAR(100) NOT NULL, -- register customer number, as delivered
|
|
name VARCHAR(255) NOT NULL,
|
|
address TEXT,
|
|
email VARCHAR(255),
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE INDEX idx_external_id (external_id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- 3. Batches (derived entity: one per register batch; created and
|
|
-- maintained by file import, recomputed by `batch update`).
|
|
-- The batch number from the filename/rows is the business key.
|
|
-- Cached aggregates mirror transactions; no FK to files (the relation
|
|
-- is derivable via transactions.imported_file_id + batch_number).
|
|
CREATE TABLE batches (
|
|
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
batch_number INT UNSIGNED NOT NULL, -- business key, from filename/rows
|
|
start_date DATE NOT NULL, -- min transaction date in the batch
|
|
end_date DATE NOT NULL, -- max transaction date in the batch
|
|
total_amount DECIMAL(10,2) NOT NULL,-- cached sum of amounts (SEK, VAT-incl.)
|
|
transaction_count INT UNSIGNED NOT NULL, -- cached row count
|
|
status VARCHAR(20) NOT NULL, -- open / closed / invoiced
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE INDEX idx_batch_number (batch_number)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- 4. Cards (contract fuel cards only: epsilon rows with a customer
|
|
-- number; retail rows carry no card). One card per card number; the
|
|
-- same number may recur across batches.
|
|
CREATE TABLE cards (
|
|
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
customer_id INT UNSIGNED NOT NULL,
|
|
card_number VARCHAR(50) NOT NULL, -- as delivered (unmasked for contract cards)
|
|
pin VARCHAR(20), -- NULL until known; cleartext by decision (on-site cards)
|
|
status VARCHAR(20) NOT NULL, -- active / suspended / cancelled
|
|
description TEXT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE INDEX idx_card_number (card_number),
|
|
INDEX idx_customer_id (customer_id),
|
|
FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE RESTRICT
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- 5. Transactions (the immutable ledger)
|
|
-- Created only by file import; no create/update/delete commands.
|
|
-- Slim projection of the 16 source fields (decision above); raw
|
|
-- values as delivered, so NO foreign keys to cards/customers --
|
|
-- customer_number/card_number are raw strings, joinable by equality
|
|
-- with customers.external_id / cards.card_number. The only FK is
|
|
-- provenance: which file the row came from.
|
|
CREATE TABLE transactions (
|
|
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
transaction_date DATETIME NOT NULL, -- parsed from M/d/yyyy h:mm:ss AM/PM
|
|
amount DECIMAL(10,2) NOT NULL, -- SEK, VAT-inclusive; = volume x price
|
|
volume DECIMAL(10,2) NOT NULL, -- liters
|
|
price DECIMAL(10,2) NOT NULL, -- SEK/liter
|
|
quality INT NOT NULL, -- 1001 = unleaded ("95 Oktan"), 4 = diesel, 0 = zero-value
|
|
card_number VARCHAR(50) NOT NULL, -- as delivered (masked for consumer cards)
|
|
customer_number VARCHAR(100), -- as delivered; NULL = retail/unknown
|
|
receipt VARCHAR(20) NOT NULL, -- zero-padded at 6 digits in all samples; widened deliberately
|
|
batch_number INT UNSIGNED NOT NULL, -- matches the filename; always present
|
|
imported_file_id INT UNSIGNED NOT NULL,
|
|
UNIQUE INDEX idx_dedup (transaction_date, receipt), -- re-ingestion dedup; verified unique in the 138k-row sample
|
|
INDEX idx_batch_number (batch_number),
|
|
INDEX idx_customer_number (customer_number),
|
|
INDEX idx_card_number (card_number),
|
|
FOREIGN KEY (imported_file_id) REFERENCES files(id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
-- (The old standalone idx_transaction_date was dropped: the unique
|
|
-- (transaction_date, receipt) prefix already serves date-range scans.)
|
|
|
|
-- 6. Invoices (outgoing fuel invoices only; one buyer per invoice)
|
|
-- Created from a batch OR a date range, per customer ("all" fans out).
|
|
-- full traceability: batch_id and/or the effective period, plus one
|
|
-- line item per source transaction; credit invoices reference the
|
|
-- sent invoice they correct. Amounts DECIMAL SEK, VAT-inclusive;
|
|
-- the 25% base/VAT split is computed at creation.
|
|
CREATE TABLE invoices (
|
|
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
invoice_number VARCHAR(100) NOT NULL, -- assigned by the app at creation
|
|
customer_id INT UNSIGNED NOT NULL,
|
|
batch_id INT UNSIGNED, -- set for --batch creation; NULL for date-range
|
|
from_date DATE NOT NULL, -- effective period start (batch dates or --from)
|
|
to_date DATE NOT NULL, -- effective period end (batch dates or --to)
|
|
issue_date DATE NOT NULL,
|
|
due_date DATE NOT NULL,
|
|
total_amount DECIMAL(10,2) NOT NULL, -- VAT-inclusive; negative for credit invoices
|
|
status VARCHAR(20) NOT NULL, -- draft / sent (v1)
|
|
original_invoice_id INT UNSIGNED, -- set on credit invoices; NULL otherwise
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE INDEX idx_invoice_number (invoice_number),
|
|
INDEX idx_customer_id (customer_id),
|
|
INDEX idx_batch_id (batch_id),
|
|
INDEX idx_issue_date (issue_date),
|
|
INDEX idx_original_invoice_id (original_invoice_id),
|
|
FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE RESTRICT,
|
|
FOREIGN KEY (batch_id) REFERENCES batches(id) ON DELETE RESTRICT,
|
|
FOREIGN KEY (original_invoice_id) REFERENCES invoices(id) ON DELETE RESTRICT
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- 7. Invoice line items (one per source transaction)
|
|
-- description is derived at creation (quality code -> name lookup,
|
|
-- volume/price text); amount is the transaction amount, VAT-incl.
|
|
CREATE TABLE invoice_items (
|
|
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
invoice_id INT UNSIGNED NOT NULL,
|
|
source_transaction_id BIGINT UNSIGNED NOT NULL,
|
|
description VARCHAR(255) NOT NULL,
|
|
amount DECIMAL(10,2) NOT NULL,
|
|
INDEX idx_invoice_id (invoice_id),
|
|
INDEX idx_source_transaction_id (source_transaction_id),
|
|
FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (source_transaction_id) REFERENCES transactions(id) ON DELETE RESTRICT
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|