22 lines
1021 B
SQL
22 lines
1021 B
SQL
-- 031: Create reports table for tracking generated financial reports
|
|
-- Used by clearing-service admin reports feature
|
|
|
|
CREATE TABLE IF NOT EXISTS reports (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
type VARCHAR(20) NOT NULL CHECK (type IN ('daily', 'monthly', 'quarterly', 'annual')),
|
|
title VARCHAR(200) NOT NULL,
|
|
period VARCHAR(50) NOT NULL,
|
|
status VARCHAR(20) NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'generated', 'failed')),
|
|
file_url VARCHAR(500),
|
|
generated_at TIMESTAMPTZ,
|
|
generated_by UUID,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
version INTEGER NOT NULL DEFAULT 1
|
|
);
|
|
|
|
CREATE INDEX idx_reports_type ON reports (type);
|
|
CREATE INDEX idx_reports_status ON reports (status);
|
|
CREATE INDEX idx_reports_generated_by ON reports (generated_by);
|
|
CREATE INDEX idx_reports_created_at ON reports (created_at DESC);
|