AISA v2.0 / Technical documentation / 005-registry.sql
005-registry.sql
SQL · 290 lines · 22,786 bytes · wiki path 10 Architecture/db/SrdSupport/005-registry.sql · download the raw file · cited from Data Model — `SRD_SUPPORT`
Same folder: 001-work-and-planning.sql · 002-decisions-and-grants.sql · 003-evidence.sql · 004-ledger.sql · 006-memory.sql · 007-policy-and-operations.sql · 008-immutability-and-payload-guards.sql · 009-seed-platform-policy.sql · 010-runtime-execution.sql · acceptance-install-clean.sql · acceptance-upgrade.sql · grant-runtime.sql · install-clean.sql · lint_ddl.py · upgrade.sql · verify-ledger.sql · verify-schema.sql
SET DEFINE OFF
WHENEVER SQLERROR EXIT SQL.SQLCODE
-- 005 · REGISTRY — Architecture § 3.1 group "Registry"; Agents § 0.1 (the profile); Agent Framework § 6 (governance);
-- Failure and Recovery § 4 (capability descriptions); Software Architecture § 10.3 (reach — direct connections only, D132).
-- Everything model-facing is a versioned row with state draft → testing → published → retired. A published row is
-- immutable (008); publishing needs a PASSED evaluation and an approval recorded in GOVERNANCE_EVENTS (008).
-- The finer estate lifecycle (EDITING → LINTED → EVALUATED → APPROVAL_PENDING → APPROVED → ACTIVE → RETIRED,
-- Agent Framework § 6.1) is recorded as GOVERNANCE_EVENTS; the four states here are its projection.
DECLARE
PROCEDURE ddl(p_sql IN VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE p_sql;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE NOT IN (-955, -1408, -1430, -2260, -2261, -2264, -2275) THEN RAISE; END IF;
END;
BEGIN
-- ---------------------------------------------------------------- PROMPTS
ddl(q'~CREATE TABLE "SRD_SUPPORT"."PROMPTS" (
"ID" NVARCHAR2(64) NOT NULL,
"PROMPT_KEY" NVARCHAR2(128) NOT NULL,
"VERSION" NUMBER(10) NOT NULL,
"MODULE" NVARCHAR2(16) NOT NULL,
"STAGE_ID" NVARCHAR2(16),
"BODY" CLOB NOT NULL,
"VARIABLES" CLOB NOT NULL,
"CONTENT_HASH" NVARCHAR2(64) NOT NULL,
"STATE" NVARCHAR2(16) NOT NULL,
"OWNER_USER" NVARCHAR2(128) NOT NULL,
"PUBLISHED_BY" NVARCHAR2(128),
"PUBLISHED_ON_UTC" TIMESTAMP(6),
"RETIRED_ON_UTC" TIMESTAMP(6),
"CREATED_ON_UTC" TIMESTAMP(6) NOT NULL,
"ROW_VERSION" NUMBER(19) DEFAULT 0 NOT NULL,
CONSTRAINT "PK_PROMPTS" PRIMARY KEY ("ID"),
CONSTRAINT "UK_PROMPTS" UNIQUE ("PROMPT_KEY", "VERSION"),
CONSTRAINT "CK_PROMPTS_MODULE" CHECK ("MODULE" IN ('configuration','support','source','platform','connector')),
CONSTRAINT "CK_PROMPTS_STATE" CHECK ("STATE" IN ('draft','testing','published','retired')),
CONSTRAINT "CK_PROMPTS_VARS_JSON" CHECK ("VARIABLES" IS JSON),
CONSTRAINT "CK_PROMPTS_PUBLISHED" CHECK ("STATE" NOT IN ('published','retired') OR "PUBLISHED_ON_UTC" IS NOT NULL)
)~');
ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_PROMPTS_KEY_STATE" ON "SRD_SUPPORT"."PROMPTS" ("PROMPT_KEY", "STATE")~');
ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."PROMPTS" IS 'Agent Framework § 6: immutable prompt binding, versioned, {{variable}} placeholders declared in VARIABLES (AI.Prompting convention, name-only). A profile references a prompt version, never inline text (Agents § 0.1).'~');
-- ---------------------------------------------------------------- MODEL_ROUTES
ddl(q'~CREATE TABLE "SRD_SUPPORT"."MODEL_ROUTES" (
"ID" NVARCHAR2(64) NOT NULL,
"ROUTE_KEY" NVARCHAR2(128) NOT NULL,
"VERSION" NUMBER(10) NOT NULL,
"PROVIDER_CODE" NVARCHAR2(32) NOT NULL,
"ENDPOINT_REF" NVARCHAR2(256) NOT NULL,
"DEPLOYMENT_NAME" NVARCHAR2(128) NOT NULL,
"MODEL_NAME" NVARCHAR2(128) NOT NULL,
"DATA_ZONE" NVARCHAR2(16) NOT NULL,
"DPA_COVERED" NUMBER(1) NOT NULL,
"ALLOWED_MODULES" CLOB NOT NULL,
"CONTEXT_BUDGET_TOKENS" NUMBER(10) NOT NULL,
"SUPPORTS_CACHING" NUMBER(1) NOT NULL,
"STATE" NVARCHAR2(16) NOT NULL,
"PUBLISHED_BY" NVARCHAR2(128),
"PUBLISHED_ON_UTC" TIMESTAMP(6),
"RETIRED_ON_UTC" TIMESTAMP(6),
"CREATED_ON_UTC" TIMESTAMP(6) NOT NULL,
"ROW_VERSION" NUMBER(19) DEFAULT 0 NOT NULL,
CONSTRAINT "PK_MODEL_ROUTES" PRIMARY KEY ("ID"),
CONSTRAINT "UK_MODEL_ROUTES" UNIQUE ("ROUTE_KEY", "VERSION"),
CONSTRAINT "CK_ROUTES_ZONE" CHECK ("DATA_ZONE" IN ('EU','GLOBAL','LOCAL')),
CONSTRAINT "CK_ROUTES_STATE" CHECK ("STATE" IN ('draft','testing','published','retired')),
CONSTRAINT "CK_ROUTES_FLAGS" CHECK ("DPA_COVERED" IN (0,1) AND "SUPPORTS_CACHING" IN (0,1)),
CONSTRAINT "CK_ROUTES_MODULES_JSON" CHECK ("ALLOWED_MODULES" IS JSON)
)~');
ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."MODEL_ROUTES" IS 'Governed route selection (Agents § 0.3; GovernedProviderRouteSelector, name-only): the data-boundary predicate is ALLOWED_MODULES × DATA_ZONE × DPA_COVERED — a Support profile may only bind a route with DATA_ZONE = EU and DPA_COVERED = 1 (SG-9). CONTEXT_BUDGET_TOKENS drives compaction to a common size (Software Architecture § 7.1).'~');
-- ---------------------------------------------------------------- PROFILES
ddl(q'~CREATE TABLE "SRD_SUPPORT"."PROFILES" (
"ID" NVARCHAR2(64) NOT NULL,
"PROFILE_KEY" NVARCHAR2(128) NOT NULL,
"VERSION" NUMBER(10) NOT NULL,
"NAME" NVARCHAR2(256) NOT NULL,
"MODULE" NVARCHAR2(16) NOT NULL,
"ROLE" NVARCHAR2(16) NOT NULL,
"PROMPT_ID" NVARCHAR2(64) NOT NULL,
"MODEL_ROUTE_ID" NVARCHAR2(64) NOT NULL,
"MEMORY_WRITE_NODE" NVARCHAR2(256),
"TOOLS" CLOB NOT NULL,
"DENIED_CONTEXT" CLOB NOT NULL,
"SKILLS" CLOB NOT NULL,
"ESCALATION_ATTEMPTS" NUMBER(2) NOT NULL,
"ESCALATION_TO" NVARCHAR2(128) NOT NULL,
"BUDGET_SHARE_PCT" NUMBER(5,2),
"BUDGET_MINUTES_FIXED" NUMBER(10),
"CONSULT_CONCURRENCY" NUMBER(3) DEFAULT 2 NOT NULL,
"MAX_CONCURRENT_SUBAGENTS" NUMBER(3) DEFAULT 0 NOT NULL,
"DB_SESSIONS_PER_ENV" NUMBER(3) DEFAULT 1 NOT NULL,
"EVAL_SET_ID" NVARCHAR2(64),
"STATE" NVARCHAR2(16) NOT NULL,
"CONTENT_HASH" NVARCHAR2(64) NOT NULL,
"OWNER_USER" NVARCHAR2(128) NOT NULL,
"PUBLISHED_BY" NVARCHAR2(128),
"PUBLISHED_ON_UTC" TIMESTAMP(6),
"RETIRED_ON_UTC" TIMESTAMP(6),
"CREATED_ON_UTC" TIMESTAMP(6) NOT NULL,
"ROW_VERSION" NUMBER(19) DEFAULT 0 NOT NULL,
CONSTRAINT "PK_PROFILES" PRIMARY KEY ("ID"),
CONSTRAINT "UK_PROFILES" UNIQUE ("PROFILE_KEY", "VERSION"),
CONSTRAINT "FK_PROFILES_PROMPT" FOREIGN KEY ("PROMPT_ID") REFERENCES "SRD_SUPPORT"."PROMPTS" ("ID"),
CONSTRAINT "FK_PROFILES_ROUTE" FOREIGN KEY ("MODEL_ROUTE_ID") REFERENCES "SRD_SUPPORT"."MODEL_ROUTES" ("ID"),
CONSTRAINT "CK_PROFILES_MODULE" CHECK ("MODULE" IN ('configuration','support','source','platform','connector')),
CONSTRAINT "CK_PROFILES_ROLE" CHECK ("ROLE" IN ('root','stage','sub_agent','connector','utility')),
CONSTRAINT "CK_PROFILES_STATE" CHECK ("STATE" IN ('draft','testing','published','retired')),
CONSTRAINT "CK_PROFILES_JSON" CHECK ("TOOLS" IS JSON AND "DENIED_CONTEXT" IS JSON AND "SKILLS" IS JSON),
CONSTRAINT "CK_PROFILES_BUDGET" CHECK (("BUDGET_SHARE_PCT" IS NOT NULL AND "BUDGET_SHARE_PCT" BETWEEN 0 AND 100) OR "BUDGET_MINUTES_FIXED" IS NOT NULL),
CONSTRAINT "CK_PROFILES_ROOT_NO_TOOLS" CHECK ("ROLE" <> 'root' OR "MAX_CONCURRENT_SUBAGENTS" >= 0)
)~');
ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_PROFILES_KEY_STATE" ON "SRD_SUPPORT"."PROFILES" ("PROFILE_KEY", "STATE")~');
ddl(q'~CREATE UNIQUE INDEX "SRD_SUPPORT"."UX_PROFILES_MEMORY_WRITER" ON "SRD_SUPPORT"."PROFILES" (CASE WHEN "STATE" = 'published' THEN "MEMORY_WRITE_NODE" END)~');
ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."PROFILES" IS 'Agents § 0.1, field for field. Budget is TIME (D135): BUDGET_SHARE_PCT of the case''s predicted minutes for roots and stage agents, BUDGET_MINUTES_FIXED for sub-agents. UX_PROFILES_MEMORY_WRITER enforces "one domain has exactly one writing profile" among published profiles. Whole profiles, no runtime inheritance (D50).'~');
-- ---------------------------------------------------------------- SKILLS
ddl(q'~CREATE TABLE "SRD_SUPPORT"."SKILLS" (
"ID" NVARCHAR2(64) NOT NULL,
"SKILL_KEY" NVARCHAR2(128) NOT NULL,
"VERSION" NUMBER(10) NOT NULL,
"MODULE" NVARCHAR2(16) NOT NULL,
"NAME" NVARCHAR2(256) NOT NULL,
"MANIFEST" CLOB NOT NULL,
"MANIFEST_HASH" NVARCHAR2(64) NOT NULL,
"SOURCE_NODE" NVARCHAR2(256) NOT NULL,
"SOURCE_COMMIT_SHA" NVARCHAR2(64),
"WRITES" NUMBER(1) NOT NULL,
"DRY_RUN_DEFAULT" NUMBER(1) NOT NULL,
"HAS_TEARDOWN" NUMBER(1) NOT NULL,
"STATE" NVARCHAR2(16) NOT NULL,
"OWNER_USER" NVARCHAR2(128) NOT NULL,
"PUBLISHED_BY" NVARCHAR2(128),
"PUBLISHED_ON_UTC" TIMESTAMP(6),
"RETIRED_ON_UTC" TIMESTAMP(6),
"CREATED_ON_UTC" TIMESTAMP(6) NOT NULL,
"ROW_VERSION" NUMBER(19) DEFAULT 0 NOT NULL,
CONSTRAINT "PK_SKILLS" PRIMARY KEY ("ID"),
CONSTRAINT "UK_SKILLS" UNIQUE ("SKILL_KEY", "VERSION"),
CONSTRAINT "CK_SKILLS_MODULE" CHECK ("MODULE" IN ('configuration','support','source','platform')),
CONSTRAINT "CK_SKILLS_STATE" CHECK ("STATE" IN ('draft','testing','published','retired')),
CONSTRAINT "CK_SKILLS_MANIFEST_JSON" CHECK ("MANIFEST" IS JSON),
CONSTRAINT "CK_SKILLS_FLAGS" CHECK ("WRITES" IN (0,1) AND "DRY_RUN_DEFAULT" IN (0,1) AND "HAS_TEARDOWN" IN (0,1)),
CONSTRAINT "CK_SKILLS_WRITE_SAFETY" CHECK ("WRITES" = 0 OR ("DRY_RUN_DEFAULT" = 1 AND "HAS_TEARDOWN" = 1))
)~');
ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."SKILLS" IS 'Agent Framework § 6.6: source of truth is Markdown in the memory tree (SOURCE_NODE @ SOURCE_COMMIT_SHA); this is the published runtime row (contract: contracts/skill-manifest.schema.json). CK_SKILLS_WRITE_SAFETY: a skill that writes carries its dry-run default and its teardown, by constraint.'~');
-- ---------------------------------------------------------------- EVAL_SETS · EVAL_RUNS
ddl(q'~CREATE TABLE "SRD_SUPPORT"."EVAL_SETS" (
"ID" NVARCHAR2(64) NOT NULL,
"EVAL_KEY" NVARCHAR2(128) NOT NULL,
"VERSION" NUMBER(10) NOT NULL,
"MODULE" NVARCHAR2(16) NOT NULL,
"OWNER_PROFILE_KEY" NVARCHAR2(128) NOT NULL,
"CORPUS_REF" NVARCHAR2(512) NOT NULL,
"CASE_COUNT" NUMBER(10) NOT NULL,
"TOLERANCES_HASH" NVARCHAR2(64) NOT NULL,
"STATE" NVARCHAR2(16) NOT NULL,
"LAST_RESULT" NVARCHAR2(8),
"LAST_RUN_ID" NVARCHAR2(64),
"CREATED_ON_UTC" TIMESTAMP(6) NOT NULL,
"ROW_VERSION" NUMBER(19) DEFAULT 0 NOT NULL,
CONSTRAINT "PK_EVAL_SETS" PRIMARY KEY ("ID"),
CONSTRAINT "UK_EVAL_SETS" UNIQUE ("EVAL_KEY", "VERSION"),
CONSTRAINT "CK_EVAL_STATE" CHECK ("STATE" IN ('draft','testing','published','retired')),
CONSTRAINT "CK_EVAL_RESULT" CHECK ("LAST_RESULT" IS NULL OR "LAST_RESULT" IN ('green','red','unknown')),
CONSTRAINT "CK_EVAL_COUNT" CHECK ("CASE_COUNT" > 0)
)~');
ddl(q'~ALTER TABLE "SRD_SUPPORT"."PROFILES" ADD CONSTRAINT "FK_PROFILES_EVAL" FOREIGN KEY ("EVAL_SET_ID") REFERENCES "SRD_SUPPORT"."EVAL_SETS" ("ID")~');
ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."EVAL_SETS" IS 'Agent Framework § 6.5: one directory per case under CORPUS_REF (input/, oracle/, tolerances.json — contract: contracts/eval-set.schema.json), never deployed to the QA instance (Plugin.Eval). Nothing publishes with LAST_RESULT = red (008).'~');
ddl(q'~CREATE TABLE "SRD_SUPPORT"."EVAL_RUNS" (
"ID" NVARCHAR2(64) NOT NULL,
"EVAL_SET_ID" NVARCHAR2(64) NOT NULL,
"SUBJECT_TYPE" NVARCHAR2(16) NOT NULL,
"SUBJECT_KEY" NVARCHAR2(128) NOT NULL,
"SUBJECT_VERSION" NUMBER(10) NOT NULL,
"ROUTE_KEY" NVARCHAR2(128),
"ROUTE_VERSION" NUMBER(10),
"TRIGGER" NVARCHAR2(16) NOT NULL,
"STARTED_ON_UTC" TIMESTAMP(6) NOT NULL,
"ENDED_ON_UTC" TIMESTAMP(6),
"CASES_TOTAL" NUMBER(10) NOT NULL,
"CASES_PASSED" NUMBER(10) DEFAULT 0 NOT NULL,
"CASES_FAILED" NUMBER(10) DEFAULT 0 NOT NULL,
"CASES_UNKNOWN" NUMBER(10) DEFAULT 0 NOT NULL,
"RESULT" NVARCHAR2(8),
"MINUTES_USED" NUMBER(10,2),
"REPORT_REF" NVARCHAR2(512),
"FRAMEWORK_VERSION" NVARCHAR2(128) NOT NULL,
"LEDGER_RECORD_ID" NVARCHAR2(64) NOT NULL,
CONSTRAINT "PK_EVAL_RUNS" PRIMARY KEY ("ID"),
CONSTRAINT "FK_EVALRUN_SET" FOREIGN KEY ("EVAL_SET_ID") REFERENCES "SRD_SUPPORT"."EVAL_SETS" ("ID"),
CONSTRAINT "CK_EVALRUN_SUBJECT" CHECK ("SUBJECT_TYPE" IN ('profile','prompt','skill','memory_domain','route','release')),
CONSTRAINT "CK_EVALRUN_TRIGGER" CHECK ("TRIGGER" IN ('publish','domain_change','skill_change','route_change','schedule','release','manual')),
CONSTRAINT "CK_EVALRUN_RESULT" CHECK ("RESULT" IS NULL OR "RESULT" IN ('green','red')),
CONSTRAINT "CK_EVALRUN_COUNTS" CHECK ("CASES_PASSED" + "CASES_FAILED" + "CASES_UNKNOWN" <= "CASES_TOTAL")
)~');
ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_EVALRUN_SUBJECT" ON "SRD_SUPPORT"."EVAL_RUNS" ("SUBJECT_TYPE", "SUBJECT_KEY", "SUBJECT_VERSION", "STARTED_ON_UTC")~');
ddl(q'~ALTER TABLE "SRD_SUPPORT"."EVAL_SETS" ADD CONSTRAINT "FK_EVAL_LASTRUN" FOREIGN KEY ("LAST_RUN_ID") REFERENCES "SRD_SUPPORT"."EVAL_RUNS" ("ID")~');
ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."EVAL_RUNS" IS 'Append-only. Deterministic checks first, the isolated grader only for what cannot be checked mechanically (CASES_UNKNOWN = the grader said Unknown). A run against a route records the route so a quality regression is attributable (Software Architecture § 7.1).'~');
-- ---------------------------------------------------------------- GOVERNANCE_EVENTS (append-only lifecycle)
ddl(q'~CREATE TABLE "SRD_SUPPORT"."GOVERNANCE_EVENTS" (
"ID" NVARCHAR2(64) NOT NULL,
"SUBJECT_TYPE" NVARCHAR2(16) NOT NULL,
"SUBJECT_KEY" NVARCHAR2(128) NOT NULL,
"SUBJECT_VERSION" NUMBER(10) NOT NULL,
"EVENT" NVARCHAR2(16) NOT NULL,
"RESULT" NVARCHAR2(8),
"ACTOR_ID" NVARCHAR2(128) NOT NULL,
"ACTOR_ROLE" NVARCHAR2(32) NOT NULL,
"EVAL_RUN_ID" NVARCHAR2(64),
"NOTE" NVARCHAR2(2000),
"OCCURRED_ON_UTC" TIMESTAMP(6) NOT NULL,
"LEDGER_RECORD_ID" NVARCHAR2(64) NOT NULL,
CONSTRAINT "PK_GOVERNANCE_EVENTS" PRIMARY KEY ("ID"),
CONSTRAINT "FK_GOV_EVALRUN" FOREIGN KEY ("EVAL_RUN_ID") REFERENCES "SRD_SUPPORT"."EVAL_RUNS" ("ID"),
CONSTRAINT "CK_GOV_SUBJECT" CHECK ("SUBJECT_TYPE" IN ('profile','prompt','skill','eval_set','route','write_shape','memory_article','policy')),
CONSTRAINT "CK_GOV_EVENT" CHECK ("EVENT" IN ('created','linted','evaluated','approval_pending','approved','published','canary','rolled_back','retired','suspended','extended','re_approved')),
CONSTRAINT "CK_GOV_RESULT" CHECK ("RESULT" IS NULL OR "RESULT" IN ('PASSED','FAILED'))
)~');
ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_GOV_SUBJECT" ON "SRD_SUPPORT"."GOVERNANCE_EVENTS" ("SUBJECT_TYPE", "SUBJECT_KEY", "SUBJECT_VERSION", "OCCURRED_ON_UTC")~');
ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."GOVERNANCE_EVENTS" IS 'The estate lifecycle as append-only events (Agent Framework § 6.1). The publish trigger (008) requires, for the same subject and version, one evaluated/PASSED and one approved event before STATE may become published; canary = one case reviewed before default (Delivery § 4).'~');
-- ---------------------------------------------------------------- CONNECTOR_SCOPES (reach · operation · ceiling)
ddl(q'~CREATE TABLE "SRD_SUPPORT"."CONNECTOR_SCOPES" (
"ID" NVARCHAR2(64) NOT NULL,
"KIND" NVARCHAR2(16) NOT NULL,
"CONNECTOR" NVARCHAR2(32) NOT NULL,
"CUSTOMER_CODE" NVARCHAR2(32) NOT NULL,
"ENVIRONMENT" NVARCHAR2(32) NOT NULL,
"SYSTEM" NVARCHAR2(32) NOT NULL,
"OPERATION_ID" NVARCHAR2(128),
"VERSION" NUMBER(10) NOT NULL,
"INPUT_CONTRACT" CLOB,
"TARGET_IDENTITY" CLOB NOT NULL,
"EFFECT_CLASS" NVARCHAR2(16),
"AUTHORIZATION" NVARCHAR2(16),
"IDEMPOTENCY_KEY_SHAPE" NVARCHAR2(256),
"TIMEOUT_MS" NUMBER(10),
"EFFECT_OF_TIMEOUT" NVARCHAR2(16),
"RECOVERY" NVARCHAR2(16),
"EVIDENCE_RETURNED" CLOB,
"MODES" CLOB NOT NULL,
"ROUTE_KIND" NVARCHAR2(16) DEFAULT 'direct' NOT NULL,
"CREDENTIAL_REF" NVARCHAR2(256),
"CEILING" CLOB,
"HEALTH" NVARCHAR2(8) DEFAULT 'unknown' NOT NULL,
"VERIFIED_ON_UTC" TIMESTAMP(6),
"STATE" NVARCHAR2(16) NOT NULL,
"PUBLISHED_ON_UTC" TIMESTAMP(6),
"CREATED_ON_UTC" TIMESTAMP(6) NOT NULL,
"ROW_VERSION" NUMBER(19) DEFAULT 0 NOT NULL,
CONSTRAINT "PK_CONNECTOR_SCOPES" PRIMARY KEY ("ID"),
CONSTRAINT "UK_CONNECTOR_SCOPES" UNIQUE ("CUSTOMER_CODE", "CONNECTOR", "ENVIRONMENT", "SYSTEM", "KIND", "OPERATION_ID", "VERSION"),
CONSTRAINT "CK_SCOPES_KIND" CHECK ("KIND" IN ('reach','operation','ceiling')),
CONSTRAINT "CK_SCOPES_CONNECTOR" CHECK ("CONNECTOR" IN ('oracle-ipal','oracle-insis','oracle-anlt','abacus-gateway','jira','mail','hdesk','gitlab','kibana','rabbitmq','browser','bi-publisher','camunda','filesystem')),
CONSTRAINT "CK_SCOPES_OP_REQUIRED" CHECK ("KIND" <> 'operation' OR ("OPERATION_ID" IS NOT NULL AND "EFFECT_CLASS" IS NOT NULL AND "AUTHORIZATION" IS NOT NULL AND "TIMEOUT_MS" IS NOT NULL AND "EFFECT_OF_TIMEOUT" IS NOT NULL AND "RECOVERY" IS NOT NULL)),
CONSTRAINT "CK_SCOPES_EFFECT" CHECK ("EFFECT_CLASS" IS NULL OR "EFFECT_CLASS" IN ('read','transactional','compensable','irreversible','ddl')),
CONSTRAINT "CK_SCOPES_AUTHZ" CHECK ("AUTHORIZATION" IS NULL OR "AUTHORIZATION" IN ('read_grant','write_grant','gate')),
CONSTRAINT "CK_SCOPES_TIMEOUT_EFFECT" CHECK ("EFFECT_OF_TIMEOUT" IS NULL OR "EFFECT_OF_TIMEOUT" IN ('none','unknown','applied')),
CONSTRAINT "CK_SCOPES_RECOVERY" CHECK ("RECOVERY" IS NULL OR "RECOVERY" IN ('retryable','reconcile_first','never_retry')),
CONSTRAINT "CK_SCOPES_ROUTE" CHECK ("ROUTE_KIND" IN ('direct','unreachable')),
CONSTRAINT "CK_SCOPES_HEALTH" CHECK ("HEALTH" IN ('green','amber','red','unknown')),
CONSTRAINT "CK_SCOPES_STATE" CHECK ("STATE" IN ('draft','published','retired')),
CONSTRAINT "CK_SCOPES_JSON" CHECK (("INPUT_CONTRACT" IS NULL OR "INPUT_CONTRACT" IS JSON) AND "TARGET_IDENTITY" IS JSON AND ("EVIDENCE_RETURNED" IS NULL OR "EVIDENCE_RETURNED" IS JSON) AND "MODES" IS JSON AND ("CEILING" IS NULL OR "CEILING" IS JSON)),
CONSTRAINT "CK_SCOPES_WRITE_VERIFIED" CHECK ("KIND" <> 'reach' OR "ROUTE_KIND" = 'unreachable' OR "VERIFIED_ON_UTC" IS NOT NULL OR "STATE" <> 'published')
)~');
ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_SCOPES_LOOKUP" ON "SRD_SUPPORT"."CONNECTOR_SCOPES" ("CONNECTOR", "ENVIRONMENT", "KIND", "STATE")~');
ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."CONNECTOR_SCOPES" IS 'Three row kinds. reach (D46, D132): how a target is reached per environment — ROUTE_KIND direct or unreachable; the tunnel route is dropped, so TARGET_IDENTITY carries host/port/paths and CREDENTIAL_REF the Vault path; an unverified reach is never published for writes (CK_SCOPES_WRITE_VERIFIED). operation (Failure and Recovery § 4): the capability description — its absence fails closed (contract: contracts/connector-capability.schema.json). ceiling: the scope a grant can never exceed (UI › Control › Connectors).'~');
-- ---------------------------------------------------------------- CONNECTOR_HEALTH (current state; history is ledger CONNECTOR_HEALTH events)
ddl(q'~CREATE TABLE "SRD_SUPPORT"."CONNECTOR_HEALTH" (
"CONNECTOR" NVARCHAR2(32) NOT NULL,
"ENVIRONMENT" NVARCHAR2(32) NOT NULL,
"STATE" NVARCHAR2(8) NOT NULL,
"DETAIL" NVARCHAR2(1000),
"OBSERVED_ON_UTC" TIMESTAMP(6) NOT NULL,
"SINCE_UTC" TIMESTAMP(6) NOT NULL,
CONSTRAINT "PK_CONNECTOR_HEALTH" PRIMARY KEY ("CONNECTOR", "ENVIRONMENT"),
CONSTRAINT "CK_CHEALTH_STATE" CHECK ("STATE" IN ('green','amber','red'))
)~');
ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."CONNECTOR_HEALTH" IS 'Connector health as a first-class case field (Delivery § 4): a red connector parks cases (park reason connector_down) rather than failing them; red per environment × system is a minimum alert (D66).'~');
END;
/