☰ Contents
AISA v2.0 / Technical documentation / Data Model — `SRD_SUPPORT`

Data Model — SRD_SUPPORT

F verified factP decided planC open challenge

P Case journey persistence: the execution-obligations paper uses PAPERS.KIND=artefact and its schema id; PLAN_REVISIONS/PLAN_STEPS, TASKS, RUNTIME_CALLS and assertion evidence remain authoritative. Waiting/reported step states are paper fields projected to existing parked tasks, not new Oracle CHECK values. OUTBOX_MESSAGES atomically queues post-result work with resolution; the dedup key is canonical request + verified result revision. Extended experience context lives in its immutable paper, referenced from EXPERIENCE_ENTRIES.ARTEFACTS; existing columns remain searchable projections and richer indexes are rebuildable. No DDL change is needed. See Case journeys.

The schema the platform owns, written as the versioned DbScripts/SrdSupport/ set in the estate's own pattern (baseline → numbered upgrades → guards → seed → verify — Software Architecture § 9.2). It is the concrete form of Architecture § 3.1: every table named there, plus the tables the other pages imply, each with its constraint set and the rule it enforces. P The scripts are the phase-1 deliverable for track 1 (Backend core, Delivery § 1.3); they run as the single SRD_SUPPORT user (D47) on the QA IPAL database (D30). P Nothing here has run against a database yet: SRD_SUPPORT does not exist (DB-7) and the workspace is read-only; the scripts are linted offline (lint_ddl.py — 50 tables, 26 unique guards, every foreign key in dependency order) and every rule they carry is cited to its page.

0. The scripts

Script Creates Carries
install-clean.sql — drops everything the schema owns, then runs upgrade.sql; refuses to run as any other user
upgrade.sql — the numbered chain, in dependency order; a change is a new file appended, never an edit
001-work-and-planning.sql CASES SESSIONS TASKS TURN_CHECKPOINTS CASE_IMPACT_KEYS CASE_LINKS ARRIVALS PLANS PLAN_REVISIONS PLAN_STEPS the case state table (Agent Runtime § 11.1), the park taxonomy, the two clocks, impact keys (PG-20), the origin key dedup checks first
002-decisions-and-grants.sql WRITE_SHAPES GATES GATE_DECISIONS GRANTS RISK_ACCEPTANCES HELD_BATCHES HELD_BATCH_ITEMS WRITE_LEASES a gate is a row (Gating § 9.2); a policy decision is the same row with a shape (D70); a grant is bound to an artefact hash; the N-12 register as a table; shape expiry with one extension of at most a day (D133)
003-evidence.sql PAPERS WRITE_LOG BUILD_STATE MISSING PLSQL_SNAPSHOTS CASE_KEYS CASE_HANDLES papers as immutable revision rows; the write log with WRITE_PATH (D136) and never a result row; the PL/SQL signature as a write precondition; handles as AES-GCM ciphertext under a per-case key — destroying the key shreds every value
004-ledger.sql AUDIT_LEDGER LEDGER_SEALS LEDGER_EXPORTS COST_LEDGER the chain as a constraint: PREV_HASH unique and required to equal an existing RECORD_HASH (or the genesis) — no two records can claim one predecessor, no record can sit beside the chain; seals chained too; exports as attempts
005-registry.sql PROMPTS MODEL_ROUTES PROFILES SKILLS EVAL_SETS EVAL_RUNS GOVERNANCE_EVENTS CONNECTOR_SCOPES CONNECTOR_HEALTH the profile field for field (Agents § 0.1); one writing profile per domain as a unique index; the data boundary as columns of the route; the capability description as a row whose absence fails closed; reach rows direct or unreachable (D132)
006-memory.sql MEMORY_DOMAINS MEMORY_ARTICLES MEMORY_USES EXPERIENCE_ENTRIES git is the store, these rows the projection; nothing is active without a recorded confirmation; version pinning as rows; the symptom-signature lookup
007-policy-and-operations.sql POLICY_VALUES CASE_TYPES ROLE_ASSIGNMENTS OUTBOX_MESSAGES RETENTION_ACTIONS the Whitelabel Catalogue as rows with a resolution order; case types carrying their predicted minutes (D135); roles as rows with their source (D134); the outbox; gated retention actions (D109)
008-immutability-and-payload-guards.sql 23 triggers append-only tables; the chain guard; published versions immutable; publish needs a PASSED evaluation and an approval; a red eval set blocks; a Support profile binds only an EU, DPA-covered route; approved templates immutable; the one-day extension; keys destroyed once
009-seed-platform-policy.sql 52 rows the platform-scope defaults of Catalogue § 5 / § 5b — budget.dimension = minutes, the 80/100 thresholds, writes.shape_expiry_default = P90D, connectors.route_kinds = [direct, unreachable], contracts.inter_stage.mode = draft
grant-runtime.sql — D47: one user, so only a reporting role receives SELECT — on projections, never on handles, keys or papers
verify-schema.sql · verify-ledger.sql — the structural acceptance; the chain check of Architecture § 3.2: continuity, seal integrity, export currency (two intervals late = failure)
acceptance-install-clean.sql · acceptance-upgrade.sql — the CI entry points, as in SrdAi
lint_ddl.py — the offline check: quoting, one PL/SQL unit per /, every REFERENCES after its CREATE, trigger targets exist, unique names, verify-schema inventory complete

