packages/persistence/src/hosted-migrations.ts · aryaamin/openclasp

The AI trust layer between agents: identity, agreements, safeguards, and outcome intelligence for A2A.

Viewing files at commit 20744dd65349Commit details View latest

packages/persistence/src/hosted-migrations.ts 492 lines · 18.1 KB · typescript

import type { NeonQueryFunction } from '@neondatabase/serverless';

type Sql = NeonQueryFunction<false, false>;

export const HOSTED_MIGRATIONS = [
  { version: 1, name: 'hosted_baseline' },
  { version: 2, name: 'append_only_source_records' },
  { version: 3, name: 'remove_hosted_conversations' },
  { version: 4, name: 'adaptive_assurance_probes' },
  { version: 5, name: 'assurance_decision_learning' },
] as const;

async function applyBaseline(sql: Sql) {
  await sql`
    CREATE TABLE IF NOT EXISTS openclasp_records (
      operator_id TEXT NOT NULL,
      kind TEXT NOT NULL,
      record_id TEXT NOT NULL,
      payload JSONB NOT NULL,
      created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
      updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
      PRIMARY KEY (operator_id, kind, record_id)
    )
  `;
  await sql`
    CREATE INDEX IF NOT EXISTS openclasp_records_operator_created
    ON openclasp_records(operator_id, created_at DESC)
  `;
  await sql`
    CREATE TABLE IF NOT EXISTS openclasp_account_settings (
      operator_id TEXT PRIMARY KEY,
      display_name TEXT NOT NULL DEFAULT '',
      contribution_enabled BOOLEAN NOT NULL DEFAULT FALSE,
      retention_days INTEGER NOT NULL DEFAULT 30 CHECK (retention_days BETWEEN 0 AND 3650),
      evidence_sharing TEXT NOT NULL DEFAULT 'ask'
        CHECK (evidence_sharing IN ('never', 'ask', 'contract_only')),
      updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
    )
  `;
  await sql`
    CREATE TABLE IF NOT EXISTS openclasp_public_agents (
      agent_id TEXT PRIMARY KEY,
      operator_id TEXT NOT NULL,
      card JSONB NOT NULL,
      published_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
      updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
    )
  `;
  await sql`
    CREATE INDEX IF NOT EXISTS openclasp_public_agents_updated
    ON openclasp_public_agents(updated_at DESC)
  `;
  await sql`
    CREATE TABLE IF NOT EXISTS openclasp_federated_interactions (
      interaction_id TEXT PRIMARY KEY,
      initiator_operator_id TEXT NOT NULL,
      responder_operator_id TEXT NOT NULL,
      initiator_agent_id TEXT NOT NULL,
      responder_agent_id TEXT NOT NULL,
      status TEXT NOT NULL,
      payload JSONB NOT NULL,
      expires_at TIMESTAMPTZ NOT NULL,
      created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
      updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
    )
  `;
  await sql`
    CREATE INDEX IF NOT EXISTS openclasp_federated_interactions_participants
    ON openclasp_federated_interactions(
      initiator_operator_id,
      responder_operator_id,
      updated_at DESC
    )
  `;
  await sql`
    CREATE TABLE IF NOT EXISTS openclasp_agent_runtimes (
      agent_id TEXT PRIMARY KEY,
      operator_id TEXT NOT NULL,
      endpoint TEXT NOT NULL,
      secret_ciphertext TEXT NOT NULL,
      secret_iv TEXT NOT NULL,
      secret_auth_tag TEXT NOT NULL,
      status TEXT NOT NULL CHECK (status IN ('verified', 'disabled')),
      verified_at TIMESTAMPTZ NOT NULL,
      last_delivery_at TIMESTAMPTZ,
      last_error TEXT,
      created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
      updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
    )
  `;
  await sql`
    CREATE TABLE IF NOT EXISTS openclasp_agent_access_tokens (
      token_id TEXT PRIMARY KEY,
      operator_id TEXT NOT NULL,
      agent_id TEXT NOT NULL,
      name TEXT NOT NULL,
      token_hash TEXT NOT NULL,
      scopes JSONB NOT NULL,
      expires_at TIMESTAMPTZ NOT NULL,
      last_used_at TIMESTAMPTZ,
      revoked_at TIMESTAMPTZ,
      created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
    )
  `;
  await sql`
    CREATE INDEX IF NOT EXISTS openclasp_agent_access_tokens_owner_agent
    ON openclasp_agent_access_tokens(operator_id, agent_id, created_at DESC)
  `;
  await sql`
    ALTER TABLE openclasp_agent_runtimes
    ADD COLUMN IF NOT EXISTS a2a_endpoint TEXT,
    ADD COLUMN IF NOT EXISTS last_seen_at TIMESTAMPTZ
  `;
  await sql`
    CREATE TABLE IF NOT EXISTS openclasp_live_sessions (
      interaction_id TEXT PRIMARY KEY,
      initiator_agent_id TEXT NOT NULL,
      responder_agent_id TEXT NOT NULL,
      initiator_session_id TEXT NOT NULL,
      responder_session_id TEXT NOT NULL,
      initiator_endpoint TEXT NOT NULL,
      responder_endpoint TEXT NOT NULL,
      status TEXT NOT NULL CHECK (status IN ('preparing', 'active', 'completed', 'failed')),
      expires_at TIMESTAMPTZ NOT NULL,
      created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
      activated_at TIMESTAMPTZ,
      completed_at TIMESTAMPTZ,
      last_error TEXT
    )
  `;
  await sql`
    CREATE TABLE IF NOT EXISTS openclasp_live_session_events (
      interaction_id TEXT NOT NULL,
      event_id TEXT NOT NULL,
      agent_id TEXT NOT NULL,
      sequence INTEGER NOT NULL,
      event JSONB NOT NULL,
      created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
      PRIMARY KEY (interaction_id, event_id),
      UNIQUE (interaction_id, agent_id, sequence)
    )
  `;
  await sql`
    CREATE TABLE IF NOT EXISTS openclasp_hosted_threads (
      thread_id TEXT PRIMARY KEY,
      interaction_id TEXT NOT NULL UNIQUE,
      participant_a_agent_id TEXT NOT NULL,
      participant_b_agent_id TEXT NOT NULL,
      status TEXT NOT NULL CHECK (status IN ('open', 'closed')) DEFAULT 'open',
      created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
      updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
      expires_at TIMESTAMPTZ NOT NULL
    )
  `;
  await sql`
    CREATE INDEX IF NOT EXISTS openclasp_hosted_threads_participants
    ON openclasp_hosted_threads(
      participant_a_agent_id,
      participant_b_agent_id,
      updated_at DESC
    )
  `;
  await sql`
    CREATE TABLE IF NOT EXISTS openclasp_hosted_messages (
      message_id TEXT PRIMARY KEY,
      thread_id TEXT NOT NULL REFERENCES openclasp_hosted_threads(thread_id) ON DELETE CASCADE,
      interaction_id TEXT NOT NULL,
      sender_agent_id TEXT NOT NULL,
      recipient_agent_id TEXT NOT NULL,
      request_key TEXT NOT NULL,
      content_ciphertext TEXT NOT NULL,
      content_iv TEXT NOT NULL,
      content_auth_tag TEXT NOT NULL,
      content_hash TEXT NOT NULL,
      delivery TEXT NOT NULL CHECK (delivery IN ('accepted', 'delivered', 'read')),
      created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
      read_at TIMESTAMPTZ
    )
  `;
  await sql`
    CREATE UNIQUE INDEX IF NOT EXISTS openclasp_hosted_messages_dedup
    ON openclasp_hosted_messages(thread_id, sender_agent_id, request_key)
  `;
  await sql`
    CREATE INDEX IF NOT EXISTS openclasp_hosted_messages_thread_created
    ON openclasp_hosted_messages(thread_id, created_at ASC)
  `;
}

