730 lines
34 KiB
PL/PgSQL
730 lines
34 KiB
PL/PgSQL
-- Bootstrap roles/schemas. Replace passwords via ALTER ROLE from secret-aware deployment; do not hardcode real passwords.
|
|
BEGIN;
|
|
DO $$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'identity_role') THEN CREATE ROLE identity_role LOGIN; END IF; END $$;
|
|
CREATE SCHEMA IF NOT EXISTS identity AUTHORIZATION identity_role;
|
|
REVOKE ALL ON SCHEMA identity FROM PUBLIC;
|
|
DO $$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'authority_role') THEN CREATE ROLE authority_role LOGIN; END IF; END $$;
|
|
CREATE SCHEMA IF NOT EXISTS authority AUTHORIZATION authority_role;
|
|
REVOKE ALL ON SCHEMA authority FROM PUBLIC;
|
|
DO $$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'situation_role') THEN CREATE ROLE situation_role LOGIN; END IF; END $$;
|
|
CREATE SCHEMA IF NOT EXISTS situation AUTHORIZATION situation_role;
|
|
REVOKE ALL ON SCHEMA situation FROM PUBLIC;
|
|
DO $$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'work_role') THEN CREATE ROLE work_role LOGIN; END IF; END $$;
|
|
CREATE SCHEMA IF NOT EXISTS work AUTHORIZATION work_role;
|
|
REVOKE ALL ON SCHEMA work FROM PUBLIC;
|
|
DO $$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'epistemics_role') THEN CREATE ROLE epistemics_role LOGIN; END IF; END $$;
|
|
CREATE SCHEMA IF NOT EXISTS epistemics AUTHORIZATION epistemics_role;
|
|
REVOKE ALL ON SCHEMA epistemics FROM PUBLIC;
|
|
DO $$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'continuity_role') THEN CREATE ROLE continuity_role LOGIN; END IF; END $$;
|
|
CREATE SCHEMA IF NOT EXISTS continuity AUTHORIZATION continuity_role;
|
|
REVOKE ALL ON SCHEMA continuity FROM PUBLIC;
|
|
DO $$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'capability_role') THEN CREATE ROLE capability_role LOGIN; END IF; END $$;
|
|
CREATE SCHEMA IF NOT EXISTS capability AUTHORIZATION capability_role;
|
|
REVOKE ALL ON SCHEMA capability FROM PUBLIC;
|
|
DO $$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'commit_role') THEN CREATE ROLE commit_role LOGIN; END IF; END $$;
|
|
CREATE SCHEMA IF NOT EXISTS commit AUTHORIZATION commit_role;
|
|
REVOKE ALL ON SCHEMA commit FROM PUBLIC;
|
|
DO $$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'artifact_role') THEN CREATE ROLE artifact_role LOGIN; END IF; END $$;
|
|
CREATE SCHEMA IF NOT EXISTS artifact AUTHORIZATION artifact_role;
|
|
REVOKE ALL ON SCHEMA artifact FROM PUBLIC;
|
|
DO $$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'interaction_role') THEN CREATE ROLE interaction_role LOGIN; END IF; END $$;
|
|
CREATE SCHEMA IF NOT EXISTS interaction AUTHORIZATION interaction_role;
|
|
REVOKE ALL ON SCHEMA interaction FROM PUBLIC;
|
|
|
|
SET ROLE identity_role;
|
|
CREATE TABLE IF NOT EXISTS identity.actors (
|
|
actor_id TEXT PRIMARY KEY,
|
|
display_name TEXT NOT NULL,
|
|
recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
CREATE TABLE IF NOT EXISTS identity.principals (
|
|
principal_id TEXT PRIMARY KEY,
|
|
display_name TEXT NOT NULL,
|
|
recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
CREATE TABLE IF NOT EXISTS identity.principal_bindings (
|
|
actor_id TEXT NOT NULL,
|
|
principal_id TEXT NOT NULL,
|
|
active BOOLEAN NOT NULL,
|
|
recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (actor_id, principal_id)
|
|
);
|
|
INSERT INTO identity.actors (actor_id, display_name) VALUES ('domovoy-creator-actor', 'Domovoy Creator') ON CONFLICT (actor_id) DO NOTHING;
|
|
INSERT INTO identity.principals (principal_id, display_name) VALUES ('domovoy-creator-principal', 'Domovoy Creator Principal') ON CONFLICT (principal_id) DO NOTHING;
|
|
INSERT INTO identity.principal_bindings (actor_id, principal_id, active) VALUES ('domovoy-creator-actor', 'domovoy-creator-principal', TRUE) ON CONFLICT (actor_id, principal_id) DO NOTHING;
|
|
RESET ROLE;
|
|
|
|
SET ROLE authority_role;
|
|
CREATE TABLE IF NOT EXISTS authority.scopes (
|
|
scope_id TEXT PRIMARY KEY,
|
|
label TEXT NOT NULL,
|
|
global_scope BOOLEAN NOT NULL,
|
|
recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
CREATE TABLE IF NOT EXISTS authority.authority_grants (
|
|
grant_id TEXT PRIMARY KEY,
|
|
actor_id TEXT NOT NULL,
|
|
principal_id TEXT NOT NULL,
|
|
scope_id TEXT NOT NULL,
|
|
grant_class TEXT NOT NULL,
|
|
revision BIGINT NOT NULL,
|
|
active BOOLEAN NOT NULL,
|
|
valid_from TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
valid_until TIMESTAMPTZ,
|
|
CHECK (revision > 0)
|
|
);
|
|
INSERT INTO authority.scopes (scope_id, label, global_scope) VALUES ('domovoy-global-scope', 'All Domovoy scopes', TRUE) ON CONFLICT (scope_id) DO NOTHING;
|
|
INSERT INTO authority.authority_grants (grant_id, actor_id, principal_id, scope_id, grant_class, revision, active) VALUES ('domovoy-creator-grant', 'domovoy-creator-actor', 'domovoy-creator-principal', 'domovoy-global-scope', 'CREATOR', 1, TRUE) ON CONFLICT (grant_id) DO NOTHING;
|
|
RESET ROLE;
|
|
|
|
SET ROLE commit_role;
|
|
CREATE TABLE IF NOT EXISTS commit.authorized_actions (
|
|
authorized_action_id TEXT PRIMARY KEY, action_intent_id TEXT NOT NULL,
|
|
state TEXT NOT NULL, revision BIGINT NOT NULL, CHECK (revision > 0)
|
|
);
|
|
CREATE TABLE IF NOT EXISTS commit.action_attempts (
|
|
action_attempt_id TEXT PRIMARY KEY, authorized_action_id TEXT NOT NULL,
|
|
action_intent_id TEXT NOT NULL, state TEXT NOT NULL,
|
|
recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
revision BIGINT NOT NULL DEFAULT 1,
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CHECK (revision > 0)
|
|
);
|
|
CREATE TABLE IF NOT EXISTS commit.authorized_action_envelopes (
|
|
authorized_action_id TEXT PRIMARY KEY,
|
|
action_intent_id TEXT NOT NULL UNIQUE,
|
|
actor_id TEXT NOT NULL,
|
|
principal_id TEXT NOT NULL,
|
|
grant_id TEXT NOT NULL,
|
|
grant_revision BIGINT NOT NULL,
|
|
scope_id TEXT NOT NULL,
|
|
task_id TEXT NOT NULL,
|
|
goal_id TEXT NOT NULL,
|
|
task_scope_id TEXT NOT NULL,
|
|
task_revision BIGINT NOT NULL,
|
|
capability_id TEXT NOT NULL,
|
|
operation TEXT NOT NULL,
|
|
prospective_item_id TEXT NOT NULL,
|
|
expected_prospective_revision BIGINT NOT NULL,
|
|
prospective_from_state TEXT NOT NULL,
|
|
prospective_to_state TEXT NOT NULL,
|
|
evidence_ref TEXT NOT NULL,
|
|
proposal_state TEXT NOT NULL,
|
|
action_state TEXT NOT NULL,
|
|
authorized_at_unix_millis BIGINT NOT NULL,
|
|
envelope_revision BIGINT NOT NULL,
|
|
notification_template_code TEXT NOT NULL DEFAULT '',
|
|
capability_binding_id TEXT NOT NULL DEFAULT '',
|
|
capability_binding_revision BIGINT NOT NULL DEFAULT 0,
|
|
notification_title TEXT NOT NULL DEFAULT '',
|
|
notification_message TEXT NOT NULL DEFAULT '',
|
|
request_digest TEXT NOT NULL DEFAULT '',
|
|
recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CHECK (grant_revision > 0), CHECK (task_revision > 0),
|
|
CHECK (authorized_at_unix_millis > 0), CHECK (envelope_revision = 1),
|
|
CHECK (operation <> 'deliver-prospective-notification-v1' OR (
|
|
notification_template_code = 'prospective-delivered-v1' AND notification_title <> '' AND notification_message <> '' AND capability_binding_id <> '' AND
|
|
capability_binding_revision > 0 AND request_digest ~ '^[0-9a-f]{64}$' AND
|
|
expected_prospective_revision > 0 AND prospective_item_id <> ''
|
|
))
|
|
);
|
|
RESET ROLE;
|
|
|
|
SET ROLE work_role;
|
|
CREATE TABLE IF NOT EXISTS work.tasks (
|
|
task_id TEXT PRIMARY KEY, goal_id TEXT NOT NULL, scope_id TEXT NOT NULL,
|
|
revision BIGINT NOT NULL, CHECK (revision > 0)
|
|
);
|
|
CREATE TABLE IF NOT EXISTS work.goals (
|
|
goal_id TEXT PRIMARY KEY,
|
|
source_intent_id TEXT NOT NULL,
|
|
source_utterance_id TEXT NOT NULL,
|
|
actor_id TEXT NOT NULL,
|
|
principal_id TEXT NOT NULL,
|
|
scope_id TEXT NOT NULL,
|
|
project_id TEXT NOT NULL DEFAULT '',
|
|
project_display_name TEXT NOT NULL DEFAULT '',
|
|
desired_outcome TEXT NOT NULL,
|
|
constraints_text TEXT NOT NULL,
|
|
state TEXT NOT NULL,
|
|
provenance TEXT NOT NULL DEFAULT '',
|
|
revision BIGINT NOT NULL,
|
|
created_at_unix_millis BIGINT NOT NULL,
|
|
updated_at_unix_millis BIGINT NOT NULL,
|
|
CHECK (desired_outcome <> ''),
|
|
CHECK (state IN ('PROPOSED','UNDERSTOOD','PLANNING','READY','IN_PROGRESS','BLOCKED','COMPLETED','FAILED','CANCELLED','SUPERSEDED')),
|
|
CHECK (revision > 0)
|
|
);
|
|
CREATE TABLE IF NOT EXISTS work.plan_candidates (
|
|
plan_candidate_id TEXT PRIMARY KEY,
|
|
goal_id TEXT NOT NULL,
|
|
source TEXT NOT NULL,
|
|
state TEXT NOT NULL,
|
|
summary TEXT NOT NULL,
|
|
validation_summary TEXT NOT NULL,
|
|
requires_clarification BOOLEAN NOT NULL,
|
|
accepted_at_unix_millis BIGINT NOT NULL DEFAULT 0,
|
|
recorded_at_unix_millis BIGINT NOT NULL,
|
|
CHECK (goal_id <> ''),
|
|
CHECK (source <> ''),
|
|
CHECK (summary <> ''),
|
|
CHECK (state IN ('PROPOSED','VALIDATED','ACCEPTED','REJECTED'))
|
|
);
|
|
CREATE TABLE IF NOT EXISTS work.work_plans (
|
|
work_plan_id TEXT PRIMARY KEY,
|
|
goal_id TEXT NOT NULL,
|
|
accepted_candidate_id TEXT NOT NULL,
|
|
revision BIGINT NOT NULL,
|
|
state TEXT NOT NULL,
|
|
summary TEXT NOT NULL,
|
|
recorded_at_unix_millis BIGINT NOT NULL,
|
|
CHECK (goal_id <> ''),
|
|
CHECK (accepted_candidate_id <> ''),
|
|
CHECK (revision > 0),
|
|
CHECK (state IN ('PROPOSED','VALIDATED','ACCEPTED','REJECTED'))
|
|
);
|
|
CREATE TABLE IF NOT EXISTS work.capability_requirements (
|
|
capability_requirement_id TEXT PRIMARY KEY,
|
|
plan_candidate_id TEXT NOT NULL,
|
|
capability_kind TEXT NOT NULL,
|
|
capability_id TEXT NOT NULL DEFAULT '',
|
|
purpose TEXT NOT NULL,
|
|
state TEXT NOT NULL,
|
|
epistemic_precondition TEXT NOT NULL,
|
|
recorded_at_unix_millis BIGINT NOT NULL,
|
|
CHECK (plan_candidate_id <> ''),
|
|
CHECK (capability_kind <> ''),
|
|
CHECK (purpose <> ''),
|
|
CHECK (state IN ('REQUIRED','AVAILABLE','MISSING','BLOCKED')),
|
|
CHECK (epistemic_precondition IN ('UNVERIFIED','VERIFIED','REJECTED','UNKNOWN','NOT_APPLICABLE'))
|
|
);
|
|
CREATE TABLE IF NOT EXISTS work.plan_steps (
|
|
plan_step_id TEXT PRIMARY KEY,
|
|
plan_candidate_id TEXT NOT NULL,
|
|
ordinal INTEGER NOT NULL,
|
|
kind TEXT NOT NULL,
|
|
description TEXT NOT NULL,
|
|
requires_capability_id TEXT NOT NULL DEFAULT '',
|
|
recorded_at_unix_millis BIGINT NOT NULL,
|
|
CHECK (plan_candidate_id <> ''),
|
|
CHECK (ordinal > 0),
|
|
CHECK (kind IN ('INVESTIGATE','OBSERVE','VERIFY','REASON','REPORT','PREPARE','COMMUNICATE','SCHEDULE','MODIFY','RUN_CHECKS','CLARIFY','NO_EXECUTION')),
|
|
CHECK (description <> '')
|
|
);
|
|
CREATE TABLE IF NOT EXISTS work.delegation_candidates (
|
|
delegation_candidate_id TEXT PRIMARY KEY,
|
|
plan_candidate_id TEXT NOT NULL,
|
|
capability_requirement_id TEXT NOT NULL,
|
|
kind TEXT NOT NULL,
|
|
executor_hint TEXT NOT NULL DEFAULT '',
|
|
state TEXT NOT NULL,
|
|
reason TEXT NOT NULL,
|
|
recorded_at_unix_millis BIGINT NOT NULL,
|
|
CHECK (plan_candidate_id <> ''),
|
|
CHECK (capability_requirement_id <> ''),
|
|
CHECK (kind IN ('AGENT','TOOL','ADAPTER','HUMAN','UNAVAILABLE')),
|
|
CHECK (state IN ('PROPOSED','SELECTED','BLOCKED')),
|
|
CHECK (reason <> '')
|
|
);
|
|
RESET ROLE;
|
|
|
|
SET ROLE situation_role;
|
|
CREATE TABLE IF NOT EXISTS situation.focus_bindings (
|
|
focus_binding_id TEXT PRIMARY KEY,
|
|
actor_id TEXT NOT NULL,
|
|
session_id TEXT NOT NULL,
|
|
workspace_id TEXT NOT NULL,
|
|
project_id TEXT NOT NULL,
|
|
goal_id TEXT NOT NULL,
|
|
scope_id TEXT NOT NULL,
|
|
active BOOLEAN NOT NULL,
|
|
recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
CREATE TABLE IF NOT EXISTS situation.projects (
|
|
project_id TEXT PRIMARY KEY,
|
|
display_name TEXT NOT NULL,
|
|
recorded_at_unix_millis BIGINT NOT NULL,
|
|
CHECK (project_id <> ''),
|
|
CHECK (display_name <> '')
|
|
);
|
|
CREATE TABLE IF NOT EXISTS situation.project_aliases (
|
|
project_id TEXT NOT NULL,
|
|
alias TEXT NOT NULL,
|
|
recorded_at_unix_millis BIGINT NOT NULL,
|
|
PRIMARY KEY (project_id, alias),
|
|
CHECK (project_id <> ''),
|
|
CHECK (alias <> '')
|
|
);
|
|
CREATE TABLE IF NOT EXISTS situation.active_project_focus (
|
|
actor_id TEXT NOT NULL,
|
|
principal_id TEXT NOT NULL,
|
|
project_id TEXT NOT NULL,
|
|
project_display_name TEXT NOT NULL,
|
|
provenance TEXT NOT NULL,
|
|
confidence_millis BIGINT NOT NULL,
|
|
source_utterance_id TEXT NOT NULL,
|
|
updated_at_unix_millis BIGINT NOT NULL,
|
|
PRIMARY KEY (actor_id, principal_id),
|
|
CHECK (provenance IN ('EXPLICIT','INHERITED_FOCUS','ENTITY_REFERENCE','TASK_REFERENCE','REPOSITORY_REFERENCE','INFERRED','USER_CONFIRMED','AMBIGUOUS')),
|
|
CHECK (confidence_millis BETWEEN 0 AND 1000)
|
|
);
|
|
INSERT INTO situation.projects(project_id, display_name, recorded_at_unix_millis)
|
|
VALUES ('assistant', 'Assistant', 1), ('upo', 'УПО', 1)
|
|
ON CONFLICT (project_id) DO NOTHING;
|
|
INSERT INTO situation.project_aliases(project_id, alias, recorded_at_unix_millis)
|
|
VALUES
|
|
('assistant', 'assistant', 1),
|
|
('assistant', 'домовой', 1),
|
|
('assistant', 'ассистент', 1),
|
|
('upo', 'упо', 1),
|
|
('upo', 'upo', 1)
|
|
ON CONFLICT (project_id, alias) DO NOTHING;
|
|
RESET ROLE;
|
|
|
|
SET ROLE capability_role;
|
|
CREATE TABLE IF NOT EXISTS capability.health (
|
|
capability_id TEXT PRIMARY KEY,
|
|
state TEXT NOT NULL,
|
|
reason TEXT NOT NULL,
|
|
revision BIGINT NOT NULL,
|
|
semantic_version INTEGER NOT NULL,
|
|
observed_at_unix_millis BIGINT,
|
|
recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CHECK (revision > 0),
|
|
CHECK ((semantic_version = 1 AND observed_at_unix_millis IS NULL) OR (semantic_version = 2 AND observed_at_unix_millis > 0))
|
|
);
|
|
CREATE TABLE IF NOT EXISTS capability.health_projection (
|
|
capability_id TEXT PRIMARY KEY,
|
|
state TEXT NOT NULL,
|
|
reason TEXT NOT NULL,
|
|
revision BIGINT NOT NULL,
|
|
semantic_version INTEGER NOT NULL,
|
|
observed_at_unix_millis BIGINT
|
|
);
|
|
CREATE TABLE IF NOT EXISTS capability.notification_bindings (
|
|
binding_id TEXT NOT NULL, capability_id TEXT NOT NULL, owner_principal_id TEXT NOT NULL,
|
|
revision BIGINT NOT NULL, state TEXT NOT NULL, https_origin TEXT NOT NULL,
|
|
delivery_path TEXT NOT NULL, receipt_path TEXT NOT NULL, protocol TEXT NOT NULL,
|
|
secret_ref TEXT NOT NULL, verified_at_unix_millis BIGINT NOT NULL, health TEXT NOT NULL,
|
|
recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (binding_id, revision),
|
|
CHECK (revision > 0), CHECK (verified_at_unix_millis > 0),
|
|
CHECK (state IN ('ACTIVE','RETIRED')), CHECK (health IN ('AVAILABLE','DEGRADED','UNAVAILABLE')),
|
|
CHECK (protocol = 'IDEMPOTENT_RECEIPT_V1'), CHECK (https_origin LIKE 'https://%'),
|
|
CHECK (delivery_path LIKE '/%'), CHECK (receipt_path LIKE '/%')
|
|
);
|
|
CREATE TABLE IF NOT EXISTS capability.notification_binding_heads (
|
|
capability_id TEXT NOT NULL, owner_principal_id TEXT NOT NULL,
|
|
binding_id TEXT NOT NULL, revision BIGINT NOT NULL,
|
|
PRIMARY KEY (capability_id, owner_principal_id), CHECK (revision > 0)
|
|
);
|
|
CREATE TABLE IF NOT EXISTS capability.cognitive_provider_instances (
|
|
provider_instance_id TEXT NOT NULL, revision BIGINT NOT NULL, provider_kind TEXT NOT NULL,
|
|
endpoint_ref TEXT NOT NULL, credential_ref TEXT, enabled BOOLEAN NOT NULL,
|
|
qualification TEXT NOT NULL, health TEXT NOT NULL, observed_at_unix_millis BIGINT NOT NULL,
|
|
recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (provider_instance_id, revision), CHECK (revision > 0),
|
|
CHECK (qualification IN ('DISCOVERED','PROBING','EVALUATING','VALIDATED','REJECTED')),
|
|
CHECK (health IN ('UNKNOWN','AVAILABLE','DEGRADED','UNAVAILABLE')), CHECK (observed_at_unix_millis > 0)
|
|
);
|
|
CREATE TABLE IF NOT EXISTS capability.cognitive_model_descriptors (
|
|
model_descriptor_id TEXT NOT NULL, revision BIGINT NOT NULL, provider_instance_id TEXT NOT NULL,
|
|
provider_model_id TEXT NOT NULL, fingerprint TEXT NOT NULL, max_context_bytes BIGINT NOT NULL,
|
|
modalities TEXT[] NOT NULL, capabilities TEXT[] NOT NULL, qualification TEXT NOT NULL,
|
|
health TEXT NOT NULL, observed_at_unix_millis BIGINT NOT NULL,
|
|
recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (model_descriptor_id, revision), CHECK (revision > 0), CHECK (max_context_bytes >= 0),
|
|
CHECK (cardinality(modalities) > 0), CHECK (cardinality(capabilities) > 0),
|
|
CHECK (qualification IN ('DISCOVERED','PROBING','EVALUATING','VALIDATED','REJECTED')),
|
|
CHECK (health IN ('UNKNOWN','AVAILABLE','DEGRADED','UNAVAILABLE')), CHECK (observed_at_unix_millis > 0)
|
|
);
|
|
CREATE TABLE IF NOT EXISTS capability.cognitive_bindings (
|
|
binding_id TEXT NOT NULL, revision BIGINT NOT NULL, capability_id TEXT NOT NULL,
|
|
owner_principal_id TEXT NOT NULL, provider_instance_id TEXT NOT NULL, provider_revision BIGINT NOT NULL,
|
|
model_descriptor_id TEXT NOT NULL, model_revision BIGINT NOT NULL,
|
|
data_policy TEXT NOT NULL, qualification TEXT NOT NULL, activation TEXT NOT NULL,
|
|
health TEXT NOT NULL, limit_policy_ref TEXT NOT NULL, recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (binding_id, revision), CHECK (revision > 0), CHECK (provider_revision > 0), CHECK (model_revision > 0),
|
|
CHECK (data_policy IN ('LOCAL_ONLY','EXTERNAL_MESSAGE_ONLY','EXTERNAL_MINIMIZED_CONTEXT')),
|
|
CHECK (qualification IN ('DISCOVERED','PROBING','EVALUATING','VALIDATED','REJECTED')),
|
|
CHECK (activation IN ('INACTIVE','CANARY','ACTIVE','DEPRECATED','RETIRED')),
|
|
CHECK (health IN ('UNKNOWN','AVAILABLE','DEGRADED','UNAVAILABLE'))
|
|
);
|
|
CREATE TABLE IF NOT EXISTS capability.cognitive_binding_heads (
|
|
capability_id TEXT NOT NULL, owner_principal_id TEXT NOT NULL, binding_id TEXT NOT NULL,
|
|
revision BIGINT NOT NULL, PRIMARY KEY (capability_id, owner_principal_id), CHECK (revision > 0)
|
|
);
|
|
CREATE TABLE IF NOT EXISTS capability.cognitive_binding_revision_heads (
|
|
binding_id TEXT PRIMARY KEY,
|
|
revision BIGINT NOT NULL CHECK (revision > 0),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
CREATE TABLE IF NOT EXISTS capability.cognitive_usage_observations (
|
|
observation_id TEXT PRIMARY KEY, request_id TEXT NOT NULL, binding_id TEXT NOT NULL,
|
|
binding_revision BIGINT NOT NULL, provider_instance_id TEXT NOT NULL, model_descriptor_id TEXT NOT NULL,
|
|
observed_at_unix_millis BIGINT NOT NULL, latency_millis BIGINT NOT NULL,
|
|
input_units BIGINT NOT NULL, output_units BIGINT NOT NULL, estimated_cost_micros BIGINT,
|
|
actual_cost_micros BIGINT, result_class TEXT NOT NULL, failure_type TEXT NOT NULL DEFAULT '',
|
|
recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CHECK (binding_revision > 0), CHECK (observed_at_unix_millis > 0), CHECK (latency_millis >= 0),
|
|
CHECK (input_units >= 0), CHECK (output_units >= 0),
|
|
CHECK (estimated_cost_micros IS NULL OR estimated_cost_micros >= 0),
|
|
CHECK (actual_cost_micros IS NULL OR actual_cost_micros >= 0)
|
|
);
|
|
CREATE TABLE IF NOT EXISTS capability.cognitive_provider_health (
|
|
provider_instance_id TEXT PRIMARY KEY, health TEXT NOT NULL, observed_at_unix_millis BIGINT NOT NULL,
|
|
failure_class TEXT NOT NULL DEFAULT '', recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CHECK (health IN ('UNKNOWN','AVAILABLE','DEGRADED','UNAVAILABLE')), CHECK (observed_at_unix_millis > 0)
|
|
);
|
|
CREATE TABLE IF NOT EXISTS capability.cognitive_model_availability (
|
|
provider_instance_id TEXT NOT NULL, model_descriptor_id TEXT NOT NULL,
|
|
installed TEXT NOT NULL, loaded TEXT NOT NULL, invokable TEXT NOT NULL, health TEXT NOT NULL,
|
|
observed_at_unix_millis BIGINT NOT NULL, recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (provider_instance_id, model_descriptor_id),
|
|
CHECK (installed IN ('NOT_OBSERVED','YES','NO')), CHECK (loaded IN ('NOT_OBSERVED','YES','NO')),
|
|
CHECK (invokable IN ('NOT_OBSERVED','YES','NO')), CHECK (health IN ('UNKNOWN','AVAILABLE','DEGRADED','UNAVAILABLE')),
|
|
CHECK (observed_at_unix_millis > 0)
|
|
);
|
|
CREATE TABLE IF NOT EXISTS capability.cognitive_evaluated_capabilities (
|
|
evaluation_id TEXT PRIMARY KEY, binding_id TEXT NOT NULL, binding_revision BIGINT NOT NULL,
|
|
capability_id TEXT NOT NULL, result TEXT NOT NULL, quality_score_millis BIGINT,
|
|
critical_failure_millis BIGINT, benchmark_version TEXT NOT NULL, artifact_id TEXT NOT NULL,
|
|
observed_at_unix_millis BIGINT NOT NULL, recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CHECK (binding_revision > 0), CHECK (result IN ('UNKNOWN','SUPPORTED','UNSUPPORTED')),
|
|
CHECK (quality_score_millis IS NULL OR quality_score_millis >= 0),
|
|
CHECK (critical_failure_millis IS NULL OR critical_failure_millis >= 0), CHECK (observed_at_unix_millis > 0)
|
|
);
|
|
CREATE TABLE IF NOT EXISTS capability.cognitive_performance_evidence (
|
|
evidence_id TEXT PRIMARY KEY, binding_id TEXT NOT NULL, binding_revision BIGINT NOT NULL,
|
|
model_descriptor_id TEXT NOT NULL, model_revision BIGINT NOT NULL, hardware_profile TEXT NOT NULL,
|
|
runtime TEXT NOT NULL, quantization TEXT NOT NULL, context_units BIGINT, thinking_enabled BOOLEAN,
|
|
generation_tps_millis BIGINT, prompt_tps_millis BIGINT, ttft_micros BIGINT, latency_micros BIGINT,
|
|
memory_bytes BIGINT, reliability_millis BIGINT, benchmark_version TEXT NOT NULL, artifact_id TEXT NOT NULL,
|
|
observed_at_unix_millis BIGINT NOT NULL, recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CHECK (binding_revision > 0), CHECK (model_revision > 0),
|
|
CHECK (context_units IS NULL OR context_units >= 0), CHECK (generation_tps_millis IS NULL OR generation_tps_millis >= 0),
|
|
CHECK (prompt_tps_millis IS NULL OR prompt_tps_millis >= 0), CHECK (ttft_micros IS NULL OR ttft_micros >= 0),
|
|
CHECK (latency_micros IS NULL OR latency_micros >= 0), CHECK (memory_bytes IS NULL OR memory_bytes >= 0),
|
|
CHECK (reliability_millis IS NULL OR reliability_millis >= 0), CHECK (observed_at_unix_millis > 0)
|
|
);
|
|
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;
|
|
INSERT INTO capability.notification_bindings (
|
|
binding_id, capability_id, owner_principal_id, revision, state, https_origin,
|
|
delivery_path, receipt_path, protocol, secret_ref, verified_at_unix_millis, health
|
|
)
|
|
VALUES (
|
|
'domovoy-notification-binding', 'prospective-notification-delivery', 'domovoy-creator-principal',
|
|
2, 'ACTIVE', 'https://assistant.cin.su', '/delivery/v1/deliveries', '/delivery/v1/receipts',
|
|
'IDEMPOTENT_RECEIPT_V1', 'domovoy-notification-write-v1', 1, 'AVAILABLE'
|
|
)
|
|
ON CONFLICT DO NOTHING;
|
|
INSERT INTO capability.notification_binding_heads(capability_id, owner_principal_id, binding_id, revision)
|
|
VALUES ('prospective-notification-delivery', 'domovoy-creator-principal', 'domovoy-notification-binding', 2)
|
|
ON CONFLICT (capability_id, owner_principal_id) DO UPDATE SET binding_id=EXCLUDED.binding_id, revision=EXCLUDED.revision
|
|
WHERE capability.notification_binding_heads.revision < EXCLUDED.revision;
|
|
RESET ROLE;
|
|
|
|
SET ROLE continuity_role;
|
|
CREATE TABLE IF NOT EXISTS continuity.prospective_items (
|
|
prospective_item_id TEXT PRIMARY KEY,
|
|
commitment_id TEXT NOT NULL,
|
|
owner_principal_id TEXT NOT NULL,
|
|
scope_id TEXT NOT NULL,
|
|
goal_id TEXT NOT NULL,
|
|
source_intent_id TEXT NOT NULL DEFAULT '',
|
|
source_utterance_id TEXT NOT NULL DEFAULT '',
|
|
summary TEXT NOT NULL DEFAULT '',
|
|
trigger_at_unix_millis BIGINT NOT NULL,
|
|
state TEXT NOT NULL,
|
|
revision BIGINT NOT NULL,
|
|
recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CHECK (revision > 0)
|
|
);
|
|
CREATE TABLE IF NOT EXISTS continuity.action_receipts (
|
|
action_intent_id TEXT PRIMARY KEY,
|
|
authorized_action_id TEXT NOT NULL,
|
|
owner_principal_id TEXT NOT NULL,
|
|
prospective_item_id TEXT NOT NULL,
|
|
expected_revision BIGINT NOT NULL,
|
|
from_state TEXT NOT NULL,
|
|
to_state TEXT NOT NULL,
|
|
evidence_ref TEXT NOT NULL,
|
|
result_state TEXT NOT NULL,
|
|
result_revision BIGINT NOT NULL,
|
|
recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CHECK (expected_revision > 0), CHECK (result_revision > expected_revision)
|
|
);
|
|
CREATE TABLE IF NOT EXISTS continuity.attention_items (
|
|
attention_item_id TEXT PRIMARY KEY,
|
|
owner_principal_id TEXT NOT NULL,
|
|
scope_id TEXT NOT NULL,
|
|
source_kind TEXT NOT NULL,
|
|
source_prospective_item_id TEXT NOT NULL,
|
|
level TEXT NOT NULL,
|
|
state TEXT NOT NULL,
|
|
summary TEXT NOT NULL,
|
|
due_at_unix_millis BIGINT NOT NULL,
|
|
created_at_unix_millis BIGINT NOT NULL,
|
|
delivered_at_unix_millis BIGINT NOT NULL DEFAULT 0,
|
|
action_intent_id TEXT NOT NULL DEFAULT '',
|
|
authorized_action_id TEXT NOT NULL DEFAULT '',
|
|
action_attempt_id TEXT NOT NULL DEFAULT '',
|
|
delivery_outcome TEXT NOT NULL DEFAULT '',
|
|
response_id TEXT NOT NULL DEFAULT '',
|
|
response_utterance_id TEXT NOT NULL DEFAULT '',
|
|
response_operation TEXT NOT NULL DEFAULT '',
|
|
response_outcome TEXT NOT NULL DEFAULT '',
|
|
responded_at_unix_millis BIGINT NOT NULL DEFAULT 0,
|
|
revision BIGINT NOT NULL,
|
|
recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE (source_kind, source_prospective_item_id),
|
|
CHECK (source_kind IN ('PROSPECTIVE_REMINDER')),
|
|
CHECK (level IN ('URGENT','ACTION_REQUIRED','DUE','IMPORTANT_CHANGE','FYI','BACKGROUND')),
|
|
CHECK (state IN ('PENDING','DELIVERING','DELIVERED','RESOLVED','SUPPRESSED','EXPIRED','FAILED')),
|
|
CHECK (summary <> ''),
|
|
CHECK (due_at_unix_millis > 0),
|
|
CHECK (created_at_unix_millis > 0),
|
|
CHECK (revision > 0),
|
|
CHECK (state <> 'DELIVERED' OR (delivered_at_unix_millis > 0 AND action_intent_id <> '' AND authorized_action_id <> '' AND action_attempt_id <> '' AND delivery_outcome <> '')),
|
|
CHECK (state <> 'RESOLVED' OR (
|
|
response_id <> '' AND response_utterance_id <> '' AND
|
|
response_operation IN ('ACKNOWLEDGE','COMPLETE','SNOOZE','CANCEL','DISMISS','CONTINUE','REJECT') AND
|
|
response_outcome IN ('USER_REPORTED_COMPLETE','SNOOZED','CANCELLED','DISMISSED','NOOP') AND
|
|
responded_at_unix_millis > 0
|
|
))
|
|
);
|
|
CREATE TABLE IF NOT EXISTS continuity.human_responses (
|
|
human_response_id TEXT PRIMARY KEY,
|
|
owner_principal_id TEXT NOT NULL,
|
|
scope_id TEXT NOT NULL,
|
|
source_utterance_id TEXT NOT NULL,
|
|
source_intent_id TEXT NOT NULL,
|
|
operation TEXT NOT NULL,
|
|
target_kind TEXT NOT NULL,
|
|
target_attention_id TEXT NOT NULL DEFAULT '',
|
|
target_prospective_item_id TEXT NOT NULL DEFAULT '',
|
|
target_external_ref TEXT NOT NULL DEFAULT '',
|
|
resolution_state TEXT NOT NULL,
|
|
outcome TEXT NOT NULL,
|
|
clarification TEXT NOT NULL DEFAULT '',
|
|
snooze_until_unix_millis BIGINT NOT NULL DEFAULT 0,
|
|
user_reported_not_verified BOOLEAN NOT NULL DEFAULT FALSE,
|
|
recorded_at_unix_millis BIGINT NOT NULL,
|
|
revision BIGINT NOT NULL,
|
|
recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CHECK (owner_principal_id <> ''),
|
|
CHECK (scope_id <> ''),
|
|
CHECK (source_utterance_id <> ''),
|
|
CHECK (source_intent_id <> ''),
|
|
CHECK (operation IN ('ACKNOWLEDGE','COMPLETE','SNOOZE','CANCEL','DISMISS','CONTINUE','REJECT','UNKNOWN')),
|
|
CHECK (target_kind IN ('ATTENTION_ITEM','PROSPECTIVE_ITEM','WORK_TASK','CAPABILITY_GAP','INTERACTION_CONTEXT','UNKNOWN')),
|
|
CHECK (resolution_state IN ('RESOLVED','AMBIGUOUS','NO_TARGET','NOOP')),
|
|
CHECK (outcome IN ('USER_REPORTED_COMPLETE','SNOOZED','CANCELLED','DISMISSED','CLARIFICATION_NEEDED','NOOP')),
|
|
CHECK (recorded_at_unix_millis > 0),
|
|
CHECK (revision > 0),
|
|
CHECK (operation <> 'SNOOZE' OR snooze_until_unix_millis > recorded_at_unix_millis),
|
|
CHECK (resolution_state <> 'RESOLVED' OR target_kind <> 'UNKNOWN')
|
|
);
|
|
RESET ROLE;
|
|
|
|
SET ROLE interaction_role;
|
|
CREATE TABLE IF NOT EXISTS interaction.conversations (
|
|
conversation_id TEXT PRIMARY KEY,
|
|
actor_id TEXT NOT NULL,
|
|
principal_id TEXT NOT NULL,
|
|
channel TEXT NOT NULL,
|
|
state TEXT NOT NULL,
|
|
created_at_unix_millis BIGINT NOT NULL,
|
|
updated_at_unix_millis BIGINT NOT NULL,
|
|
CHECK (channel = 'WEB_CHAT'),
|
|
CHECK (state IN ('OPEN','ARCHIVED'))
|
|
);
|
|
CREATE TABLE IF NOT EXISTS interaction.utterances (
|
|
utterance_id TEXT PRIMARY KEY,
|
|
conversation_id TEXT NOT NULL,
|
|
actor_id TEXT NOT NULL,
|
|
principal_id TEXT NOT NULL,
|
|
channel TEXT NOT NULL,
|
|
received_at_unix_millis BIGINT NOT NULL,
|
|
correlation_id TEXT NOT NULL,
|
|
content TEXT NOT NULL,
|
|
content_ref TEXT NOT NULL,
|
|
speech_act TEXT NOT NULL,
|
|
interpretation_state TEXT NOT NULL,
|
|
corrects_utterance_id TEXT NOT NULL DEFAULT '',
|
|
CHECK (channel = 'WEB_CHAT'),
|
|
CHECK (content <> ''),
|
|
CHECK (content_ref <> ''),
|
|
CHECK (speech_act IN ('DIRECTIVE','ASSERTION','DECISION','PREFERENCE','QUESTION','HYPOTHESIS','CORRECTION','REQUEST_TO_INVESTIGATE','CONFIRMATION','REJECTION','CANCELLATION','CONVERSATIONAL','UNKNOWN')),
|
|
CHECK (interpretation_state IN ('RECORDED','INTERPRETED'))
|
|
);
|
|
CREATE TABLE IF NOT EXISTS interaction.original_human_intents (
|
|
intent_id TEXT PRIMARY KEY,
|
|
utterance_id TEXT NOT NULL,
|
|
recorded_at_unix_millis BIGINT NOT NULL,
|
|
text TEXT NOT NULL,
|
|
CHECK (text <> '')
|
|
);
|
|
CREATE TABLE IF NOT EXISTS interaction.relations (
|
|
relation_id TEXT PRIMARY KEY,
|
|
from_utterance_id TEXT NOT NULL,
|
|
to_utterance_id TEXT NOT NULL,
|
|
kind TEXT NOT NULL,
|
|
recorded_at_unix_millis BIGINT NOT NULL,
|
|
CHECK (kind IN ('CORRECTS','RESPONDS_TO','REQUESTS_COGNITION','COGNITION_FALLBACK','SUPERSEDES','CLARIFIES'))
|
|
);
|
|
CREATE TABLE IF NOT EXISTS interaction.utterance_interpretations (
|
|
interpretation_id TEXT PRIMARY KEY,
|
|
utterance_id TEXT NOT NULL,
|
|
speech_act TEXT NOT NULL,
|
|
confidence_millis BIGINT NOT NULL,
|
|
source TEXT NOT NULL,
|
|
assertion_verification TEXT NOT NULL,
|
|
requested_outcome TEXT NOT NULL DEFAULT '',
|
|
clarification TEXT NOT NULL,
|
|
recorded_at_unix_millis BIGINT NOT NULL,
|
|
CHECK (speech_act IN ('DIRECTIVE','ASSERTION','DECISION','PREFERENCE','QUESTION','HYPOTHESIS','CORRECTION','REQUEST_TO_INVESTIGATE','CONFIRMATION','REJECTION','CANCELLATION','CONVERSATIONAL','UNKNOWN')),
|
|
CHECK (confidence_millis BETWEEN 0 AND 1000),
|
|
CHECK (assertion_verification IN ('UNVERIFIED','VERIFIED','REJECTED','NOT_APPLICABLE')),
|
|
CHECK (clarification IN ('NOT_REQUIRED','REQUIRED'))
|
|
);
|
|
CREATE TABLE IF NOT EXISTS interaction.project_references (
|
|
project_reference_id TEXT PRIMARY KEY,
|
|
utterance_id TEXT NOT NULL,
|
|
project_id TEXT NOT NULL,
|
|
project_display_name TEXT NOT NULL,
|
|
role TEXT NOT NULL,
|
|
provenance TEXT NOT NULL,
|
|
confidence_millis BIGINT NOT NULL,
|
|
recorded_at_unix_millis BIGINT NOT NULL,
|
|
CHECK (role IN ('PRIMARY','RELATED','REFERENCE_SOURCE')),
|
|
CHECK (provenance IN ('EXPLICIT','INHERITED_FOCUS','ENTITY_REFERENCE','TASK_REFERENCE','REPOSITORY_REFERENCE','INFERRED','USER_CONFIRMED','AMBIGUOUS')),
|
|
CHECK (confidence_millis BETWEEN 0 AND 1000)
|
|
);
|
|
RESET ROLE;
|
|
COMMIT;
|