Conventions (stated once in 001): ids are opaque NVARCHAR2(64) strings the service issues, so one id is the same in the ledger, the SignalR ticket and the papers volume; time is TIMESTAMP(6) UTC with the _UTC suffix; hashes are lower-case hex SHA-256 over the SERDICA-JCS-1 canonical form; JSON columns are CLOB … IS JSON and each has a contract under Contracts; budget columns are minutes (D135); engine states are closed CHECK lists. Modules extend business outcomes and case types through policy/validated payloads; adding an engine state requires a runtime/schema version.

0.1 Runtime storage added for implementation

P 010-runtime-execution.sql completes the durable execution mapping. The baseline's 47 tables become 50 tables; the new tables represent required runtime state, not new product functions.

Storage Purpose and invariant
RUNTIME_CALLS one model/tool operation identity per task/turn/ordinal; immutable request hash/reference; dispatch and terminal/unknown outcome; exact result reference; stable idempotency key
INBOX_MESSAGES message id and payload hash committed with the receiving transition; same id/different bytes is refused
IMPACT_LOCKS current exclusive claims for platform writes; an uncertain effect keeps its claim after worker death
TASKS.LEASE_OWNER / LEASE_UNTIL_UTC runnable-task lease beside the fencing epoch and row version
TURN_CHECKPOINTS.STATE_PAPER_ID immutable, retrievable state paper; STATE_HASH verifies its bytes
GRANTS.CONSUMED_BY_CALL_ID reserves one execution grant for one journal call; recovery cannot spend it on another action
PROFILES.EXECUTION_KIND / HANDLER_KEY distinguish a model profile from a registered deterministic handler; only model profiles require prompt/route rows
CONNECTOR_SCOPES.EXECUTION_FORM A collapsed, B deterministic lease, E external/DDL effect; maps the capability's legacy write_shape field
GATE_DECISIONS.DECISION_POLICY_REF policy version for non-write checkpoints; effect decisions additionally require the shape and risk references at service validation
RISK_ACCEPTANCES can represent W1–W7; no acceptances seeded; ordinary policy rows cannot authorise them
COST_LEDGER.USAGE_STATUS missing token measurements remain NULL/unknown rather than fabricated zero

P Restore and replay are specified in Runtime §§ 2,4,7. Schema checks enforce structural constraints; role eligibility, current risk acceptance, request hashes, state transitions and typed payloads are also checked by the service. The offline linter does not prove Oracle trigger behaviour. An authorised disposable-schema install/upgrade and negative DML suite remain acceptance prerequisites.

P A pre-existing hash-only checkpoint cannot be backfilled by inventing state. The upgrade intentionally refuses NOT NULL conversion if such rows exist; supply an explicit recovery/migration procedure or finish those cases on the old runtime first. Papers are staged to durable storage before their metadata transaction; orphan staged files are recoverable cleanup, while committed metadata must never point to missing bytes.

1. The picture