async function applyAppendOnlySourceRecords(sql: Sql) {
  await sql`
    CREATE TABLE IF NOT EXISTS openclasp_source_records (
      event_id UUID PRIMARY KEY,
      operator_id TEXT NOT NULL,
      kind TEXT NOT NULL,
      record_id TEXT NOT NULL,
      schema_name TEXT NOT NULL,
      schema_version TEXT NOT NULL,
      payload JSONB NOT NULL,
      payload_digest TEXT NOT NULL,
      entity_refs JSONB NOT NULL DEFAULT '{}'::jsonb,
      provenance TEXT NOT NULL,
      visibility TEXT NOT NULL,
      retention_class TEXT NOT NULL,
      learning_scope TEXT NOT NULL,
      reported_at TIMESTAMPTZ NOT NULL,
      ingested_at TIMESTAMPTZ NOT NULL,
      UNIQUE (operator_id, kind, record_id, payload_digest),
      CHECK (provenance IN (
        'self_declared',
        'operator_attested',
        'cryptographically_verified',
        'domain_verified',
        'third_party_verified',
        'observed',
        'disputed'
      )),
      CHECK (visibility IN (
        'local_only',
        'private_requester',
        'private_responder',
        'shared_participants',
        'network_aggregate'
      )),
      CHECK (retention_class IN ('account', 'audit', 'operational', 'temporary')),
      CHECK (learning_scope IN (
        'not_evaluated',
        'excluded',
        'local_only',
        'network_aggregate'
      ))
    )
  `;
  await sql`
    CREATE INDEX IF NOT EXISTS openclasp_source_records_operator_ingested
    ON openclasp_source_records(operator_id, ingested_at DESC)
  `;
  await sql`
    CREATE INDEX IF NOT EXISTS openclasp_source_records_history
    ON openclasp_source_records(operator_id, kind, record_id, ingested_at ASC)
  `;
  await sql`
    CREATE INDEX IF NOT EXISTS openclasp_source_records_entities
    ON openclasp_source_records USING GIN(entity_refs)
  `;
}

async function removeHostedConversations(sql: Sql) {
  await sql`DROP TABLE IF EXISTS openclasp_hosted_messages`;
  await sql`DROP TABLE IF EXISTS openclasp_hosted_threads`;
}

