mirror of
https://gh-proxy.org/https://github.com/klinsmaya/multica.git
synced 2026-11-08 01:16:45 +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]>
92 lines
2.3 KiB
Go
92 lines
2.3 KiB
Go
// Code generated by sqlc. DO NOT EDIT.
|
|
// versions:
|
|
// sqlc v1.31.1
|
|
// source: task_token.sql
|
|
|
|
package db
|
|
|
|
import (
|
|
"context"
|
|
|
|
"github.com/jackc/pgx/v5/pgtype"
|
|
)
|
|
|
|
const createTaskToken = `-- 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($7::uuid, gen_random_uuid()))
|
|
RETURNING id, token_hash, task_id, agent_id, workspace_id, user_id, expires_at, created_at
|
|
`
|
|
|
|
type CreateTaskTokenParams struct {
|
|
TokenHash string `json:"token_hash"`
|
|
TaskID pgtype.UUID `json:"task_id"`
|
|
AgentID pgtype.UUID `json:"agent_id"`
|
|
WorkspaceID pgtype.UUID `json:"workspace_id"`
|
|
UserID pgtype.UUID `json:"user_id"`
|
|
ExpiresAt pgtype.Timestamptz `json:"expires_at"`
|
|
ID pgtype.UUID `json:"id"`
|
|
}
|
|
|
|
func (q *Queries) CreateTaskToken(ctx context.Context, arg CreateTaskTokenParams) (TaskToken, error) {
|
|
row := q.db.QueryRow(ctx, createTaskToken,
|
|
arg.TokenHash,
|
|
arg.TaskID,
|
|
arg.AgentID,
|
|
arg.WorkspaceID,
|
|
arg.UserID,
|
|
arg.ExpiresAt,
|
|
arg.ID,
|
|
)
|
|
var i TaskToken
|
|
err := row.Scan(
|
|
&i.ID,
|
|
&i.TokenHash,
|
|
&i.TaskID,
|
|
&i.AgentID,
|
|
&i.WorkspaceID,
|
|
&i.UserID,
|
|
&i.ExpiresAt,
|
|
&i.CreatedAt,
|
|
)
|
|
return i, err
|
|
}
|
|
|
|
const deleteExpiredTaskTokens = `-- name: DeleteExpiredTaskTokens :exec
|
|
DELETE FROM task_token WHERE expires_at <= now()
|
|
`
|
|
|
|
func (q *Queries) DeleteExpiredTaskTokens(ctx context.Context) error {
|
|
_, err := q.db.Exec(ctx, deleteExpiredTaskTokens)
|
|
return err
|
|
}
|
|
|
|
const deleteTaskTokensByTask = `-- name: DeleteTaskTokensByTask :exec
|
|
DELETE FROM task_token WHERE task_id = $1
|
|
`
|
|
|
|
func (q *Queries) DeleteTaskTokensByTask(ctx context.Context, taskID pgtype.UUID) error {
|
|
_, err := q.db.Exec(ctx, deleteTaskTokensByTask, taskID)
|
|
return err
|
|
}
|
|
|
|
const getTaskTokenByHash = `-- name: GetTaskTokenByHash :one
|
|
SELECT id, token_hash, task_id, agent_id, workspace_id, user_id, expires_at, created_at FROM task_token
|
|
WHERE token_hash = $1 AND expires_at > now()
|
|
`
|
|
|
|
func (q *Queries) GetTaskTokenByHash(ctx context.Context, tokenHash string) (TaskToken, error) {
|
|
row := q.db.QueryRow(ctx, getTaskTokenByHash, tokenHash)
|
|
var i TaskToken
|
|
err := row.Scan(
|
|
&i.ID,
|
|
&i.TokenHash,
|
|
&i.TaskID,
|
|
&i.AgentID,
|
|
&i.WorkspaceID,
|
|
&i.UserID,
|
|
&i.ExpiresAt,
|
|
&i.CreatedAt,
|
|
)
|
|
return i, err
|
|
}
|