f8cd37ac8c
Includes: API (Express/TypeScript), Astro site, Python workers, document generators, FCC compliance tools, Canada CRTC formation, Ansible infrastructure, and deployment scripts. Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>
51 lines
2.6 KiB
PL/PgSQL
51 lines
2.6 KiB
PL/PgSQL
-- 016_sanctions_screenings.sql
|
|
-- Audit log for all CASL (Consolidated Canadian Autonomous Sanctions List) screenings.
|
|
--
|
|
-- Every director name submitted on a CRTC order is screened before the order is
|
|
-- accepted and before any payment is collected. Results are logged here for audit
|
|
-- and regulatory purposes.
|
|
--
|
|
-- The CASL list is maintained by Global Affairs Canada under the Special Economic
|
|
-- Measures Act (SEMA) and Justice for Victims of Corrupt Foreign Officials Act (JVCFOA).
|
|
-- Source: https://www.international.gc.ca/world-monde/assets/office_docs/
|
|
-- international_relations-relations_internationales/sanctions/sema-lmes.xml
|
|
|
|
BEGIN;
|
|
|
|
CREATE TABLE IF NOT EXISTS sanctions_screenings (
|
|
id SERIAL PRIMARY KEY,
|
|
-- Who was screened
|
|
screened_name TEXT NOT NULL, -- exact name as entered by customer
|
|
order_number TEXT, -- linked order (null if pre-check before order created)
|
|
order_type TEXT DEFAULT 'canada_crtc',
|
|
-- Result
|
|
result TEXT NOT NULL -- 'clear' | 'hit' | 'possible_match' | 'error'
|
|
CHECK (result IN ('clear', 'hit', 'possible_match', 'error')),
|
|
match_score NUMERIC(5,2), -- 0-100 fuzzy match score (null if clear)
|
|
matched_entry JSONB, -- the CASL record that matched {last_name, given_name, country, schedule}
|
|
-- List metadata
|
|
list_date DATE, -- date the CASL list was last updated (from XML)
|
|
list_url TEXT DEFAULT 'https://www.international.gc.ca/world-monde/assets/office_docs/international_relations-relations_internationales/sanctions/sema-lmes.xml',
|
|
-- Admin
|
|
reviewed_by TEXT, -- admin user who reviewed a possible_match (null if auto-cleared)
|
|
review_notes TEXT,
|
|
reviewed_at TIMESTAMPTZ,
|
|
override BOOLEAN DEFAULT FALSE, -- admin manually cleared a possible_match
|
|
-- Timestamps
|
|
screened_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
ip_address TEXT,
|
|
user_agent TEXT
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_sanctions_order ON sanctions_screenings (order_number);
|
|
CREATE INDEX IF NOT EXISTS idx_sanctions_result ON sanctions_screenings (result, screened_at);
|
|
CREATE INDEX IF NOT EXISTS idx_sanctions_name ON sanctions_screenings (screened_name);
|
|
|
|
-- View: recent hits and possible matches requiring admin review
|
|
CREATE OR REPLACE VIEW sanctions_pending_review AS
|
|
SELECT * FROM sanctions_screenings
|
|
WHERE result IN ('hit', 'possible_match')
|
|
AND override = FALSE
|
|
ORDER BY screened_at DESC;
|
|
|
|
COMMIT;
|