119 lines
4.9 KiB
SQL
119 lines
4.9 KiB
SQL
-- SPDX-License-Identifier: LicenseRef-ConusAI-Template-1.0
|
|
-- The Agent is the workspace's (2026-11-03): its conversations are a workspace's, with a project optional. Its tables
|
|
-- are made again from the kit's migrations/0001_assistant.sql, and what 0004 held goes with them: no conversation is
|
|
-- carried over, because there was none to keep (no user had the template's Agent yet).
|
|
drop table "assistant_receipt";
|
|
|
|
drop table "assistant_thread";
|
|
|
|
drop table "assistant_turn";
|
|
|
|
drop table "assistant_conversation";
|
|
|
|
create table "assistant_conversation" (
|
|
"id" uuid primary key,
|
|
"version" integer not null,
|
|
"created_at" timestamptz not null,
|
|
"updated_at" timestamptz not null,
|
|
"org_id" uuid not null,
|
|
"workspace_id" uuid not null,
|
|
"title" text not null,
|
|
"created_by" text not null,
|
|
"last_turn_at" text not null,
|
|
"project_id" uuid
|
|
);
|
|
|
|
create index "assistant_conversation_org_id_workspace_id_created_by_idx" on "assistant_conversation" ("org_id", "workspace_id", "created_by");
|
|
|
|
create index "assistant_conversation_org_id_workspace_id_last_turn_at_idx" on "assistant_conversation" ("org_id", "workspace_id", "last_turn_at");
|
|
|
|
create index "assistant_conversation_org_id_workspace_id_project_id_idx" on "assistant_conversation" ("org_id", "workspace_id", "project_id");
|
|
|
|
create table "assistant_receipt" (
|
|
"id" uuid primary key,
|
|
"version" integer not null,
|
|
"created_at" timestamptz not null,
|
|
"updated_at" timestamptz not null,
|
|
"org_id" uuid not null,
|
|
"workspace_id" uuid not null,
|
|
"conversation_id" uuid not null,
|
|
"turn_id" text not null,
|
|
"step" integer not null,
|
|
"tool_call_id" text not null,
|
|
"tool" text not null,
|
|
"kind" text not null,
|
|
"outcome" text not null,
|
|
"proposal_id" text,
|
|
"arguments_hash" text not null,
|
|
"created_by" text not null,
|
|
"project_id" uuid
|
|
);
|
|
|
|
alter table "assistant_receipt" add constraint "assistant_receipt_org_id_workspace_id_turn_id_step_tool_call_id_key" unique nulls not distinct ("org_id", "workspace_id", "turn_id", "step", "tool_call_id");
|
|
|
|
create index "assistant_receipt_org_id_workspace_id_created_by_idx" on "assistant_receipt" ("org_id", "workspace_id", "created_by");
|
|
|
|
create index "assistant_receipt_org_id_workspace_id_conversation_id_idx" on "assistant_receipt" ("org_id", "workspace_id", "conversation_id");
|
|
|
|
create table "assistant_thread" (
|
|
"id" uuid primary key,
|
|
"version" integer not null,
|
|
"created_at" timestamptz not null,
|
|
"updated_at" timestamptz not null,
|
|
"org_id" uuid not null,
|
|
"workspace_id" uuid not null,
|
|
"thread_id" text not null,
|
|
"messages" jsonb not null
|
|
);
|
|
|
|
alter table "assistant_thread" add constraint "assistant_thread_org_id_workspace_id_thread_id_key" unique nulls not distinct ("org_id", "workspace_id", "thread_id");
|
|
|
|
create table "assistant_turn" (
|
|
"id" uuid primary key,
|
|
"version" integer not null,
|
|
"created_at" timestamptz not null,
|
|
"updated_at" timestamptz not null,
|
|
"org_id" uuid not null,
|
|
"workspace_id" uuid not null,
|
|
"turn_id" text not null,
|
|
"conversation_id" uuid not null,
|
|
"created_by" text not null,
|
|
"input_hash" text not null,
|
|
"status" text not null,
|
|
"steps" integer not null,
|
|
"tool_calls" integer not null,
|
|
"degraded" boolean not null,
|
|
"answer_provenance" jsonb,
|
|
"started_at" text not null,
|
|
"deadline_at" text not null,
|
|
"finished_at" text
|
|
);
|
|
|
|
alter table "assistant_turn" add constraint "assistant_turn_org_id_workspace_id_turn_id_key" unique nulls not distinct ("org_id", "workspace_id", "turn_id");
|
|
|
|
create index "assistant_turn_org_id_workspace_id_conversation_id_idx" on "assistant_turn" ("org_id", "workspace_id", "conversation_id");
|
|
|
|
alter table "assistant_conversation" enable row level security;
|
|
|
|
alter table "assistant_conversation" force row level security;
|
|
|
|
create policy "assistant_conversation_tenant" on "assistant_conversation" using ("org_id" = nullif(current_setting('app.org_id', true), '')::uuid) with check ("org_id" = nullif(current_setting('app.org_id', true), '')::uuid);
|
|
|
|
alter table "assistant_receipt" enable row level security;
|
|
|
|
alter table "assistant_receipt" force row level security;
|
|
|
|
create policy "assistant_receipt_tenant" on "assistant_receipt" using ("org_id" = nullif(current_setting('app.org_id', true), '')::uuid) with check ("org_id" = nullif(current_setting('app.org_id', true), '')::uuid);
|
|
|
|
alter table "assistant_thread" enable row level security;
|
|
|
|
alter table "assistant_thread" force row level security;
|
|
|
|
create policy "assistant_thread_tenant" on "assistant_thread" using ("org_id" = nullif(current_setting('app.org_id', true), '')::uuid) with check ("org_id" = nullif(current_setting('app.org_id', true), '')::uuid);
|
|
|
|
alter table "assistant_turn" enable row level security;
|
|
|
|
alter table "assistant_turn" force row level security;
|
|
|
|
create policy "assistant_turn_tenant" on "assistant_turn" using ("org_id" = nullif(current_setting('app.org_id', true), '')::uuid) with check ("org_id" = nullif(current_setting('app.org_id', true), '')::uuid);
|