55 lines
2.7 KiB
MySQL
55 lines
2.7 KiB
MySQL
|
-- 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;
|