async function addAdaptiveAssuranceProbes(sql: Sql) {
  await sql`
    CREATE TABLE IF NOT EXISTS openclasp_ai_generations (
      generation_id UUID PRIMARY KEY,
      operator_id TEXT NOT NULL,
      interaction_id TEXT NOT NULL,
      phase TEXT NOT NULL CHECK (phase IN ('pre_task', 'post_task')),
      model TEXT NOT NULL,
      prompt_version TEXT NOT NULL,
      status TEXT NOT NULL CHECK (status IN ('pending', 'complete', 'fallback', 'error')),
      input JSONB NOT NULL,
      input_digest TEXT NOT NULL,
      output JSONB,
      token_usage JSONB,
      error_code TEXT,
      started_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
      completed_at TIMESTAMPTZ
    )
  `;
  await sql`
    CREATE INDEX IF NOT EXISTS openclasp_ai_generations_interaction
    ON openclasp_ai_generations(operator_id, interaction_id, started_at DESC)
  `;
  await sql`
    CREATE TABLE IF NOT EXISTS openclasp_assurance_probe_plans (
      plan_id UUID PRIMARY KEY,
      generation_id UUID NOT NULL REFERENCES openclasp_ai_generations(generation_id),
      operator_id TEXT NOT NULL,
      interaction_id TEXT NOT NULL,
      contract_hash TEXT NOT NULL,
      phase TEXT NOT NULL CHECK (phase IN ('pre_task', 'post_task')),
      generated_for_agent_id TEXT NOT NULL,
      target_agent_id TEXT NOT NULL,
      payload JSONB NOT NULL,
      created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
      expires_at TIMESTAMPTZ NOT NULL
    )
  `;
  await sql`
    CREATE INDEX IF NOT EXISTS openclasp_assurance_probe_plans_interaction
    ON openclasp_assurance_probe_plans(interaction_id, phase, created_at DESC)
  `;
  await sql`
    CREATE TABLE IF NOT EXISTS openclasp_assurance_probe_responses (
      response_id UUID PRIMARY KEY,
      plan_id UUID NOT NULL REFERENCES openclasp_assurance_probe_plans(plan_id),
      operator_id TEXT NOT NULL,
      interaction_id TEXT NOT NULL,
      contract_hash TEXT NOT NULL,
      phase TEXT NOT NULL CHECK (phase IN ('pre_task', 'post_task')),
      agent_id TEXT NOT NULL,
      payload JSONB NOT NULL,
      created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
      UNIQUE(plan_id, agent_id)
    )
  `;
  await sql`
    CREATE INDEX IF NOT EXISTS openclasp_assurance_probe_responses_interaction
    ON openclasp_assurance_probe_responses(interaction_id, agent_id, created_at DESC)
  `;
  await sql`
    CREATE TABLE IF NOT EXISTS openclasp_assurance_claim_comparisons (
      comparison_id UUID PRIMARY KEY,
      operator_id TEXT NOT NULL,
      interaction_id TEXT NOT NULL,
      target_agent_id TEXT NOT NULL,
      pre_task_response_id UUID NOT NULL REFERENCES openclasp_assurance_probe_responses(response_id),
      post_task_response_id UUID REFERENCES openclasp_assurance_probe_responses(response_id),
      payload JSONB NOT NULL,
      created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
      UNIQUE(operator_id, interaction_id, target_agent_id, pre_task_response_id)
    )
  `;
  await sql`
    CREATE INDEX IF NOT EXISTS openclasp_assurance_claim_comparisons_interaction
    ON openclasp_assurance_claim_comparisons(operator_id, interaction_id, created_at DESC)
  `;
}

async function addAssuranceDecisionLearning(sql: Sql) {
  await sql`
    CREATE TABLE IF NOT EXISTS openclasp_assurance_assessments (
      assessment_id UUID PRIMARY KEY,
      generation_id UUID NOT NULL REFERENCES openclasp_ai_generations(generation_id),
      operator_id TEXT NOT NULL,
      interaction_id TEXT NOT NULL,
      phase TEXT NOT NULL CHECK (phase IN ('pre_task', 'post_task')),
      round INTEGER NOT NULL CHECK (round BETWEEN 1 AND 3),
      target_agent_id TEXT NOT NULL,
      target_agent_version TEXT NOT NULL,
      payload JSONB NOT NULL,
      created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
      UNIQUE(operator_id, interaction_id, phase, round, target_agent_id)
    )
  `;
  await sql`
    CREATE INDEX IF NOT EXISTS openclasp_assurance_assessments_interaction
    ON openclasp_assurance_assessments(operator_id, interaction_id, created_at DESC)
  `;
  await sql`
    CREATE TABLE IF NOT EXISTS openclasp_assurance_predictions (
      prediction_id UUID PRIMARY KEY,
      assessment_id UUID REFERENCES openclasp_assurance_assessments(assessment_id),
      operator_id TEXT NOT NULL,
      interaction_id TEXT NOT NULL,
      target_agent_id TEXT NOT NULL,
      target_agent_version TEXT NOT NULL,
      task_category TEXT NOT NULL,
      stage TEXT NOT NULL CHECK (stage IN ('baseline', 'after_probe', 'after_safeguard', 'final')),
      success_probability DOUBLE PRECISION NOT NULL CHECK (success_probability BETWEEN 0.05 AND 0.95),
      payload JSONB NOT NULL,
      created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
    )
  `;
  await sql`
    CREATE INDEX IF NOT EXISTS openclasp_assurance_predictions_context
    ON openclasp_assurance_predictions(operator_id, target_agent_id, target_agent_version, task_category, created_at DESC)
  `;
  await sql`
    CREATE TABLE IF NOT EXISTS openclasp_assurance_safeguards (
      safeguard_id UUID PRIMARY KEY,
      assessment_id UUID NOT NULL REFERENCES openclasp_assurance_assessments(assessment_id),
      operator_id TEXT NOT NULL,
      interaction_id TEXT NOT NULL,
      target_agent_id TEXT NOT NULL,
      status TEXT NOT NULL CHECK (status IN ('recommended', 'accepted', 'rejected', 'modified')),
      payload JSONB NOT NULL,
      created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
      updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
    )
  `;
  await sql`
    CREATE INDEX IF NOT EXISTS openclasp_assurance_safeguards_interaction
    ON openclasp_assurance_safeguards(operator_id, interaction_id, created_at DESC)
  `;
  await sql`
    CREATE TABLE IF NOT EXISTS openclasp_assurance_evaluations (
      evaluation_id UUID PRIMARY KEY,
      operator_id TEXT NOT NULL,
      interaction_id TEXT NOT NULL,
      target_agent_id TEXT NOT NULL,
      target_agent_version TEXT NOT NULL,
      task_category TEXT NOT NULL,
      completion_report_id UUID NOT NULL,
      payload JSONB NOT NULL,
      created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
      UNIQUE(operator_id, completion_report_id)
    )
  `;
  await sql`
    CREATE INDEX IF NOT EXISTS openclasp_assurance_evaluations_learning
    ON openclasp_assurance_evaluations(operator_id, target_agent_id, target_agent_version, task_category, created_at DESC)
  `;
}

