a36e3e716f
- Implemented a new HTML page for generating service contract reports. - Added CSS styles for report layout and components. - Developed JavaScript functionality for loading customers and contracts, fetching report data, and rendering metrics and cases. - Included buttons for downloading reports in PDF and Excel formats. docs: Create Route Auth Audit for route access control - Generated an audit report detailing route access requirements. - Classified routes based on authentication needs and documented them in a markdown file. feat: Introduce buzzwords and mission projects tables in the database - Created `buzzwords` and `sag_buzzwords` tables for managing keywords related to SAG cases. - Established `mission_projects`, `mission_project_milestones`, and `mission_project_blockers` tables for project management. - Updated `sag_sager` table to link with mission projects and milestones, including necessary foreign key constraints.
42 lines
1.2 KiB
PL/PgSQL
42 lines
1.2 KiB
PL/PgSQL
-- Migration: 188_sag_buzzwords
|
|
-- Description: Separate buzzwords module for SAG with free-text keywords and case links
|
|
|
|
BEGIN;
|
|
|
|
CREATE TABLE IF NOT EXISTS buzzwords (
|
|
id SERIAL PRIMARY KEY,
|
|
word VARCHAR(120) NOT NULL,
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
deleted_at TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS sag_buzzwords (
|
|
id SERIAL PRIMARY KEY,
|
|
sag_id INTEGER NOT NULL REFERENCES sag_sager(id) ON DELETE CASCADE,
|
|
buzzword_id INTEGER NOT NULL REFERENCES buzzwords(id) ON DELETE CASCADE,
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
deleted_at TIMESTAMP
|
|
);
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS uq_buzzwords_word_active
|
|
ON buzzwords (word)
|
|
WHERE deleted_at IS NULL;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_buzzwords_word_active
|
|
ON buzzwords (word)
|
|
WHERE deleted_at IS NULL;
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS uq_sag_buzzwords_active
|
|
ON sag_buzzwords (sag_id, buzzword_id)
|
|
WHERE deleted_at IS NULL;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_sag_buzzwords_sag_id_active
|
|
ON sag_buzzwords (sag_id)
|
|
WHERE deleted_at IS NULL;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_sag_buzzwords_buzzword_id_active
|
|
ON sag_buzzwords (buzzword_id)
|
|
WHERE deleted_at IS NULL;
|
|
|
|
COMMIT;
|