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;
/
