Engagement — Database

Last Updated: 2026-09-05 · Status: Active

Own Neon Postgres (vlozi-engagement), separate from core. Schema in src/db/schema.ts, migrations in drizzle/.

The tables

Every table carries tenant_id except eng_platform_blocklist, and that absence is the whole point of it: a spammer hitting fifty customers shouldn't need fifty bans.

Why the counters are split three ways

eng_target_stats (all-time), eng_target_daily (per-day) and the Durable Object's own SQLite (the last minute) look redundant. They aren't:

• The DO absorbs the write rate. A post on the front page of Hacker News is thousands of views a minute; a Postgres upsert per view would melt.
• eng_target_stats answers "how many views does this post have?" in one indexed read. Summing eng_target_daily for an all-time count would scan every day ever.
• eng_target_daily answers "the last 30 days" and every chart. eng_target_stats cannot, because it's a single number with no history.

Writing both on each flush costs one extra upsert per target per minute, which is nothing next to what either alternative costs on read.

Where each number actually comes from

The last row is the one to be careful with. AE samples above a write-rate threshold and reports a multiplier; every count is scaled back up by SUM(_sample_interval), which makes the figures comparable with each other but not with the Postgres rollups. Never reconcile billing against them.

Notable columns

eng_comments.author_email_hash — sha256 of the lowercased address. It powers the "has this person been approved before?" lookup without a second plaintext copy sitting in an index.

eng_comments.published_at — never cleared, even on an unpublish. It doubles as the "has this comment ever been billed?" marker, which is what stops a published → edited → re-approved comment from charging twice.

eng_comments.parent_id — a self-reference with no foreign key and no cascade. Deleting a parent leaves replies in place; parent_id is only used for display grouping, and cascading would delete other people's comments because one person's was removed.

eng_members.session_version — the only revocation lever. Sessions are stateless signed tokens, so bumping this integer is what makes "log out everywhere" and a ban take effect immediately.

eng_members.email_hash — unique per (tenant_id, email_hash). GDPR erasure changes it along with the address, or the original would stay confirmable by guessing.

eng_settings.notify.lastDigestAt / lastWeeklyAt — JSONB bookkeeping that makes the digest crons safe to over-call.

eng_settings defaults

getSettings returns a fully-populated object when no row exists, so a tenant who never opened Settings reads a complete object without forcing a write.

moderation_mode: "first_time" and ai_moderation: true together mean a brand-new tenant gets held first comments and AI screening without configuring anything. That is deliberate — the safe default for a surface facing the open internet — and it does surprise people writing their first test.

Privacy

A reader who only reads has nothing written to their browser and nothing identifying written to our database.

Migrations

drizzle-kit generate into drizzle/, journal version 7.

ENGAGEMENT_DATABASE_URL="postgres://…" npx drizzle-kit generate   # from apps/engagement-service
pnpm --filter engagement-service migrate

Tests run the real migrations against PGlite (test/setup.ts), so a migration that doesn't apply cleanly fails the suite rather than the deploy.