perf(db): corsair_entities / corsair_accounts have no index on their query access paths

[yashksaini-coder]

perf(db): corsair_entities / corsair_accounts have no index on their query access paths

Type: bug (performance) · Template: Report an issue

Steps to reproduce

1. Set up a Corsair DB by following the documented schema — docs/concepts/database.mdx (SQLite ~L114, Postgres ~L189), duplicated in docs/getting-started/quick-start.mdx and docs/guides/dashboard.mdx.
2. Sync entities so corsair_entities grows (this is the table the sync layer writes on every webhook event).
3. Read any entity via the *.db.* ORM (findByEntityId, existsByEntityId, upsertByEntityId, list, count).

Actual behaviour

corsair_entities is created with only id as PRIMARY KEY (+ an FK on account_id); corsair_accounts only with id PK. There is no CREATE INDEX anywhere in the repo (packages or docs) for either table, and the ORM ships no migrations — users build the schema from the docs.

But the query code always filters on other columns:

• packages/corsair/db/kysely/orm.ts baseQuery → WHERE account_id = ? AND entity_type = ?, and the common paths add AND entity_id = ?.
• packages/corsair/core/account-lookup.ts:48-51 → WHERE tenant_id = ? AND integration_id = ?, resolved before every entity operation.

With no matching index, every one of these is a full table scan (EXPLAIN QUERY PLAN → SCAN corsair_entities), whose cost grows linearly with the total row count across all tenants and all plugins.

Expected behaviour

Index the two access paths:

• UNIQUE (account_id, entity_type, entity_id) on corsair_entities — this identity is already enforced by demo/mcp/db.ts and assumed by upsertByEntityId, but is missing from the documented schema.
• (tenant_id, integration_id) on corsair_accounts.

Benchmark (evidence)

Python stdlib sqlite3, exact documented schema, findByEntityId pattern:

Rows	No index (current)	With index	Speedup	Plan
100,000	6,691 µs/query	7.7 µs	870×	SCAN → SEARCH USING INDEX
900,000	61,425 µs/query	9.3 µs	6,630×	SCAN → SEARCH USING INDEX

Without the index, per-query time scales linearly with table size (6.7 ms → 61 ms as rows go 100k → 900k); with it, it stays flat (~9 µs). That's O(n) vs O(log n) on the hottest table in a Corsair app.

Proposed fix

Add the two indexes to every hand-authored schema (the three docs pages + demo/testing/migration.sql + demo/mcp/db.ts). PR ready. Note for existing deployments: a UNIQUE index requires de-duplicating any rows the current non-atomic upsertByEntityId may have produced first (fresh CREATE TABLE installs are unaffected). The www/ Drizzle schema and the runtime test setups are follow-ups, tracked separately.