Postgres analytics

@voltro/plugin-analytics-postgres is the day-1 AnalyticsSink — it stores events in an _voltro_events table on the main DataStore, so there's no external service to run. It implements all four contract methods (track / aggregate / timeseries / topN) by compiling to portable SQL that works on every dialect (postgres / mysql / mariadb / mssql / sqlite). Pick it first; swap to a real OLAP sink (DuckDB / ClickHouse / Tinybird) only once you outgrow its ~10M events/day ceiling — the swap is a one-line config change because every sink shares the same contract. For the shared AnalyticsSink API and the useAnalytics() read methods, see Analytics & warehouse sinks.

#Install

pnpm add @voltro/plugin-analytics-postgres

#Wiring

// app.config.ts
import { postgresAnalytics } from '@voltro/plugin-analytics-postgres'

export default {
  type:      'api' as const,
  name:      'myApi',
  store:     'postgres' as const,
  analytics: postgresAnalytics(),
}

The framework calls the factory at boot, hands it the SqlClient bound to the running DataStore, and provides the resulting sink under AnalyticsSink. The plugin's _voltro_events table is auto-merged into your schema, so applySchema() creates it on first boot — no *.entity.ts needed.

#Options

postgresAnalytics(options?) — all optional:

Option	Type	Default	Notes
sqlClient	SqlClient.SqlClient	the main DataStore's client	Override to land events in a SEPARATE database (e.g. a dedicated analytics Postgres while the app runs MySQL). You must create the events table there yourself.
mirrorTables	ReadonlyArray<string>	[] (events-only)	Reactive tables to CDC-mirror into _voltro_mirror_<table> ({ id, data }) so analytical queries can JOIN events against live user data.
mirrorPrimaryKey	string	'id'	Primary-key column on the mirrored source rows.

analytics: postgresAnalytics({ mirrorTables: ['users', 'teams'] })

#Environment

• VOLTRO_EVENTS_TTL_HOURS — retention TTL for _voltro_events. Default 8760 (365 days). A boot retention sweep drains events past the TTL so the table never grows without bound.

#Honest ceiling

By ~10M events/day, range aggregates over 30 days take >5s on Postgres. Past that, switch to @voltro/plugin-duckdb (embedded) or @voltro/plugin-clickhouse (external). Reads are tenant-scoped automatically — useAnalytics() stamps the caller's tenantId onto every track and every aggregate.

#Schema introspection

The plugin re-exports its events-table descriptor so you can extend the schema (add a GIN index on properties, partition by month) via your own migration:

import { _voltroEventsTable } from '@voltro/plugin-analytics-postgres'

#See also

• Analytics & warehouse sinks — the shared AnalyticsSink contract, the useAnalytics() read API, composeAnalytics, and the CDC-mirror details.