34 lines
4.2 KiB
SQL
34 lines
4.2 KiB
SQL
-- Proposed operation only. No schema installation. Execute only after destination preflight,
|
|
-- fresh validated backup/restore and the separate authenticated rollout decision.
|
|
DO $reconcile$
|
|
DECLARE current_row opportunities%ROWTYPE; expected_row opportunities%ROWTYPE;
|
|
rollout_actor bigint := NULLIF(current_setting('investmoz.actor_user_id',true),'')::bigint;
|
|
BEGIN
|
|
IF NULLIF(current_setting('investmoz.operation_key',true),'') IS NULL OR NULLIF(current_setting('investmoz.authorized_revision',true),'') IS NULL THEN RAISE EXCEPTION 'Server operation/revision binding missing'; END IF;
|
|
IF rollout_actor IS NULL OR NOT EXISTS(SELECT 1 FROM users WHERE id=rollout_actor)
|
|
THEN RAISE EXCEPTION 'Authenticated rollout actor is missing; stop'; END IF;
|
|
IF (SELECT count(*) FROM migrations WHERE migration IN
|
|
('2026_10_16_140000_add_investment_zone_operational_details',
|
|
'2026_10_16_150000_create_investor_navigation_preferences')) <> 2
|
|
THEN RAISE EXCEPTION 'Accepted schema ledger changed; stop'; END IF;
|
|
SELECT * INTO STRICT current_row FROM opportunities WHERE id=2 FOR UPDATE;
|
|
IF EXISTS (SELECT 1 FROM audit_events WHERE source_system='sector-reconciliation-20261018') THEN
|
|
IF current_row.sector_id <> 8 THEN RAISE EXCEPTION 'Replay conflict; stop'; END IF;
|
|
RETURN; -- Preserve later user edits; never overwrite them on replay.
|
|
END IF;
|
|
SELECT * INTO expected_row FROM jsonb_populate_record(NULL::opportunities, '{"id":2,"sector_id":2,"reference":"INV-MZ-ENE-002","title":"Mini-rede solar para polo produtivo","slug":"inv-mz-ene-002-mini-rede-solar-para-polo-produtivo","summary":"Acesso a energia para 40 unidades produtivas.","description":"Acesso a energia para 40 unidades produtivas.","province":"Zambézia","district":null,"promoter_name":"Promotor em validação","promoter_type":"privado","maturity_level":3,"maturity_label":"Estudo de viabilidade em curso","estimated_investment_amount":"1200000.00","estimated_investment_currency":"USD","financing_modality":"PPP ou financiamento misto","expected_impact":"Acesso a energia para 40 unidades produtivas.","status":"published","is_featured":true,"published_at":"2026-09-24 19:02:25","last_validated_at":null,"created_at":"2026-09-24 19:02:25","updated_at":"2026-09-24 19:02:25","deleted_at":null,"primary_territory_id":"c20efcce-8af8-588d-84b2-b4b138b6e066","territory_version":1,"geography_scope":"unspecified","management_version":1,"opportunity_category":null,"management_draft":null,"management_creation_key":null,"first_submitted_at":null,"management_closed_once":false,"owner_user_id":null,"organization_id":null,"workflow_context":null}'::jsonb);
|
|
IF to_jsonb(current_row) IS DISTINCT FROM to_jsonb(expected_row)
|
|
THEN RAISE EXCEPTION 'Opportunity preimage changed; stop and reconcile'; END IF;
|
|
IF (SELECT count(*) FROM sectors WHERE (id,slug) IN
|
|
((2,'energia'),(8,'energia-especifica'),(7,'mineracao'),(6,'mercado-financeiro'),(9,'fintech')) AND deleted_at IS NULL) <> 5
|
|
THEN RAISE EXCEPTION 'Master identities changed; stop'; END IF;
|
|
UPDATE opportunities SET sector_id=8,management_version=current_row.management_version+1,updated_at=clock_timestamp() WHERE id=2;
|
|
INSERT INTO audit_events(source_system,entity_type,action,actor_user_id,correlation_id,data_classification,occurred_at,before_data,after_data)
|
|
VALUES('sector-reconciliation-20261018','opportunity','sector_reclassified',rollout_actor,current_setting('investmoz.operation_key')::uuid,'internal',clock_timestamp(),
|
|
jsonb_build_object('opportunity_id',2,'reference',current_row.reference,'sector_id',2,'management_version',current_row.management_version),
|
|
jsonb_build_object('opportunity_id',2,'sector_id',8,'management_version',current_row.management_version+1,
|
|
'reason','Tempo 18/10/2026: mini-rede solar; descrição documentada: Acesso a energia para 40 unidades produtivas.',
|
|
'relationships_changed',false,'operation_key',current_setting('investmoz.operation_key'),'authorized_revision',current_setting('investmoz.authorized_revision'),'connection_pid',pg_backend_pid(),'transaction_id',txid_current()::text,'operator_attribution','Authenticated rollout actor recorded in actor_user_id'));
|
|
END $reconcile$;
|
|
-- Transaction is owned exclusively by SectorReconciliationExecution on its write PDO.
|