Full-text search with Postgres tsvector in SvelteKit

Why Postgres native search is enough

Postgres ships full-text search built in: it tokenizes text into tsvector, indexes it with GIN, and matches queries through tsquery operators. For an audit log — where rows are append-only, org-scoped, and small — this removes the operational cost of a separate search cluster while keeping queries fast.

• No external service: nothing to host, secure, or keep in sync — the index lives inside your existing Postgres.
• Transactional consistency: because the tsvector is a generated column, it updates atomically with the row.
• Tenant-scoped naturally: you filter by org_id exactly as you do for every other audit read, so search never becomes a cross-tenant path.
• Good enough for activity/audit search: thousands to low-millions of rows with GIN search in milliseconds.

In v0.1 the starter ships the plain audit_log table (append-only, action text, metadata_json text). Native full-text search is a documented add-yourself pattern in docs/deployment.md — the SQL you need is copied below.

Adding a tsvector column the right way

The recommended pattern adds a generated, stored tsvector column over the fields you want to search — in this case the audit action plus any metadata_json. Because it is STORED, Postgres maintains it automatically; you never write to it directly. The exact snippet from the kit's deployment guide:

ALTER TABLE audit_log ADD COLUMN search tsvector
  GENERATED ALWAYS AS (to_tsvector('simple', coalesce(action,'') || ' ' || coalesce(metadata_json,''))) STORED;
CREATE INDEX audit_log_search_idx ON audit_log USING GIN (search);

Two choices worth understanding:

• simple config — lowercases and splits on whitespace without stemming. Predictable and easy to reason about; swap to english if you want stemming for user-facing product search.
• coalesce(..., '') — audit_log.org_id/actor_user_id can be NULL (history survives member removal), and metadata_json can be empty. Concatenating NULL would make the whole generated value NULL, which silently drops the row from search — coalescing avoids that.

Querying with tsquery

Once the column and index exist, query it with a tsquery match. For free-text user input, plainto_tsquery tokenizes the phrase the same way to_tsvector tokenizes the row (use the same config on both sides so the index is usable):

SELECT * FROM audit_log
WHERE search @@ plainto_tsquery('simple', 'ownership.transferred');

From a SvelteKit load function or service, scope it to the tenant the same way every audit read is scoped — the org membership is part of the WHERE clause, so a search never leaks across orgs:

// Abridged — orgId comes from the caller's membership, exactly like other reads.
const rows = await db.select().from(auditLog)
  .where(and(
    eq(auditLog.orgId, orgId),
    sql`${auditLog.search} @@ plainto_tsquery('simple', ${query})`
  ))
  .limit(50);

Adjust the query builder to your Drizzle setup — the key points are the @@ match and the always-present org_id filter.

Keeping it append-only and tenant-safe

Audit rows remain immutable: you only add the generated search column and a GIN index — no UPDATE or DELETE path is introduced. Because the filter is org-scoped, enabling search does not weaken the append-only or tenancy guarantees. If you later adopt JSONB metadata (see the JSONB metadata guide), the generated column can include the JSONB text the same way.

When to reach for a real search engine

• Cross-field relevance ranking with custom weighting (vector similarity, boosts) at scale.
• Fuzzy / typo-tolerant and prefix-as-you-type UX that readers expect from a search box.
• Very large corpora (tens of millions of rows and up) where a dedicated index is warranted.
• Analytics aggregations over the search results.

For the common case — an org-scoped audit/activity search on append-only rows — native tsvector + GIN is simpler and cheaper. You graduate to a dedicated engine when the UX demands dictate it, not before.

Production checklist

• Search what your users actually query — the audit log action text and any JSON metadata — and build the tsvector over those columns with coalesce so NULLs never break the generated column.
• Use a STORED generated column for the tsvector: it is kept in sync by Postgres itself, so you never have to maintain a separate triggers/application write path.
• Add a GIN index over the tsvector column; without it every query is a full sequential scan that defeats the purpose.
• Constrain every search query to the current tenant (org_id) just like every other audit read — full-text search must not become a cross-tenant query path.
• Use plainto_tsquery for free-text user input and to_tsquery for controlled keywords; pick one config (e.g. simple) consistently so the index matches the query.
• Treat this as a documented add-yourself pattern in v0.1: the starter ships the plain append-only audit log with a text JSON metadata column; switch to jsonb + tsvector when you need native search.

Related guides: JSONB metadata · Multi-tenant DB design · RLS defense-in-depth