Database Schema Designer

At 50M events/month (~19 events/sec avg), PostgreSQL with TimescaleDB is the right call — time-series optimizations without leaving SQL.

CREATE EXTENSION IF NOT EXISTS timescaledb;

CREATE TABLE events (
  time        TIMESTAMPTZ NOT NULL,
  user_id     UUID NOT NULL,
  session_id  UUID NOT NULL,
  event_type  TEXT NOT NULL,
  page        TEXT,
  properties  JSONB
);

-- Hypertable auto-partitions by week
SELECT create_hypertable('events', 'time',
  chunk_time_interval => INTERVAL '1 week'
);

-- Indexes for your three query patterns
CREATE INDEX idx_events_user_time  ON events (user_id, time DESC);
CREATE INDEX idx_events_type_time  ON events (event_type, time DESC);
CREATE INDEX idx_events_session    ON events (session_id);

-- Continuous aggregate for daily rollups (pre-computed, instant queries)
CREATE MATERIALIZED VIEW events_daily
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 day', time) AS day,
       event_type,
       count(*) AS event_count,
       count(DISTINCT user_id) AS unique_users
FROM events GROUP BY 1, 2;

Funnel query:

SELECT
  count(*) FILTER (WHERE event_type = 'page_view')    AS step1_views,
  count(*) FILTER (WHERE event_type = 'click')        AS step2_clicks,
  count(*) FILTER (WHERE event_type = 'form_submit')  AS step3_submits
FROM events WHERE time > now() - INTERVAL '30 days';

Storage: 50M events × 200 bytes = ~10GB/month raw. TimescaleDB columnar compression → ~1.5GB/month. Enable with: SELECT add_compression_policy('events', INTERVAL '7 days');

This skill includes executable scripts — I'll need your permission before running them.

Zero-downtime strategy — three safe steps:

-- Step 1: Add nullable column (milliseconds — no table rewrite)
ALTER TABLE users ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMPTZ;
ALTER TABLE posts ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMPTZ;
-- Safe on 40M rows — adding a nullable column is instant in PG 11+

-- Step 2: Add partial indexes (CONCURRENTLY — zero downtime, ~5-10 min)
CREATE INDEX CONCURRENTLY idx_users_active
  ON users (id) WHERE deleted_at IS NULL;
CREATE INDEX CONCURRENTLY idx_posts_active
  ON posts (user_id, created_at DESC) WHERE deleted_at IS NULL;
-- Most queries only touch active records — this index is tiny and fast

Step 3: Deploy app code with the filter (before telling anyone soft-delete is live)

// Prisma middleware — auto-adds filter to all queries
prisma.$use(async (params, next) => {
  const softDeleteModels = ['User', 'Post']
  if (softDeleteModels.includes(params.model) &&
      ['findMany', 'findFirst', 'count'].includes(params.action)) {
    params.args.where = { ...params.args.where, deletedAt: null }
  }
  return next(params)
})

// Soft delete helper
async function softDelete(model: string, id: string) {
  return prisma[model].update({
    where: { id },
    data: { deletedAt: new Date() }
  })
}

Critical ordering: Deploy the app code with the deletedAt: null filter before you start soft-deleting records. Otherwise a rollback leaves 'deleted' records visible.