export async function runHostedMigrations(sql: Sql): Promise<void> {
  await sql`
    CREATE TABLE IF NOT EXISTS openclasp_schema_migrations (
      version INTEGER PRIMARY KEY,
      name TEXT NOT NULL UNIQUE,
      applied_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
    )
  `;
  const appliedRows = await sql`
    SELECT version, name FROM openclasp_schema_migrations ORDER BY version ASC
  `;
  const applied = new Map(appliedRows.map((row) => [Number(row.version), String(row.name)]));
  for (const migration of HOSTED_MIGRATIONS) {
    const appliedName = applied.get(migration.version);
    if (appliedName && appliedName !== migration.name)
      throw new Error(
        `Hosted migration ${migration.version} name mismatch: ${appliedName} != ${migration.name}`,
      );
    if (appliedName) continue;
    if (migration.version === 1) await applyBaseline(sql);
    if (migration.version === 2) await applyAppendOnlySourceRecords(sql);
    if (migration.version === 3) await removeHostedConversations(sql);
    if (migration.version === 4) await addAdaptiveAssuranceProbes(sql);
    if (migration.version === 5) await addAssuranceDecisionLearning(sql);
    await sql`
      INSERT INTO openclasp_schema_migrations(version, name)
      VALUES (${migration.version}, ${migration.name})
      ON CONFLICT (version) DO NOTHING
    `;
  }
}

export async function verifyHostedMigrations(sql: Sql): Promise<void> {
  const tableRows = await sql`
    SELECT to_regclass('public.openclasp_schema_migrations') AS table_name
  `;
  if (!tableRows[0]?.table_name)
    throw new Error('Hosted database is not migrated; run `pnpm migrate` before starting the API');
  const appliedRows = await sql`
    SELECT version, name FROM openclasp_schema_migrations ORDER BY version ASC
  `;
  const applied = new Map(appliedRows.map((row) => [Number(row.version), String(row.name)]));
  for (const migration of HOSTED_MIGRATIONS) {
    const name = applied.get(migration.version);
    if (name !== migration.name)
      throw new Error(
        `Hosted database migration ${migration.version} is missing or invalid; run \`pnpm migrate\``,
      );
  }
  const removedConversationTables = await sql`
    SELECT
      to_regclass('public.openclasp_hosted_messages') AS messages,
      to_regclass('public.openclasp_hosted_threads') AS threads
  `;
  if (removedConversationTables[0]?.messages || removedConversationTables[0]?.threads)
    throw new Error('Hosted conversation tables still exist; run `pnpm migrate`');
  const assuranceTables = await sql`
    SELECT
      to_regclass('public.openclasp_ai_generations') AS generations,
      to_regclass('public.openclasp_assurance_probe_plans') AS plans,
      to_regclass('public.openclasp_assurance_probe_responses') AS responses,
      to_regclass('public.openclasp_assurance_claim_comparisons') AS comparisons
  `;
  if (
    !assuranceTables[0]?.generations ||
    !assuranceTables[0]?.plans ||
    !assuranceTables[0]?.responses ||
    !assuranceTables[0]?.comparisons
  )
    throw new Error('Adaptive assurance probe tables are missing; run `pnpm migrate`');
  const decisionTables = await sql`
    SELECT
      to_regclass('public.openclasp_assurance_assessments') AS assessments,
      to_regclass('public.openclasp_assurance_predictions') AS predictions,
      to_regclass('public.openclasp_assurance_safeguards') AS safeguards,
      to_regclass('public.openclasp_assurance_evaluations') AS evaluations
  `;
  if (
    !decisionTables[0]?.assessments ||
    !decisionTables[0]?.predictions ||
    !decisionTables[0]?.safeguards ||
    !decisionTables[0]?.evaluations
  )
    throw new Error('Assurance decision learning tables are missing; run `pnpm migrate`');
}

