mirror of
https://github.com/tiennm99/goclaw.git
synced 2026-10-18 02:14:43 +00:00
viettranx
c4d13ca4a7
- Convert importCron/importUserProfiles/importUserOverrides to multi-row UPSERT with chunking (200→2 queries per import) - Add GetTenantsByIDs batch method to TenantStore (PG+SQLite), refactor handleMine() to single-query tenant fetch (N→1) - Refactor buildAgentKeyMap() to use existing GetByKeys with tenant-scoped context instead of per-key SELECT (N→1) - Add GetDocumentsByIDs, GetDocumentByBasename, CreateLinks batch methods to VaultStore (PG+SQLite) - Batch-fetch docs in enrichment Phase 0, carry title through pipeline to avoid per-doc refetch in classify phase - Replace ListDocuments(limit=500) fallback in wikilink resolution with targeted GetDocumentByBasename DB query - Batch CreateLink calls in SyncDocLinks and classifyLinks - Refactor usage handleGet/handleSummary to use ListPagedRich instead of List+GetOrCreate N+1 loop (100→1 queries) - Batch INSERT for team import members/comments/events/links (250→5) - Add migration 47: UNIQUE constraint on cron_jobs(agent_id,tenant_id,name) with dedup, SQLite schema v15
20 lines
784 B
SQL
20 lines
784 B
SQL
-- Deduplicate cron_jobs: keep most recently updated row per (agent_id, tenant_id, name).
|
|
DELETE FROM cron_jobs
|
|
WHERE id NOT IN (
|
|
SELECT DISTINCT ON (agent_id, tenant_id, name) id
|
|
FROM cron_jobs
|
|
ORDER BY agent_id, tenant_id, name, updated_at DESC
|
|
);
|
|
|
|
-- Create unique index (will succeed after dedup).
|
|
CREATE UNIQUE INDEX IF NOT EXISTS uq_cron_jobs_agent_tenant_name
|
|
ON cron_jobs (agent_id, tenant_id, name);
|
|
|
|
-- Generated column for path basename (case-insensitive wikilink resolution).
|
|
ALTER TABLE vault_documents ADD COLUMN IF NOT EXISTS path_basename TEXT
|
|
GENERATED ALWAYS AS (lower(regexp_replace(path, '.+/', ''))) STORED;
|
|
|
|
-- Index for fast basename lookup by tenant.
|
|
CREATE INDEX IF NOT EXISTS idx_vault_docs_basename
|
|
ON vault_documents(tenant_id, path_basename);
|