AISA v2.0 / Technical documentation / 006-memory.sql
006-memory.sql
SQL · 136 lines · 10,352 bytes · wiki path 10 Architecture/db/SrdSupport/006-memory.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 · 005-registry.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
-- 006 · MEMORY — Agents Memory § 6 (the experience sub-index), § 7.4b (publication states), Software Architecture § Challenges
-- ("git is the store, Oracle the projection"): article bodies and AGENTS.md files live in the platform-owned git repository
-- aisa-memory; these rows are an index rebuildable from the repository (commit first, row second, reconciled at start).
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
-- ---------------------------------------------------------------- MEMORY_DOMAINS (the tree skeleton as rows — D67)
ddl(q'~CREATE TABLE "SRD_SUPPORT"."MEMORY_DOMAINS" (
"NODE_PATH" NVARCHAR2(256) NOT NULL,
"PARENT_PATH" NVARCHAR2(256),
"BRANCH" NVARCHAR2(16) NOT NULL,
"OWNER_PROFILE_KEY" NVARCHAR2(128),
"INDEX_COMMIT_SHA" NVARCHAR2(64),
"INDEX_LINES" NUMBER(10),
"ARTICLE_COUNT" NUMBER(10) DEFAULT 0 NOT NULL,
"CREATED_BY" NVARCHAR2(16) NOT NULL,
"CREATED_ON_UTC" TIMESTAMP(6) NOT NULL,
"REFRESHED_ON_UTC" TIMESTAMP(6),
"RETIRED_ON_UTC" TIMESTAMP(6),
CONSTRAINT "PK_MEMORY_DOMAINS" PRIMARY KEY ("NODE_PATH"),
CONSTRAINT "FK_MDOM_PARENT" FOREIGN KEY ("PARENT_PATH") REFERENCES "SRD_SUPPORT"."MEMORY_DOMAINS" ("NODE_PATH"),
CONSTRAINT "CK_MDOM_BRANCH" CHECK ("BRANCH" IN ('platform','connectors','configuration','source','support')),
CONSTRAINT "CK_MDOM_CREATED" CHECK ("CREATED_BY" IN ('seed','gate','summarizer','migration'))
)~');
ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."MEMORY_DOMAINS" IS 'One row per node of the memory tree (Agents Memory § 1); the AGENTS.md index of the node is at INDEX_COMMIT_SHA. OWNER_PROFILE_KEY = the single writing profile (Agents § 0.1). CREATED_BY summarizer = the auto-learning path (Agents § 0.6); gate = the manual node-creation gate.'~');
-- ---------------------------------------------------------------- MEMORY_ARTICLES (index over git; immutable once active)
ddl(q'~CREATE TABLE "SRD_SUPPORT"."MEMORY_ARTICLES" (
"ID" NVARCHAR2(64) NOT NULL,
"NODE_PATH" NVARCHAR2(256) NOT NULL,
"ARTICLE_KEY" NVARCHAR2(128) NOT NULL,
"VERSION" NUMBER(10) NOT NULL,
"KIND" NVARCHAR2(16) NOT NULL,
"TITLE" NVARCHAR2(512) NOT NULL,
"STATE" NVARCHAR2(16) NOT NULL,
"OWNER_PROFILE_KEY" NVARCHAR2(128) NOT NULL,
"GIT_SHA" NVARCHAR2(64) NOT NULL,
"CONTENT_HASH" NVARCHAR2(64) NOT NULL,
"PROVENANCE" CLOB NOT NULL,
"REACHES_UNTRUSTED" NUMBER(1) NOT NULL,
"HUMAN_REVIEWED" NUMBER(1) DEFAULT 0 NOT NULL,
"CONFIRMATION_KIND" NVARCHAR2(16),
"CONFIRMED_BY" NVARCHAR2(128),
"CONFIRMED_ON_UTC" TIMESTAMP(6),
"OBSERVED_ENVIRONMENT" NVARCHAR2(32),
"OBSERVED_VERSION" NVARCHAR2(128),
"SUPERSEDED_BY_ID" NVARCHAR2(64),
"RETIRED_ON_UTC" TIMESTAMP(6),
"RETIRED_REASON" NVARCHAR2(1000),
"PROPOSED_BY_CASE_ID" NVARCHAR2(64),
"CREATED_ON_UTC" TIMESTAMP(6) NOT NULL,
"ROW_VERSION" NUMBER(19) DEFAULT 0 NOT NULL,
CONSTRAINT "PK_MEMORY_ARTICLES" PRIMARY KEY ("ID"),
CONSTRAINT "UK_MEMORY_ARTICLES" UNIQUE ("NODE_PATH", "ARTICLE_KEY", "VERSION"),
CONSTRAINT "FK_MART_NODE" FOREIGN KEY ("NODE_PATH") REFERENCES "SRD_SUPPORT"."MEMORY_DOMAINS" ("NODE_PATH"),
CONSTRAINT "FK_MART_SUPERSEDED" FOREIGN KEY ("SUPERSEDED_BY_ID") REFERENCES "SRD_SUPPORT"."MEMORY_ARTICLES" ("ID"),
CONSTRAINT "FK_MART_CASE" FOREIGN KEY ("PROPOSED_BY_CASE_ID") REFERENCES "SRD_SUPPORT"."CASES" ("ID"),
CONSTRAINT "CK_MART_KIND" CHECK ("KIND" IN ('article','skill_source','index_line','checklist','experience')),
CONSTRAINT "CK_MART_STATE" CHECK ("STATE" IN ('proposed','active','retired')),
CONSTRAINT "CK_MART_FLAGS" CHECK ("REACHES_UNTRUSTED" IN (0,1) AND "HUMAN_REVIEWED" IN (0,1)),
CONSTRAINT "CK_MART_PROV_JSON" CHECK ("PROVENANCE" IS JSON),
CONSTRAINT "CK_MART_CONFIRM" CHECK ("CONFIRMATION_KIND" IS NULL OR "CONFIRMATION_KIND" IN ('human_review','two_uses','migration_measured')),
CONSTRAINT "CK_MART_ACTIVE" CHECK ("STATE" <> 'active' OR ("CONFIRMATION_KIND" IS NOT NULL AND "CONFIRMED_ON_UTC" IS NOT NULL))
)~');
ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_MART_NODE_STATE" ON "SRD_SUPPORT"."MEMORY_ARTICLES" ("NODE_PATH", "STATE")~');
ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_MART_GIT" ON "SRD_SUPPORT"."MEMORY_ARTICLES" ("GIT_SHA")~');
ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."MEMORY_ARTICLES" IS 'Agents Memory § 7.4b. case-local entries are papers, not rows; the tree holds proposed/active/retired only. CK_MART_ACTIVE: nothing is active without a recorded confirmation (human review, or two independent uncontradicted uses — the initial rule, D56). PROVENANCE = {cases[], profile_versions[], sources[]}; REACHES_UNTRUSTED stays marked.'~');
-- ---------------------------------------------------------------- MEMORY_USES (version pinning; the evidence for two-uses)
ddl(q'~CREATE TABLE "SRD_SUPPORT"."MEMORY_USES" (
"ARTICLE_ID" NVARCHAR2(64) NOT NULL,
"CASE_ID" NVARCHAR2(64) NOT NULL,
"TASK_ID" NVARCHAR2(64) NOT NULL,
"USED_ON_UTC" TIMESTAMP(6) NOT NULL,
"CONTRADICTED" NUMBER(1) DEFAULT 0 NOT NULL,
"CONTRADICTION_PAPER_ID" NVARCHAR2(64),
CONSTRAINT "PK_MEMORY_USES" PRIMARY KEY ("ARTICLE_ID", "CASE_ID"),
CONSTRAINT "FK_MUSE_ARTICLE" FOREIGN KEY ("ARTICLE_ID") REFERENCES "SRD_SUPPORT"."MEMORY_ARTICLES" ("ID"),
CONSTRAINT "FK_MUSE_CASE" FOREIGN KEY ("CASE_ID") REFERENCES "SRD_SUPPORT"."CASES" ("ID"),
CONSTRAINT "FK_MUSE_TASK" FOREIGN KEY ("TASK_ID") REFERENCES "SRD_SUPPORT"."TASKS" ("ID"),
CONSTRAINT "FK_MUSE_PAPER" FOREIGN KEY ("CONTRADICTION_PAPER_ID") REFERENCES "SRD_SUPPORT"."PAPERS" ("ID"),
CONSTRAINT "CK_MUSE_FLAG" CHECK ("CONTRADICTED" IN (0,1))
)~');
ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_MUSE_CASE" ON "SRD_SUPPORT"."MEMORY_USES" ("CASE_ID")~');
ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."MEMORY_USES" IS 'Version pinning (Agents Memory § 7.4b): a case records the article versions it relied on, so retiring an article lists the cases that pinned it. Two rows from distinct cases with CONTRADICTED = 0 satisfy the two-uses confirmation; one CONTRADICTED = 1 blocks it.'~');
-- ---------------------------------------------------------------- EXPERIENCE_ENTRIES (support/experience and platform/experience)
ddl(q'~CREATE TABLE "SRD_SUPPORT"."EXPERIENCE_ENTRIES" (
"ID" NVARCHAR2(64) NOT NULL,
"INDEX_NODE" NVARCHAR2(64) NOT NULL,
"CASE_ID" NVARCHAR2(64) NOT NULL,
"STATE" NVARCHAR2(16) NOT NULL,
"SYMPTOM_SIGNATURE" NVARCHAR2(1000) NOT NULL,
"SYMPTOM_NORMALISED" NVARCHAR2(1000) NOT NULL,
"SYMPTOM_HASH" NVARCHAR2(64) NOT NULL,
"SYSTEM_ERROR" NVARCHAR2(512),
"SYSTEMS" CLOB NOT NULL,
"ENVIRONMENT" NVARCHAR2(32),
"REQUEST_TYPE" NVARCHAR2(32),
"MECHANISM" NVARCHAR2(2000),
"MECHANISM_LAYER" NVARCHAR2(64),
"FIX_SHAPE" NVARCHAR2(16),
"SKILL_KEY" NVARCHAR2(128),
"LINKS" CLOB,
"ARTEFACTS" CLOB,
"OBSERVED_VERSION" NVARCHAR2(128),
"ARTICLE_ID" NVARCHAR2(64),
"CREATED_BY_PROFILE" NVARCHAR2(128) NOT NULL,
"CREATED_ON_UTC" TIMESTAMP(6) NOT NULL,
"CONFIRMED_BY" NVARCHAR2(128),
"CONFIRMED_ON_UTC" TIMESTAMP(6),
"RETIRED_ON_UTC" TIMESTAMP(6),
"ROW_VERSION" NUMBER(19) DEFAULT 0 NOT NULL,
CONSTRAINT "PK_EXPERIENCE_ENTRIES" PRIMARY KEY ("ID"),
CONSTRAINT "FK_EXP_CASE" FOREIGN KEY ("CASE_ID") REFERENCES "SRD_SUPPORT"."CASES" ("ID"),
CONSTRAINT "FK_EXP_ARTICLE" FOREIGN KEY ("ARTICLE_ID") REFERENCES "SRD_SUPPORT"."MEMORY_ARTICLES" ("ID"),
CONSTRAINT "CK_EXP_NODE" CHECK ("INDEX_NODE" IN ('support/experience','platform/experience')),
CONSTRAINT "CK_EXP_STATE" CHECK ("STATE" IN ('proposed','active','retired')),
CONSTRAINT "CK_EXP_FIX" CHECK ("FIX_SHAPE" IS NULL OR "FIX_SHAPE" IN ('data_fix','config_cell','code','answer','combination','none')),
CONSTRAINT "CK_EXP_JSON" CHECK ("SYSTEMS" IS JSON AND ("LINKS" IS NULL OR "LINKS" IS JSON) AND ("ARTEFACTS" IS NULL OR "ARTEFACTS" IS JSON))
)~');
ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_EXP_SYMPTOM" ON "SRD_SUPPORT"."EXPERIENCE_ENTRIES" ("INDEX_NODE", "STATE", "SYMPTOM_HASH")~');
ddl(q'~CREATE INDEX "SRD_SUPPORT"."IX_EXP_CASE" ON "SRD_SUPPORT"."EXPERIENCE_ENTRIES" ("CASE_ID")~');
ddl(q'~COMMENT ON TABLE "SRD_SUPPORT"."EXPERIENCE_ENTRIES" IS 'Agents Memory § 6 (contract: contracts/drafts/experience-entry.schema.json): symptom signature verbatim + normalised (lower-cased, identifiers → handles, digits collapsed — the initial normalisation; depth is a class-C measurement) + its hash for the exact lookup; a grep index over SYMPTOM_NORMALISED is the first retrieval, vector search only if it beats the measured baseline. No PII by construction: identifiers are already handles at the connector.'~');
END;
/