数据库表结构
三个服务的建表脚本全文。新装环境把这三段依次执行完,库就是完整的;脚本全部是
CREATE TABLE IF NOT EXISTS,可以整段重放,重复执行不会动已有数据。
1. 三个库,三段脚本
| 库 | 建表脚本 | 表数 | 没建会发生什么 |
|---|---|---|---|
| themis-auth | themis-auth-init.sql |
11 | 注册、登录直接失败 |
| thia-agent | thia-agent-init-db.sql |
22 | 进程照常启动、探针全绿,第一次对话才 500 |
| rhea-payment | rhea-payment-init-db.sql |
5 | 注册仍然成功,但赠送积分静默不发 |
按上表从上到下执行——themis-auth 的 users 是另外两库的业务起点:
psql "$AUTH_DATABASE_URL" -v ON_ERROR_STOP=1 -f themis-auth-init.sql
psql "$AGENT_DATABASE_URL" -v ON_ERROR_STOP=1 -f thia-agent-init-db.sql
psql "$PAYMENT_DATABASE_URL" -v ON_ERROR_STOP=1 -f rhea-payment-init-db.sql
⚠️ 三个库必须是三个 database,不能把三段脚本灌进同一个库:
processed_events在thia-agent与rhea-payment里各有一张,表名与结构都不同,撞上之后两边的幂等保证同时失效。
脚本里的注释是英文——同一份文件同时服务中英文文档站,注释不随语言分叉。
2. themis-auth:用户与身份
11 张表:users、refresh_tokens、token_blacklist、roles、user_roles、organizations、
organization_user、federated_identities、federated_organizations、authorization_codes、
sso_assert_jti。末尾带六条角色种子(ADMIN / SYSTEM / FREE / PRO / KEYUSER / ENTERPRISE,
固定 UUID,其它服务的配置按名字引用它们)。
-- FewBox deployment documentation copy.
-- Synced from the source repository file fewbox-themis-auth/scripts/init.sql; the DDL is identical, only
-- the comments are in English. When the source schema changes, update both.
CREATE TABLE IF NOT EXISTS users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
username VARCHAR(255) NOT NULL UNIQUE,
email VARCHAR(255) UNIQUE,
password VARCHAR(255),
family_name VARCHAR(255),
given_name VARCHAR(255),
display_name VARCHAR(255),
avatar_url TEXT,
email_verified BOOLEAN DEFAULT false,
verification_token UUID,
verification_expires_at TIMESTAMP WITH TIME ZONE,
password_reset_token UUID,
password_reset_expires_at TIMESTAMP WITH TIME ZONE,
created_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
-- Marketing attribution, written once per user and never on the signup path
-- itself (see attribution.controller.ts). All nullable on purpose: a failed or
-- absent attribution write must never be able to block a registration.
first_touch JSONB,
signup_click_id TEXT,
signup_click_type TEXT,
-- Disabled (not deleted): non-NULL = sign-in and token issuance refused;
-- everything else kept as-is. Billing and audit trace by person, so "this
-- person may no longer sign in" must be expressible without destroying data —
-- a hard delete leaves orphan records in payment / agent. Enforced in
-- utils/token.ts issueTokens. Same shape as organizations.deleted_at.
-- Existing databases: scripts/migrations/2026-08-12-user-disabled.sql.
disabled_at TIMESTAMP WITH TIME ZONE
);
CREATE INDEX IF NOT EXISTS idx_users_email ON users (email);
-- Partial: only rows with a click id are ever scanned, and that is the minority.
-- Drives the offline-conversion upload query (click id + created_at within the
-- ad platform's conversion window).
CREATE INDEX IF NOT EXISTS idx_users_signup_click_id ON users (signup_click_id)
WHERE signup_click_id IS NOT NULL;
CREATE TABLE IF NOT EXISTS refresh_tokens (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
token VARCHAR(512) NOT NULL UNIQUE,
expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
-- NULL for the legacy web-app login path; the OIDC IdP binds it to the
-- registered client_id so one RP's refresh token can't be replayed by another.
client_id TEXT,
created_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_refresh_tokens_token ON refresh_tokens (token);
CREATE INDEX IF NOT EXISTS idx_refresh_tokens_user_id ON refresh_tokens (user_id);
CREATE TABLE IF NOT EXISTS token_blacklist (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
token_jti VARCHAR(512) NOT NULL UNIQUE,
expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
created_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_token_blacklist_jti ON token_blacklist (token_jti);
-- OIDC IdP authorization codes (one-time, PKCE-bound). The CODE itself is never
-- stored — only its SHA-256 hash — and exchange is an atomic DELETE ... RETURNING
-- so a code can be redeemed exactly once (replay gets nothing back).
CREATE TABLE IF NOT EXISTS authorization_codes (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
code_hash VARCHAR(128) NOT NULL UNIQUE,
client_id TEXT NOT NULL,
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
redirect_uri TEXT NOT NULL,
code_challenge TEXT NOT NULL,
code_challenge_method TEXT NOT NULL DEFAULT 'S256',
nonce TEXT,
scope TEXT,
expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
created_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_authorization_codes_expires_at ON authorization_codes (expires_at);
CREATE TABLE IF NOT EXISTS roles (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name VARCHAR(100) NOT NULL UNIQUE
);
CREATE TABLE IF NOT EXISTS user_roles (
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
role_id UUID NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
PRIMARY KEY (user_id, role_id)
);
INSERT INTO roles (id, name) VALUES
('fb23a66a-b57d-4681-8710-99df1c31bd36', 'ADMIN'),
('a4833fcc-df0b-46ce-9cda-7f9d1c225f92', 'SYSTEM'),
('54fc1425-079c-4fd3-80ec-477f5a719a60', 'FREE'),
('391ab92a-a3a2-4aba-8a06-3af74de07f41', 'PRO'),
('c10f03ba-f71b-4e54-8743-ec5d7affa9a4', 'KEYUSER'),
('7d5a9c1e-4b2f-4e8a-9c3d-6f1e8b2a7d40', 'ENTERPRISE')
ON CONFLICT (name) DO NOTHING;
-- ============ Organizations (minimal org table + pure membership table, no role column) ============
-- Creating an org requires the ENTERPRISE role (checked live against user_roles); membership is open to any role.
CREATE TABLE IF NOT EXISTS organizations (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
owner_id UUID NOT NULL REFERENCES users(id), -- single source of truth; default NO ACTION: a user owning an org cannot be deleted directly
name VARCHAR(255) NOT NULL, -- display name only; no UNIQUE (only needed once slugs / invite-by-name exist)
created_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
deleted_at TIMESTAMP WITH TIME ZONE -- soft delete (delete org = soft-delete + sweep balance + re-parent ownership)
);
CREATE INDEX IF NOT EXISTS idx_organizations_owner_id ON organizations (owner_id);
-- Pure membership table: a row means "member" (the owner gets one automatically when creating the org).
-- No role column; adding an admin tier later is an incremental migration: ALTER TABLE ADD COLUMN role VARCHAR(50) NOT NULL DEFAULT 'MEMBER'.
CREATE TABLE IF NOT EXISTS organization_user (
org_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
created_at TIMESTAMP WITH TIME ZONE DEFAULT now(), -- joined at
PRIMARY KEY (org_id, user_id)
);
CREATE INDEX IF NOT EXISTS idx_organization_user_user_id ON organization_user (user_id); -- the query path for snapshotting org_ids into the JWT
-- Generic OIDC SSO: binds an external identity to an external tenant (see migrations/2026-07-28-federated-identities.sql header)
CREATE TABLE IF NOT EXISTS federated_identities (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
provider_key VARCHAR(64) NOT NULL,
iss TEXT NOT NULL,
sub TEXT NOT NULL,
display_name_snapshot VARCHAR(255),
raw_claims JSONB,
created_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
last_login_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
UNIQUE (iss, sub)
);
CREATE INDEX IF NOT EXISTS idx_federated_identities_user_id ON federated_identities (user_id);
CREATE TABLE IF NOT EXISTS federated_organizations (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
iss TEXT NOT NULL,
external_org_id TEXT NOT NULL,
org_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
created_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
UNIQUE (iss, external_org_id)
);
-- Signed-assertion SSO (public-key exchange channel): one-time jti replay protection (see migrations/2026-08-04-sso-assert-jti.sql header)
CREATE TABLE IF NOT EXISTS sso_assert_jti (
provider_key VARCHAR(64) NOT NULL,
jti TEXT NOT NULL,
expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
PRIMARY KEY (provider_key, jti)
);
CREATE INDEX IF NOT EXISTS idx_sso_assert_jti_expires_at ON sso_assert_jti (expires_at);
管理员账号
脚本只建权限模型,不建任何用户——装完谁都登录不了,管理员要自己造一个:
- 打开注册页注册第一个账号(密码自己记住);
- 到库里把它的邮箱标成已验证(没配发信服务时,验证邮件根本发不出来),再绑
ADMIN:
UPDATE users SET email_verified = true WHERE email = '<您的邮箱>';
INSERT INTO user_roles (user_id, role_id)
SELECT u.id, r.id FROM users u, roles r
WHERE u.email = '<您的邮箱>' AND r.name = 'ADMIN'
ON CONFLICT (user_id, role_id) DO NOTHING;
之后用这个账号登录,/console 下的管理页就都可见了。
3. thia-agent:会话、记忆与任务
22 张表,按用途分六组:
| 组 | 表 |
|---|---|
| 会话与 Agent 定义 | conversations、prompts、agents、agent_translations |
| 长期记忆 | memories(vector(1024) 列)、consolidation_locks、consolidation_log |
| 例行任务与通知 | routines、routine_runs、routine_items、notification_channels |
| 定时任务运行时 | scheduled_jobs、job_runs、job_tick_state |
| 实时事件 | sse_subscriptions、agent_events、bridge_results |
| 用量与审计 | usage_records、invocation_records、admin_audit_logs |
# 建库前先在服务器上装 pgvector 扩展(脚本第一行就会 CREATE EXTENSION vector)
psql "$AGENT_DATABASE_URL" -c 'CREATE EXTENSION IF NOT EXISTS vector;'
-- FewBox deployment documentation copy.
-- Synced from the source repository file fewbox-thia-agent/scripts/init-db.sql; the DDL is identical, only
-- the comments are in English. When the source schema changes, update both.
-- Thia Agent PostgreSQL Schema Initialization
-- Usage: save this block as a .sql file, then psql -h <host> -U <user> -d <database> -f <file>.sql
--
-- Requires the pgvector extension binary installed on the server.
-- Use the pgvector/pgvector:pg16 image in docker-compose, or apt install
-- postgresql-XX-pgvector for self-hosted PG. Most managed PG providers
-- (RDS, Aurora, Aliyun RDS, Supabase, Neon) support it out of the box.
-- ============================================================
-- Extensions
-- ============================================================
CREATE EXTENSION IF NOT EXISTS vector;
-- ============================================================
-- Billing: usage_records
-- ============================================================
CREATE TABLE IF NOT EXISTS usage_records (
id UUID PRIMARY KEY,
timestamp TIMESTAMPTZ NOT NULL,
type TEXT NOT NULL,
resource_id TEXT NOT NULL,
resource_detail TEXT NOT NULL,
conversation_id UUID,
user_id UUID,
billing_account_id UUID,
input_tokens INTEGER,
output_tokens INTEGER,
cost DOUBLE PRECISION NOT NULL,
currency TEXT NOT NULL,
credits DOUBLE PRECISION NOT NULL DEFAULT 0,
credit_status TEXT NOT NULL DEFAULT 'pending', -- pending | processing | settled | failed
settling_since TIMESTAMPTZ, -- set while 'processing' (claim time); stale claims are recovered
unpriced BOOLEAN NOT NULL DEFAULT false, -- no pricing rule covered the call: charged 0 (billing-collector resolveMcpRule)
-- The agent that produced this cost (agentic.yaml agent.code). When several products
-- share one agent core, this is the only place that tells "who is spending"; it cannot
-- be derived from the conversation (conversation_id is NULL for embedding / memory /
-- decision rounds). Added 2026-11-15 by 2026-11-add-usage-records-agent-code.sql.
agent_code TEXT
);
CREATE INDEX IF NOT EXISTS idx_usage_records_timestamp ON usage_records(timestamp);
CREATE INDEX IF NOT EXISTS idx_usage_records_type ON usage_records(type);
CREATE INDEX IF NOT EXISTS idx_usage_records_resource_id ON usage_records(resource_id);
CREATE INDEX IF NOT EXISTS idx_usage_records_user_id ON usage_records(user_id);
CREATE INDEX IF NOT EXISTS idx_usage_records_conversation_id ON usage_records(conversation_id);
CREATE INDEX IF NOT EXISTS idx_usage_records_credit_status ON usage_records(credit_status);
CREATE INDEX IF NOT EXISTS idx_usage_records_unsettled ON usage_records(timestamp) WHERE credit_status IN ('pending', 'failed', 'processing');
-- ============================================================
-- Compliance: admin_audit_logs
-- ============================================================
-- One row per admin access to user-owned content (console conversation
-- viewer). Append-only evidence trail: who (actor) looked at whose
-- (target) data, when, and why. Never updated or deleted by the app.
CREATE TABLE IF NOT EXISTS admin_audit_logs (
id UUID PRIMARY KEY,
actor_user_id UUID NOT NULL,
action TEXT NOT NULL,
resource_type TEXT NOT NULL,
resource_id TEXT NOT NULL,
target_user_id UUID,
reason TEXT,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_admin_audit_logs_created_at ON admin_audit_logs(created_at);
CREATE INDEX IF NOT EXISTS idx_admin_audit_logs_actor ON admin_audit_logs(actor_user_id);
CREATE INDEX IF NOT EXISTS idx_admin_audit_logs_target ON admin_audit_logs(target_user_id);
-- ============================================================
-- Observability: invocation_records
-- ============================================================
-- One row per agent invocation ATTEMPT. usage_records only records calls
-- that got far enough to be BILLED, so failures never land there; this
-- table is what the console's failure rate is computed from. Written once,
-- never updated.
CREATE TABLE IF NOT EXISTS invocation_records (
id UUID PRIMARY KEY,
invocation_id TEXT,
conversation_id TEXT,
user_id UUID,
status TEXT NOT NULL, -- success | error | cancelled
error_code TEXT, -- only meaningful when status = 'error'
duration_ms INTEGER NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_invocation_records_created_at ON invocation_records(created_at);
CREATE INDEX IF NOT EXISTS idx_invocation_records_status ON invocation_records(status);
-- One send = one row; writes use ON CONFLICT DO NOTHING against this index
-- (the controller's retry de-dupe is per-instance). NULLs stay distinct.
CREATE UNIQUE INDEX IF NOT EXISTS idx_invocation_records_invocation_id ON invocation_records(invocation_id);
-- ============================================================
-- Periodic jobs: scheduled_jobs / job_runs / job_tick_state
-- ============================================================
-- One K8s CronJob pokes /api/v1/jobs/tick; each registered job claims its
-- row with one conditional UPDATE (the lease) before running. Rows are
-- seeded by the registry at boot. See scripts/2026-09-add-scheduled-jobs.sql
-- and fewbox-thia-web/docs/scheduled-jobs-design.md.
CREATE TABLE IF NOT EXISTS scheduled_jobs (
job_key TEXT PRIMARY KEY,
enabled BOOLEAN NOT NULL DEFAULT TRUE,
cadence_ms BIGINT NOT NULL,
next_run_at TIMESTAMPTZ NOT NULL,
retry_at TIMESTAMPTZ,
locked_by TEXT,
locked_until TIMESTAMPTZ,
last_run_at TIMESTAMPTZ,
last_status TEXT,
last_error TEXT,
consecutive_failures INTEGER NOT NULL DEFAULT 0,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TABLE IF NOT EXISTS job_runs (
id UUID PRIMARY KEY,
job_key TEXT NOT NULL,
trigger TEXT NOT NULL, -- cron | manual
dry_run BOOLEAN NOT NULL DEFAULT FALSE,
pod TEXT NOT NULL,
started_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
finished_at TIMESTAMPTZ,
status TEXT NOT NULL, -- running | completed | failed
summary JSONB,
error TEXT
);
CREATE INDEX IF NOT EXISTS idx_job_runs_key_started ON job_runs(job_key, started_at DESC);
CREATE TABLE IF NOT EXISTS job_tick_state (
id TEXT PRIMARY KEY, -- 'singleton'
last_tick_at TIMESTAMPTZ NOT NULL,
pod TEXT NOT NULL
);
-- ============================================================
-- Cluster: sse_subscriptions (one row per live SSE connection)
-- Liveness is time-based (heartbeat touches last_seen_at; fresh = 90s).
-- See scripts/2026-09-add-sse-subscriptions.sql for the rationale.
-- ============================================================
CREATE TABLE IF NOT EXISTS sse_subscriptions (
id UUID PRIMARY KEY,
scope TEXT NOT NULL, -- 'conversation' | 'user'
key TEXT NOT NULL, -- conversationId or userId
user_id TEXT NOT NULL,
pod TEXT NOT NULL,
connected_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
last_seen_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_sse_subscriptions_lookup ON sse_subscriptions(scope, key, last_seen_at);
CREATE INDEX IF NOT EXISTS idx_sse_subscriptions_user ON sse_subscriptions(user_id, last_seen_at);
-- ============================================================
-- Cluster: agent_events (cross-pod SSE path; pruned to 72 hours by the agent)
-- See scripts/2026-09-add-agent-events.sql for the rationale.
-- ============================================================
CREATE TABLE IF NOT EXISTS agent_events (
id BIGSERIAL PRIMARY KEY,
conversation_id TEXT,
user_id TEXT,
invocation_id TEXT,
type TEXT NOT NULL,
event JSONB NOT NULL,
origin_pod TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_agent_events_conversation ON agent_events(conversation_id, id DESC);
CREATE INDEX IF NOT EXISTS idx_agent_events_created_at ON agent_events(created_at);
-- ============================================================
-- Cluster: bridge_results (browser bridge uplink across pods; 1h retention)
-- See scripts/2026-09-add-bridge-results.sql for the rationale.
-- ============================================================
CREATE TABLE IF NOT EXISTS bridge_results (
bridge_request_id TEXT PRIMARY KEY,
user_id TEXT,
payload JSONB NOT NULL,
received_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_bridge_results_received_at ON bridge_results(received_at);
-- ============================================================
-- Conversation: conversations
-- ============================================================
CREATE TABLE IF NOT EXISTS conversations (
id UUID PRIMARY KEY,
user_id UUID,
title TEXT,
messages JSONB NOT NULL,
created_at TIMESTAMPTZ NOT NULL,
updated_at TIMESTAMPTZ NOT NULL,
deleted_at TIMESTAMPTZ
);
CREATE INDEX IF NOT EXISTS idx_conversations_updated_at ON conversations(updated_at);
CREATE INDEX IF NOT EXISTS idx_conversations_user_id_deleted_at ON conversations(user_id, deleted_at);
-- ============================================================
-- Prompt: prompts
-- ============================================================
CREATE TABLE IF NOT EXISTS prompts (
id UUID PRIMARY KEY,
user_id TEXT, -- owner of a personal prompt; NULL = global (admin) prompt
locale TEXT NOT NULL,
value TEXT NOT NULL,
caption TEXT NOT NULL,
hint TEXT NOT NULL DEFAULT '',
condition TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL,
updated_at TIMESTAMPTZ NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_prompts_locale ON prompts(locale);
CREATE INDEX IF NOT EXISTS idx_prompts_user_id ON prompts(user_id);
CREATE INDEX IF NOT EXISTS idx_prompts_updated_at ON prompts(updated_at);
-- ============================================================
-- Agent Definition: agents (+ agent_translations)
-- ============================================================
CREATE TABLE IF NOT EXISTS agents (
id UUID PRIMARY KEY,
code TEXT NOT NULL UNIQUE,
icon TEXT NOT NULL DEFAULT '',
roles JSONB NOT NULL DEFAULT '[]'::jsonb,
created_at TIMESTAMPTZ NOT NULL,
updated_at TIMESTAMPTZ NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_agents_code ON agents(code);
CREATE INDEX IF NOT EXISTS idx_agents_updated_at ON agents(updated_at);
CREATE TABLE IF NOT EXISTS agent_translations (
id UUID PRIMARY KEY,
code_id UUID NOT NULL REFERENCES agents(id) ON DELETE CASCADE,
locale TEXT NOT NULL,
name TEXT NOT NULL,
description TEXT NOT NULL DEFAULT '',
created_at TIMESTAMPTZ NOT NULL,
updated_at TIMESTAMPTZ NOT NULL,
UNIQUE (code_id, locale)
);
CREATE INDEX IF NOT EXISTS idx_agent_translations_code_id ON agent_translations(code_id);
CREATE INDEX IF NOT EXISTS idx_agent_translations_locale ON agent_translations(locale);
-- ============================================================
-- Memory: memories
-- ============================================================
CREATE TABLE IF NOT EXISTS memories (
id UUID PRIMARY KEY,
user_id UUID,
category TEXT NOT NULL,
content TEXT NOT NULL,
source TEXT NOT NULL,
confidence DOUBLE PRECISION NOT NULL DEFAULT 1.0,
status TEXT NOT NULL DEFAULT 'active',
superseded_by UUID,
tags TEXT[] NOT NULL DEFAULT '{}',
conversation_id UUID,
created_at TIMESTAMPTZ NOT NULL,
updated_at TIMESTAMPTZ NOT NULL,
last_accessed_at TIMESTAMPTZ,
access_count INTEGER NOT NULL DEFAULT 0,
search_vector tsvector GENERATED ALWAYS AS (to_tsvector('english', content)) STORED,
embedding vector(1024)
);
CREATE INDEX IF NOT EXISTS idx_memories_user_id ON memories(user_id);
CREATE INDEX IF NOT EXISTS idx_memories_category ON memories(category);
CREATE INDEX IF NOT EXISTS idx_memories_status ON memories(status);
CREATE INDEX IF NOT EXISTS idx_memories_created_at ON memories(created_at);
CREATE INDEX IF NOT EXISTS idx_memories_tags ON memories USING GIN(tags);
CREATE INDEX IF NOT EXISTS idx_memories_search ON memories USING GIN(search_vector);
-- ============================================================
-- Memory: consolidation_locks
-- ============================================================
CREATE TABLE IF NOT EXISTS consolidation_locks (
id TEXT PRIMARY KEY DEFAULT 'singleton',
pod_id TEXT NOT NULL,
acquired_at TIMESTAMPTZ NOT NULL,
expires_at TIMESTAMPTZ NOT NULL,
phase TEXT NOT NULL DEFAULT 'orient'
);
-- ============================================================
-- Memory: consolidation_log
-- ============================================================
CREATE TABLE IF NOT EXISTS consolidation_log (
id UUID PRIMARY KEY,
started_at TIMESTAMPTZ NOT NULL,
completed_at TIMESTAMPTZ,
pod_id TEXT NOT NULL,
memories_before INTEGER NOT NULL,
memories_after INTEGER NOT NULL,
memories_pruned INTEGER NOT NULL DEFAULT 0,
memories_merged INTEGER NOT NULL DEFAULT 0,
status TEXT NOT NULL DEFAULT 'running',
error TEXT
);
CREATE INDEX IF NOT EXISTS idx_consolidation_log_started_at ON consolidation_log(started_at);
-- Shares (conversation showcases) — immutable snapshots published under a fresh slug.
-- curated_* columns power the curated (SEO-indexable) tier: an admin promotes a
-- share, gives it a semantic slug + category + CTA prefill, and writes editorial
-- copy per locale in share_translations.
CREATE TABLE IF NOT EXISTS shares (
slug TEXT PRIMARY KEY,
conversation_id TEXT NOT NULL,
user_id UUID,
title TEXT NOT NULL,
cover_url TEXT,
messages JSONB NOT NULL,
sort_order INTEGER,
created_at TIMESTAMPTZ NOT NULL,
revoked_at TIMESTAMPTZ,
disabled_at TIMESTAMPTZ,
curated_at TIMESTAMPTZ,
semantic_slug TEXT UNIQUE,
category TEXT,
cta_prompt TEXT,
cta_images JSONB,
-- scrypt hash of the optional share password (self-describing string:
-- `scrypt$N$r$p$salt$hash`). NULL = public share. Never serialized to any
-- client JSON — only the derived boolean `passwordRequired` is surfaced.
password_hash TEXT
);
CREATE INDEX IF NOT EXISTS idx_shares_user_id ON shares(user_id);
CREATE INDEX IF NOT EXISTS idx_shares_created_at ON shares(created_at DESC);
-- Curated editorial copy per locale (mirrors agent_translations)
CREATE TABLE IF NOT EXISTS share_translations (
id UUID PRIMARY KEY,
slug TEXT NOT NULL REFERENCES shares(slug) ON DELETE CASCADE,
locale TEXT NOT NULL,
h1 TEXT NOT NULL,
intro TEXT NOT NULL DEFAULT '',
body TEXT NOT NULL DEFAULT '',
seo_title TEXT NOT NULL DEFAULT '',
seo_description TEXT NOT NULL DEFAULT '',
created_at TIMESTAMPTZ NOT NULL,
updated_at TIMESTAMPTZ NOT NULL,
UNIQUE (slug, locale)
);
CREATE INDEX IF NOT EXISTS idx_share_translations_slug ON share_translations(slug);
-- Per-user heartbeat: routines / routine_runs / routine_items.
-- Design: fewbox-thia-web/docs/heartbeat-design.md. NOT the same thing as
-- scheduled_jobs above: that one is the projection of a code registry owned by
-- the platform; these are user data created at runtime from a sentence in a
-- conversation. The clock is shared (one system job `routines.tick` scans this
-- table), the ownership, quota and billing are not.
CREATE TABLE IF NOT EXISTS routines (
id UUID PRIMARY KEY,
user_id TEXT NOT NULL,
-- Which agent variant created this routine, and therefore which one may run
-- it. The three variants share ONE database, but only the variant with the
-- job runtime enabled ticks — without this column a routine created on an
-- experimental variant would sit in the table and never run, silently.
-- The tool is simply absent on variants without the runtime, so this is the
-- second line of defence rather than the first.
agent_code TEXT NOT NULL,
-- The routine's home conversation (§0f-2): execution never loads its history,
-- it only appends deliveries there. NULL until the first delivery creates it.
conversation_id TEXT,
title TEXT NOT NULL, -- short label for lists/notifications
-- WHAT to do, in the user's own words. Deliberately free text: the runtime
-- does not know about scenarios (design section 0a, "no recipes").
prompt TEXT NOT NULL,
-- ---- Cross-run state: THREE separate slots, never one opaque blob (§0j) ----
-- Mechanical state written by CODE, never by the model: paging cursor, last
-- seen timestamp. The "already seen" id set itself lives in routine_items.
cursor JSONB NOT NULL DEFAULT '{}'::jsonb,
-- What the MODEL proposed it learned ("this advertiser floods the feed").
-- Bounded and user-visible on purpose: an unbounded blob is how a routine
-- silently stops surfacing a whole category and nobody can tell why.
notes JSONB NOT NULL DEFAULT '[]'::jsonb,
-- What the USER confirmed. The model may NEVER write this column; it is
-- changed only through the routine tool on an interactive turn.
criteria TEXT,
-- ---- Schedule ----
-- Two shapes, because "every 6 hours" and "every day at 08:00" are both
-- common and neither expresses the other (§0e④).
cadence_kind TEXT NOT NULL, -- interval | daily | weekly
cadence_ms BIGINT, -- interval only
cadence_hour SMALLINT, -- daily/weekly: local hour
cadence_minute SMALLINT, -- daily/weekly: local minute
cadence_weekday SMALLINT, -- weekly only: 0=Sunday
timezone TEXT NOT NULL DEFAULT 'UTC', -- whose 08:00 (§0e③)
next_run_at TIMESTAMPTZ NOT NULL,
retry_at TIMESTAMPTZ, -- separate from the anchor grid, as in scheduled_jobs
-- ---- Capability & safety (§0e②, §0h) ----
-- Empty array = no browser needed -> runs unconditionally. Non-empty = the
-- user's desktop must be online; a due run with nobody home is `skipped`,
-- NOT retried (§0h).
needs_browser BOOLEAN NOT NULL DEFAULT FALSE,
tool_allowlist JSONB NOT NULL DEFAULT '[]'::jsonb,
-- Source declarations [{kind:'feed', url, limit?}]; empty = the model fetches by itself (decision-model design 3.7)
sources JSONB NOT NULL DEFAULT '[]'::jsonb,
organization_id TEXT, -- owner: NULL = personal, non-empty = the org chosen at creation (see 2026-09-add-routines-organization.sql)
domain_allowlist JSONB NOT NULL DEFAULT '[]'::jsonb,
-- ---- Budget (values come from config, never hard-coded in code) ----
max_cost_credits_per_day NUMERIC(20,6), -- CREDITS, not money. NULL = use routines.maxCostCreditsPerDay. Exceeded -> skip until tomorrow
wall_clock_ms BIGINT, -- nobody is there to press cancel (§0h C1)
-- ---- Lifecycle ----
status TEXT NOT NULL DEFAULT 'active', -- active | paused | seeding
pause_reason TEXT, -- balance | ignored | failures | user | resource_gone
-- The last failure, verbatim. routine_runs keeps the full history; this one
-- exists so "why did it pause" can be answered without a second query — the
-- user is owed the actual reason, not just the word 'failures' (§0e).
last_error TEXT,
-- Balance exhaustion must NOT count towards `consecutive_failures`: topping up
-- should resume the routine, not leave it one failure from permanent pause.
consecutive_failures INTEGER NOT NULL DEFAULT 0,
-- "Delivered and the user never opened the conversation" (§0f-5). Read
-- receipts of e-mail are unreliable (image proxies); this is our own data.
unopened_deliveries INTEGER NOT NULL DEFAULT 0,
-- 1 = never notify (mute this one noisy routine); >= 2 = push to the user's
-- configured channels — with no channel configured nothing happens, which is
-- why 2 is a safe default. v1 does not implement the 3-vs-4 difference
-- (digest vs immediate): everything is immediate. Channels themselves live in
-- notification_channels, per user rather than per routine.
notify_level SMALLINT NOT NULL DEFAULT 2, -- 1 none | 2 notify | 3 digest | 4 immediate
locked_by TEXT,
locked_until TIMESTAMPTZ,
last_run_at TIMESTAMPTZ,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
deleted_at TIMESTAMPTZ -- soft delete, mirrors conversations
);
-- The dispatcher's only hot query: "which rows are due". Partial index keeps it
-- proportional to the DUE set, not to the total number of routines.
CREATE INDEX IF NOT EXISTS idx_routines_due
ON routines(agent_code, next_run_at)
WHERE deleted_at IS NULL AND status = 'active';
CREATE INDEX IF NOT EXISTS idx_routines_user ON routines(user_id) WHERE deleted_at IS NULL;
-- One row per tick that touched this routine — INCLUDING the silent ones.
-- This table is the answer to "is it still working?", which is otherwise
-- indistinguishable from "nothing happened" (§0f-1: failure here is silent).
CREATE TABLE IF NOT EXISTS routine_runs (
id UUID PRIMARY KEY,
routine_id UUID NOT NULL,
user_id TEXT NOT NULL,
trigger TEXT NOT NULL, -- schedule | manual
pod TEXT NOT NULL,
started_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
finished_at TIMESTAMPTZ,
-- seed : first run, recorded current state and deliberately delivered nothing
-- silent : ran, nothing new -> no message, no notification
-- delivered : produced something the user should see
-- skipped : did not run (browser offline, user busy, quota/budget gate), or was
-- cancelled mid-turn (pod restart; skip_reason='cancelled', next tick retries)
-- failed : errored
outcome TEXT NOT NULL, -- used to be called decision (renamed 2026-09-20; decision is reserved for the decision model)
skip_reason TEXT,
items_seen INTEGER NOT NULL DEFAULT 0, -- how many candidates the fetch returned
items_new INTEGER NOT NULL DEFAULT 0, -- how many survived the diff
invocation_id TEXT, -- links to invocation_records / agent_events
error TEXT,
judgments JSONB -- shadow-judgment records (decision model), see 2026-09-add-routine-runs-judgments.sql
);
CREATE INDEX IF NOT EXISTS idx_routine_runs_routine ON routine_runs(routine_id, started_at DESC);
-- The diff's output AND its state in one place: `external_id` doubles as the
-- "already seen" set, so there is no second copy to drift out of sync (§0f-3).
CREATE TABLE IF NOT EXISTS routine_items (
id UUID PRIMARY KEY,
routine_id UUID NOT NULL,
external_id TEXT NOT NULL, -- stable key from the source (post id, ad id, URL)
fingerprint TEXT, -- hash of the fields we care about -> detects CHANGE, not just arrival
payload JSONB NOT NULL, -- title/link/excerpt, already structured by stage one
first_seen_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
last_seen_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
status TEXT NOT NULL DEFAULT 'new', -- new | delivered | acted | ignored
run_id UUID -- the run that first saw it
);
-- The diff is a set difference on this pair; it must be exact and fast.
CREATE UNIQUE INDEX IF NOT EXISTS idx_routine_items_key ON routine_items(routine_id, external_id);
CREATE INDEX IF NOT EXISTS idx_routine_items_seen ON routine_items(routine_id, first_seen_at DESC);
-- ---------------------------------------------------------------------------
-- Where a routine delivers when it has something to say. Keyed by USER, not by
-- routine: one user has one Feishu group plus one Web Push subscription per
-- browser, and ten routines — the address must not be pasted ten times.
-- "Whether to notify" lives on routines.notify_level (per routine); this table
-- only stores "where". See scripts/2026-09-add-notification-channels.sql and
-- design section 3.8c.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS notification_channels (
id UUID PRIMARY KEY,
user_id TEXT NOT NULL,
-- 'webhook' (Slack / Discord / Feishu / WeCom / Teams are all "give me a URL")
-- or 'webpush'. Deliberately TEXT: supporting 'email' later is one more kind
-- of row. The code recognises no vendor names.
kind TEXT NOT NULL,
-- WARNING: the webhook URL itself is a credential: never log it, show only the last few characters, never return it whole.
target TEXT NOT NULL,
secret JSONB NOT NULL DEFAULT '{}'::jsonb, -- Web Push {p256dh, auth}
label TEXT,
enabled BOOLEAN NOT NULL DEFAULT TRUE,
-- A dead webhook must stop itself: it cannot keep hammering a 404 every hour,
-- and a 404/410 from the push service means "this subscription is gone" —
-- it must be deleted.
last_ok_at TIMESTAMPTZ,
last_error TEXT,
consecutive_failures INTEGER NOT NULL DEFAULT 0,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- Subscribing the same browser twice / pasting the same group twice is normal: the unique key turns it into an upsert.
CREATE UNIQUE INDEX IF NOT EXISTS idx_notification_channels_target
ON notification_channels(user_id, kind, target);
-- The only query shape at delivery time: this user's live channels.
CREATE INDEX IF NOT EXISTS idx_notification_channels_user
ON notification_channels(user_id) WHERE enabled;
4. rhea-payment:账本
5 张表:processed_events(支付事件幂等)、stripe_customers、user_credits、credit_transactions、
credit_packages。
-- FewBox deployment documentation copy.
-- Synced from the source repository file fewbox-rhea-payment/scripts/init-db.sql; the DDL is identical, only
-- the comments are in English. When the source schema changes, update both.
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE TABLE IF NOT EXISTS processed_events (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
event_id TEXT NOT NULL UNIQUE,
provider TEXT NOT NULL,
type TEXT NOT NULL,
status TEXT NOT NULL DEFAULT 'completed' CHECK (status IN ('completed', 'skipped')),
user_id TEXT,
amount INTEGER,
currency TEXT,
reason TEXT,
processed_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_processed_events_status ON processed_events (status);
CREATE INDEX IF NOT EXISTS idx_processed_events_user_id ON processed_events (user_id);
-- Stable user <-> Stripe Customer mapping: each user has exactly one customer, and all
-- one-off/subscription purchases, invoices and saved cards hang off it, which is
-- what lets the Customer Portal (self-service cancel / change card / invoices) work.
CREATE TABLE IF NOT EXISTS stripe_customers (
user_id UUID PRIMARY KEY,
customer_id TEXT NOT NULL UNIQUE,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- balance / amount use NUMERIC(20,6) exact decimals to allow fractional credits (0.1, 0.5).
-- Config keeps floats (creditsPerCall: 0.5); the DB stores exactly, so settle has no ROUND drift.
CREATE TABLE IF NOT EXISTS user_credits (
user_id UUID PRIMARY KEY,
balance NUMERIC(20, 6) NOT NULL DEFAULT 0,
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TABLE IF NOT EXISTS credit_transactions (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
user_id UUID NOT NULL,
amount NUMERIC(20, 6) NOT NULL,
type TEXT NOT NULL CHECK (type IN ('credit', 'debit')),
event_id TEXT NOT NULL UNIQUE, -- idempotency: one payment event credits at most once (concurrency-safe)
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_credit_transactions_user_id ON credit_transactions (user_id);
-- The UNIQUE constraint on event_id already provides the index; no separate idx_credit_transactions_event_id
-- Package definitions: explicitly declare "what this package costs (per region/currency)
-- and how many credits it grants". Grants always follow the credits column, never a
-- conversion from the paid amount (red line 5 in thia-agent/BILLING_DESIGN.md). Discounts
-- show up only in price_minor; regional tiers of one logical package get different ids.
CREATE TABLE IF NOT EXISTS credit_packages (
id TEXT PRIMARY KEY, -- 'pro_monthly' / 'pro_monthly_t3' ...
kind TEXT NOT NULL CHECK (kind IN ('subscription', 'topup')), -- subscription = recurring | topup = one-off
credits NUMERIC(20, 6) NOT NULL, -- credits granted (identical worldwide for the same package)
price_minor BIGINT NOT NULL, -- smallest unit of the local currency; zero-decimal currencies (JPY/KRW) by exponent
currency TEXT NOT NULL, -- 'usd' | 'eur' | 'cny' ...
ppp_tier SMALLINT NOT NULL DEFAULT 1, -- 1 | 2 | 3 (annotation only)
provider_ref TEXT, -- Stripe/Paddle price id (required for subscriptions, optional for one-offs)
active BOOLEAN NOT NULL DEFAULT TRUE,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_credit_packages_active ON credit_packages (active);
套餐目录(可选)
不卖订阅、不接 Stripe 时整段跳过——注册赠分与对话扣费都不读这张表,只有购买套餐会读。
表的语义是「这个套餐收多少钱(price_minor,最小货币单位)、发多少积分(credits)」:
-- 套餐 id 由前端固定发送,改 id 要同步改 web 侧。
-- 种子是 ON CONFLICT DO NOTHING,重放不会覆盖已有价格。
INSERT INTO credit_packages (id, kind, credits, price_minor, currency, ppp_tier, provider_ref, active) VALUES
('trial', 'topup', 5000, 990, 'usd', 1, NULL, TRUE),
('pro', 'subscription', 16000, 2990, 'usd', 1, '<price_xxx>', TRUE),
('enterprise', 'subscription', 55000, 9900, 'usd', 1, '<price_yyy>', TRUE)
ON CONFLICT (id) DO NOTHING;
provider_ref 填在您自己的 Stripe 后台建出来的价格 id;一次性购买(topup)在结算时按
price_minor 现组装,留 NULL 即可。积分永远按 credits 发,不按支付金额换算。
5. 验证
# 三个库各跑一次,表数应为 11 / 22 / 5
psql "$DATABASE_URL" -c "select count(*) from information_schema.tables where table_schema = 'public';"
升级已有库时不要重放初始化脚本:
CREATE TABLE IF NOT EXISTS不会改已有的列类型与约束, 它只补缺的表。结构变更按发布说明给出的语句执行。