A local-first multi-agent app development platform, built on PenguinHarness
/**
* SQLite table-creation SQL.
*
* SQLite stores only indexes and aggregates: users / login sessions / Project authorization /
* Agent & Session indexes / usage summaries / error records / UI preferences. Agent State,
* Trace, and Workspace still follow the local directory-based storage rules.
* Product not yet released: no migration branches — everything is CREATE IF NOT EXISTS, formed
* once. The one exception: columns added to an existing table after release of a web.db are
* ALTERed in by the idempotent per-column guard in database.ts (ensureColumn), since CREATE
* TABLE IF NOT EXISTS never touches an existing table. A *reshaped* index follows the same
* rule under a new name, with the superseded one dropped on open (idx_usage_session →
* idx_usage_session_ts): CREATE INDEX IF NOT EXISTS never rebuilds an existing index either.
*/
export const MODEL_PROMOTIONS_SQL = `
CREATE TABLE IF NOT EXISTS model_promotions ( -- NOT a cache rebuildable from files: the only record of a Project row's running promotion (.project_config.toml keeps the list price), written by preset seeding at Project creation, "sync presets" and Penguin Go authorization / sync, and read by cost; lost rows price usage at list until the next sync writes them back
project_id TEXT NOT NULL REFERENCES projects(project_id) ON DELETE CASCADE,
provider TEXT NOT NULL,
model_id TEXT NOT NULL,
discount REAL NOT NULL CHECK (discount > 0 AND discount < 1), -- fraction off the row's list price: billed = list × (1 − discount)
updated_at TEXT NOT NULL,
PRIMARY KEY (project_id, provider, model_id)
);`;
export const MODEL_PROVIDER_AUTH_TOKENS_SQL = `
CREATE TABLE IF NOT EXISTS model_provider_auth_tokens ( -- Server-side OAuth refresh metadata for provider groups. The current request token remains in .project_config.toml as api_key; this table holds only the refresh material that must never be returned to the frontend or written into Project files.
project_id TEXT NOT NULL REFERENCES projects(project_id) ON DELETE CASCADE,
provider TEXT NOT NULL,
refresh_token TEXT NOT NULL,
access_token_expires_at TEXT,
updated_at TEXT NOT NULL,
PRIMARY KEY (project_id, provider)
);`;
export const SCHEMA_SQL = `
CREATE TABLE IF NOT EXISTS users (
user_id TEXT PRIMARY KEY, -- semantic id doubles as login name: ^[a-z][a-z0-9_-]{1,31}$
password_hash TEXT NOT NULL, -- scrypt$N$r$p$salt$hash(base64)
is_admin INTEGER NOT NULL DEFAULT 0, -- 1 for the built-in admin (seeded at startup)
password_is_initial INTEGER NOT NULL DEFAULT 0, -- 1=initial password (seeded/admin-set); cleared once the user changes it
display_name TEXT, -- nickname shown in place of user_id (1-32 characters); NULL = never set, surfaces show the id
avatar TEXT, -- avatar as a data URL (image/png|jpeg|webp, <= 131072 characters); NULL = never set, surfaces draw the letter placeholder
last_login_at TEXT, -- LAST successful sign-in (ISO); NULL = never signed in (an account created but not yet used). Stamped by AuthService, read by the admin user backend
created_at TEXT NOT NULL
);
-- Server-side sessions backing the HttpOnly cookie: the cookie carries a 32-byte random
-- token, the row stores only its sha256. A session outlives a restart (it is on disk), which
-- is why the model is a row and not a signed statement.
CREATE TABLE IF NOT EXISTS auth_sessions (
token_hash TEXT PRIMARY KEY, -- sha256(token) hex; the cookie stores only the raw token
user_id TEXT NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
created_at TEXT NOT NULL,
expires_at TEXT NOT NULL, -- 30-day sliding renewal, topped up in place when <29 days remain
via TEXT -- 'password'|'desktop'|'setup'|'cli'; NULL = legacy (password)
);
-- deleteExpired runs on every login and mint, deleteByUser on every admin reset; without
-- these both scan the whole table.
CREATE INDEX IF NOT EXISTS idx_auth_sessions_expires ON auth_sessions(expires_at);
CREATE INDEX IF NOT EXISTS idx_auth_sessions_user ON auth_sessions(user_id);
CREATE TABLE IF NOT EXISTS projects (
project_id TEXT PRIMARY KEY, -- directory name doubles as id; display name lives in project_config.toml
owner_user_id TEXT NOT NULL REFERENCES users(user_id),
created_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS project_members ( -- member grants only; owners are not stored here
project_id TEXT NOT NULL REFERENCES projects(project_id) ON DELETE CASCADE,
user_id TEXT NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
created_at TEXT NOT NULL,
PRIMARY KEY (project_id, user_id)
);
${MODEL_PROMOTIONS_SQL}
${MODEL_PROVIDER_AUTH_TOKENS_SQL}
CREATE TABLE IF NOT EXISTS agents ( -- index only; name/description live in system_config.yaml
project_id TEXT NOT NULL,
agent_id TEXT NOT NULL,
created_at TEXT NOT NULL,
PRIMARY KEY (project_id, agent_id)
);
CREATE TABLE IF NOT EXISTS sessions (
session_id TEXT PRIMARY KEY,
project_id TEXT NOT NULL,
agent_id TEXT NOT NULL,
provider TEXT NOT NULL, -- provider group of the session's CURRENT model (paired with model_id as a model reference; moves with each in-session switch)
model_id TEXT NOT NULL, -- upstream model id of the current model (sent to AgentHub as-is; never concatenate <provider>/<id>)
workspace TEXT NOT NULL,
approval_mode TEXT NOT NULL DEFAULT 'allow-all', -- allow-all|deny-all|read-only|always-ask
thinking_level TEXT, -- level pinned for THIS session (none|low|medium|high|xhigh|max); NULL=not pinned, runs follow the Agent config
title TEXT, -- auto-generated by the model after the first exchange; NULL=not yet (frontend shows "New chat")
archived_at TEXT, -- archive time; NULL=not archived (shown by default; archived ones move under "Archived")
client TEXT, -- creating client: 'web' (created via the Web App) | 'cli' (adopted from a CLI Trace) | 'org' (company mode) | 'api' (an Agent API run); NULL = legacy row, treated as web
has_trace INTEGER NOT NULL DEFAULT 0, -- cache: a Trace record exists (set at task start / adoption / subagent registration; lazily backfilled by list hydration)
fork_count INTEGER NOT NULL DEFAULT 0, -- monotonically allocated child-fork number for this source Session; never decremented when a fork is deleted
sandbox TEXT, -- the Session's sandbox policy snapshot (JSON SandboxSettings); NULL = legacy row, snapshotted at its next command
last_active_at TEXT, -- last activity this server drove for the session (ISO; stamped once when a run starts and once when it ends, initialized to created_at); monotonic, never moves backwards; NULL only before openDatabase's one-time backfill
created_at TEXT NOT NULL
); -- the Session's SOURCE (user/api/schedule/subagent/cli/company) is NOT stored: session_meta in the Trace is the single source of truth (see runtime/session-sources.ts); "client" is a different, DB-only axis (who created the row), read for the source only where the Trace records none ('org' → company)
CREATE INDEX IF NOT EXISTS idx_sessions_agent_created ON sessions(project_id, agent_id, created_at DESC);
CREATE TABLE IF NOT EXISTS usage_records (
id INTEGER PRIMARY KEY AUTOINCREMENT,
ts TEXT NOT NULL,
date TEXT NOT NULL, -- local date yyyy-mm-dd (aggregation key; same convention as Trace date directories)
project_id TEXT NOT NULL,
agent_id TEXT NOT NULL,
session_id TEXT NOT NULL, -- top-level Session (child-session usage counts toward its main Session)
origin_session_id TEXT, -- immediate source child Session (last of the origin chain); NULL=main session
provider TEXT NOT NULL, -- provider group: paired with model_id as the attribution key; always GROUP BY provider, model_id
model_id TEXT NOT NULL, -- upstream model id (paired with provider; same model_id across providers aggregates separately)
cache_read INTEGER NOT NULL,
cache_write INTEGER NOT NULL,
output INTEGER NOT NULL,
total INTEGER NOT NULL, -- from token_usage.request (one row per Request)
status TEXT NOT NULL DEFAULT 'completed', -- request outcome: completed=success (with tokens); others=failure (0 tokens, for success rate)
cost REAL, -- USD, fixed by the recorder when the row is written, from the price the Project stores at that instant; NULL = the model has no price (or the row is not settled yet)
cost_settled INTEGER NOT NULL DEFAULT 0 -- 1 once cost is fixed; 0 = written by a build that did not price rows (settled once at startup)
); -- rebuildable from the Traces: the token counts still come from token_usage
CREATE INDEX IF NOT EXISTS idx_usage_project_date ON usage_records(project_id, date);
CREATE INDEX IF NOT EXISTS idx_usage_session_ts ON usage_records(session_id, ts); -- (session_id) alone was idx_usage_session (dropped on open): ts makes the last_active_at backfill's MAX(ts) a covering index scan instead of a random row lookup per usage record
CREATE TABLE IF NOT EXISTS error_records ( -- server-side error capture (the Costs page's "Errors" tab)
id INTEGER PRIMARY KEY AUTOINCREMENT,
ts TEXT NOT NULL,
date TEXT NOT NULL, -- local date yyyy-mm-dd (same convention as usage_records)
project_id TEXT, -- nullable: sign-in/registration and process-level errors have no Project context
agent_id TEXT,
session_id TEXT,
source TEXT NOT NULL, -- http | session | usage | title | subagent | process | llm | environment | compaction | schedule | messaging
kind TEXT NOT NULL, -- expected (HttpError, business 4xx) | unexpected (500/runtime)
code TEXT NOT NULL, -- HttpError.code / internal / session_run_failed / ...
status INTEGER, -- HTTP status code; NULL for non-HTTP sources
message TEXT NOT NULL -- truncated to 500 chars (no stack stored: stacks go to logs only)
);
CREATE INDEX IF NOT EXISTS idx_error_project_date ON error_records(project_id, date);
CREATE TABLE IF NOT EXISTS error_ignore_rules ( -- what the error panel's "ignore" leaves out: the records stay in error_records and the reads stop selecting them (ErrorsRepo.conds); NOT rebuildable from files, and only a person makes one
id INTEGER PRIMARY KEY AUTOINCREMENT,
project_id TEXT NOT NULL,
source TEXT NOT NULL, -- the error_records.source this rule covers
code TEXT NOT NULL, -- the error_records.code this rule covers
message_like TEXT, -- NULL = the whole source · code class; otherwise only messages containing this substring. Never "": instr(x, '') is > 0 for every row, so the empty string would silently mean "everything"
note TEXT, -- why it was silenced, in the owner's own words; shown in the rule list
created_by TEXT NOT NULL, -- the Project owner who made it
created_at TEXT NOT NULL
);
-- One rule per (source, code, message fragment): re-adding one is what this refuses. ifnull maps the NULL fragment (the whole class) onto '' so the two spellings of "no fragment" cannot both exist; the index also serves the per-Project read, whose only other predicate is the (source, code) pair.
CREATE UNIQUE INDEX IF NOT EXISTS idx_error_ignore_rule ON error_ignore_rules(project_id, source, code, ifnull(message_like, ''));
CREATE TABLE IF NOT EXISTS schedule_state ( -- schedule runtime state (files are declarative intent; the system never writes back)
project_id TEXT NOT NULL,
agent_id TEXT NOT NULL,
name TEXT NOT NULL, -- file name (without .toml) doubles as the identifier
creator_user_id TEXT, -- creator (recorded on API create; hand-edited file reconciliation falls back to the Project owner)
start_at_ms INTEGER NOT NULL, -- defines identity: a start_at change counts as a new task instance, resetting fire state
def_hash TEXT NOT NULL, -- file content fingerprint: any change clears the invalid flag (the file takes effect again after edits)
last_slot_ms INTEGER, -- latest due slot already consumed (advances on fire or skip; no re-fire across restarts)
last_fired_at TEXT, -- last actual fire time (for display)
fired_once INTEGER NOT NULL DEFAULT 0, -- one-shot task has fired
missed INTEGER NOT NULL DEFAULT 0, -- one-shot task was missed (flagged by startup/registration reconciliation; not backfilled)
invalid_reason TEXT, -- invalidation reason (e.g. the bound Session was deleted); NULL=healthy
PRIMARY KEY (project_id, agent_id, name)
);
CREATE TABLE IF NOT EXISTS messaging_bindings ( -- Session ↔ messaging-channel bot bindings (runtime/messaging/ holds the long connections)
session_id TEXT NOT NULL, -- a Session keeps at most one saved config PER channel (composite PK below); at most ONE of them is enabled at a time (route-enforced, 409 another_channel_enabled), and enabling is what binds the account to this Session
channel TEXT NOT NULL, -- messaging channel discriminator ('feishu' | 'telegram' | 'qq' | 'wechat')
account_id TEXT NOT NULL, -- channel-scoped bot/app identity (feishu: app_id; telegram: the bot token's numeric id; qq: the bot's App ID; wechat: the scanned bot id); NOT unique — any number of Sessions may keep the same account saved side by side, each with its own config and last-chat memory; only ENABLING is exclusive (route-enforced, 409 account_enabled_elsewhere: one account has one event stream, two live connections on it would race)
config_json TEXT NOT NULL, -- channel-specific credentials/config JSON (feishu: appId/appSecret/baseDomain; telegram: botToken; qq: appId/appSecret; wechat: botId/botToken/baseUrl/userId); secrets plaintext at rest (same trade-off as the proxy address in server_settings), masked at every API surface; a cleared secret is stored as "" (the row and its account identity stay)
enabled INTEGER NOT NULL DEFAULT 0, -- INTENT state (the connection's runtime status stays in memory): new bindings start disabled — saving credentials never opens a connection, the explicit state toggle does
line_per_message INTEGER NOT NULL DEFAULT 0, -- delivery preference: 1 = every non-blank line of a relayed assistant reply is sent as its own message (a reply written as spoken lines arrives as spoken lines), 0 = one message per reply. Channel-agnostic, so it lives here rather than in config_json, which each connector owns
final_reply_only INTEGER NOT NULL DEFAULT 0, -- delivery preference: 1 = a run's intermediate assistant messages are held and only its LAST completed one is sent, at the run's end, 0 = every completed assistant message mirrors the moment it completes. Channel-agnostic like the column above and independent of it: with both set, that one final text is the one split per line
render_markdown INTEGER NOT NULL DEFAULT 1, -- delivery preference: 1 = a relayed reply's Markdown is rendered in the channel's own markup (Telegram HTML, a Feishu card, QQ markdown, the subset WeChat itself reads), 0 = the reply's characters are sent as written. DEFAULT 1 because the model writes Markdown and showing it raw was the defect; the per-channel subsets and the plain-text fallback live in the connectors
last_chat_id TEXT, -- most recent inbound chat (NULL until the bot is messaged once; replies and test messages target it); CONNECTOR-OPAQUE, not necessarily a bare chat id — telegram appends the forum topic (see telegram-connector.ts chatRefOf), so only that channel's connector may interpret it
last_chat_is_direct INTEGER NOT NULL DEFAULT 1, -- 1 = direct chat (reply by chat id), 0 = group chat (prefer reply-to-message)
last_inbound_message_id TEXT, -- channel id of the most recently PROCESSED inbound message (NULL until one arrives); the durable half of the bridge's redelivery guard, so a channel replaying an event this server already turned into a Task after a restart does not run it twice
created_at TEXT NOT NULL,
updated_at TEXT NOT NULL,
PRIMARY KEY (session_id, channel)
);
CREATE INDEX IF NOT EXISTS idx_messaging_by_account ON messaging_bindings(channel, account_id); -- serves the enable guard's by-account lookup; deliberately NOT unique, unlike its predecessor idx_messaging_account (dropped on open) which made an account exclusive to one Session forever
CREATE TABLE IF NOT EXISTS ui_prefs (
user_id TEXT PRIMARY KEY REFERENCES users(user_id) ON DELETE CASCADE,
prefs_json TEXT NOT NULL -- {theme?, lastProjectId?, ...} free-form JSON
);
CREATE TABLE IF NOT EXISTS agent_api ( -- per-Agent public API settings (amsp/routes.ts); NOT rebuildable from files
project_id TEXT NOT NULL,
agent_id TEXT NOT NULL,
enabled INTEGER NOT NULL DEFAULT 0, -- the API tab's switch; off = every /api/amsp request for it is 404 agent_not_found
open INTEGER NOT NULL DEFAULT 0, -- keyless access: a request without Authorization is accepted; off = 401 unauthorized
approval_mode TEXT NOT NULL DEFAULT 'allow-all', -- the approval mode an API Session is CREATED with (allow-all|deny-all|read-only|always-ask); copied onto sessions.approval_mode, never read per decision
updated_at TEXT NOT NULL,
PRIMARY KEY (project_id, agent_id)
);
CREATE TABLE IF NOT EXISTS agent_api_keys ( -- one row per key the API tab or the CLI minted; the caller alone holds the key
key_id TEXT PRIMARY KEY, -- 16 base64url chars, shown in the list
project_id TEXT NOT NULL,
agent_id TEXT NOT NULL,
token_hash TEXT NOT NULL UNIQUE, -- sha256(key) hex
prefix TEXT NOT NULL, -- the key's first 16 characters ("penguin_" + 8), shown in the list
name TEXT NOT NULL, -- 1-64 characters
created_by TEXT NOT NULL REFERENCES users(user_id),
created_at TEXT NOT NULL,
last_used_at TEXT -- stamped once per run request that authenticated with it; NULL = never
);
CREATE INDEX IF NOT EXISTS idx_agent_api_keys_agent ON agent_api_keys(project_id, agent_id);
CREATE TABLE IF NOT EXISTS server_settings ( -- admin-level server-global settings (GET/PUT /api/admin/settings)
key TEXT PRIMARY KEY, -- setting name, e.g. 'proxy_for_app'
value TEXT NOT NULL -- JSON-encoded value; an absent row means the setting's built-in default
);
CREATE TABLE IF NOT EXISTS machine ( -- this server's OWN identity, minted here and never assigned from outside (what 'penguin server status' reports)
singleton INTEGER PRIMARY KEY CHECK (singleton = 1), -- exactly one row, enforced by the schema rather than by convention
machine_id TEXT NOT NULL -- 16 base64url characters; what every stored reference to this machine points at
);
CREATE TABLE IF NOT EXISTS machines ( -- one row per machine this server installed on or reached (machines/service.ts)
address TEXT PRIMARY KEY, -- 'ssh:<alias>'
machine_id TEXT, -- that machine's own id, once heard
version TEXT, -- what this server last installed there; NULL = never
installed_at TEXT,
session_pid INTEGER, -- the ssh session this server holds to it; what a successor App closes after a hot swap
remote_port INTEGER, -- its server's port over there, as of the last connect
platform TEXT -- linux | darwin | win32, as the install found it: the shell dialect the status probe speaks to it in
);
CREATE TABLE IF NOT EXISTS machine_project ( -- which machines a Project uses; no row = none yet. Follows the Project out, like project_members
project_id TEXT PRIMARY KEY REFERENCES projects(project_id) ON DELETE CASCADE,
addresses TEXT NOT NULL -- JSON array of 'ssh:<alias>'
);
CREATE TABLE IF NOT EXISTS trace_files ( -- DERIVED CACHE of the on-disk Trace tree (services/trace-index.ts): the directories stay the single source of truth, every row is rebuildable from disk, and a row is never authority for absence — consumers reconcile + retry on a miss, so a stale index costs one extra scan, never a false 404
project_id TEXT NOT NULL,
agent_id TEXT NOT NULL,
session_id TEXT NOT NULL,
file_index INTEGER NOT NULL, -- shard index NNN of <session_id>_NNN.jsonl (name/path are reconstructed from the row, never stored: the data root may move)
date TEXT NOT NULL, -- date directory name (local yyyy-mm-dd)
size_bytes INTEGER NOT NULL, -- last observed size (listings stat-refresh the returned page and write back; an actively-appended shard may lag in between)
page_stats TEXT, -- cached message-window scan state at this shard's END (services/message-window.ts ShardPrefixRecord JSON); immutable shards only, NULL = not yet computed, nulled whenever size_bytes changes
PRIMARY KEY (project_id, agent_id, session_id, file_index)
);
CREATE INDEX IF NOT EXISTS idx_trace_files_agent_date ON trace_files(project_id, agent_id, date);
CREATE INDEX IF NOT EXISTS idx_trace_files_session ON trace_files(session_id);
CREATE TABLE IF NOT EXISTS trace_sessions ( -- per-session facts read ONCE at registration from the earliest shard's head (same derived-cache rules as trace_files; listing needs zero head-reads)
session_id TEXT PRIMARY KEY,
project_id TEXT NOT NULL,
agent_id TEXT NOT NULL,
source TEXT, -- session_meta source as the head carried it: 'user' | 'api' | 'schedule' | 'subagent' | 'cli' | 'company'; NULL = the head records none (written before the source was required: read as 'company' for an 'org' row, else 'user' — readRecordedSource); 'benchmark' from older rows reads as 'cli' (normalizeSessionSource; meaningless while meta_read = 0)
workspace TEXT NOT NULL DEFAULT '',
title TEXT, -- first-prompt fallback title (sessions.title always wins when present)
provider TEXT, -- model reference from session_meta: the model the Session was created on (CLI adoption reads it from here, and the runtime's own answer corrects the row at load; NULL = meta unreadable / legacy without provider)
model_id TEXT,
first_ts TEXT, -- first record's timestamp (adoption's createdAt fallback when the id embeds no time)
meta_read INTEGER NOT NULL DEFAULT 0 -- 1 once the head parsed; 0 = facts unknown, retried by the next reconcile that touches the session
);
CREATE INDEX IF NOT EXISTS idx_trace_sessions_agent ON trace_sessions(project_id, agent_id);
CREATE TABLE IF NOT EXISTS org_sessions ( -- DERIVED CACHE (company mode): desk sessions, rebuilt from each organization's desks.toml (current + previous); trigger_hop is mention-chain accounting and reads 0 after a rebuild
session_id TEXT PRIMARY KEY,
project_id TEXT NOT NULL,
org_id TEXT NOT NULL,
agent_id TEXT NOT NULL,
current INTEGER NOT NULL DEFAULT 1,
trigger_hop INTEGER NOT NULL DEFAULT 0
);
CREATE INDEX IF NOT EXISTS idx_org_sessions_org ON org_sessions(project_id, org_id);
CREATE TABLE IF NOT EXISTS org_ticket_sessions ( -- DERIVED CACHE (company mode): ticket <-> contributing session, rebuilt from each ticket's Sessions header
project_id TEXT NOT NULL,
org_id TEXT NOT NULL,
ticket_id TEXT NOT NULL,
session_id TEXT NOT NULL,
agent_id TEXT NOT NULL,
trigger_hop INTEGER NOT NULL DEFAULT 0,
PRIMARY KEY (project_id, org_id, ticket_id, session_id)
);
CREATE INDEX IF NOT EXISTS idx_org_ticket_sessions_session ON org_ticket_sessions(session_id);
CREATE TABLE IF NOT EXISTS org_calendar_state ( -- calendar run state (company mode; the files are declarative intent), same rules as schedule_state
project_id TEXT NOT NULL,
org_id TEXT NOT NULL,
agent_id TEXT NOT NULL,
name TEXT NOT NULL,
start_at_ms INTEGER NOT NULL,
def_hash TEXT NOT NULL,
last_slot_ms INTEGER,
last_fired_at TEXT,
fired_once INTEGER NOT NULL DEFAULT 0,
missed INTEGER NOT NULL DEFAULT 0,
invalid_reason TEXT,
last_outcome TEXT,
PRIMARY KEY (project_id, org_id, agent_id, name)
);
CREATE TABLE IF NOT EXISTS org_ticket_state ( -- DERIVED CACHE (company mode): the last (status, owner, blocked) seen per ticket, so a change is notified once; rebuilt silently from the ticket files
project_id TEXT NOT NULL,
org_id TEXT NOT NULL,
ticket_id TEXT NOT NULL,
status TEXT NOT NULL,
owner TEXT NOT NULL DEFAULT '',
blocked TEXT NOT NULL DEFAULT '',
blocked_by TEXT NOT NULL DEFAULT '',
PRIMARY KEY (project_id, org_id, ticket_id)
);
CREATE TABLE IF NOT EXISTS org_channel_state ( -- DERIVED CACHE (company mode): tail-scan byte cursor per channel and day file
project_id TEXT NOT NULL,
org_id TEXT NOT NULL,
channel_id TEXT NOT NULL,
date TEXT NOT NULL,
offset_bytes INTEGER NOT NULL DEFAULT 0,
PRIMARY KEY (project_id, org_id, channel_id, date)
);
CREATE TABLE IF NOT EXISTS org_channel_reads ( -- user data (company mode): each user's read cursor in one channel
project_id TEXT NOT NULL,
org_id TEXT NOT NULL,
channel_id TEXT NOT NULL,
user_id TEXT NOT NULL,
last_read_id TEXT NOT NULL,
PRIMARY KEY (project_id, org_id, channel_id, user_id)
);
CREATE TABLE IF NOT EXISTS org_budget_state ( -- DERIVED CACHE (company mode): warn / pause marks per employee and period, recomputed from usage and the chart's budgets
project_id TEXT NOT NULL,
org_id TEXT NOT NULL,
agent_id TEXT NOT NULL,
period TEXT NOT NULL,
warned_at TEXT,
paused_at TEXT,
PRIMARY KEY (project_id, org_id, agent_id, period)
);
CREATE TABLE IF NOT EXISTS org_desk_notices ( -- DERIVED CACHE (company mode): ticket changes waiting for an employee's next calendar sweep; dropping it loses only the digests not delivered yet
seq INTEGER PRIMARY KEY AUTOINCREMENT,
project_id TEXT NOT NULL,
org_id TEXT NOT NULL,
agent_id TEXT NOT NULL,
ticket_id TEXT NOT NULL,
change TEXT NOT NULL,
at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_org_desk_notices_agent ON org_desk_notices(project_id, org_id, agent_id);
CREATE TABLE IF NOT EXISTS org_desk_mentions ( -- DERIVED CACHE (company mode): channel mentions waiting for an employee's desk to be idle; dropping it loses only the mentions not delivered yet (the messages stay in the channel files)
seq INTEGER PRIMARY KEY AUTOINCREMENT,
project_id TEXT NOT NULL,
org_id TEXT NOT NULL,
agent_id TEXT NOT NULL,
channel_id TEXT NOT NULL,
date TEXT NOT NULL,
message_id TEXT NOT NULL,
hop INTEGER NOT NULL,
UNIQUE (project_id, org_id, agent_id, channel_id, message_id)
);
`;