Four focused relationship maps show the records needed to run, govern, prove and improve a case. They are not exhaustive foreign-key diagrams; click a record group for its defining SQL, and use the table catalogue for the full schema.

Work and planning (001) — a case, what runs it, what it declares, what it plans:

flowchart TB
  A["ARRIVALS"]:::store
  C["CASES"]:::store
  T["TASKS"]:::store
  R["RUNTIME_CALLS"]:::store
  CP["TURN_CHECKPOINTS"]:::store
  P["PLANS and revisions"]:::store
  S["SESSIONS"]:::store
  Q["INBOX_MESSAGES"]:::store
  A -->|"opens or merges"| C
  C -->|"owns work"| T
  C -->|"owns plan"| P
  C -->|"view and control"| S
  T -->|"journals calls"| R
  T -->|"resumes from"| CP
  T -.->|"durable messages"| Q
  classDef work fill:#eef4ff,stroke:#6889ba,color:#17365b,stroke-width:1.3px
  classDef decision fill:#fff4df,stroke:#b78c36,color:#65470d,stroke-width:1.5px
  classDef proof fill:#e9f5ef,stroke:#689b81,color:#224e39,stroke-width:1.3px
  classDef learn fill:#f1edf9,stroke:#9580b9,color:#534172,stroke-width:1.3px
  classDef store fill:#f5f7fa,stroke:#98a6b7,color:#34445a,stroke-width:1.2px
  classDef owner fill:#24486b,stroke:#24486b,color:#ffffff,stroke-width:1.4px
  click A href "db/srdsupport/001-work-and-planning.sql.html" "Canonical origin and source aliases."
  click C href "db/srdsupport/001-work-and-planning.sql.html" "The requested outcome, state and clocks."
  click T href "db/srdsupport/001-work-and-planning.sql.html" "Parent/profile identity and scheduler state."
  click R href "db/srdsupport/010-runtime-execution.sql.html" "Durable intent, results and unknown-effect reconciliation."
  click CP href "db/srdsupport/010-runtime-execution.sql.html" "References retrievable state bytes."
  click P href "db/srdsupport/001-work-and-planning.sql.html" "Approved scope and stable steps."
  click S href "db/srdsupport/001-work-and-planning.sql.html" "Control and viewing; case work outlives a session."
  click Q href "db/srdsupport/010-runtime-execution.sql.html" "State-changing messages are durable and deduplicated."

Work and recovery. These are responsibility/reference maps, not a claim that every drawn edge is a foreign key. Click a record for its defining SQL; the full table catalogue remains below.

Decisions and grants (002) — the gates, who answered, what a shape permits, what a grant covers:

flowchart TB
  C["CASES"]:::store
  G["GATES"]:::store
  D["GATE_DECISIONS"]:::store
  P["GRANTS"]:::store
  W["WRITE_SHAPES"]:::store
  R["RISK_ACCEPTANCES"]:::store
  L["IMPACT_LOCKS"]:::store
  H["HELD_BATCHES"]:::store
  C -->|"requests"| G
  G -->|"records"| D
  D -->|"authorises exact scope"| P
  W -.->|"eligible shape"| G
  R -.->|"required acceptance"| G
  P -->|"execution claim"| L
  D -->|"may hold work"| H
  classDef work fill:#eef4ff,stroke:#6889ba,color:#17365b,stroke-width:1.3px
  classDef decision fill:#fff4df,stroke:#b78c36,color:#65470d,stroke-width:1.5px
  classDef proof fill:#e9f5ef,stroke:#689b81,color:#224e39,stroke-width:1.3px
  classDef learn fill:#f1edf9,stroke:#9580b9,color:#534172,stroke-width:1.3px
  classDef store fill:#f5f7fa,stroke:#98a6b7,color:#34445a,stroke-width:1.2px
  classDef owner fill:#24486b,stroke:#24486b,color:#ffffff,stroke-width:1.4px
  click C href "db/srdsupport/001-work-and-planning.sql.html" "The current objective and target scope."
  click G href "db/srdsupport/002-decisions-and-grants.sql.html" "Exact packet and required decision role."
  click D href "db/srdsupport/002-decisions-and-grants.sql.html" "Person/policy verdict bound to a revision and hash."
  click P href "db/srdsupport/010-runtime-execution.sql.html" "Current target scope, expiry, reserved call and fence."
  click W href "db/srdsupport/002-decisions-and-grants.sql.html" "Approved operation shapes and clean-instance evidence."
  click R href "db/srdsupport/010-runtime-execution.sql.html" "Organisational risk acceptance, never an inferred success benefit."
  click L href "db/srdsupport/010-runtime-execution.sql.html" "Claims held through execution and uncertain effects."
  click H href "db/srdsupport/002-decisions-and-grants.sql.html" "Approved but unapplied items retain age and per-item preflight."

