Files
rust-discovery/migrations/0020_windows_sync_alignment.sql
kevin_janssen cafd13bca9
Rust development slice / linux (push) Failing after 7s
Deploy reviewed Windows sync alignment with schema 20 server
Bind the administrative connection-205 review to original signed history and preserve closed readiness. Include the qualified server dependency closure, UI review, native ARM64 build provenance, and guarded deployment evidence. Migration, API/gateway replacement and TLS checks passed with unchanged job authority, protected mounts and time/database services; native Windows receipt application remains separate.
2026-11-16 13:30:31 +01:00

69 lines
3.9 KiB
PL/PgSQL

-- Explicit connection-205 administrative closure only. No reconciliation,
-- grant, ready state, job, offer or original-history mutation is performed.
CREATE TABLE windows_sync_alignment_reviews (
tenant_id uuid PRIMARY KEY CHECK(tenant_id='11111111-1111-4111-8111-111111111111'),
request_id uuid NOT NULL UNIQUE,
agent_id uuid NOT NULL CHECK(agent_id='63ea15b2-0643-403d-b5b4-cba1288dbea3'),
witness jsonb NOT NULL,
witness_sha256 text NOT NULL CHECK(witness_sha256 ~ '^[0-9a-f]{64}$'),
submitted_by text NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
UNIQUE(tenant_id,request_id),
FOREIGN KEY(tenant_id,agent_id) REFERENCES agent_installations(tenant_id,agent_id),
CHECK(witness->>'schema_version'='1' AND witness->>'tenant_id'=tenant_id::text
AND witness->>'agent_id'=agent_id::text
AND witness->>'asset_id'='a176520e-8a91-4e93-a78b-8e7620f430e0'
AND witness->>'connection_generation'='205'
AND witness->>'boot_id'='a9ad0895-5a96-8f2c-8fb6-cfdc2d5225ea'
AND witness->>'agent_last_decision'='2af359c5-5b5c-4992-b445-1c00e271a288'
AND witness->>'helper_last_decision'='1a4758ad-0b39-449e-8551-16846851b09a')
);
CREATE TABLE windows_sync_alignment_receipts (
tenant_id uuid NOT NULL,
request_id uuid NOT NULL,
receipt_id uuid PRIMARY KEY,
witness_sha256 text NOT NULL CHECK(witness_sha256 ~ '^[0-9a-f]{64}$'),
payload bytea NOT NULL,
signature bytea NOT NULL CHECK(octet_length(signature)=64),
confirmed_by text NOT NULL CHECK(confirmed_by='simulated-testprincipal-a'),
session_hash text NOT NULL CHECK(session_hash ~ '^[0-9a-f]{64}$'),
issued_at timestamptz NOT NULL,
valid_until timestamptz NOT NULL CHECK(valid_until>issued_at AND valid_until<=issued_at+interval '5 minutes'),
FOREIGN KEY(tenant_id,request_id) REFERENCES windows_sync_alignment_reviews(tenant_id,request_id)
);
DO $$ DECLARE t text; BEGIN
FOREACH t IN ARRAY ARRAY['windows_sync_alignment_reviews','windows_sync_alignment_receipts'] LOOP
EXECUTE format('ALTER TABLE %I ENABLE ROW LEVEL SECURITY',t);
EXECUTE format('ALTER TABLE %I FORCE ROW LEVEL SECURITY',t);
EXECUTE format('CREATE POLICY tenant_scope ON %I USING(tenant_id::text=current_setting(''app.tenant_id'',true)) WITH CHECK(tenant_id::text=current_setting(''app.tenant_id'',true))',t);
EXECUTE format('GRANT SELECT,INSERT ON %I TO rmm_app',t);
EXECUTE format('CREATE TRIGGER immutable_content BEFORE UPDATE OR DELETE ON %I FOR EACH ROW EXECUTE FUNCTION immutable_plan()',t);
END LOOP;
END $$;
CREATE FUNCTION check_windows_sync_alignment_receipt() RETURNS trigger LANGUAGE plpgsql
SET search_path=pg_catalog,public AS $$
DECLARE w windows_sync_alignment_reviews%ROWTYPE; r jsonb;
BEGIN
SELECT * INTO STRICT w FROM windows_sync_alignment_reviews WHERE tenant_id=NEW.tenant_id AND request_id=NEW.request_id;
r=convert_from(NEW.payload,'UTF8')::jsonb;
IF r->>'domain' IS DISTINCT FROM 'rmm.dev.windows-sync-admin-closed-alignment.v1'
OR r->>'schema_version' IS DISTINCT FROM '1'
OR r->>'request_id' IS DISTINCT FROM NEW.request_id::text
OR r->>'receipt_id' IS DISTINCT FROM NEW.receipt_id::text
OR r->>'witness_sha256' IS DISTINCT FROM NEW.witness_sha256
OR NEW.witness_sha256 IS DISTINCT FROM w.witness_sha256
OR r->'witness' IS DISTINCT FROM w.witness
OR r->>'confirmed_by' IS DISTINCT FROM NEW.confirmed_by
OR r->>'approval_origin' IS DISTINCT FROM 'dev_test_browser_simulation'
OR r->'testsimulation' IS DISTINCT FROM 'true'::jsonb
OR r->'ready' IS DISTINCT FROM 'false'::jsonb
OR r->'helper_ready' IS DISTINCT FROM 'false'::jsonb
OR r->'unlock' IS DISTINCT FROM 'false'::jsonb
OR (r->>'issued_at')::timestamptz IS DISTINCT FROM NEW.issued_at
OR (r->>'valid_until')::timestamptz IS DISTINCT FROM NEW.valid_until
THEN RAISE EXCEPTION 'WINDOWS_SYNC_ALIGNMENT_MISMATCH'; END IF;
RETURN NEW;
END $$;
CREATE TRIGGER validate_alignment_receipt BEFORE INSERT ON windows_sync_alignment_receipts
FOR EACH ROW EXECUTE FUNCTION check_windows_sync_alignment_receipt();