57 lines
4.1 KiB
PL/PgSQL
57 lines
4.1 KiB
PL/PgSQL
-- Additive, inactive reserved recovery ledger. Existing manual evidence is retained.
|
|
-- Legacy reserved rows cannot prove an original two-write debit: never backfill them.
|
|
BEGIN;
|
|
LOCK TABLE public.mcp_scoped_recovery_intents IN ACCESS EXCLUSIVE MODE;
|
|
DO $$ BEGIN
|
|
IF EXISTS(SELECT 1 FROM public.mcp_scoped_recovery_intents WHERE mode='reserved')
|
|
THEN RAISE EXCEPTION 'legacy reserved recovery needs explicit reconciliation'; END IF;
|
|
IF EXISTS(SELECT 1 FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON n.oid=c.relnamespace
|
|
WHERE n.nspname='public' AND c.relname IN('mcp_routing_reservations','mcp_routing_reservation_slots','mcp_routing_reservation_slots_window'))
|
|
OR EXISTS(SELECT 1 FROM pg_catalog.pg_attribute a WHERE a.attrelid='public.mcp_scoped_recovery_intents'::regclass
|
|
AND NOT a.attisdropped AND a.attname IN('reservation_id','slot_phase','inverse_intent_sealed','inverse_intent_proof','inverse_configuration_sealed','inverse_configuration_proof'))
|
|
OR EXISTS(SELECT 1 FROM pg_catalog.pg_constraint c WHERE c.conrelid='public.mcp_scoped_recovery_intents'::regclass
|
|
AND c.conname IN('scoped_reservation_slot_shape','reserved_inverse_evidence_pairs'))
|
|
THEN RAISE EXCEPTION 'unmarked routing reservation objects need explicit reconciliation'; END IF;
|
|
END $$;
|
|
CREATE TABLE public.mcp_routing_reservations (
|
|
id uuid PRIMARY KEY,
|
|
source_plan_id uuid NOT NULL REFERENCES public.mcp_plans(id),
|
|
source_operation_id uuid NOT NULL REFERENCES public.mcp_operations(id),
|
|
source_step_index integer NOT NULL CHECK(source_step_index=0),
|
|
profile_id uuid NOT NULL REFERENCES public.mcp_routing_profiles(id),
|
|
connection_id uuid NOT NULL REFERENCES public.mcp_connections(id),
|
|
binding_sealed text NOT NULL,binding_proof text NOT NULL,
|
|
state_sealed text NOT NULL,state_proof text NOT NULL,revision integer NOT NULL CHECK(revision>=1),
|
|
deadline timestamptz NOT NULL,created_at timestamptz NOT NULL DEFAULT now(),
|
|
UNIQUE(source_operation_id,source_step_index)
|
|
);
|
|
CREATE TABLE public.mcp_routing_reservation_slots (
|
|
reservation_id uuid NOT NULL REFERENCES public.mcp_routing_reservations(id),
|
|
profile_id uuid NOT NULL REFERENCES public.mcp_routing_profiles(id),
|
|
lane text NOT NULL CHECK(lane IN ('forward','inverse')),
|
|
state text NOT NULL CHECK(state IN ('held','spent','proven-unused','unknown')),
|
|
created_at timestamptz NOT NULL DEFAULT now(),PRIMARY KEY(reservation_id,lane)
|
|
);
|
|
CREATE INDEX mcp_routing_reservation_slots_window ON public.mcp_routing_reservation_slots(profile_id,created_at);
|
|
ALTER TABLE public.mcp_scoped_recovery_intents ADD COLUMN reservation_id uuid REFERENCES public.mcp_routing_reservations(id);
|
|
ALTER TABLE public.mcp_scoped_recovery_intents ADD COLUMN slot_phase text NOT NULL DEFAULT 'recovery-intent';
|
|
ALTER TABLE public.mcp_scoped_recovery_intents ALTER COLUMN source_configuration_proof DROP NOT NULL;
|
|
ALTER TABLE public.mcp_scoped_recovery_intents ADD COLUMN inverse_intent_sealed text;
|
|
ALTER TABLE public.mcp_scoped_recovery_intents ADD COLUMN inverse_intent_proof text;
|
|
ALTER TABLE public.mcp_scoped_recovery_intents ADD COLUMN inverse_configuration_sealed text;
|
|
ALTER TABLE public.mcp_scoped_recovery_intents ADD COLUMN inverse_configuration_proof text;
|
|
ALTER TABLE public.mcp_scoped_recovery_intents ADD CONSTRAINT scoped_reservation_slot_shape CHECK (
|
|
(mode='manual' AND reservation_id IS NULL AND slot_phase='recovery-intent' AND source_configuration_proof IS NOT NULL)
|
|
OR (mode='reserved' AND reservation_id IS NOT NULL AND
|
|
((slot_phase='reservation-held' AND source_configuration_proof IS NULL)
|
|
OR (slot_phase IN ('reservation-bound','reserved-attempt') AND source_configuration_proof IS NOT NULL)))
|
|
);
|
|
ALTER TABLE public.mcp_scoped_recovery_intents ADD CONSTRAINT reserved_inverse_evidence_pairs CHECK (
|
|
(inverse_intent_sealed IS NULL)=(inverse_intent_proof IS NULL)
|
|
AND (inverse_configuration_sealed IS NULL)=(inverse_configuration_proof IS NULL)
|
|
AND (inverse_configuration_sealed IS NULL OR inverse_intent_sealed IS NOT NULL)
|
|
AND (inverse_intent_sealed IS NULL OR mode='reserved')
|
|
);
|
|
INSERT INTO public.schema_migrations(id) VALUES ('0040_mcp_routing_reservations') ON CONFLICT(id) DO NOTHING;
|
|
COMMIT;
|