AgentBlock/db/migrations/202606230001_create_visa_schema.sql

create table if not exists models (
  id text primary key,
  name text not null,
  description text not null default '',
  domain text not null default '',
  author text not null default '',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists model_versions (
  id text primary key,
  model_id text not null references models (id) on delete cascade,
  version_name text not null,
  status text not null default 'draft' check (status in ('draft', 'checked', 'released', 'archived')),
  source_protocol text not null default 'VISA',
  created_by text not null default '',
  created_at timestamptz not null default now(),
  notes text not null default ''
);

create table if not exists visa_agents (
  id text primary key,
  model_version_id text not null references model_versions (id) on delete cascade,
  name text not null,
  symbol_set text not null,
  instance_symbol text not null,
  category text not null check (category in ('Environment', 'Space', 'Decision-maker', 'Passive')),
  description text not null default '',
  quantity_symbol text not null,
  quantity_type text not null check (quantity_type in ('fixed', 'variable')),
  quantity_value text not null,
  sort_order integer not null default 0,
  unique (model_version_id, name),
  unique (model_version_id, symbol_set)
);

create table if not exists visa_variables (
  id text primary key,
  model_version_id text not null references model_versions (id) on delete cascade,
  agent_id text not null references visa_agents (id) on delete cascade,
  name text not null,
  symbol text not null,
  variable_type text not null check (variable_type in ('exog_homo', 'exog_hetero', 'endog_decision', 'endog_non_decision')),
  data_type text not null,
  value_source text not null,
  unit text not null default '',
  description text not null default '',
  is_time_indexed boolean not null default false,
  sort_order integer not null default 0,
  unique (model_version_id, agent_id, name),
  unique (model_version_id, symbol)
);

create table if not exists visa_sensing_relations (
  id text primary key,
  model_version_id text not null references model_versions (id) on delete cascade,
  observer_agent_id text not null references visa_agents (id) on delete cascade,
  observed_agent_id text not null references visa_agents (id) on delete cascade,
  access_type text not null check (access_type in ('none', 'partial', 'all')),
  scope text not null check (scope in ('self', 'peer', 'other', 'all', 'singleton')),
  condition text not null default '',
  notes text not null default ''
);

create table if not exists visa_sensing_variables (
  sensing_relation_id text not null references visa_sensing_relations (id) on delete cascade,
  variable_id text not null references visa_variables (id) on delete cascade,
  primary key (sensing_relation_id, variable_id)
);

create table if not exists visa_internal_functions (
  id text primary key,
  model_version_id text not null references model_versions (id) on delete cascade,
  agent_id text not null references visa_agents (id) on delete cascade,
  function_code text not null,
  name text not null,
  method text not null,
  reference text not null default '',
  description text not null default '',
  sort_order integer not null default 0,
  unique (model_version_id, function_code)
);

create table if not exists visa_function_inputs (
  function_id text not null references visa_internal_functions (id) on delete cascade,
  variable_id text not null references visa_variables (id) on delete cascade,
  source_type text not null check (source_type in ('self', 'sensed', 'input', 'output')),
  expression text not null default '',
  primary key (function_id, variable_id, source_type)
);

create table if not exists visa_function_updates (
  id text primary key,
  function_id text not null references visa_internal_functions (id) on delete cascade,
  target_agent_id text references visa_agents (id) on delete set null,
  variable_id text references visa_variables (id) on delete set null,
  update_type text not null check (update_type in ('self_state', 'external_effect', 'create_agent', 'remove_agent')),
  expression text not null default ''
);

create table if not exists visa_associated_data (
  id text primary key,
  model_version_id text not null references model_versions (id) on delete cascade,
  data_code text not null,
  title text not null,
  data_type text not null check (data_type in ('Empirical', 'Literature', 'Generated')),
  temporal_type text not null check (temporal_type in ('Static', 'Dynamic')),
  source text not null,
  collection_method text not null check (collection_method in ('Survey', 'Administrative', 'Sensor', 'Experimental', 'Computational')),
  preprocessing_method text not null check (preprocessing_method in ('None', 'Selected', 'Aggregated', 'Transformed')),
  record_count text not null default '',
  availability text not null check (availability in ('Open', 'Restricted', 'Private')),
  license text not null default '',
  url_or_reference text not null default '',
  description text not null default '',
  unique (model_version_id, data_code)
);

create table if not exists visa_inputs (
  id text primary key,
  model_version_id text not null references model_versions (id) on delete cascade,
  agent_id text not null references visa_agents (id) on delete cascade,
  variable_id text not null references visa_variables (id) on delete cascade,
  symbol text not null,
  value_or_distribution text not null,
  data_source_id text references visa_associated_data (id) on delete set null,
  derivation text not null check (derivation in ('Direct', 'Estimated', 'Computed', 'Assumed')),
  algorithm text not null default '',
  reference text not null default '',
  notes text not null default '',
  unique (model_version_id, variable_id)
);

create table if not exists visa_outputs (
  id text primary key,
  model_version_id text not null references model_versions (id) on delete cascade,
  symbol text not null,
  indicator_name text not null,
  formula text not null,
  data_type text not null,
  unit text not null default '',
  frequency text not null,
  description text not null default '',
  unique (model_version_id, symbol)
);

create table if not exists visa_schedule_steps (
  id text primary key,
  model_version_id text not null references model_versions (id) on delete cascade,
  step_number integer not null,
  agent_id text not null references visa_agents (id) on delete cascade,
  function_id text not null references visa_internal_functions (id) on delete cascade,
  execution_mode text not null check (execution_mode in ('Synchronous', 'Sequential', 'Random-order', 'Asynchronous')),
  execution_parameters text not null default '',
  condition text not null default '',
  sort_order integer not null default 0,
  unique (model_version_id, step_number, function_id)
);

create table if not exists visa_termination_conditions (
  id text primary key,
  model_version_id text not null references model_versions (id) on delete cascade,
  condition_code text not null,
  indicator_symbol text not null,
  condition_expression text not null,
  description text not null default '',
  value_source text not null default '',
  termination_logic text not null,
  unique (model_version_id, condition_code)
);

create table if not exists visa_validations (
  id text primary key,
  model_version_id text not null references model_versions (id) on delete cascade,
  validation_code text not null,
  level text not null check (level in ('Agent', 'Model', 'Output')),
  validation_object text not null,
  benchmark_data_id text references visa_associated_data (id) on delete set null,
  method text not null,
  indicator text not null,
  passing_condition text not null,
  reference text not null default '',
  status text not null default 'pending' check (status in ('pending', 'passed', 'failed', 'blocked')),
  notes text not null default '',
  unique (model_version_id, validation_code)
);

create table if not exists visa_consistency_check_results (
  id text primary key,
  model_version_id text not null references model_versions (id) on delete cascade,
  rule_code text not null,
  rule_type text not null check (rule_type in ('within_table', 'cross_table')),
  status text not null check (status in ('passed', 'failed', 'warning')),
  message text not null,
  related_table text not null default '',
  related_record_id text not null default '',
  checked_at timestamptz not null default now()
);

create index if not exists idx_model_versions_model on model_versions (model_id);
create index if not exists idx_visa_agents_model_version on visa_agents (model_version_id);
create index if not exists idx_visa_variables_agent on visa_variables (agent_id);
create index if not exists idx_visa_sensing_observer on visa_sensing_relations (observer_agent_id);
create index if not exists idx_visa_functions_agent on visa_internal_functions (agent_id);
create index if not exists idx_visa_schedule_model_version on visa_schedule_steps (model_version_id, step_number);
create index if not exists idx_visa_checks_model_version on visa_consistency_check_results (model_version_id, checked_at);