james.maesandClaude Opus 5.5
01cc719c5b
Owner decisions (2026-10-13): tools via the UI API, configurable and verifiable data access, and confirmation for writes. - Every data tool runs a spec'd /qqq/v1 operation (UiApiGateway, a loopback request carrying the user's own sessionUUID cookie), so it passes the UI's authentication, permissions, record locks, validation, audit and error mapping. LoopbackUiApiGateway is a stand-in for core's UiApiInvoker (QRun-IO/qqq#1128). navigate and search_help (no records) stay on the core kernel. - Data access (assistant_data_access): NONE / READ / READ_WRITE per table or process; sensitive tables NONE and cannot be raised; it only narrows. - Writes (create/update/delete_record) propose a pending action (assistant_pending_action) the owner confirms; confirm runs it once through the UI API with the confirming request's session. - Policy: writesRequireConfirmation, allowedTools, maxRowsPerCall, redactPii. Tool-call log: UI API operation, HTTP status, audit link, pending action; inputs stored with secrets redacted. - UiApiToolsSecurityTest proves a user without a permission gets the same denial through the assistant as through the UI; golden and red-team evals run through a real application server. Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
289 lines
9.8 KiB
SQL
289 lines
9.8 KiB
SQL
-- Reference PostgreSQL DDL for the qbit-assistant tables (snake_case, as mapped by a
|
|
-- snake_case table customizer). Adjust to your conventions; the QBit only needs the
|
|
-- QQQ field names to map onto columns.
|
|
|
|
CREATE TABLE assistant_conversation
|
|
(
|
|
id SERIAL PRIMARY KEY,
|
|
user_id VARCHAR(255),
|
|
title VARCHAR(255),
|
|
status VARCHAR(16),
|
|
pinned BOOLEAN,
|
|
last_message_at TIMESTAMP WITH TIME ZONE,
|
|
unread_count INTEGER,
|
|
context_json TEXT,
|
|
total_tokens_in BIGINT,
|
|
total_tokens_out BIGINT,
|
|
total_cost_micros BIGINT,
|
|
create_date TIMESTAMP WITH TIME ZONE,
|
|
modify_date TIMESTAMP WITH TIME ZONE
|
|
);
|
|
CREATE INDEX assistant_conversation_user_id ON assistant_conversation (user_id);
|
|
|
|
CREATE TABLE assistant_message
|
|
(
|
|
id SERIAL PRIMARY KEY,
|
|
conversation_id INTEGER,
|
|
user_id VARCHAR(255),
|
|
parent_message_id INTEGER,
|
|
role VARCHAR(16),
|
|
status VARCHAR(16),
|
|
turn_id INTEGER,
|
|
seq INTEGER,
|
|
model VARCHAR(64),
|
|
parts_json TEXT,
|
|
create_date TIMESTAMP WITH TIME ZONE,
|
|
modify_date TIMESTAMP WITH TIME ZONE
|
|
);
|
|
CREATE INDEX assistant_message_user_id ON assistant_message (user_id);
|
|
CREATE INDEX assistant_message_conversation_id ON assistant_message (conversation_id);
|
|
|
|
CREATE TABLE assistant_turn
|
|
(
|
|
id SERIAL PRIMARY KEY,
|
|
conversation_id INTEGER,
|
|
user_id VARCHAR(255),
|
|
user_message_id INTEGER,
|
|
assistant_message_id INTEGER,
|
|
status VARCHAR(16),
|
|
stop_reason VARCHAR(64),
|
|
error_code VARCHAR(32),
|
|
cancel_reason VARCHAR(64),
|
|
model VARCHAR(64),
|
|
started_at TIMESTAMP WITH TIME ZONE,
|
|
ended_at TIMESTAMP WITH TIME ZONE,
|
|
tool_call_count INTEGER,
|
|
model_call_count INTEGER,
|
|
input_tokens BIGINT,
|
|
output_tokens BIGINT,
|
|
cache_read_tokens BIGINT,
|
|
cache_write_tokens BIGINT,
|
|
cost_micros BIGINT,
|
|
provider_request_ids TEXT,
|
|
create_date TIMESTAMP WITH TIME ZONE,
|
|
modify_date TIMESTAMP WITH TIME ZONE
|
|
);
|
|
CREATE INDEX assistant_turn_user_id ON assistant_turn (user_id);
|
|
CREATE INDEX assistant_turn_conversation_id ON assistant_turn (conversation_id);
|
|
|
|
CREATE TABLE assistant_turn_event
|
|
(
|
|
id SERIAL PRIMARY KEY,
|
|
turn_id INTEGER,
|
|
user_id VARCHAR(255),
|
|
seq INTEGER,
|
|
event_type VARCHAR(32),
|
|
data_json TEXT,
|
|
create_date TIMESTAMP WITH TIME ZONE
|
|
);
|
|
CREATE INDEX assistant_turn_event_user_id ON assistant_turn_event (user_id);
|
|
CREATE INDEX assistant_turn_event_turn ON assistant_turn_event (turn_id, seq);
|
|
|
|
CREATE TABLE assistant_tool_call
|
|
(
|
|
id SERIAL PRIMARY KEY,
|
|
turn_id INTEGER,
|
|
conversation_id INTEGER,
|
|
user_id VARCHAR(255),
|
|
tool_call_id VARCHAR(128),
|
|
tool_name VARCHAR(64),
|
|
input_json TEXT,
|
|
outcome VARCHAR(16),
|
|
permission_check VARCHAR(255),
|
|
table_name VARCHAR(128),
|
|
process_name VARCHAR(128),
|
|
widget_name VARCHAR(128),
|
|
row_count INTEGER,
|
|
duration_ms BIGINT,
|
|
result_summary VARCHAR(500),
|
|
result_json TEXT,
|
|
redactions_json TEXT,
|
|
ui_api_operation VARCHAR(255),
|
|
http_status INTEGER,
|
|
audit_href VARCHAR(500),
|
|
pending_action_id INTEGER,
|
|
create_date TIMESTAMP WITH TIME ZONE
|
|
);
|
|
CREATE INDEX assistant_tool_call_user_id ON assistant_tool_call (user_id);
|
|
CREATE INDEX assistant_tool_call_conversation_id ON assistant_tool_call (conversation_id);
|
|
CREATE INDEX assistant_tool_call_turn ON assistant_tool_call (turn_id);
|
|
|
|
CREATE TABLE assistant_feedback
|
|
(
|
|
id SERIAL PRIMARY KEY,
|
|
message_id INTEGER,
|
|
conversation_id INTEGER,
|
|
user_id VARCHAR(255),
|
|
rating INTEGER,
|
|
reason VARCHAR(64),
|
|
comment TEXT,
|
|
create_date TIMESTAMP WITH TIME ZONE
|
|
);
|
|
CREATE INDEX assistant_feedback_user_id ON assistant_feedback (user_id);
|
|
CREATE INDEX assistant_feedback_conversation_id ON assistant_feedback (conversation_id);
|
|
|
|
CREATE TABLE assistant_policy
|
|
(
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(64),
|
|
kill_switch BOOLEAN,
|
|
kill_switch_reason VARCHAR(255),
|
|
main_model VARCHAR(64),
|
|
main_effort VARCHAR(16),
|
|
user_daily_tokens_soft BIGINT,
|
|
user_daily_tokens_hard BIGINT,
|
|
user_daily_cost_micros_soft BIGINT,
|
|
user_daily_cost_micros_hard BIGINT,
|
|
instance_monthly_tokens_soft BIGINT,
|
|
instance_monthly_tokens_hard BIGINT,
|
|
instance_monthly_cost_micros_soft BIGINT,
|
|
instance_monthly_cost_micros_hard BIGINT,
|
|
writes_require_confirmation BOOLEAN,
|
|
allowed_tools VARCHAR(1000),
|
|
max_rows_per_call INTEGER,
|
|
redact_pii BOOLEAN,
|
|
create_date TIMESTAMP WITH TIME ZONE,
|
|
modify_date TIMESTAMP WITH TIME ZONE
|
|
);
|
|
|
|
CREATE TABLE assistant_usage_daily
|
|
(
|
|
id SERIAL PRIMARY KEY,
|
|
scope VARCHAR(16),
|
|
user_id VARCHAR(255),
|
|
usage_date DATE,
|
|
turn_count INTEGER,
|
|
input_tokens BIGINT,
|
|
output_tokens BIGINT,
|
|
cache_read_tokens BIGINT,
|
|
cache_write_tokens BIGINT,
|
|
cost_micros BIGINT,
|
|
create_date TIMESTAMP WITH TIME ZONE,
|
|
modify_date TIMESTAMP WITH TIME ZONE
|
|
);
|
|
CREATE INDEX assistant_usage_daily_user_id ON assistant_usage_daily (user_id);
|
|
CREATE INDEX assistant_usage_daily_date ON assistant_usage_daily (usage_date, scope);
|
|
|
|
-- 0.1.0-RC.4: provider settings, model prices and per-call usage.
|
|
-- api_key_status is filled when a row is read (never written); the API key itself
|
|
-- is never stored: api_key_env_name holds only the NAME of an environment variable.
|
|
|
|
CREATE TABLE assistant_provider
|
|
(
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(100) NOT NULL UNIQUE,
|
|
provider_type VARCHAR(50) NOT NULL,
|
|
model VARCHAR(100),
|
|
enabled BOOLEAN,
|
|
is_default BOOLEAN,
|
|
base_url VARCHAR(255),
|
|
api_key_env_name VARCHAR(100),
|
|
api_key_status VARCHAR(200),
|
|
monthly_cost_cap_usd NUMERIC(12, 2),
|
|
max_output_tokens_per_request INTEGER,
|
|
max_concurrent_requests INTEGER,
|
|
last_test_at TIMESTAMP WITH TIME ZONE,
|
|
last_test_status VARCHAR(16),
|
|
last_test_latency_ms INTEGER,
|
|
last_test_model VARCHAR(100),
|
|
last_test_error VARCHAR(500),
|
|
create_date TIMESTAMP WITH TIME ZONE,
|
|
modify_date TIMESTAMP WITH TIME ZONE
|
|
);
|
|
|
|
CREATE TABLE assistant_model_price
|
|
(
|
|
id SERIAL PRIMARY KEY,
|
|
provider_type VARCHAR(50) NOT NULL,
|
|
model VARCHAR(100) NOT NULL,
|
|
input_usd_per_m_tok NUMERIC(12, 4) NOT NULL,
|
|
output_usd_per_m_tok NUMERIC(12, 4) NOT NULL,
|
|
cache_read_usd_per_m_tok NUMERIC(12, 4),
|
|
cache_write5m_usd_per_m_tok NUMERIC(12, 4),
|
|
cache_write1h_usd_per_m_tok NUMERIC(12, 4),
|
|
notes VARCHAR(255),
|
|
create_date TIMESTAMP WITH TIME ZONE,
|
|
modify_date TIMESTAMP WITH TIME ZONE,
|
|
UNIQUE (provider_type, model)
|
|
);
|
|
|
|
CREATE TABLE assistant_usage
|
|
(
|
|
id SERIAL PRIMARY KEY,
|
|
user_id VARCHAR(255),
|
|
conversation_id INTEGER,
|
|
turn_id INTEGER,
|
|
provider_id INTEGER,
|
|
provider_type VARCHAR(50),
|
|
model VARCHAR(100),
|
|
purpose VARCHAR(16),
|
|
status VARCHAR(16),
|
|
error_kind VARCHAR(32),
|
|
http_status INTEGER,
|
|
latency_ms INTEGER,
|
|
input_tokens BIGINT,
|
|
output_tokens BIGINT,
|
|
cache_read_tokens BIGINT,
|
|
cache_write_tokens BIGINT,
|
|
cost_micros BIGINT,
|
|
request_id VARCHAR(100),
|
|
create_date TIMESTAMP WITH TIME ZONE
|
|
);
|
|
CREATE INDEX assistant_usage_create_date ON assistant_usage (create_date);
|
|
CREATE INDEX assistant_usage_provider ON assistant_usage (provider_id, create_date);
|
|
CREATE INDEX assistant_usage_conversation ON assistant_usage (conversation_id);
|
|
CREATE INDEX assistant_usage_user_id ON assistant_usage (user_id);
|
|
|
|
-- Anthropic list prices, USD per million tokens (cached 2026-10-06). Cache writes:
|
|
-- 1.25x input (5 minute) and 2x input (1 hour). Re-check before budgeting.
|
|
INSERT INTO assistant_model_price (provider_type, model, input_usd_per_m_tok, output_usd_per_m_tok, cache_read_usd_per_m_tok, cache_write5m_usd_per_m_tok, cache_write1h_usd_per_m_tok, notes, create_date, modify_date)
|
|
VALUES
|
|
('anthropic', 'claude-sonnet-5-5', 2.00, 10.00, 0.20, 2.50, 4.00, 'Anthropic list price, 2026-10', NOW(), NOW()),
|
|
('anthropic', 'claude-opus-5-5', 4.00, 20.00, 0.20, 5.00, 8.00, 'Anthropic list price, 2026-10', NOW(), NOW()),
|
|
('anthropic', 'claude-haiku-5-5', 0.10, 0.50, 0.01, 0.125, 0.20, 'Anthropic list price, 2026-10 (prompts up to 100K tokens)', NOW(), NOW()),
|
|
('anthropic', 'claude-fable-5-1', 10.00, 50.00, 0.25, 12.50, 20.00, 'Anthropic list price, 2026-10', NOW(), NOW()),
|
|
('anthropic', 'claude-opus-5', 5.00, 25.00, 0.50, 6.25, 10.00, 'Anthropic list price, 2026-10', NOW(), NOW()),
|
|
('anthropic', 'claude-sonnet-5', 2.00, 10.00, 0.20, 2.50, 4.00, 'Anthropic list price, 2026-10', NOW(), NOW()),
|
|
('anthropic', 'claude-opus-4-8', 5.00, 25.00, 0.50, 6.25, 10.00, 'Anthropic list price, 2026-10', NOW(), NOW()),
|
|
('anthropic', 'claude-sonnet-4-6', 3.00, 15.00, 0.30, 3.75, 6.00, 'Anthropic list price, 2026-10', NOW(), NOW()),
|
|
('anthropic', 'claude-haiku-4-5', 1.00, 5.00, 0.10, 1.25, 2.00, 'Anthropic list price, 2026-10', NOW(), NOW());
|
|
|
|
-- 0.1.0-RC.6: the assistant's data access per table / process, and writes waiting
|
|
-- for the user's confirmation. Existing databases: add the tool-call and policy
|
|
-- columns above with ALTER TABLE ... ADD COLUMN IF NOT EXISTS.
|
|
CREATE TABLE assistant_data_access
|
|
(
|
|
id SERIAL PRIMARY KEY,
|
|
object_type VARCHAR(16) NOT NULL,
|
|
object_name VARCHAR(128) NOT NULL,
|
|
mode VARCHAR(16) NOT NULL,
|
|
notes VARCHAR(500),
|
|
create_date TIMESTAMP WITH TIME ZONE,
|
|
modify_date TIMESTAMP WITH TIME ZONE,
|
|
CONSTRAINT assistant_data_access_object UNIQUE (object_type, object_name)
|
|
);
|
|
|
|
CREATE TABLE assistant_pending_action
|
|
(
|
|
id SERIAL PRIMARY KEY,
|
|
conversation_id INTEGER,
|
|
turn_id INTEGER,
|
|
user_id VARCHAR(255),
|
|
tool_call_id VARCHAR(128),
|
|
tool_name VARCHAR(64),
|
|
ui_api_operation VARCHAR(255),
|
|
table_name VARCHAR(128),
|
|
record_id VARCHAR(128),
|
|
params_json TEXT,
|
|
summary VARCHAR(500),
|
|
status VARCHAR(16),
|
|
http_status INTEGER,
|
|
result_summary VARCHAR(500),
|
|
expires_at TIMESTAMP WITH TIME ZONE,
|
|
decided_at TIMESTAMP WITH TIME ZONE,
|
|
create_date TIMESTAMP WITH TIME ZONE,
|
|
modify_date TIMESTAMP WITH TIME ZONE
|
|
);
|
|
CREATE INDEX assistant_pending_action_user_id ON assistant_pending_action (user_id);
|
|
CREATE INDEX assistant_pending_action_conversation_id ON assistant_pending_action (conversation_id);
|