54 lines
2.7 KiB
SQL
54 lines
2.7 KiB
SQL
-- Rechnungen: Entwurf ist frei änderbar, ab "Ausstellen" unveränderlich (nur per Storno korrigierbar,
|
||
-- niemals nachträglich bearbeitet oder gelöscht – siehe docs/rechnungen.md).
|
||
CREATE TABLE company_settings (
|
||
id TINYINT PRIMARY KEY DEFAULT 1,
|
||
name VARCHAR(200) NULL, street VARCHAR(200) NULL, zip VARCHAR(20) NULL, city VARCHAR(100) NULL, country CHAR(2) NULL DEFAULT 'DE',
|
||
tax_number VARCHAR(50) NULL, -- Steuernummer
|
||
vat_id VARCHAR(30) NULL, -- USt-IdNr.
|
||
bank_name VARCHAR(150) NULL, iban VARCHAR(34) NULL, bic VARCHAR(11) NULL,
|
||
invoice_prefix VARCHAR(10) NOT NULL DEFAULT 'RE',
|
||
default_due_days INT NOT NULL DEFAULT 14,
|
||
payment_methods JSON NOT NULL DEFAULT (JSON_ARRAY('Überweisung')),
|
||
footer_text VARCHAR(500) NULL,
|
||
updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
INSERT INTO company_settings (id) VALUES (1);
|
||
INSERT INTO number_sequences (name, next_value) VALUES ('invoice', 1000) ON DUPLICATE KEY UPDATE name = name;
|
||
|
||
CREATE TABLE invoices (
|
||
id CHAR(36) PRIMARY KEY,
|
||
number VARCHAR(30) NULL UNIQUE, -- erst ab "Ausstellen" vergeben; Entwurf hat keine Nummer
|
||
org_id CHAR(36) NOT NULL REFERENCES organizations(id),
|
||
status ENUM('draft','open','paid','cancelled') NOT NULL DEFAULT 'draft',
|
||
issue_date DATE NULL,
|
||
due_date DATE NULL,
|
||
currency CHAR(3) NOT NULL DEFAULT 'EUR',
|
||
total_net_cents INT NOT NULL DEFAULT 0,
|
||
total_tax_cents INT NOT NULL DEFAULT 0,
|
||
total_gross_cents INT NOT NULL DEFAULT 0,
|
||
payment_method VARCHAR(50) NULL,
|
||
note VARCHAR(500) NULL,
|
||
paid_at DATETIME(3) NULL,
|
||
cancels_invoice_id CHAR(36) NULL REFERENCES invoices(id), -- gesetzt bei einer Stornorechnung (negative Beträge)
|
||
cancelled_by_invoice_id CHAR(36) NULL REFERENCES invoices(id), -- Rückverweis von der stornierten Rechnung auf ihr Storno
|
||
created_by CHAR(36) NOT NULL REFERENCES users(id),
|
||
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
||
issued_at DATETIME(3) NULL,
|
||
cancelled_at DATETIME(3) NULL,
|
||
KEY idx_inv_org (org_id), KEY idx_inv_status (status)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
CREATE TABLE invoice_items (
|
||
id CHAR(36) PRIMARY KEY,
|
||
invoice_id CHAR(36) NOT NULL REFERENCES invoices(id),
|
||
contract_id CHAR(36) NULL REFERENCES contracts(id),
|
||
description VARCHAR(300) NOT NULL,
|
||
quantity DECIMAL(10,2) NOT NULL DEFAULT 1,
|
||
unit_price_net_cents INT NOT NULL,
|
||
tax_bp INT NOT NULL,
|
||
net_cents INT NOT NULL,
|
||
tax_cents INT NOT NULL,
|
||
gross_cents INT NOT NULL,
|
||
sort_order INT NOT NULL DEFAULT 0,
|
||
KEY idx_ii_invoice (invoice_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|