64 lines
3.4 KiB
SQL
64 lines
3.4 KiB
SQL
CREATE TABLE "user" (
|
|
user_id TEXT PRIMARY KEY DEFAULT (lower(substr(hex(randomblob(4)), 1, 8) || '-' || substr(hex(randomblob(2)), 1, 4) || '-4' || substr(hex(randomblob(2)), 2, 3) || '-' || substr('89ab', abs(random()) % 4 + 1, 1) || substr(hex(randomblob(2)), 2, 3) || '-' || hex(randomblob(6)))),
|
|
email TEXT NOT NULL UNIQUE,
|
|
display_name TEXT NOT NULL,
|
|
password_hash TEXT,
|
|
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP -- @date
|
|
);
|
|
|
|
CREATE TABLE company (
|
|
company_id TEXT PRIMARY KEY DEFAULT (lower(substr(hex(randomblob(4)), 1, 8) || '-' || substr(hex(randomblob(2)), 1, 4) || '-4' || substr(hex(randomblob(2)), 2, 3) || '-' || substr('89ab', abs(random()) % 4 + 1, 1) || substr(hex(randomblob(2)), 2, 3) || '-' || hex(randomblob(6)))),
|
|
name TEXT NOT NULL,
|
|
registry_no TEXT,
|
|
address_line1 TEXT,
|
|
address_line2 TEXT,
|
|
postcode TEXT,
|
|
town TEXT,
|
|
country TEXT,
|
|
total_shares INTEGER NOT NULL DEFAULT 0,
|
|
fiscal_year_end TEXT,
|
|
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP -- @date
|
|
);
|
|
|
|
CREATE TABLE company_member (
|
|
member_id TEXT PRIMARY KEY DEFAULT (lower(substr(hex(randomblob(4)), 1, 8) || '-' || substr(hex(randomblob(2)), 1, 4) || '-4' || substr(hex(randomblob(2)), 2, 3) || '-' || substr('89ab', abs(random()) % 4 + 1, 1) || substr(hex(randomblob(2)), 2, 3) || '-' || hex(randomblob(6)))),
|
|
company_id TEXT NOT NULL REFERENCES company(company_id) ON DELETE CASCADE,
|
|
user_id TEXT NOT NULL REFERENCES "user"(user_id) ON DELETE CASCADE,
|
|
role TEXT NOT NULL CHECK (role IN ('managing_director', 'shareholder', 'angel')),
|
|
shares INTEGER,
|
|
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, -- @date
|
|
UNIQUE (company_id, user_id)
|
|
);
|
|
|
|
CREATE INDEX idx_company_member_company ON company_member(company_id);
|
|
CREATE INDEX idx_company_member_user ON company_member(user_id);
|
|
|
|
CREATE TABLE resolution (
|
|
resolution_id TEXT PRIMARY KEY DEFAULT (lower(substr(hex(randomblob(4)), 1, 8) || '-' || substr(hex(randomblob(2)), 1, 4) || '-4' || substr(hex(randomblob(2)), 2, 3) || '-' || substr('89ab', abs(random()) % 4 + 1, 1) || substr(hex(randomblob(2)), 2, 3) || '-' || hex(randomblob(6)))),
|
|
company_id TEXT NOT NULL REFERENCES company(company_id) ON DELETE CASCADE,
|
|
template_key TEXT NOT NULL,
|
|
title TEXT NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'published', 'archived')),
|
|
field_values TEXT NOT NULL DEFAULT '{}', -- @json:Record<string, unknown>
|
|
pdf_key TEXT,
|
|
created_by TEXT NOT NULL REFERENCES "user"(user_id),
|
|
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, -- @date
|
|
published_at TEXT -- @date
|
|
);
|
|
|
|
CREATE INDEX idx_resolution_company ON resolution(company_id);
|
|
CREATE INDEX idx_resolution_status ON resolution(status);
|
|
|
|
CREATE TABLE audit_log (
|
|
audit_id TEXT PRIMARY KEY DEFAULT (lower(substr(hex(randomblob(4)), 1, 8) || '-' || substr(hex(randomblob(2)), 1, 4) || '-4' || substr(hex(randomblob(2)), 2, 3) || '-' || substr('89ab', abs(random()) % 4 + 1, 1) || substr(hex(randomblob(2)), 2, 3) || '-' || hex(randomblob(6)))),
|
|
table_name TEXT NOT NULL,
|
|
record_id TEXT NOT NULL,
|
|
action TEXT NOT NULL CHECK (action IN ('INSERT', 'UPDATE', 'DELETE')),
|
|
user_id TEXT REFERENCES "user"(user_id),
|
|
changed_fields TEXT, -- @json:Record<string, unknown>
|
|
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP -- @date
|
|
);
|
|
|
|
CREATE INDEX idx_audit_log_record ON audit_log(table_name, record_id);
|
|
CREATE INDEX idx_audit_log_created ON audit_log(created_at);
|