Decisions and execution authority. A published skill, successful case or learned article does not issue a grant; current scope and risk decisions are checked separately.

Evidence and ledger (003, 004) — the papers, the write log, the handles, the chain:

flowchart TB
  C["CASES"]:::store
  P["PAPERS"]:::store
  W["WRITE_LOG"]:::store
  B["BUILD_STATE"]:::store
  M["MISSING"]:::store
  K["CASE_KEYS and handles"]:::store
  A["AUDIT_LEDGER"]:::store
  S["Seals and exports"]:::store
  C -->|"retains bytes"| P
  C -->|"records effects"| W
  C -->|"resumes work"| B
  C -->|"states gaps"| M
  C -->|"protects identity"| K
  P -.->|"hash references"| A
  W -.->|"effect events"| A
  A -->|"seal and export"| S
  classDef work fill:#eef4ff,stroke:#6889ba,color:#17365b,stroke-width:1.3px
  classDef decision fill:#fff4df,stroke:#b78c36,color:#65470d,stroke-width:1.5px
  classDef proof fill:#e9f5ef,stroke:#689b81,color:#224e39,stroke-width:1.3px
  classDef learn fill:#f1edf9,stroke:#9580b9,color:#534172,stroke-width:1.3px
  classDef store fill:#f5f7fa,stroke:#98a6b7,color:#34445a,stroke-width:1.2px
  classDef owner fill:#24486b,stroke:#24486b,color:#ffffff,stroke-width:1.4px
  click C href "db/srdsupport/001-work-and-planning.sql.html" "One objective owns its evidence."
  click P href "db/srdsupport/003-evidence.sql.html" "Immutable request, plan, evidence and result bytes."
  click W href "db/srdsupport/003-evidence.sql.html" "What the platform actually executed."
  click B href "db/srdsupport/003-evidence.sql.html" "Verified steps and returned identifiers."
  click M href "db/srdsupport/003-evidence.sql.html" "Explicit unresolved or accepted gaps."
  click K href "db/srdsupport/003-evidence.sql.html" "Protected identifier mapping, separate from model context."
  click A href "db/srdsupport/004-ledger.sql.html" "Append-only events and record hashes."
  click S href "db/srdsupport/004-ledger.sql.html" "Independently retained integrity and recovery evidence."

Evidence before a completion claim. Paper bodies, execution evidence and the audit index have different jobs; a hash without its bytes cannot restore a case.

Registry, memory and policy (005–007) — what a profile is made of, what proves it, what it remembers, what expires:

