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>
24 lines
1.1 KiB
SQL
24 lines
1.1 KiB
SQL
-- 060: Multi-year filing orders
|
||
--
|
||
-- When a customer orders the same service slug (typically fcc-499a)
|
||
-- for multiple reporting years at once — e.g., catching up on 3 years
|
||
-- of past-due 499-A filings simultaneously — they receive a 15%
|
||
-- multi-year discount (same magnitude as the existing bundle discount
|
||
-- for multi-slug orders).
|
||
--
|
||
-- We represent this as a single compliance_order with a multi_year_filings
|
||
-- array; the handler runs N Playwright sessions against USAC, one per
|
||
-- year, and records N confirmation numbers.
|
||
|
||
ALTER TABLE compliance_orders
|
||
ADD COLUMN IF NOT EXISTS multi_year_filings INT[];
|
||
|
||
-- Per-year confirmation numbers captured after each sub-filing succeeds.
|
||
-- Shape: [{"year":2023,"confirmation":"X"},{"year":2024,"confirmation":"Y"}]
|
||
ALTER TABLE compliance_orders
|
||
ADD COLUMN IF NOT EXISTS multi_year_confirmations JSONB DEFAULT '[]'::jsonb;
|
||
|
||
-- Discount bookkeeping: when multi_year_filings has 2+ entries, the
|
||
-- resolver applies a 15% discount on top of the N × price base.
|
||
ALTER TABLE compliance_orders
|
||
ADD COLUMN IF NOT EXISTS multi_year_discount_pct NUMERIC(4,1);
|