Claude VM
a6c03a091e
Moved from gov-agreg/src/pages/achizitii/* to root (drop prefix). - 22 pages migrated, 127 files total - All internal links: /achizitii/X → /X (176 occurrences fixed) - AchizitiiLayout subnav rewritten: /X paths, top-right link to vreaudigital.ro hub - BaseLayout new (vreau.digital branding, OG tags, site URL) - astro.config.mjs: site https://vreau.digital, server output (was static) - docker-compose: port 5096 (vreaudigital is 5095), container vreau-digital - deploy.sh: paths /opt/vreau-digital, log /var/log/vreau-digital-deploy.log Backend shared with gov-agreg: - PostgreSQL satra (same schemas: seap, firms, anaf, anre, ...) - Photon, Martin tiles - Infisical /vreaudigital path (DATABASE_URL etc. shared) build: PASS (npx astro check 0 errors, npm run build 5s vite + 10s server)
175 lines
8.0 KiB
PL/PgSQL
175 lines
8.0 KiB
PL/PgSQL
-- WSP integration: schema extensions for SEAP web service ingestion.
|
|
-- Idempotent: safe to re-run on existing DB (already has ~600K rows in seap.announcements).
|
|
BEGIN;
|
|
|
|
-- ── Extend seap.announcements for WSP-specific structured + raw data ──
|
|
ALTER TABLE seap.announcements ADD COLUMN IF NOT EXISTS county_code TEXT;
|
|
ALTER TABLE seap.announcements ADD COLUMN IF NOT EXISTS notice_state TEXT;
|
|
ALTER TABLE seap.announcements ADD COLUMN IF NOT EXISTS notice_state_id INT;
|
|
ALTER TABLE seap.announcements ADD COLUMN IF NOT EXISTS deadline_submission TIMESTAMPTZ;
|
|
ALTER TABLE seap.announcements ADD COLUMN IF NOT EXISTS opening_date TIMESTAMPTZ;
|
|
ALTER TABLE seap.announcements ADD COLUMN IF NOT EXISTS duration_months INT;
|
|
ALTER TABLE seap.announcements ADD COLUMN IF NOT EXISTS duration_days INT;
|
|
ALTER TABLE seap.announcements ADD COLUMN IF NOT EXISTS authority_address TEXT;
|
|
ALTER TABLE seap.announcements ADD COLUMN IF NOT EXISTS authority_email TEXT;
|
|
ALTER TABLE seap.announcements ADD COLUMN IF NOT EXISTS authority_phone TEXT;
|
|
ALTER TABLE seap.announcements ADD COLUMN IF NOT EXISTS authority_url TEXT;
|
|
ALTER TABLE seap.announcements ADD COLUMN IF NOT EXISTS authority_type TEXT;
|
|
ALTER TABLE seap.announcements ADD COLUMN IF NOT EXISTS authority_main_activity TEXT;
|
|
ALTER TABLE seap.announcements ADD COLUMN IF NOT EXISTS supplier_address TEXT;
|
|
ALTER TABLE seap.announcements ADD COLUMN IF NOT EXISTS supplier_is_sme BOOLEAN;
|
|
ALTER TABLE seap.announcements ADD COLUMN IF NOT EXISTS framework_agreement BOOLEAN;
|
|
ALTER TABLE seap.announcements ADD COLUMN IF NOT EXISTS lots_count INT;
|
|
ALTER TABLE seap.announcements ADD COLUMN IF NOT EXISTS contract_has_lots BOOLEAN;
|
|
ALTER TABLE seap.announcements ADD COLUMN IF NOT EXISTS award_criteria JSONB;
|
|
ALTER TABLE seap.announcements ADD COLUMN IF NOT EXISTS lots JSONB;
|
|
ALTER TABLE seap.announcements ADD COLUMN IF NOT EXISTS documents JSONB;
|
|
ALTER TABLE seap.announcements ADD COLUMN IF NOT EXISTS details JSONB; -- raw Section1-6 nested
|
|
ALTER TABLE seap.announcements ADD COLUMN IF NOT EXISTS notice_id_internal BIGINT; -- WSP CNoticeId / CaNoticeId
|
|
ALTER TABLE seap.announcements ADD COLUMN IF NOT EXISTS authority_entity_id INT;
|
|
ALTER TABLE seap.announcements ADD COLUMN IF NOT EXISTS supplier_entity_id INT;
|
|
ALTER TABLE seap.announcements ADD COLUMN IF NOT EXISTS enriched_at TIMESTAMPTZ;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_ann_county ON seap.announcements(county_code);
|
|
CREATE INDEX IF NOT EXISTS idx_ann_state ON seap.announcements(notice_state);
|
|
CREATE INDEX IF NOT EXISTS idx_ann_deadline ON seap.announcements(deadline_submission) WHERE deadline_submission IS NOT NULL;
|
|
CREATE INDEX IF NOT EXISTS idx_ann_authority_name_trgm ON seap.announcements USING gin(authority_name gin_trgm_ops);
|
|
CREATE INDEX IF NOT EXISTS idx_ann_supplier_name_trgm ON seap.announcements USING gin(supplier_name gin_trgm_ops);
|
|
|
|
-- pg_trgm for fuzzy authority/supplier name search (idempotent)
|
|
CREATE EXTENSION IF NOT EXISTS pg_trgm;
|
|
|
|
|
|
-- ── Sync state: cursor per WSP feed ──
|
|
CREATE TABLE IF NOT EXISTS seap.wsp_sync_state (
|
|
feed TEXT PRIMARY KEY, -- e.g. 'ca_notices', 'c_notices', 'su_contracts'
|
|
last_run_at TIMESTAMPTZ,
|
|
last_cursor_date TIMESTAMPTZ, -- highest publication_date successfully ingested
|
|
last_window_start TIMESTAMPTZ,
|
|
last_window_end TIMESTAMPTZ,
|
|
items_imported_total BIGINT DEFAULT 0,
|
|
items_imported_24h INT DEFAULT 0,
|
|
consecutive_errors INT DEFAULT 0,
|
|
last_error TEXT,
|
|
last_error_at TIMESTAMPTZ,
|
|
notes TEXT
|
|
);
|
|
|
|
-- ── Backfill window queue: each window is a checkpoint ──
|
|
CREATE TABLE IF NOT EXISTS seap.wsp_backfill_windows (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
feed TEXT NOT NULL,
|
|
window_start TIMESTAMPTZ NOT NULL,
|
|
window_end TIMESTAMPTZ NOT NULL,
|
|
county_code TEXT, -- optional partition
|
|
state TEXT NOT NULL DEFAULT 'pending', -- pending, in_progress, completed, failed, skipped
|
|
items_imported INT DEFAULT 0,
|
|
page_total INT,
|
|
attempts INT DEFAULT 0,
|
|
last_error TEXT,
|
|
started_at TIMESTAMPTZ,
|
|
completed_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ DEFAULT now(),
|
|
UNIQUE(feed, window_start, window_end, county_code)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_wsp_bf_state ON seap.wsp_backfill_windows(feed, state, window_start);
|
|
CREATE INDEX IF NOT EXISTS idx_wsp_bf_pending ON seap.wsp_backfill_windows(feed, window_start) WHERE state = 'pending';
|
|
|
|
|
|
-- ── Beletage-scoped tables (Su* operations) ──
|
|
CREATE TABLE IF NOT EXISTS seap.beletage_contracts (
|
|
contract_id BIGINT PRIMARY KEY, -- WSP ContractId
|
|
contract_no TEXT,
|
|
contract_title TEXT,
|
|
contract_type TEXT,
|
|
contract_phase TEXT,
|
|
contract_state TEXT,
|
|
awarding_date DATE,
|
|
contract_date DATE,
|
|
publication_date TIMESTAMPTZ,
|
|
duration_months INT,
|
|
contract_value NUMERIC(15,2),
|
|
default_currency_value NUMERIC(15,2),
|
|
currency TEXT,
|
|
ca_notice_id BIGINT, -- link to public CA notice
|
|
ca_notice_no TEXT,
|
|
authority_name TEXT,
|
|
authority_cui TEXT,
|
|
is_current_version BOOLEAN,
|
|
is_rejected BOOLEAN,
|
|
version_no INT,
|
|
version_date TIMESTAMPTZ,
|
|
justification TEXT,
|
|
additional_information TEXT,
|
|
details JSONB, -- raw CANotice + ContractPhases + ContractSections
|
|
imported_at TIMESTAMPTZ DEFAULT now(),
|
|
enriched_at TIMESTAMPTZ
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_beletage_contracts_date ON seap.beletage_contracts(awarding_date DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_beletage_contracts_authority ON seap.beletage_contracts(authority_cui);
|
|
|
|
CREATE TABLE IF NOT EXISTS seap.beletage_invoices (
|
|
invoice_id BIGINT PRIMARY KEY, -- WSP InvoiceId
|
|
invoice_no TEXT,
|
|
invoice_date DATE,
|
|
due_date DATE,
|
|
contract_id BIGINT, -- FK soft to beletage_contracts
|
|
contract_no TEXT,
|
|
authority_name TEXT,
|
|
authority_cui TEXT,
|
|
total_value NUMERIC(15,2),
|
|
total_value_no_vat NUMERIC(15,2),
|
|
vat_value NUMERIC(15,2),
|
|
currency TEXT,
|
|
state TEXT,
|
|
paid_value NUMERIC(15,2),
|
|
paid_at TIMESTAMPTZ,
|
|
details JSONB, -- raw InvoiceItem + payments + details
|
|
imported_at TIMESTAMPTZ DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_beletage_invoices_date ON seap.beletage_invoices(invoice_date DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_beletage_invoices_contract ON seap.beletage_invoices(contract_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS seap.beletage_direct_acquisitions (
|
|
da_id BIGINT PRIMARY KEY, -- WSP DirectAcquisitionId
|
|
da_name TEXT,
|
|
unique_identification_code TEXT,
|
|
cpv_code TEXT,
|
|
cpv_name TEXT,
|
|
contract_type TEXT,
|
|
publication_date TIMESTAMPTZ,
|
|
finalization_date TIMESTAMPTZ,
|
|
estimated_value NUMERIC(15,2),
|
|
closing_value NUMERIC(15,2),
|
|
currency TEXT,
|
|
da_state TEXT,
|
|
authority_id INT,
|
|
authority_name TEXT,
|
|
authority_cui TEXT,
|
|
details JSONB,
|
|
imported_at TIMESTAMPTZ DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_beletage_da_date ON seap.beletage_direct_acquisitions(finalization_date DESC);
|
|
|
|
-- ── Beletage catalog (if used) ──
|
|
CREATE TABLE IF NOT EXISTS seap.beletage_catalog (
|
|
item_code TEXT PRIMARY KEY,
|
|
item_name TEXT,
|
|
cpv_code TEXT,
|
|
unit_price NUMERIC(15,2),
|
|
currency TEXT,
|
|
last_updated TIMESTAMPTZ,
|
|
details JSONB,
|
|
imported_at TIMESTAMPTZ DEFAULT now()
|
|
);
|
|
|
|
|
|
-- ── Materialized views for hub UI (refresh nightly) ──
|
|
-- Will be added in 005 once bulk data is in; placeholder comment here for traceability.
|
|
|
|
COMMIT;
|