flowchart TB
  P["PROFILES"]:::learn
  I["PROMPTS and routes"]:::learn
  S["SKILLS"]:::learn
  E["EVAL_SETS and runs"]:::learn
  G["GOVERNANCE_EVENTS"]:::learn
  D["MEMORY_DOMAINS"]:::learn
  A["MEMORY_ARTICLES"]:::learn
  U["MEMORY_USES"]:::learn
  X["EXPERIENCE_ENTRIES"]:::learn
  I -->|"configures"| P
  S -->|"declared tools"| P
  P -->|"evaluated"| E
  E -->|"publication evidence"| G
  D -->|"owns"| A
  X -.->|"proposes learning"| A
  A -->|"records use"| U
  U -.->|"regression evidence"| E
  A -.->|"compatible context"| P
  classDef work fill:#eef4ff,stroke:#6889ba,color:#17365b,stroke-width:1.3px
  classDef decision fill:#fff4df,stroke:#b78c36,color:#65470d,stroke-width:1.5px
  classDef proof fill:#e9f5ef,stroke:#689b81,color:#224e39,stroke-width:1.3px
  classDef learn fill:#f1edf9,stroke:#9580b9,color:#534172,stroke-width:1.3px
  classDef store fill:#f5f7fa,stroke:#98a6b7,color:#34445a,stroke-width:1.2px
  classDef owner fill:#24486b,stroke:#24486b,color:#ffffff,stroke-width:1.4px
  click P href "db/srdsupport/005-registry.sql.html" "Published role and task configuration."
  click I href "db/srdsupport/005-registry.sql.html" "Instruction and model selection are versioned separately."
  click S href "db/srdsupport/005-registry.sql.html" "Executable procedures with declared operations and assertions."
  click E href "db/srdsupport/005-registry.sql.html" "Changes are tested with pinned dependencies before publication."
  click G href "db/srdsupport/005-registry.sql.html" "Who approved and published each version."
  click D href "db/srdsupport/006-memory.sql.html" "One canonical owner per domain."
  click A href "db/srdsupport/006-memory.sql.html" "Proposed, active and retired revisions with provenance."
  click U href "db/srdsupport/006-memory.sql.html" "Which independent request used which version, and its verdict."
  click X href "db/srdsupport/006-memory.sql.html" "Actual case outcomes, mechanisms and reusable evidence."

The trainable part is versioned and tested. Experience, active knowledge, executable skills and authority remain distinct records. Edges show meaningful references and evaluated dependencies; click through for the SQL definition.

CONNECTOR_SCOPES, CONNECTOR_HEALTH, ROLE_ASSIGNMENTS and OUTBOX_MESSAGES stand alone by design: connectors know no case (Software Architecture § 8 rule 2), roles are rechecked before protected actions, and inbox/outbox delivery is committed with the associated case transition.

2. What the guards make true

P These are the intended schema guardrails, pending live installation and adversarial DML acceptance — the reason the schema, not the service, carries them (Architecture § 3.2, "the service is the thing being audited"):

Rule Where it was stated How the schema holds it
The ledger is append-only and hash-chained; a mistake is a CORRECTION record Architecture § 3.2 AUDIT_LEDGER_IMM_TRG; UK_LEDGER_PREV_HASH + AUDIT_LEDGER_CHAIN_TRG (genesis once, predecessor must exist, a correction must name its target)
The ledger holds statements and counts, never result rows or payloads Trust and Data § 5 REQUESTED_ACTION/ACTUAL_ACTION bounded text, PAYLOAD_HASH only; WRITE_LOG likewise; CK_LEDGER_WRITE_COUNT
Nothing publishes with a red eval set; activation needs a passed evaluation and an approval; published versions are immutable Agent Framework § 6.1, § 6.5 PROMPTS_GOV_TRG, SKILLS_GOV_TRG, PROFILES_GOV_TRG read GOVERNANCE_EVENTS and EVAL_SETS.LAST_RESULT
A Support profile runs only on EU-resident, DPA-covered routes Agents § 0.3, SG-9 PROFILES_GOV_TRG on MODEL_ROUTES.DATA_ZONE/DPA_COVERED; COST_LEDGER.DATA_ZONE records every call
One domain has exactly one writing profile Agents § 0.1 one OWNER_PROFILE_KEY per MEMORY_DOMAINS node, checked at publication and write dispatch; a profile can own several explicitly declared nodes
An approved write shape is immutable; three clean instances precede approval; one revert suspends; a suspended shape returns only through a new HW-approve Gating § 4, § 7, § 9.3 WRITE_SHAPES_GOV_TRG (-20060…-20063)
A shape expires on a default and an approver may extend it once, by at most one day D133 CK_SHAPES_EXTENSION, WRITE_SHAPES_GOV_TRG (-20064/-20065), writes.shape_expiry_default, writes.shape_extension_max
W1–W7 eligibility requires recorded risk acceptance; no acceptance is seeded; the register is withdraw-only N-12, Gating § 3 CK_RISK_WCLASS_V2, RISK_ACCEPTANCES_IMM_TRG; service checks current role/scope
A policy-confirmed decision is the same row with the policy as actor and the shape named Gating § 5, D70 CK_GDEC_POLICY_REF_V2, GATE_DECISIONS_IMM_TRG; service additionally binds effect decisions to shapes
A write grant is bound to an artefact; nothing executes without a grant Trust and Data § 2 CK_GRANTS_BOUND, CK_WLOG_GRANTED
A skill that writes carries its dry-run default and its teardown Agent Framework § 6.6 CK_SKILLS_WRITE_SAFETY
Nothing is active in memory without a recorded confirmation; active articles are immutable Agents Memory § 7.4b CK_MART_ACTIVE, MEMORY_ARTICLES_GOV_TRG, MEMORY_USES_IMM_TRG
Identifiers never rest in the database in clear; destruction is the key Trust and Data § 4, Software Architecture § Challenges CASE_HANDLES.VALUE_ENC BLOB + CASE_KEYS; CASE_HANDLES_IMM_TRG, CASE_KEYS_GOV_TRG
Reach is direct or unreachable; an unverified reach never publishes for writes D132, Software Architecture § 10.3 CK_SCOPES_ROUTE, CK_SCOPES_WRITE_VERIFIED
Manual retention actions are gated writes D109, PG-13 CK_RET_MANUAL_GATED

