Assistant/database/migrations/018_capability_resolution_selection_gap.sql

BEGIN;

SET ROLE capability_role;

CREATE TABLE IF NOT EXISTS capability.capability_descriptors (
  capability_id TEXT PRIMARY KEY,
  capability_kind TEXT NOT NULL,
  display_name TEXT NOT NULL,
  description TEXT NOT NULL,
  revision BIGINT NOT NULL,
  recorded_at_unix_millis BIGINT NOT NULL,
  recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CHECK (capability_id <> ''),
  CHECK (capability_kind <> ''),
  CHECK (display_name <> ''),
  CHECK (revision > 0),
  CHECK (recorded_at_unix_millis > 0)
);

CREATE TABLE IF NOT EXISTS capability.capability_implementations (
  implementation_id TEXT NOT NULL,
  capability_id TEXT NOT NULL,
  capability_kind TEXT NOT NULL,
  owner_principal_id TEXT NOT NULL,
  revision BIGINT NOT NULL,
  implementation_kind TEXT NOT NULL,
  target_ref TEXT NOT NULL,
  credential_ref TEXT NOT NULL DEFAULT '',
  qualification TEXT NOT NULL,
  activation TEXT NOT NULL,
  health TEXT NOT NULL,
  authority_policy_ref TEXT NOT NULL,
  data_constraints TEXT[] NOT NULL,
  cost_score_millis BIGINT,
  latency_score_millis BIGINT,
  reliability_millis BIGINT,
  observed_at_unix_millis BIGINT NOT NULL,
  recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (implementation_id, revision),
  CHECK (implementation_id <> ''),
  CHECK (capability_id <> ''),
  CHECK (capability_kind <> ''),
  CHECK (owner_principal_id <> ''),
  CHECK (revision > 0),
  CHECK (implementation_kind IN ('INTERNAL_FUNCTION','COGNITIVE_BINDING','EXTERNAL_ADAPTER','AGENT')),
  CHECK (target_ref <> ''),
  CHECK (qualification IN ('DISCOVERED','PROBING','EVALUATING','VALIDATED','REJECTED')),
  CHECK (activation IN ('INACTIVE','CANARY','ACTIVE','DEPRECATED','RETIRED')),
  CHECK (health IN ('UNKNOWN','AVAILABLE','DEGRADED','UNAVAILABLE')),
  CHECK (authority_policy_ref <> ''),
  CHECK (cardinality(data_constraints) > 0),
  CHECK (cost_score_millis IS NULL OR cost_score_millis >= 0),
  CHECK (latency_score_millis IS NULL OR latency_score_millis >= 0),
  CHECK (reliability_millis IS NULL OR reliability_millis >= 0),
  CHECK (observed_at_unix_millis > 0)
);

CREATE TABLE IF NOT EXISTS capability.capability_implementation_heads (
  implementation_id TEXT PRIMARY KEY,
  revision BIGINT NOT NULL,
  capability_kind TEXT NOT NULL,
  owner_principal_id TEXT NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CHECK (revision > 0)
);

CREATE TABLE IF NOT EXISTS capability.capability_gaps (
  gap_id TEXT PRIMARY KEY,
  requirement_id TEXT NOT NULL,
  capability_kind TEXT NOT NULL,
  capability_id TEXT NOT NULL DEFAULT '',
  owner_principal_id TEXT NOT NULL,
  original_intent_id TEXT NOT NULL,
  source_utterance_id TEXT NOT NULL,
  reason TEXT NOT NULL,
  detail TEXT NOT NULL,
  strategies TEXT[] NOT NULL,
  recorded_at_unix_millis BIGINT NOT NULL,
  recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CHECK (requirement_id <> ''),
  CHECK (capability_kind <> ''),
  CHECK (owner_principal_id <> ''),
  CHECK (original_intent_id <> ''),
  CHECK (source_utterance_id <> ''),
  CHECK (reason IN ('NO_IMPLEMENTATION','UNHEALTHY','INACTIVE','UNQUALIFIED','UNAUTHORIZED','INCOMPATIBLE','UNKNOWN')),
  CHECK (detail <> ''),
  CHECK (cardinality(strategies) > 0),
  CHECK (recorded_at_unix_millis > 0)
);

CREATE TABLE IF NOT EXISTS capability.capability_usage_observations (
  observation_id TEXT PRIMARY KEY,
  implementation_id TEXT NOT NULL,
  implementation_revision BIGINT NOT NULL,
  capability_id TEXT NOT NULL,
  capability_kind TEXT NOT NULL,
  owner_principal_id TEXT NOT NULL,
  original_intent_id TEXT NOT NULL,
  source_utterance_id TEXT NOT NULL,
  outcome TEXT NOT NULL,
  outcome_ref TEXT NOT NULL DEFAULT '',
  latency_millis BIGINT,
  observed_at_unix_millis BIGINT NOT NULL,
  recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CHECK (implementation_id <> ''),
  CHECK (implementation_revision > 0),
  CHECK (capability_id <> ''),
  CHECK (capability_kind <> ''),
  CHECK (owner_principal_id <> ''),
  CHECK (original_intent_id <> ''),
  CHECK (source_utterance_id <> ''),
  CHECK (outcome IN ('SUCCEEDED','FAILED','SKIPPED','UNKNOWN')),
  CHECK (latency_millis IS NULL OR latency_millis >= 0),
  CHECK (observed_at_unix_millis > 0)
);

INSERT INTO capability.capability_descriptors(capability_id, capability_kind, display_name, description, revision, recorded_at_unix_millis)
VALUES
  ('schedule-reminder', 'SCHEDULE_REMINDER', 'Reminder scheduling', 'Create a Continuity-owned prospective reminder item from an authenticated human directive.', 1, 1),
  ('creator-advisory', 'COGNITIVE_REASONING', 'Creator advisory cognition', 'Reasoning capability resolved through the specialized Cognitive Registry and Gateway.', 1, 1)
ON CONFLICT (capability_id) DO NOTHING;

INSERT INTO capability.capability_implementations(
  implementation_id, capability_id, capability_kind, owner_principal_id, revision, implementation_kind, target_ref,
  credential_ref, qualification, activation, health, authority_policy_ref, data_constraints,
  cost_score_millis, latency_score_millis, reliability_millis, observed_at_unix_millis)
VALUES (
  'internal-reminder-scheduler-v1', 'schedule-reminder', 'SCHEDULE_REMINDER', 'domovoy-creator-principal',
  1, 'INTERNAL_FUNCTION', 'continuity://prospective-reminder-v1', '', 'VALIDATED', 'ACTIVE', 'AVAILABLE',
  'creator-directive-v1', ARRAY['LOCAL_ONLY','NO_CREDENTIAL_FORWARD'], 0, 10, 950, 1
)
ON CONFLICT DO NOTHING;

INSERT INTO capability.capability_implementation_heads(implementation_id, revision, capability_kind, owner_principal_id)
VALUES ('internal-reminder-scheduler-v1', 1, 'SCHEDULE_REMINDER', 'domovoy-creator-principal')
ON CONFLICT (implementation_id) DO NOTHING;

RESET ROLE;
COMMIT;