1	`import type { NeonQueryFunction } from '@neondatabase/serverless';`
2
3	`type Sql = NeonQueryFunction<false, false>;`
4
5	`export const HOSTED_MIGRATIONS = [`
6	`{ version: 1, name: 'hosted_baseline' },`
7	`{ version: 2, name: 'append_only_source_records' },`
8	`{ version: 3, name: 'remove_hosted_conversations' },`
9	`{ version: 4, name: 'adaptive_assurance_probes' },`
10	`{ version: 5, name: 'assurance_decision_learning' },`
11	`] as const;`
12
13	`async function applyBaseline(sql: Sql) {`
14	await sql`
15	`CREATE TABLE IF NOT EXISTS openclasp_records (`
16	`operator_id TEXT NOT NULL,`
17	`kind TEXT NOT NULL,`
18	`record_id TEXT NOT NULL,`
19	`payload JSONB NOT NULL,`
20	`created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),`
21	`updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),`
22	`PRIMARY KEY (operator_id, kind, record_id)`
23	`)`
24	`;
25	await sql`
26	`CREATE INDEX IF NOT EXISTS openclasp_records_operator_created`
27	`ON openclasp_records(operator_id, created_at DESC)`
28	`;
29	await sql`
30	`CREATE TABLE IF NOT EXISTS openclasp_account_settings (`
31	`operator_id TEXT PRIMARY KEY,`
32	`display_name TEXT NOT NULL DEFAULT '',`
33	`contribution_enabled BOOLEAN NOT NULL DEFAULT FALSE,`
34	`retention_days INTEGER NOT NULL DEFAULT 30 CHECK (retention_days BETWEEN 0 AND 3650),`
35	`evidence_sharing TEXT NOT NULL DEFAULT 'ask'`
36	`CHECK (evidence_sharing IN ('never', 'ask', 'contract_only')),`
37	`updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()`
38	`)`
39	`;
40	await sql`
41	`CREATE TABLE IF NOT EXISTS openclasp_public_agents (`
42	`agent_id TEXT PRIMARY KEY,`
43	`operator_id TEXT NOT NULL,`
44	`card JSONB NOT NULL,`
45	`published_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),`
46	`updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()`
47	`)`
48	`;
49	await sql`
50	`CREATE INDEX IF NOT EXISTS openclasp_public_agents_updated`
51	`ON openclasp_public_agents(updated_at DESC)`
52	`;
53	await sql`
54	`CREATE TABLE IF NOT EXISTS openclasp_federated_interactions (`
55	`interaction_id TEXT PRIMARY KEY,`
56	`initiator_operator_id TEXT NOT NULL,`
57	`responder_operator_id TEXT NOT NULL,`
58	`initiator_agent_id TEXT NOT NULL,`
59	`responder_agent_id TEXT NOT NULL,`
60	`status TEXT NOT NULL,`
61	`payload JSONB NOT NULL,`
62	`expires_at TIMESTAMPTZ NOT NULL,`
63	`created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),`
64	`updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()`
65	`)`
66	`;
67	await sql`
68	`CREATE INDEX IF NOT EXISTS openclasp_federated_interactions_participants`
69	`ON openclasp_federated_interactions(`
70	`initiator_operator_id,`
71	`responder_operator_id,`
72	`updated_at DESC`
73	`)`
74	`;
75	await sql`
76	`CREATE TABLE IF NOT EXISTS openclasp_agent_runtimes (`
77	`agent_id TEXT PRIMARY KEY,`
78	`operator_id TEXT NOT NULL,`
79	`endpoint TEXT NOT NULL,`
80	`secret_ciphertext TEXT NOT NULL,`
81	`secret_iv TEXT NOT NULL,`
82	`secret_auth_tag TEXT NOT NULL,`
83	`status TEXT NOT NULL CHECK (status IN ('verified', 'disabled')),`
84	`verified_at TIMESTAMPTZ NOT NULL,`
85	`last_delivery_at TIMESTAMPTZ,`
86	`last_error TEXT,`
87	`created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),`
88	`updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()`
89	`)`
90	`;
91	await sql`
92	`CREATE TABLE IF NOT EXISTS openclasp_agent_access_tokens (`
93	`token_id TEXT PRIMARY KEY,`
94	`operator_id TEXT NOT NULL,`
95	`agent_id TEXT NOT NULL,`
96	`name TEXT NOT NULL,`
97	`token_hash TEXT NOT NULL,`
98	`scopes JSONB NOT NULL,`
99	`expires_at TIMESTAMPTZ NOT NULL,`
100	`last_used_at TIMESTAMPTZ,`
101	`revoked_at TIMESTAMPTZ,`
102	`created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()`
103	`)`
104	`;
105	await sql`
106	`CREATE INDEX IF NOT EXISTS openclasp_agent_access_tokens_owner_agent`
107	`ON openclasp_agent_access_tokens(operator_id, agent_id, created_at DESC)`
108	`;
109	await sql`
110	`ALTER TABLE openclasp_agent_runtimes`
111	`ADD COLUMN IF NOT EXISTS a2a_endpoint TEXT,`
112	`ADD COLUMN IF NOT EXISTS last_seen_at TIMESTAMPTZ`
113	`;
114	await sql`
115	`CREATE TABLE IF NOT EXISTS openclasp_live_sessions (`
116	`interaction_id TEXT PRIMARY KEY,`
117	`initiator_agent_id TEXT NOT NULL,`
118	`responder_agent_id TEXT NOT NULL,`
119	`initiator_session_id TEXT NOT NULL,`
120	`responder_session_id TEXT NOT NULL,`
121	`initiator_endpoint TEXT NOT NULL,`
122	`responder_endpoint TEXT NOT NULL,`
123	`status TEXT NOT NULL CHECK (status IN ('preparing', 'active', 'completed', 'failed')),`
124	`expires_at TIMESTAMPTZ NOT NULL,`
125	`created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),`
126	`activated_at TIMESTAMPTZ,`
127	`completed_at TIMESTAMPTZ,`
128	`last_error TEXT`
129	`)`
130	`;
131	await sql`
132	`CREATE TABLE IF NOT EXISTS openclasp_live_session_events (`
133	`interaction_id TEXT NOT NULL,`
134	`event_id TEXT NOT NULL,`
135	`agent_id TEXT NOT NULL,`
136	`sequence INTEGER NOT NULL,`
137	`event JSONB NOT NULL,`
138	`created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),`
139	`PRIMARY KEY (interaction_id, event_id),`
140	`UNIQUE (interaction_id, agent_id, sequence)`
141	`)`
142	`;
143	await sql`
144	`CREATE TABLE IF NOT EXISTS openclasp_hosted_threads (`
145	`thread_id TEXT PRIMARY KEY,`
146	`interaction_id TEXT NOT NULL UNIQUE,`
147	`participant_a_agent_id TEXT NOT NULL,`
148	`participant_b_agent_id TEXT NOT NULL,`
149	`status TEXT NOT NULL CHECK (status IN ('open', 'closed')) DEFAULT 'open',`
150	`created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),`
151	`updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),`
152	`expires_at TIMESTAMPTZ NOT NULL`
153	`)`
154	`;
155	await sql`
156	`CREATE INDEX IF NOT EXISTS openclasp_hosted_threads_participants`
157	`ON openclasp_hosted_threads(`
158	`participant_a_agent_id,`
159	`participant_b_agent_id,`
160	`updated_at DESC`
161	`)`
162	`;
163	await sql`
164	`CREATE TABLE IF NOT EXISTS openclasp_hosted_messages (`
165	`message_id TEXT PRIMARY KEY,`
166	`thread_id TEXT NOT NULL REFERENCES openclasp_hosted_threads(thread_id) ON DELETE CASCADE,`
167	`interaction_id TEXT NOT NULL,`
168	`sender_agent_id TEXT NOT NULL,`
169	`recipient_agent_id TEXT NOT NULL,`
170	`request_key TEXT NOT NULL,`
171	`content_ciphertext TEXT NOT NULL,`
172	`content_iv TEXT NOT NULL,`
173	`content_auth_tag TEXT NOT NULL,`
174	`content_hash TEXT NOT NULL,`
175	`delivery TEXT NOT NULL CHECK (delivery IN ('accepted', 'delivered', 'read')),`
176	`created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),`
177	`read_at TIMESTAMPTZ`
178	`)`
179	`;
180	await sql`
181	`CREATE UNIQUE INDEX IF NOT EXISTS openclasp_hosted_messages_dedup`
182	`ON openclasp_hosted_messages(thread_id, sender_agent_id, request_key)`
183	`;
184	await sql`
185	`CREATE INDEX IF NOT EXISTS openclasp_hosted_messages_thread_created`
186	`ON openclasp_hosted_messages(thread_id, created_at ASC)`
187	`;
188	`}`
189
190	`async function applyAppendOnlySourceRecords(sql: Sql) {`
191	await sql`
192	`CREATE TABLE IF NOT EXISTS openclasp_source_records (`
193	`event_id UUID PRIMARY KEY,`
194	`operator_id TEXT NOT NULL,`
195	`kind TEXT NOT NULL,`
196	`record_id TEXT NOT NULL,`
197	`schema_name TEXT NOT NULL,`
198	`schema_version TEXT NOT NULL,`
199	`payload JSONB NOT NULL,`
200	`payload_digest TEXT NOT NULL,`
201	`entity_refs JSONB NOT NULL DEFAULT '{}'::jsonb,`
202	`provenance TEXT NOT NULL,`
203	`visibility TEXT NOT NULL,`
204	`retention_class TEXT NOT NULL,`
205	`learning_scope TEXT NOT NULL,`
206	`reported_at TIMESTAMPTZ NOT NULL,`
207	`ingested_at TIMESTAMPTZ NOT NULL,`
208	`UNIQUE (operator_id, kind, record_id, payload_digest),`
209	`CHECK (provenance IN (`
210	`'self_declared',`
211	`'operator_attested',`
212	`'cryptographically_verified',`
213	`'domain_verified',`
214	`'third_party_verified',`
215	`'observed',`
216	`'disputed'`
217	`)),`
218	`CHECK (visibility IN (`
219	`'local_only',`
220	`'private_requester',`
221	`'private_responder',`
222	`'shared_participants',`
223	`'network_aggregate'`
224	`)),`
225	`CHECK (retention_class IN ('account', 'audit', 'operational', 'temporary')),`
226	`CHECK (learning_scope IN (`
227	`'not_evaluated',`
228	`'excluded',`
229	`'local_only',`
230	`'network_aggregate'`
231	`))`
232	`)`
233	`;
234	await sql`
235	`CREATE INDEX IF NOT EXISTS openclasp_source_records_operator_ingested`
236	`ON openclasp_source_records(operator_id, ingested_at DESC)`
237	`;
238	await sql`
239	`CREATE INDEX IF NOT EXISTS openclasp_source_records_history`
240	`ON openclasp_source_records(operator_id, kind, record_id, ingested_at ASC)`
241	`;
242	await sql`
243	`CREATE INDEX IF NOT EXISTS openclasp_source_records_entities`
244	`ON openclasp_source_records USING GIN(entity_refs)`
245	`;
246	`}`
247
248	`async function removeHostedConversations(sql: Sql) {`
249	await sql`DROP TABLE IF EXISTS openclasp_hosted_messages`;
250	await sql`DROP TABLE IF EXISTS openclasp_hosted_threads`;
251	`}`
252
253	`async function addAdaptiveAssuranceProbes(sql: Sql) {`
254	await sql`
255	`CREATE TABLE IF NOT EXISTS openclasp_ai_generations (`
256	`generation_id UUID PRIMARY KEY,`
257	`operator_id TEXT NOT NULL,`
258	`interaction_id TEXT NOT NULL,`
259	`phase TEXT NOT NULL CHECK (phase IN ('pre_task', 'post_task')),`
260	`model TEXT NOT NULL,`
261	`prompt_version TEXT NOT NULL,`
262	`status TEXT NOT NULL CHECK (status IN ('pending', 'complete', 'fallback', 'error')),`
263	`input JSONB NOT NULL,`
264	`input_digest TEXT NOT NULL,`
265	`output JSONB,`
266	`token_usage JSONB,`
267	`error_code TEXT,`
268	`started_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),`
269	`completed_at TIMESTAMPTZ`
270	`)`
271	`;
272	await sql`
273	`CREATE INDEX IF NOT EXISTS openclasp_ai_generations_interaction`
274	`ON openclasp_ai_generations(operator_id, interaction_id, started_at DESC)`
275	`;
276	await sql`
277	`CREATE TABLE IF NOT EXISTS openclasp_assurance_probe_plans (`
278	`plan_id UUID PRIMARY KEY,`
279	`generation_id UUID NOT NULL REFERENCES openclasp_ai_generations(generation_id),`
280	`operator_id TEXT NOT NULL,`
281	`interaction_id TEXT NOT NULL,`
282	`contract_hash TEXT NOT NULL,`
283	`phase TEXT NOT NULL CHECK (phase IN ('pre_task', 'post_task')),`
284	`generated_for_agent_id TEXT NOT NULL,`
285	`target_agent_id TEXT NOT NULL,`
286	`payload JSONB NOT NULL,`
287	`created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),`
288	`expires_at TIMESTAMPTZ NOT NULL`
289	`)`
290	`;
291	await sql`
292	`CREATE INDEX IF NOT EXISTS openclasp_assurance_probe_plans_interaction`
293	`ON openclasp_assurance_probe_plans(interaction_id, phase, created_at DESC)`
294	`;
295	await sql`
296	`CREATE TABLE IF NOT EXISTS openclasp_assurance_probe_responses (`
297	`response_id UUID PRIMARY KEY,`
298	`plan_id UUID NOT NULL REFERENCES openclasp_assurance_probe_plans(plan_id),`
299	`operator_id TEXT NOT NULL,`
300	`interaction_id TEXT NOT NULL,`
301	`contract_hash TEXT NOT NULL,`
302	`phase TEXT NOT NULL CHECK (phase IN ('pre_task', 'post_task')),`
303	`agent_id TEXT NOT NULL,`
304	`payload JSONB NOT NULL,`
305	`created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),`
306	`UNIQUE(plan_id, agent_id)`
307	`)`
308	`;
309	await sql`
310	`CREATE INDEX IF NOT EXISTS openclasp_assurance_probe_responses_interaction`
311	`ON openclasp_assurance_probe_responses(interaction_id, agent_id, created_at DESC)`
312	`;
313	await sql`
314	`CREATE TABLE IF NOT EXISTS openclasp_assurance_claim_comparisons (`
315	`comparison_id UUID PRIMARY KEY,`
316	`operator_id TEXT NOT NULL,`
317	`interaction_id TEXT NOT NULL,`
318	`target_agent_id TEXT NOT NULL,`
319	`pre_task_response_id UUID NOT NULL REFERENCES openclasp_assurance_probe_responses(response_id),`
320	`post_task_response_id UUID REFERENCES openclasp_assurance_probe_responses(response_id),`
321	`payload JSONB NOT NULL,`
322	`created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),`
323	`UNIQUE(operator_id, interaction_id, target_agent_id, pre_task_response_id)`
324	`)`
325	`;
326	await sql`
327	`CREATE INDEX IF NOT EXISTS openclasp_assurance_claim_comparisons_interaction`
328	`ON openclasp_assurance_claim_comparisons(operator_id, interaction_id, created_at DESC)`
329	`;
330	`}`
331
332	`async function addAssuranceDecisionLearning(sql: Sql) {`
333	await sql`
334	`CREATE TABLE IF NOT EXISTS openclasp_assurance_assessments (`
335	`assessment_id UUID PRIMARY KEY,`
336	`generation_id UUID NOT NULL REFERENCES openclasp_ai_generations(generation_id),`
337	`operator_id TEXT NOT NULL,`
338	`interaction_id TEXT NOT NULL,`
339	`phase TEXT NOT NULL CHECK (phase IN ('pre_task', 'post_task')),`
340	`round INTEGER NOT NULL CHECK (round BETWEEN 1 AND 3),`
341	`target_agent_id TEXT NOT NULL,`
342	`target_agent_version TEXT NOT NULL,`
343	`payload JSONB NOT NULL,`
344	`created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),`
345	`UNIQUE(operator_id, interaction_id, phase, round, target_agent_id)`
346	`)`
347	`;
348	await sql`
349	`CREATE INDEX IF NOT EXISTS openclasp_assurance_assessments_interaction`
350	`ON openclasp_assurance_assessments(operator_id, interaction_id, created_at DESC)`
351	`;
352	await sql`
353	`CREATE TABLE IF NOT EXISTS openclasp_assurance_predictions (`
354	`prediction_id UUID PRIMARY KEY,`
355	`assessment_id UUID REFERENCES openclasp_assurance_assessments(assessment_id),`
356	`operator_id TEXT NOT NULL,`
357	`interaction_id TEXT NOT NULL,`
358	`target_agent_id TEXT NOT NULL,`
359	`target_agent_version TEXT NOT NULL,`
360	`task_category TEXT NOT NULL,`
361	`stage TEXT NOT NULL CHECK (stage IN ('baseline', 'after_probe', 'after_safeguard', 'final')),`
362	`success_probability DOUBLE PRECISION NOT NULL CHECK (success_probability BETWEEN 0.05 AND 0.95),`
363	`payload JSONB NOT NULL,`
364	`created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()`
365	`)`
366	`;
367	await sql`
368	`CREATE INDEX IF NOT EXISTS openclasp_assurance_predictions_context`
369	`ON openclasp_assurance_predictions(operator_id, target_agent_id, target_agent_version, task_category, created_at DESC)`
370	`;
371	await sql`
372	`CREATE TABLE IF NOT EXISTS openclasp_assurance_safeguards (`
373	`safeguard_id UUID PRIMARY KEY,`
374	`assessment_id UUID NOT NULL REFERENCES openclasp_assurance_assessments(assessment_id),`
375	`operator_id TEXT NOT NULL,`
376	`interaction_id TEXT NOT NULL,`
377	`target_agent_id TEXT NOT NULL,`
378	`status TEXT NOT NULL CHECK (status IN ('recommended', 'accepted', 'rejected', 'modified')),`
379	`payload JSONB NOT NULL,`
380	`created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),`
381	`updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()`
382	`)`
383	`;
384	await sql`
385	`CREATE INDEX IF NOT EXISTS openclasp_assurance_safeguards_interaction`
386	`ON openclasp_assurance_safeguards(operator_id, interaction_id, created_at DESC)`
387	`;
388	await sql`
389	`CREATE TABLE IF NOT EXISTS openclasp_assurance_evaluations (`
390	`evaluation_id UUID PRIMARY KEY,`
391	`operator_id TEXT NOT NULL,`
392	`interaction_id TEXT NOT NULL,`
393	`target_agent_id TEXT NOT NULL,`
394	`target_agent_version TEXT NOT NULL,`
395	`task_category TEXT NOT NULL,`
396	`completion_report_id UUID NOT NULL,`
397	`payload JSONB NOT NULL,`
398	`created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),`
399	`UNIQUE(operator_id, completion_report_id)`
400	`)`
401	`;
402	await sql`
403	`CREATE INDEX IF NOT EXISTS openclasp_assurance_evaluations_learning`
404	`ON openclasp_assurance_evaluations(operator_id, target_agent_id, target_agent_version, task_category, created_at DESC)`
405	`;
406	`}`
407
408	`export async function runHostedMigrations(sql: Sql): Promise<void> {`
409	await sql`
410	`CREATE TABLE IF NOT EXISTS openclasp_schema_migrations (`
411	`version INTEGER PRIMARY KEY,`
412	`name TEXT NOT NULL UNIQUE,`
413	`applied_at TIMESTAMPTZ NOT NULL DEFAULT NOW()`
414	`)`
415	`;
416	const appliedRows = await sql`
417	`SELECT version, name FROM openclasp_schema_migrations ORDER BY version ASC`
418	`;
419	`const applied = new Map(appliedRows.map((row) => [Number(row.version), String(row.name)]));`
420	`for (const migration of HOSTED_MIGRATIONS) {`
421	`const appliedName = applied.get(migration.version);`
422	`if (appliedName && appliedName !== migration.name)`
423	`throw new Error(`
424	`Hosted migration ${migration.version} name mismatch: ${appliedName} != ${migration.name}`,
425	`);`
426	`if (appliedName) continue;`
427	`if (migration.version === 1) await applyBaseline(sql);`
428	`if (migration.version === 2) await applyAppendOnlySourceRecords(sql);`
429	`if (migration.version === 3) await removeHostedConversations(sql);`
430	`if (migration.version === 4) await addAdaptiveAssuranceProbes(sql);`
431	`if (migration.version === 5) await addAssuranceDecisionLearning(sql);`
432	await sql`
433	`INSERT INTO openclasp_schema_migrations(version, name)`
434	`VALUES (${migration.version}, ${migration.name})`
435	`ON CONFLICT (version) DO NOTHING`
436	`;
437	`}`
438	`}`
439
440	`export async function verifyHostedMigrations(sql: Sql): Promise<void> {`
441	const tableRows = await sql`
442	`SELECT to_regclass('public.openclasp_schema_migrations') AS table_name`
443	`;
444	`if (!tableRows[0]?.table_name)`
445	throw new Error('Hosted database is not migrated; run `pnpm migrate` before starting the API');
446	const appliedRows = await sql`
447	`SELECT version, name FROM openclasp_schema_migrations ORDER BY version ASC`
448	`;
449	`const applied = new Map(appliedRows.map((row) => [Number(row.version), String(row.name)]));`
450	`for (const migration of HOSTED_MIGRATIONS) {`
451	`const name = applied.get(migration.version);`
452	`if (name !== migration.name)`
453	`throw new Error(`
454	`Hosted database migration ${migration.version} is missing or invalid; run \`pnpm migrate\``,
455	`);`
456	`}`
457	const removedConversationTables = await sql`
458	`SELECT`
459	`to_regclass('public.openclasp_hosted_messages') AS messages,`
460	`to_regclass('public.openclasp_hosted_threads') AS threads`
461	`;
462	`if (removedConversationTables[0]?.messages \|\| removedConversationTables[0]?.threads)`
463	throw new Error('Hosted conversation tables still exist; run `pnpm migrate`');
464	const assuranceTables = await sql`
465	`SELECT`
466	`to_regclass('public.openclasp_ai_generations') AS generations,`
467	`to_regclass('public.openclasp_assurance_probe_plans') AS plans,`
468	`to_regclass('public.openclasp_assurance_probe_responses') AS responses,`
469	`to_regclass('public.openclasp_assurance_claim_comparisons') AS comparisons`
470	`;
471	`if (`
472	`!assuranceTables[0]?.generations \|\|`
473	`!assuranceTables[0]?.plans \|\|`
474	`!assuranceTables[0]?.responses \|\|`
475	`!assuranceTables[0]?.comparisons`
476	`)`
477	throw new Error('Adaptive assurance probe tables are missing; run `pnpm migrate`');
478	const decisionTables = await sql`
479	`SELECT`
480	`to_regclass('public.openclasp_assurance_assessments') AS assessments,`
481	`to_regclass('public.openclasp_assurance_predictions') AS predictions,`
482	`to_regclass('public.openclasp_assurance_safeguards') AS safeguards,`
483	`to_regclass('public.openclasp_assurance_evaluations') AS evaluations`
484	`;
485	`if (`
486	`!decisionTables[0]?.assessments \|\|`
487	`!decisionTables[0]?.predictions \|\|`
488	`!decisionTables[0]?.safeguards \|\|`
489	`!decisionTables[0]?.evaluations`
490	`)`
491	throw new Error('Assurance decision learning tables are missing; run `pnpm migrate`');
492	`}`