mirror of
https://gh-proxy.org/https://github.com/klinsmaya/multica.git
synced 2026-11-08 09:26:50 +08:00
b4bfe083be
* perf(db): mint UUIDv7 primary keys for the append-heavy tables
Random v4 primary keys scatter every insert across the whole primary-key
B-tree, so each row dirties a different page. On the tables that grow
continuously — the task queue, messages, comments, activity and audit rows —
that is pure write amplification. UUIDv7 puts a big-endian millisecond
timestamp in the leading 48 bits, so consecutive inserts share a page.
The 12 tables converted here are the append-only hot paths:
agent_task_queue, activity_log, inbox_item, comment, issue, chat_session,
chat_message, autopilot_run, webhook_delivery, task_token,
channel_inbound_audit, sys_cron_executions
Ids come from the new pkg/dbid, which returns a pgtype.UUID ready to assign to
a sqlc params struct. Upsert-dominated tables (task_usage, agent_runtime, ...)
are deliberately left alone: v7 only buys locality for rows that are actually
inserted. Lease and claim tokens keep gen_random_uuid() — they must stay
unpredictable and carry no timestamp.
No migration, and no backfill. Every converted INSERT binds its id as
COALESCE(sqlc.narg('id')::uuid, gen_random_uuid()), so the column default stays
reachable: a caller that passes no id inserts exactly as it does today. Tables
will hold a mix of v4 and v7 ids permanently, which the uuid type and every
parser in the repo already accept.
Verification: - pkg/dbid: version/variant, uniqueness, and a non-decreasing timestamp
prefix; a shape test asserting every converted query keeps the COALESCE
fallback; and a source scan asserting every production params literal mints
an id, so a call site added later cannot silently fall back to v4.
- internal/handler: round-trips issue, comment, activity_log, inbox_item,
chat_session, chat_message, agent_task_queue, task_token and
channel_inbound_audit against Postgres and asserts the stored id is v7,
plus one case proving an unset id still inserts (as v4).
- internal/scheduler: covers the one hand-written INSERT, asserting the
execution id is v7 while lease_token stays v4.
Co-authored-by: multica-agent <[email protected]>
* fix(db): address UUIDv7 review feedback
Co-authored-by: multica-agent <[email protected]>
* fix(db): cover Telegram audit UUIDv7
Co-authored-by: multica-agent <[email protected]>
---------
Co-authored-by: Sol-Boy <[email protected]>
Co-authored-by: multica-agent <[email protected]>
15 lines
498 B
SQL
15 lines
498 B
SQL
-- name: CreateTaskToken :one
|
|
INSERT INTO task_token (token_hash, task_id, agent_id, workspace_id, user_id, expires_at, id)
|
|
VALUES ($1, $2, $3, $4, $5, $6, COALESCE(sqlc.narg('id')::uuid, gen_random_uuid()))
|
|
RETURNING *;
|
|
|
|
-- name: GetTaskTokenByHash :one
|
|
SELECT * FROM task_token
|
|
WHERE token_hash = $1 AND expires_at > now();
|
|
|
|
-- name: DeleteTaskTokensByTask :exec
|
|
DELETE FROM task_token WHERE task_id = $1;
|
|
|
|
-- name: DeleteExpiredTaskTokens :exec
|
|
DELETE FROM task_token WHERE expires_at <= now();
|