3. What is here beyond Architecture § 3.1, and why

P § 3.1 names 25 tables. The baseline scripts create 47, and upgrade 010 adds three runtime tables. The baseline's 22 additions are each the durable form of something another page already required as state:

Added Required by
TURN_CHECKPOINTS the turn record a resumed worker continues from (Agent Runtime § 1, § 2 rule 4)
CASE_IMPACT_KEYS · CASE_LINKS · ARRIVALS impact keys (PG-20); the ticket system as system of record (N-11); the Inbox before a case exists (Agents § 5c)
RISK_ACCEPTANCES "the register" of N-12 — the only way a class leaves the always-human set
HELD_BATCH_ITEMS · WRITE_LEASES ordinal preflight of held batches (D66); write shape B (Failure and Recovery § 1)
CASE_KEYS the per-case data key the handles are encrypted under
LEDGER_SEALS · LEDGER_EXPORTS the seal and the off-database copy (D45, D66)
MODEL_ROUTES · EVAL_RUNS · GOVERNANCE_EVENTS · CONNECTOR_HEALTH governed route selection (Agents § 0.3); the eval runner; the lifecycle events publishing depends on; health as a case field (Delivery § 4)
MEMORY_DOMAINS · MEMORY_USES · EXPERIENCE_ENTRIES the tree skeleton as rows (D67); version pinning; the experience sub-index (Agents Memory § 6, § 7.4b)
POLICY_VALUES · CASE_TYPES · ROLE_ASSIGNMENTS · OUTBOX_MESSAGES · RETENTION_ACTIONS the Catalogue as data (CR-10, D51); case types (Catalogue § 6); roles as rows (Software Architecture § 10.7); the outbox; the retention evaluator (D109)

Not modelled as tables, deliberately: consults and handovers are TASKS rows of that kind plus, for a handover, a CASES row with PARENT_CASE_ID and an Hd gate; alerts are ledger events (D66); the kill switch is versioned live control policy checked at admission and effect dispatch (Software Architecture § 10.5); paper access is a PAPER_ACCESS ledger event, not a second audit table.

4. Running it

sql SRD_SUPPORT/***@db.serdicaqa.bulstrad.bg:1521:orcl1
SQL> @install-clean.sql        -- first time, or to rebuild an empty schema
SQL> @upgrade.sql              -- every later change; twice-runnable
SQL> @verify-schema.sql
SQL> @verify-ledger.sql

P In the repository the set lives at src/Serdica/Ablera.Serdica.AI.Support/__Libraries/Ablera.Serdica.AI.Support.Persistence/DbScripts/SrdSupport/ (Software Architecture § 0); the copy here is the architecture's, and the two are kept identical by the same CI lint that runs lint_ddl.py. The EF model (SupportDbContext) is generated from these scripts, not the other way round — the estate's SrdAi order.

Challenges

PreviousModules and their runtime declarationsNextContracts