44 lines
2.1 KiB
SQL
44 lines
2.1 KiB
SQL
CREATE TABLE IF NOT EXISTS report_customer_config (
|
|
customer_id VARCHAR(64) PRIMARY KEY,
|
|
customer_name VARCHAR(160) NOT NULL,
|
|
property_name VARCHAR(160) NOT NULL,
|
|
location VARCHAR(160) NOT NULL,
|
|
contact_line VARCHAR(180) NOT NULL DEFAULT '',
|
|
logo_data_uri TEXT,
|
|
logo_text VARCHAR(5) NOT NULL,
|
|
theme VARCHAR(40) NOT NULL DEFAULT 'modern'
|
|
CHECK (length(trim(theme)) > 0),
|
|
primary_color CHAR(7) NOT NULL DEFAULT '#1F5D66',
|
|
secondary_color CHAR(7) NOT NULL DEFAULT '#2C7C82',
|
|
accent_color CHAR(7) NOT NULL DEFAULT '#DBA25D',
|
|
text_color CHAR(7) NOT NULL DEFAULT '#24343A',
|
|
muted_color CHAR(7) NOT NULL DEFAULT '#718087',
|
|
surface_color CHAR(7) NOT NULL DEFAULT '#F2F6F5',
|
|
font_family VARCHAR(100) NOT NULL DEFAULT 'Arial',
|
|
active BOOLEAN NOT NULL DEFAULT TRUE,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS monthly_report_runs (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
customer_id VARCHAR(64) NOT NULL REFERENCES report_customer_config(customer_id),
|
|
report_year INTEGER NOT NULL CHECK (report_year BETWEEN 2020 AND 2100),
|
|
report_month INTEGER NOT NULL CHECK (report_month BETWEEN 1 AND 12),
|
|
status VARCHAR(20) NOT NULL CHECK (status IN ('queued', 'processing', 'completed', 'failed')),
|
|
report_version VARCHAR(20) NOT NULL,
|
|
prompt_version VARCHAR(20) NOT NULL,
|
|
template_version VARCHAR(20) NOT NULL,
|
|
claude_model VARCHAR(80),
|
|
storage_path TEXT,
|
|
checksum_sha256 CHAR(64),
|
|
error_message TEXT,
|
|
started_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
completed_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
UNIQUE (customer_id, report_year, report_month, report_version)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_monthly_report_runs_status
|
|
ON monthly_report_runs(status, report_year, report_month);
|