47 lines
1.5 KiB
SQL
47 lines
1.5 KiB
SQL
-- PDF Accessibility Checker - PostgreSQL Schema
|
|
-- Run automatically on first Docker Compose startup
|
|
|
|
CREATE TABLE IF NOT EXISTS jobs (
|
|
id SERIAL PRIMARY KEY,
|
|
job_id VARCHAR(64) UNIQUE NOT NULL,
|
|
filename VARCHAR(255),
|
|
status VARCHAR(20) DEFAULT 'queued',
|
|
score INTEGER,
|
|
grade CHAR(1),
|
|
total_issues INTEGER,
|
|
critical_count INTEGER,
|
|
error_count INTEGER,
|
|
warning_count INTEGER,
|
|
result_json JSONB,
|
|
created_at TIMESTAMP DEFAULT NOW(),
|
|
completed_at TIMESTAMP,
|
|
processing_time FLOAT,
|
|
api_key_hash VARCHAR(64),
|
|
ip_address INET
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS audit_log (
|
|
id SERIAL PRIMARY KEY,
|
|
job_id VARCHAR(64),
|
|
action VARCHAR(50),
|
|
details JSONB,
|
|
created_at TIMESTAMP DEFAULT NOW(),
|
|
ip_address INET
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_jobs_status ON jobs(status);
|
|
CREATE INDEX IF NOT EXISTS idx_jobs_created ON jobs(created_at);
|
|
CREATE INDEX IF NOT EXISTS idx_jobs_job_id ON jobs(job_id);
|
|
CREATE INDEX IF NOT EXISTS idx_audit_job ON audit_log(job_id);
|
|
CREATE INDEX IF NOT EXISTS idx_audit_created ON audit_log(created_at);
|
|
|
|
CREATE TABLE IF NOT EXISTS dismissed_issues (
|
|
id SERIAL PRIMARY KEY,
|
|
job_id VARCHAR(64) NOT NULL,
|
|
issue_index INTEGER NOT NULL,
|
|
reason VARCHAR(255),
|
|
dismissed_at TIMESTAMP DEFAULT NOW(),
|
|
UNIQUE(job_id, issue_index)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_dismissed_job ON dismissed_issues(job_id);
|