Privacy-First Web Analytics Platform (Plausible Alternative)

Quietstat — Privacy-First Web Analytics Platform: Product Specification #

Quietstat is a cookie-free, open-source web analytics product that matches Plausible Analytics feature for feature. It counts visitors with a daily rotating salted hash instead of cookies, never stores raw IP addresses or User-Agent strings, and shows traffic, conversions, funnels, user journeys and revenue on a single-page dashboard. One codebase ships two editions: a multi-tenant hosted service sold as a one-time lifetime deal in three tiers with fixed site and monthly event caps, and a free self-hosted edition deployed with Docker Compose.

This document is the complete build specification. It is written so an AI coding agent or an engineering team can build the product from it alone, without clarifying questions: every default is decided, each concern has one owning section, and other sections refer to that owner by number. "Quietstat" is a working name controlled by a single setting (Section 16.4). Start with Section 1, then follow the reading order in Section 20.

Table of Contents #

1. Before You Start
2. Product Overview & Vision
3. Technology Stack & Architecture
4. Conventions & Cross-Cutting Standards
5. Postgres Data Model
6. ClickHouse Event Store
7. Tracking Script & Integrations
8. Ingestion Pipeline & Privacy Engine
9. Stats Dashboard & Reports
10. Goals, Custom Properties, Funnels, User Journeys & Revenue
11. Accounts, Authentication, Teams, Roles & Permissions
12. Sharing, Embeds, Email Reports & Notifications
13. Public APIs: Stats API, Events API, Sites API, API Keys & Rate Limits
14. Data Import & Export
15. Lifetime Deal Licensing, Payments, Usage Caps & Operator Console
16. Editions, Deployment, Configuration & Operations
17. Security, Privacy & Compliance
18. Testing & Quality Strategy
19. Milestones & Execution Plan
20. Executor Instructions

1. Before You Start #

This document is the complete specification for building Quietstat, a privacy-first, cookie-free web analytics product. You are expected to build it from this document alone. Every default, limit, validation rule and error is decided here; there is nothing to ask the owner before you begin.

1.1 How to read this document #

1. Section numbers are the addresses. Each concern has exactly one owning section (table below). When two sections appear to disagree, the owning section wins. Cross-references such as "Section 8.3" are binding.
2. Normative words. MUST / MUST NOT mean the build fails acceptance if violated. SHOULD means follow it unless a concrete technical blocker exists, and record the deviation in the pull request. MAY means optional.
3. Never block on a missing value. Section 1.2 lists every owner-supplied value with a default. If a value is unset at runtime, the product uses the default and behaves as described in the "Behavior when unset" column. You never stop to ask.
4. Names are literal. Table names, column names, route paths, environment variable names, error codes, enum values and job names written in code font are exact. Do not rename them.
5. Out-of-scope means do not build. Section 2.5 lists exclusions. Do not add stubs, flags or UI for them.
6. Versions are stated only in Section 3.1. Every other section says "per Section 3".
7. Order of work. Section 19 gives the milestone order and Section 20 gives the rules for executing. Read Sections 1 to 4 fully before writing code, because they define the vocabulary, the repository layout, the response envelopes and the design system that every other section assumes.
8. Examples are normative where they show shapes. A JSON example that appears in a section is the exact wire shape; sample values inside it are illustrative only.

1.1.1 Canonical ownership map #

1.2 Owner customization questions and defaults #

Each row is a decision the product owner may want to change. The Default is used when the owner supplies nothing. Environment variable names are canonical in Section 16.4, which lists types, validation and .env.example values; this table lists names only. "Seed" means the value is inserted by a database migration or seed script and can be changed afterwards in the UI named in the row.

1.3 Decisions log #

These are the major decisions already made. Do not re-open them while building. "Where" names the section that specifies the detail.

2. Product Overview & Vision #

2.1 Problem #

Mainstream web analytics tools set cookies, collect personal data, require consent banners, ship heavy scripts and present hundreds of reports that most site owners never read. Cookie-free alternatives exist, but they are subscription products: owners pay every month for as long as they run a site, and agencies multiply that cost across clients.

Quietstat is a privacy-first web analytics product that:

• measures traffic without cookies, without writing to browser storage (the tracker only reads a site-owner opt-out flag), without fingerprinting and without storing personal data by design, so no consent banner is needed for it. Page paths and referrer paths are stored as sent (query strings removed), so integrators must keep personal data out of URL paths or scrub it with the tracker's transformRequest hook (Section 17.9.2);
• shows everything on one simple dashboard that loads in under a second;
• matches the feature set of the leading cookie-free analytics product (parity table in 2.6);
• is sold on the hosted edition as a one-time lifetime deal in three tiers, with no recurring fees, and is also available as a free, unrestricted, self-hosted edition from the same codebase.

2.2 Target users #

2.2.1 Jobs to be done #

2.3 Editions #

One codebase builds both editions. The edition is chosen at runtime by EDITION (Section 1.2, row 2).

There is no feature gating: every feature is available in every tier and in both editions. Tiers differ only in site count and monthly billable-event cap.

2.4 Core features #

2.5 Out of scope #

The following MUST NOT be built, stubbed or hinted at in the UI:

1. Recurring subscriptions, monthly or annual billing, and free trials.
2. Google Search Console integration.
3. Looker Studio connector.
4. Slack reports (email is the only report channel).
5. Single sign-on of any kind (Google Workspace, Microsoft Entra, Okta, SAML, OIDC).
6. Native mobile SDKs for iOS and Android. Apps send events through the Events API (Section 13).
7. Feature gating between tiers. No code path may branch on the tier except for the site and event limits.
8. Marketing website, blog and documentation-site copy. In-app UI text and the framework setup guides inside the app are in scope.
9. Nonprofit and education discounts.
10. Session replay, heatmaps and A/B testing.
11. Data-residency selection settings. The hosted edition runs in one region (Section 1.2, row 20).
12. Cross-device, cross-day or cross-site visitor identification of any kind.
13. An interactive in-app API playground. Section 13 provides copyable request examples instead.
14. Legal copy: terms of service, privacy policy and data-processing-agreement text. The product only links to URLs supplied by the owner.

2.6 Plausible parity table #

Each capability of the reference product is mapped to the section that specifies it, or marked as excluded.

2.7 Success criteria #

The product is accepted when all of the following measurements are met on the reference environment defined in Section 3.8 (4 vCPU, 16 GB RAM, NVMe storage for the single node running all services).

2.8 Glossary #

3. Technology Stack & Architecture #

3.1 Version table #

This table is the only place in the document that states dependency versions. Every other section says "per Section 3".

These version lines are a known-good floor, not a lockfile. At build time, install the current stable release of each dependency (npm install <pkg>@latest, or your ecosystem's equivalent), confirm the major line still matches, and let the lockfile record the exact resolved versions.

Component licenses:

Each package directory contains its own LICENSE file with the matching text; the repository root contains the AGPL text and a LICENSING.md stating this table.

3.1.1 Why each choice #

3.2 Monorepo layout #

quietstat/
├── package.json                  # root scripts: dev, build, lint, typecheck, test, format
├── pnpm-workspace.yaml           # apps/*, packages/*, tools/*, integrations/gtm (JS tooling only)
├── tsconfig.base.json            # strict base config extended by every package
├── eslint.config.js              # flat config, one for the whole repository
├── .prettierrc.json
├── .editorconfig
├── .env.example                  # every variable from Section 16.4 with defaults
├── Dockerfile                    # multi-stage; produces quietstat/quietstat
├── LICENSE                       # AGPL-3.0-only
├── LICENSING.md                  # component license table
├── CHANGELOG.md
├── README.md                     # install, develop, upgrade
├── apps/
│   ├── server/                   # @quietstat/server (Fastify)
│   │   ├── package.json          # "exports" map exposes modules to the worker
│   │   ├── src/
│   │   │   ├── main.ts           # command dispatcher: web | worker | migrate
│   │   │   ├── app.ts            # builds the Fastify instance
│   │   │   ├── config/           # the only reader of process.env; parses it with the schema in packages/shared/src/config.ts (Section 16.4.1)
│   │   │   ├── plugins/          # request id, error handler, auth, csrf, rate limit, static SPA
│   │   │   ├── routes/
│   │   │   │   ├── app/          # /api/app/v1/*  (dashboard API, one folder per domain)
│   │   │   │   ├── admin/        # /api/app/v1/admin/*  (operator console API)
│   │   │   │   ├── public-v1/    # /api/v1/*  (Sites API)
│   │   │   │   ├── public-v2/    # /api/v2/*  (Stats API)
│   │   │   │   ├── share/        # /api/share/v1/*
│   │   │   │   ├── ingest/       # POST /api/event
│   │   │   │   ├── webhooks/     # /api/webhooks/stripe
│   │   │   │   └── health.ts     # /healthz, /readyz
│   │   │   ├── modules/          # business logic, one folder per domain:
│   │   │   │   │                 # auth, users, teams, sites, goals, funnels, segments,
│   │   │   │   │                 # annotations, sharing, reports, api-keys, imports, exports,
│   │   │   │   │                 # licensing, billing, usage, audit
│   │   │   │   │                 # (licensing/entitlement.ts is the ingest entitlement decision used by Section 8.9)
│   │   │   ├── ingestion/        # pipeline, visitor hash, sessions, enrichment, filters, buffer
│   │   │   ├── db/
│   │   │   │   ├── postgres/     # Drizzle client and repositories (the schema lives in @quietstat/shared/db)
│   │   │   │   ├── clickhouse/   # client, inserts, migration runner
│   │   │   │   └── valkey/       # client and key helpers
│   │   │   ├── security/         # guardedFetch outbound HTTP helper (Section 17.4) and CSRF middleware (Section 17)
│   │   │   ├── mail/             # transport, layout, template functions, plain-text builders
│   │   │   ├── jobs/             # job names, payload schemas, handlers registered by the worker
│   │   │   └── lib/              # errors, ids, cursor, hmac, time helpers used only server-side
│   │   ├── data/                 # bundled DB-IP MMDB only (other reference data lives in packages/shared/data)
│   │   └── test/
│   ├── worker/                   # @quietstat/worker (pg-boss consumers and cron)
│   │   └── src/main.ts           # imports handlers from @quietstat/server/jobs
│   └── web/                      # @quietstat/web (React SPA)
│       ├── index.html
│       ├── vite.config.ts
│       ├── public/               # brand/, fonts/, favicon, robots.txt, assets/theme-init.js (served at /assets/theme-init.js)
│       ├── e2e/                  # Playwright specs for UI flows
│       └── src/
│           ├── main.tsx
│           ├── routes/           # route modules, one per entry in Section 4.13
│           ├── features/         # feature folders: dashboard, goals, funnels, teams, licensing...
│           ├── components/       # design-system components from Section 4.14
│           ├── api/              # typed fetch client, query keys, TanStack Query hooks
│           ├── i18n/             # t() helper and locales/en.json
│           ├── styles/           # tokens.css, base.css
│           └── lib/              # formatting, theme, keyboard shortcuts
├── packages/
│   ├── shared/                   # @quietstat/shared: Zod schemas, types, constants, error catalogue,
│   │   │                         #   formatting helpers, time helpers, currency table, config schema
│   │   ├── src/
│   │   │   ├── config.ts         # env schema (Section 16.4.1)
│   │   │   └── db/schema/        # Drizzle table definitions (Section 5), exported as @quietstat/shared/db
│   │   ├── migrations/
│   │   │   ├── postgres/         # drizzle-kit generated SQL, immutable once merged
│   │   │   └── clickhouse/       # NNNN_name.sql files applied in order (Section 6)
│   │   └── data/                 # spam-referrers.txt, datacenter-ranges.txt, subdivisions.json,
│   │                             #   cities.json, currencies.json (reference data bundled in the image)
│   ├── query/                    # @quietstat/query: query JSON -> ClickHouse SQL (Section 13)
│   │   └── src/
│   └── tracker/                  # @quietstat/tracker: tracker source, build script, npm package
│       ├── src/
│       ├── build/                # esbuild script, check-size.ts and size-budget.json (Section 7.8)
│       └── dist/                 # generated: script bundles served by the server
├── integrations/
│   ├── wordpress/                # PHP plugin (own readme.txt, GPL-2.0-or-later)
│   └── gtm/                      # Tag Manager template (template.tpl, metadata.yaml, Apache-2.0)
├── deploy/
│   ├── docker-compose.yml        # self-hosted stack: web, worker, postgres, clickhouse, valkey, proxy
│   ├── docker-compose.hosted.yml # overrides for the hosted topology
│   ├── caddy/                    # reverse-proxy sample config with automatic TLS
│   └── nginx/                    # alternative proxy sample config
├── tests/
│   ├── e2e/                      # cross-app Playwright scenarios (ingestion to dashboard)
│   └── load/                     # throughput and latency scripts for Section 2.7
├── tools/
│   └── eslint-rules/             # custom lint rules: package boundaries (3.2 rule 2), no process.env outside config,
│                                 #   no literal JSX text (4.11)
└── scripts/                      # dev helpers: seed demo data, generate synthetic traffic

Rules:

1. Package names: @quietstat/server, @quietstat/worker, @quietstat/web, @quietstat/shared, @quietstat/query, @quietstat/tracker. All first-party imports between packages use these names with the workspace protocol; relative imports that cross package boundaries are forbidden.
2. Allowed dependency directions: shared depends on no first-party package (it may depend on drizzle-orm and zod); query depends on shared; tracker depends on nothing first-party (it must stay dependency-free to meet its byte budget; shared constants are copied at build time by a script, not imported at runtime); server depends on shared and query; worker depends on server; web depends on shared. web MUST NOT import from server, worker or query. A custom lint rule in tools/eslint-rules/ enforces this.
3. The worker reuses server modules through the exports map of @quietstat/server (./modules/*, ./jobs, ./db/*, ./mail, ./config, ./lib/*). It never imports route files.
4. Migrations are append-only. Editing a merged migration file is forbidden; fix forward with a new file. Postgres migrations and ClickHouse migrations live in packages/shared/migrations/{postgres,clickhouse}, and the Drizzle table definitions in packages/shared/src/db/schema.
5. apps/server/data/ ships inside the image with the bundled GeoIP file. Newer GeoIP files are downloaded to DATA_DIR/geoip by the geoip-update job, and refreshed datacenter and spam-referrer lists are written to DATA_DIR/lists by lists-refresh (Section 16.2.2). The image copy is the fallback when no downloaded file exists.

3.3 Component diagram #

                              ┌───────────────────────────────────────────────┐
  Visitor browser             │                 Reverse proxy (TLS)           │
  ┌──────────────┐  beacon    │         Caddy or nginx; forwards X-Forwarded  │
  │ q-{id}.js    ├───────────►│                                               │
  └──────────────┘            └───────┬─────────────────────────┬─────────────┘
  Customer server                     │                         │
  ┌──────────────┐  Events API        ▼                         ▼
  │ HTTP client  ├───────────► ┌──────────────────────────────────────────┐
  └──────────────┘             │        web process (stateless, N copies) │
  Dashboard user               │  ┌────────────┐  ┌────────────────────┐  │
  ┌──────────────┐  SPA + API  │  │ Routes:    │  │ Ingestion pipeline │  │
  │ React SPA    ├────────────►│  │ app, admin │  │ validate → filter  │  │
  └──────────────┘             │  │ v1, v2,    │  │ → enrich → hash →  │  │
  API client (Bearer key)      │  │ share, hook│  │ session → buffer   │  │
  ┌──────────────┐  /api/v2    │  └─────┬──────┘  └─────────┬──────────┘  │
  │ Script / BI  ├────────────►│        │ query engine      │ batch insert│
  └──────────────┘             └────────┼───────────────────┼─────────────┘
                                        │                   │
          ┌─────────────────────────────┼───────────────────┼──────────────┐
          │                             ▼                   ▼              │
          │   ┌──────────────┐   ┌──────────────┐    ┌──────────────┐      │
          │   │ PostgreSQL   │   │   Valkey     │    │ ClickHouse   │      │
          │   │ accounts,    │   │ sessions,    │    │ events,      │      │
          │   │ settings,    │   │ rate limits, │    │ sessions,    │      │
          │   │ licenses,    │   │ realtime,    │    │ imported_*   │      │
          │   │ pg-boss jobs │   │ locks, cache │    └──────▲───────┘      │
          │   └──────▲───────┘   └──────▲───────┘           │              │
          │          │                  │                   │              │
          │   ┌──────┴──────────────────┴───────────────────┴──────────┐   │
          │   │            worker process (1..N, pg-boss)              │   │
          │   │ email reports, spike checks, salt rotation, usage      │   │
          │   │ rollups, imports, exports, purges, GeoIP refresh       │   │
          │   └───────┬─────────────────────────────┬──────────────────┘   │
          └───────────┼─────────────────────────────┼──────────────────────┘
                      ▼                             ▼
                SMTP server                  Google Data API (GA4 import)

  web also talks to:  Stripe API + Stripe webhooks (hosted edition only)

3.4 Process types #

One Docker image, quietstat/quietstat, selects its role from its command. The same image is used by both editions.

Startup order in Compose: Postgres, ClickHouse and Valkey healthy; migrate completes; then web and worker. Readiness (/readyz) is false until all three stores answer.

3.5 Request flows #

3.5.1 Ingestion (tracker or Events API to ClickHouse) #

1. The browser sends POST /api/event with Content-Type: text/plain (no CORS preflight) or the Events API caller sends JSON. CORS allows any origin.
2. The web process applies the event_ingest rate-limit policy of Section 17.6 before reading the body (429 above it), then reads the body with a 32 KB limit (413 above it) and parses JSON; malformed payloads return 400 with a plain-text reason.
3. Zod validates the payload (Section 8). The domain in d is normalized (lowercased, any port and one leading www. removed) and resolved through the in-process site cache (Section 3.6). Unknown domains, and sites of teams pending deletion, are dropped silently.
4. For each resolved site, in the order defined in Section 8.1: hostname allowlist (site_settings.allowed_hostnames, part of site resolution), bot filter, IP exclusions, datacenter filter, page exclusions, spam-referrer filter, then the license and cap check (hosted only). A drop at any step returns 202 and increments the per-site per-day filtered counter in Valkey (Section 8.8.6).
5. Accepted events are enriched: User-Agent parsing, GeoIP lookup, referrer and channel classification, UTM extraction, URL normalization.
6. The visitor user_id is computed with the daily salt; the session is looked up or created in Valkey (30-minute sliding expiry); session_id is assigned.
7. The enriched event is appended to the in-process buffer; realtime and billable counters are incremented in Valkey.
8. The server answers 202 with an empty body. The response does not wait for ClickHouse.
9. The buffer flushes a batch insert to the events table and the corresponding rows to sessions every 2 seconds or at 5,000 rows, whichever comes first. Failed inserts retry with exponential backoff from 1 s up to 30 s while the buffer holds up to 100,000 event rows; beyond that bound new events are dropped with a 202 response (reason buffer_full) and a warning is logged. The batch size, flush interval and buffer bound are the variables INGEST_BATCH_ROWS, INGEST_FLUSH_INTERVAL_MS and INGEST_BUFFER_MAX_ROWS (Section 16.4.6). Section 8.15 specifies the failure modes.

3.5.2 Dashboard query #

1. The SPA calls a dashboard API route under /api/app/v1/sites/{domain}/... with the session cookie (and X-CSRF-Token for mutating calls).
2. The auth plugin resolves the session from Valkey (fallback to Postgres), loads the user and checks the permission for the site (Section 11).
3. The route validates the request with Zod, then builds a query object (metrics, dimensions, filters, date range in the site time zone, comparison range).
4. The result cache is checked (Section 3.6). On hit, the cached payload is returned.
5. On miss, @quietstat/query generates parameterized ClickHouse SQL. The site's UUID predicate is injected by the engine and cannot be omitted. Segments are expanded into filters. Imported data is merged only for dates strictly earlier than the site's first native day (Section 13.4.10).
6. The query runs under the read-only ClickHouse user (Section 6.1) with a 30-second timeout, a result row cap, a memory cap (max_memory_usage) and a rows-to-read guard that raises 422 query_too_large (the guards are constants in packages/shared, Section 13.4.11), returns rows, and the route shapes the response in the standard envelope (Section 4.4).
7. The result is stored in the cache and returned. The SPA keeps it in TanStack Query for 30 seconds.

The Stats API (POST /api/v2/query) uses steps 3 to 7 with an API key instead of a session (Section 13).

3.5.3 Scheduled email report #

1. The email-reports-dispatch cron job in the worker (schedule in Section 16.6) finds email_report_subscriptions whose next send time is due and enqueues one email-report-send job per (site, frequency, period key) with a singleton key of that tuple, so duplicates cannot occur.
2. The email-report-send job loads the site and recipients, runs the report queries through the shared query engine for the report period in the site time zone, and builds the HTML and plain-text bodies with the template functions (Section 12).
3. Each message is delivered by an email-send job through Nodemailer and SMTP. Success updates the subscription's last-sent time. Retries and back-off follow the email-report-send and email-send rows of Section 16.6; after the last failure the job is marked failed and logged.
4. Without SMTP configured the job completes immediately and logs email_not_sent (Section 1.2, row 6).

3.5.4 Data import #

1. The user uploads a CSV (multipart, size limit in Section 14) or authorizes Google and selects a GA4 property.
2. The server stores the upload in DATA_DIR, inserts an imports row with its initial status (Section 14.6) and enqueues an import-run job (CSV) or a ga4-import-run job (GA4); the API returns 202 with the import resource.
3. The worker streams the file or pages the Google Data API, validates rows with the schemas in Section 14, writes batches into the imported_* ClickHouse tables, and updates progress on the imports row every 5,000 rows.
4. On completion the import is marked completed, the site's cache epoch is incremented so reports refresh, and the owner is notified in the UI. On failure the import is marked failed with an error code and message; rows already written for it are never visible in reports (Section 14.5) and are removed by the import-purge job when the import is deleted or cancelled.

3.5.5 Checkout (hosted edition) #

1. In the SPA the user chooses a tier on the activate page. The checkout endpoint (Section 15) validates the team, the role (owner only) and that the tier is a legitimate purchase or upgrade.
2. The server creates a Stripe Checkout Session in payment mode with inline price_data (amount from license_tiers, or the price difference for an upgrade), metadata {team_id, tier, kind}, and the success and cancel URLs under BASE_URL. It returns the Stripe URL; the SPA redirects.
3. The customer pays on the Stripe-hosted page. Stripe redirects to the success URL (/activate/return) and sends checkout.session.completed to /api/webhooks/stripe.
4. The webhook handler verifies the signature and is idempotent by purchase state transition (Section 5.10; there is no event-ID table): in one Postgres transaction it moves the purchases row from pending to paid and creates or upgrades the team's licenses row. A replayed event finds the purchase already paid and changes nothing. A paid checkout always ends with either an active license or an automatic refund, never neither (Section 15.6).
5. The webhook transaction assigns the sequential invoice number (Section 15) and enqueues the confirmation email as an email-send job; the invoice PDF is rendered on demand when downloaded (Section 15). The SPA, polling GET of the license state on the return page, shows success within seconds. Section 15 specifies failure handling when the webhook is late.

3.6 Caching strategy #

Valkey holds only reproducible or short-lived state. Flushing it MUST NOT lose durable data: sessions fall back to Postgres, counters rebuild from ClickHouse, rate limits restart.

3.7 Performance budgets #

3.8 Scaling model #

Reference sizing, used for the acceptance numbers in Section 2.7:

3.9 Third-party services #

No third-party analytics, fonts, CDN scripts or error-reporting services are loaded by the dashboard. The SPA serves all assets from its own origin.

3.10 Dependency failure behavior #

/readyz returns 503 unless Postgres, ClickHouse and Valkey all respond; /healthz returns 200 as long as the process runs.

4. Conventions & Cross-Cutting Standards #

4.1 Naming #

Boundary conversion: the Fastify layer receives snake_case JSON validated by Zod schemas that already use snake_case keys; repositories map to camelCase TypeScript objects at the module boundary through a single toCamel / toSnake pair in @quietstat/shared. No handler hand-renames individual fields.

Booleans are named as predicates (is_active, has_password); timestamps end in _at; dates (no time) end in _on in JSON and _date is forbidden; durations end in _seconds or _ms; billing amounts end in _minor (Section 4.7); event revenue columns are the exception: revenue_amount and revenue_reporting_amount hold Decimal(18,3) major units (Section 6.3); counts end in _count; foreign keys end in _id.

4.2 Identifiers #

Rules:

1. A client may send its own X-Request-Id request header. The server uses it only if it matches ^[A-Za-z0-9_-]{8,64}$; otherwise it generates one. The value used is always echoed in the response header.
2. UUID route parameters are validated with Zod z.uuid(); a malformed value returns 404 not_found (not 400), to avoid distinguishing "bad format" from "absent".
3. Identifiers in URLs are never reused after deletion because UUIDs are unique; when a site is deleted or its domain is changed, the old domain is reserved for 30 days for the same team (which may add it again); other teams receive 409 domain_taken until the reservation expires (Section 5.5.1).
4. The site domain is case-insensitive on input and stored lowercase, without scheme, port, path or a leading www.; ingestion strips a leading www. and any port from d before lookup, so www.example.com and example.com are the same site. Its character rules and uniqueness are owned by Section 5.5.1 (unique across the whole installation on the normalized domain); local development uses localhost.

4.3 Route families and HTTP semantics #

HTTP semantics for the dashboard, Sites and share families:

Additional rules:

1. PUT is not used anywhere. Updates are PATCH with partial bodies; arrays inside a PATCH body replace the whole array.
2. Request bodies are application/json (UTF-8). Any other content type on a JSON route returns 415 unsupported_media_type. File uploads use multipart/form-data on routes that say so.
3. Unknown JSON keys in request bodies of the dashboard, Sites and Stats APIs are rejected with 400 validation_failed (strict objects). POST /api/event ignores unknown keys (Section 8).
4. A HEAD request is answered for GET routes; OPTIONS is answered only where CORS applies. Other method mismatches return 405 method_not_allowed with an Allow header.
5. Idempotency keys are not supported in v1. Safe retries are provided by uniqueness constraints and, for Stripe, by idempotency through purchase state transitions (Section 15; there is no event-ID table).
6. Every API response carries X-Request-Id, Cache-Control: no-store (except /healthz, static files and the public tracker script) and X-Content-Type-Options: nosniff. Rate-limited routes also send X-RateLimit-Limit, X-RateLimit-Remaining and X-RateLimit-Reset (Section 17.6), and a 429 adds Retry-After in seconds.
7. Maximum request body sizes per route family are defined once in the body-limit table of Section 17.4 (including POST /api/event, the Stats and Sites APIs, the Stripe webhook and the CSV import upload route). A larger body returns 413 payload_too_large (plain text for POST /api/event).
8. Boolean query parameters use the literals true and false; arrays use repeated keys (?status=a&status=b); unknown query parameters on the dashboard and Sites APIs are rejected with 400 validation_failed.
9. The SPA never calls the Sites API or the Stats API. The Sites API and Stats API never accept a cookie session.
10. Redirect targets supplied by clients (return_to) are accepted only when they are relative paths starting with a single / and not //, /\ or containing a scheme; otherwise they are ignored and the default destination is used.

4.4 Response envelopes and errors #

4.4.1 Success envelopes #

Single resource:

{ "data": { "id": "0194...", "name": "Signup", "created_at": "2026-11-03T14:05:09.123Z" } }

List:

{
  "data": [ { "id": "0194...", "name": "Signup" } ],
  "meta": { "next_cursor": "eyJ2IjoxLC4uLn0", "limit": 50 }
}

next_cursor is null on the last page. The meta object of a list MAY contain additional documented keys defined by the owning section; consumers ignore unknown keys.

Exceptions: POST /api/v2/query returns { "results": [...], "meta": {...}, "query": {...} } (Section 13); POST /api/event returns 202 with an empty body; 204 responses have no body.

4.4.2 Error envelope #

Every error from every route family except POST /api/event has this exact shape:

{
  "error": {
    "code": "validation_failed",
    "message": "The request body is invalid.",
    "details": { "fields": [ { "path": "steps.1.name", "message": "Required." } ] },
    "request_id": "req_9f2c0a41d3b84e57a1c6e2b0"
  }
}

Rules:

1. code is one of the codes in 4.4.3. Codes are stable API contract; message is human-readable English and MAY change. Clients branch on code and HTTP status, never on message.
2. message is a complete sentence ending in a period, at most 200 characters, and never contains secrets, SQL, stack traces, file paths or personal data other than what the caller sent.
3. details is null unless the code defines a shape (listed in 4.4.3). It is never omitted.
4. Validation failures put every field issue in details.fields, an array of { "path", "message" }. path uses dot notation with array indexes (goals.0.value); a body-level issue has path equal to an empty string.
5. POST /api/event returns, for 400, 405, 408, 413, 429 and 503, a plain-text one-line reason (Content-Type: text/plain; charset=utf-8, at most 200 characters, no JSON). Reason strings are defined in Section 8.3.
6. 5xx responses never reveal internals: internal_error has the fixed message "Something went wrong on our side." and the request_id is how an operator finds the cause in logs.
7. Unhandled exceptions are caught by the global error handler, logged at error with the stack trace, and returned as internal_error. Thrown AppError instances (class in apps/server/src/lib/errors.ts, constructor (code, details?, messageOverride?)) are mapped using the status in the catalogue.
8. Authentication failures never reveal whether an account exists: login failure is always invalid_credentials.
9. Unknown resources and resources the caller may not know exist are 404 not_found; resources the caller can see but may not act on are 403 forbidden. Site-level and team-level resources of other tenants are always 404.

4.4.3 Error code catalogue #

Statuses used: 400, 401, 403, 404, 405, 408 (POST /api/event only, plain text), 409, 413, 415, 422, 429, 500, 503, 507 (CSV import upload only); 402 is never used. This table is the closed list of error codes: domain sections MUST use these codes and MUST NOT invent others. If an owning section needs a condition not listed, it uses the closest generic code and puts the discriminator in details.reason. A build check fails if any AppError code is not a key of ERROR_CATALOG.

Every row-count limit of Section 5.16 other than the site limit (which uses site_limit_reached) returns limit_reached with details.limit_name set to the limit's name in Section 5.16, for example goals, funnels, custom_properties, site_segments, personal_segments, annotations, pinned_sites, shared_links, api_keys, report_subscriptions, spike_recipients, teams_owned, pending_invitations, site_guests. Conditions that name a missing object (goal, funnel, segment, annotation, import, export, member) are always 404 not_found (rule 9 of 4.4.2); a funnel step that points at a goal of another site is 400 validation_failed on steps.N.goal_id.

Values of the error_code field of imports and exports resources (for example export_too_large, ga4_permission_denied, import_validation_failed) are owned by Section 14; they describe a failed asynchronous job, are not API error codes and never appear in the error envelope.

Standard messages (used when no override is given):

The full catalogue, with messages, lives in packages/shared/src/errors.ts as a typed map ERROR_CATALOG: Record<ErrorCode, { status, message }>; the SPA imports the same map to show friendly text.

4.5 Pagination #

Management lists (dashboard API, Sites API, share API):

1. The response is the list envelope (4.4.1) with meta.next_cursor and meta.limit (the limit actually applied).
2. Keyset pagination: every sort is completed with id as a tiebreaker so ordering is total. The cursor encodes { "v": 1, "s": "<sort field>", "o": "<order>", "k": [<last sort value>, "<last id>"] }, JSON-encoded, then base64url, followed by . and the first 16 characters of the base64url HMAC-SHA256 of the encoded part keyed with a key derived from SECRET_KEY_BASE (Section 17.7). A cursor whose signature fails, whose s or o differs from the request, or that cannot be decoded returns 400 invalid_cursor.
3. No total count is returned. Routes that need one document a separate count field in meta under their own name.
4. Lists never exceed 200 items per page; endpoints with naturally small collections (members of a team, goals of a site) still paginate using the same envelope.
5. Stats queries do not use cursors: the Stats API takes pagination: { limit, offset } in the body with default and maximum limit 10,000 (Section 13). Report previews on the dashboard request 9 rows; "Details" views load 100 rows per "Load more" click through the same limit/offset pair (Section 9.6).

4.6 Time zones and dates #

Date and time labels in the UI use en-US formats: day buckets Nov 3, month buckets Nov 2026, hour buckets 2 PM, full date-time Nov 3, 2026, 2:05 PM, using a 12-hour clock.

4.7 Money, currency and number formatting #

4.7.1 Money #

Formatting (formatCurrency in packages/shared/src/format.ts):

4.7.2 Number formatting #

Helpers formatNumber, formatCompact, formatPercent, formatDuration, formatCurrency, formatDelta and formatBytes are the only formatting code used by the SPA, the email templates and the CSV export labels (CSV export itself writes raw numbers, Section 14).

4.8 Validation #

1. Zod schemas live in packages/shared/src/schemas/{domain}.ts, named {Thing}Schema, with inferred types exported as {Thing}. A schema used by a route is imported by both the Fastify route (for validation) and the SPA (for forms and typed clients). Fastify routes call schema.parse through a shared validate(schema, data) helper that converts a ZodError into validation_failed (4.4.2).
2. Object schemas are strict (z.strictObject) except the ingestion payload, which is lenient.
3. String normalization happens in the schema: trim(), Unicode NFC normalization, and rejection of control characters (U+0000 to U+001F and U+007F) except line breaks in fields documented as multi-line. Name and ShortText additionally reject bidirectional control characters (U+202A to U+202E and U+2066 to U+2069) and zero-width characters (U+200B to U+200F).
4. Standard primitives, defined once in packages/shared/src/schemas/primitives.ts and reused:

1. Any field limit that an owning section does not specify defaults to Name for names, ShortText for short free text and LongText for descriptions.
2. Server-side checks that need the database (uniqueness, existence, limits) throw AppError codes from 4.4.3; they are not expressed as Zod issues.
3. The same schema validates a form on blur and on submit in the SPA; the server revalidates every request regardless of client validation.
4. Passwords are validated by the rules in Section 11. Passwords are never trimmed, normalized or logged.

4.9 Logging #

1. All logging uses pino JSON on stdout, one object per line. The server logger is the Fastify logger; modules use child loggers (logger.child({ module: 'billing' })). Fastify is created with disableRequestLogging: true, and the req serializer is replaced by one that emits only method, route (the pattern) and req_id. The default request and response log lines, which would carry the raw URL (share and reset tokens travel in paths), the remote address and the host, therefore never exist. One access line per request is written explicitly by the server's own hook with the fields of rule 2.
2. Standard fields on every line: level (string label), time (ISO 8601 UTC), msg, module, and when in a request context req_id, method, route (the route pattern, not the raw URL), status, duration_ms, plus account_user_id, team_id and site_id (UUIDs) when known. The field is named account_user_id so it can never be confused with the visitor hash user_id; the visitor hash and session_id are never logged.
3. Levels: fatal the process is exiting; error an operation failed unexpectedly (always includes err with stack); warn degraded or rejected (rate limits, dependency retries, config fallbacks); info lifecycle events and one access line per dashboard or API request; debug development detail; trace never enabled in production. Default level is set by LOG_LEVEL.
4. POST /api/event is not access-logged at info. It logs only errors and, at debug, a 1-in-1,000 sample of accepted requests with no payload fields.
5. Redaction (pino redact, applied to every logger) covers these header paths: req.headers.authorization, req.headers.cookie, req.headers['x-csrf-token'], req.headers['x-forwarded-for'], req.headers['x-real-ip'], req.headers['cf-connecting-ip'], req.headers['true-client-ip'], req.headers.forwarded, req.headers['x-quietstat-client-ip'], req.headers['x-quietstat-proxy-key'], req.headers['user-agent'], req.headers.referer, req.headers.origin, res.headers['set-cookie']. For body and context properties pino does not support "any depth", so the list is explicit paths plus wildcard paths one and two levels deep (*.password, *.*.password, and the same for token, secret, api_key, ip, user_agent, email_body and authorization). The redaction list is a constant in apps/server/src/lib/logger.ts. A unit test logs a fixture object containing every forbidden key at the top level, one level and two levels deep and asserts that none of the values survive in the output. Request serializers drop the remote address and the user-agent entirely rather than masking them.
6. Never logged under any level: raw IP addresses, User-Agent strings, event payloads, URLs from tracked sites, referrers, email bodies, password hashes, TOTP secrets, API keys, share tokens, Stripe secrets and card data. The only deliberate exceptions are the one-time setup token on first start (Section 16.3.5) and, for a self-hosted deployment with no SMTP configured, the action_url field of the email_not_sent line (Section 12.9.6) so the owner can complete the flow by hand.
7. Emails are logged as a template name plus the recipient domain only.
8. Business audit records (who did what to which resource) go to the audit_log table (columns in Section 5, event catalogue in Section 11.20), not to the application log. The application log is operational and may be discarded after 30 days; the audit log is retained per AUDIT_RETENTION_MONTHS (Section 16.4; Section 1.2, row 35).
9. Each job run logs job_started and job_finished with job, attempt, duration_ms, and job_failed with the error.
10. Client-side errors are never sent to a third party. The SPA logs to the browser console only.

4.10 Feature flags and edition checks #

1. There is no feature-flag service and no per-user or per-tier flags. Behavior differences come from exactly two sources: the edition (EDITION) and the presence of optional configuration (SMTP, Stripe, Google).
2. The server builds one immutable RuntimeConfig at start. Its public subset is:

{
  "product_name": "Quietstat",
  "edition": "hosted",
  "base_url": "https://app.example.com",
  "registration_mode": "open",
  "features": { "licensing": true, "stripe": true, "google_import": false, "email": true },
  "geoip_attribution": "IP geolocation by DB-IP",
  "terms_url": null,
  "privacy_url": null,
  "dpa_url": null,
  "hosting_region_label": "EU (Germany)"
}

1. The public subset is the base of the runtime bootstrap, which is delivered two ways: inlined into index.html as a non-executable <script type="application/json" id="qs-bootstrap"> element when the server serves the SPA (allowed by the CSP because it is data, not code; the JSON is serialized with <, >, &, U+2028 and U+2029 escaped as \u003c, \u003e, \u0026, \u2028 and \u2029), and through GET /api/app/v1/bootstrap (single envelope) for refresh. Section 11.2 owns the complete response, which MUST contain every key of the subset above unchanged (product_name, edition, base_url, registration_mode, features, geoip_attribution, terms_url, privacy_url, dpa_url, hosting_region_label) plus setup_required, banner, csrf_token, user and the password-policy fields; the response is never cacheable when a session cookie is present. Secrets never appear in it. features.licensing and features.stripe are true only when EDITION=hosted (and, for stripe, when STRIPE_SECRET_KEY is set); features.google_import is true when GOOGLE_CLIENT_ID is set; features.email is true when SMTP_HOST is set.
2. Server code checks the edition with requireEdition('hosted'), a route-level guard. In the self-hosted edition, hosted-only routes (licensing, checkout, codes, usage, /admin, the Stripe webhook) are not registered at all, so they return 404 not_found.
3. The SPA reads the config through a useRuntimeConfig() hook and renders hosted-only navigation and pages only when the matching feature is true. Hosted-only SPA routes (4.13) redirect to / in self-hosted.
4. Feature code MUST NOT inspect the license tier except in the limit checks owned by Section 15 (site count, billable events). No UI or API capability may differ between Solo, Pro and Agency.
5. The tracker and POST /api/event behave identically in both editions except for the cap and license checks (hosted).

4.11 Internationalization #

1. v1 ships English (en-US) only. There is no language selector and no Accept-Language negotiation. The <html lang="en"> attribute is fixed.
2. All user-visible strings in apps/web are externalized in apps/web/src/i18n/locales/en.json (flat object, keys per 4.1) and read through a typed helper t(key, params?) in apps/web/src/i18n/index.ts. Interpolation uses {name} placeholders; plurals use the keys {key}_one and {key}_other chosen by Intl.PluralRules('en-US'); the helper's key type is generated from en.json so a missing key is a compile error.
3. An ESLint rule (react/jsx-no-literals with noStrings: true, allowing only punctuation and symbol-only strings) forbids literal text in JSX.
4. Server-generated text: API error messages come from ERROR_CATALOG (4.4.3); email subject and body strings live in apps/server/src/mail/strings.ts (one exported function per template, Section 12). Neither is inline in handlers.
5. Dates, numbers, currency and lists are formatted through the helpers in 4.6 and 4.7 using en-US, never by manual string concatenation.
6. Technical identifiers shown to users (event names, page paths, property values, goal names, sources) are displayed verbatim and are never translated.
7. Text direction is left-to-right. Layout uses logical CSS properties (margin-inline-start, padding-inline-end) so right-to-left can be added without layout rewrites.

4.12 Code style, linting, formatting and Git #

4.12.1 TypeScript and style #

Root scripts (names are canonical): pnpm dev (server, worker and web in watch mode), pnpm build, pnpm lint, pnpm typecheck, pnpm test (Vitest), pnpm test:e2e (Playwright), pnpm format, pnpm format:check, pnpm db:generate (drizzle-kit), pnpm db:migrate (runs the migrate command locally). CI runs format:check, lint, typecheck, test, build, in that order; a failure blocks merge.

4.12.2 Git and release conventions #

4.13 App shell and navigation map #

4.13.1 Shell #

4.13.2 Guard vocabulary #

A route whose guard fails renders the 403 page ("You do not have access to this page") for signed-in users, redirects unauthenticated users to /login?return_to=<path>, and renders the 404 page for resources the user cannot know exist (4.4.2, rule 9).

4.13.3 Routes #

Rules:

1. Hosted-only routes redirect to / in the self-hosted edition (4.10).
2. A site route whose :domain does not exist or is not visible renders the 404 page. After a first login the app opens /sites; when the user has exactly one site it opens that site's dashboard.
3. Dashboard state lives in the query string (the parameters defined in Section 9.17: period, comparison, f and the report-specific keys) so any view is linkable and bookmarkable. Changing it replaces history entries within 500 ms of the last change and pushes a new entry on explicit period or filter commits.
4. Every route module is lazy-loaded; the route table is declared once in apps/web/src/routes/index.tsx and mirrored by the table above.
5. Page titles follow {Page} · {Site or Team} · {PRODUCT_NAME}; a document title is set on every route change and announced by moving focus to the page's h1.
6. Dashboard keyboard shortcuts (single keys, ignored while typing in a field and when a modifier key is held) are defined only in Section 9.12.3, together with the period tokens they map to. ? opens the shortcut list (4.14.3 KeyboardShortcutsDialog).

4.14 UI design system #

All tokens are CSS custom properties defined in apps/web/src/styles/tokens.css on :root (light) and :root[data-theme="dark"] (dark), surfaced to Tailwind through its theme configuration so utilities such as bg-surface and text-muted exist. Theme choice is system, light or dark, stored in localStorage under qs:theme (default system, which follows prefers-color-scheme) and applied before first paint by /assets/theme-init.js, a small external, non-module, render-blocking <script src> in the <head> of index.html, loaded before the app bundle. There is no inline script anywhere in the app (the CSP is script-src 'self', Section 17.5.2), which is why the theme bootstrap is a static file. Shared, embedded and public dashboards read and write no browser storage: they take the theme from the theme query parameter (light, dark or system) and otherwise follow prefers-color-scheme.

4.14.1 Color tokens #

Chart palette (--chart-1 to --chart-6, categorical, distinguishable under common color-vision deficiencies and also differentiated by line style or marker): light #4F46E5, #0E7490, #B45309, #BE185D, #15803D, #6D28D9; dark #818CF8, #22D3EE, #FBBF24, #F472B6, #4ADE80, #A78BFA. The primary series (visitors) uses --chart-1; the comparison series uses the same hue at 45% opacity with a dashed stroke. Gridlines use --color-border. Funnel bars use --chart-1 for reached and --color-surface-2 for dropped.

Contrast requirement: every text and icon color pairing used in the tokens above meets 4.5:1 (3:1 for large text, graphics and input borders). Section 18 includes an automated check that computes these ratios from tokens.css.

4.14.2 Type, spacing, shape and motion #

4.14.3 Component inventory #

Every component below lives in apps/web/src/components/, is keyboard-operable, supports both themes and has all listed states. No page may define a visually equivalent component locally.

4.15 Accessibility (WCAG 2.2 AA) #

The dashboard, the shared and embedded dashboards, and every auth page conform to WCAG 2.2 level AA. Requirements:

1. Perceivable. Color contrast per 4.14.1 (1.4.3, 1.4.11). Color is never the only means of conveying information: chart series also differ by stroke style and marker, deltas carry arrows and signs, statuses carry text. Text can be resized to 200% and content reflows at 320 CSS px wide without two-dimensional scrolling, except data tables and charts that scroll horizontally inside their container (1.4.4, 1.4.10). Text spacing overrides do not break layout (1.4.12). Images have alternative text; decorative images use empty alt.
2. Charts. Every chart has a visible title, a text summary available to screen readers (such as "Visitors per day, 7 days, total 12,345, highest on Nov 3") and a "View as table" toggle that renders the same data in an accessible table. Hover-only information is also available on focus. Tooltips are dismissible with Escape and persistent while hovered (1.4.13).
3. Operable. Everything is operable with a keyboard (2.1.1) with no traps (2.1.2); single-character shortcuts can be turned off in the shortcuts dialog (2.1.4); focus order follows reading order; the focus indicator is always visible and never hidden by sticky headers (2.4.7, 2.4.11); a skip link and landmarks (header, nav, main, footer) are present on every page; page titles and the focused h1 change on each route (2.4.2, 2.4.3).
4. Pointer targets. Interactive targets are at least 24 x 24 CSS px, with spacing exceptions only for inline text links (2.5.8). Drag interactions (chart range selection, reordering funnel steps) have single-pointer alternatives: date inputs and move-up / move-down buttons (2.5.7).
5. Timing. No time limits except session expiry, which warns 2 minutes before and allows extension by any interaction (2.2.1). Auto-refreshing views (realtime) can be paused, and refresh never moves focus.
6. Understandable. lang="en"; consistent navigation and identification (3.2.3, 3.2.4); consistent placement of help and support link in the footer (3.2.6). Form errors are associated with fields, summarized at the top of the form with links to the first invalid field, and describe how to fix the problem (3.3.1, 3.3.3). Previously entered information is not requested again within a flow (3.3.7). Authentication does not rely on cognitive tests: password managers and pasting into password and TOTP fields are allowed (3.3.8).
7. Robust. Native elements first; ARIA only where needed and per the WAI-ARIA Authoring Practices; status messages use live regions (4.1.3); toasts and banners as in 4.14.3. Valid, unique ids.
8. Motion and theming. prefers-reduced-motion and prefers-color-scheme are honored; forced-colors mode keeps focus rings and borders visible using system colors.
9. Verification. Playwright specs run axe-core on every route in 4.13.3 in both themes at 360 px and 1280 px widths; the build fails on any serious or critical violation. A manual checklist (keyboard-only pass and one screen reader pass per release) is part of Section 18.

4.16 Responsive behavior #

Touch: targets stay at least 40 px high for primary actions at every breakpoint. Hover-only affordances have tap or focus equivalents. No feature is desktop-only; the embed layout is fluid down to 320 px.

4.17 Loading, empty and error state patterns #

4.17.1 Loading #

1. Data regions show a Skeleton placeholder matching the final layout (tile, chart area, table rows) after a 150 ms delay (nothing is shown before that), and keep it for at least 300 ms to avoid flicker.
2. When refetching with existing data (period or filter change), the old data stays visible at 60% opacity with aria-busy="true" on the region until the new data arrives.
3. Buttons show a loading state during mutations and are disabled; forms cannot be submitted twice.
4. Routes show a full-page Spinner only while the route chunk or the session is loading, and only after 300 ms.
5. TanStack Query defaults: staleTime 30 s, gcTime 5 min, refetchOnWindowFocus true for dashboard queries and false for settings forms, retry 2 times with 1 s then 2 s delays for GET requests on network errors and 5xx responses, no retry on 4xx, no retry for mutations. Realtime queries use refetchInterval 10 s and the current-visitors pill 30 s; both pause when the tab is hidden.
6. Mutations are not optimistic, except toggles (switches) that revert with an error toast on failure.

4.17.2 Empty states #

Every list and report has an EmptyState with an icon, one-sentence explanation and, where it exists, one primary action.

4.17.3 Errors #

5. Postgres Data Model #

PostgreSQL holds everything that is not an analytics event: accounts, teams, sites and their settings, goals and funnels, sharing, API keys, imports/exports, privacy salts, licensing, usage counters and the audit log. Analytics events live in ClickHouse (Section 6). This section owns every Postgres table, column, constraint, index and retention rule. Other sections reference tables by name and must not redefine them.

5.1 Principles and Conventions #

5.2 Shared Database Objects (migration 0000) #

CREATE EXTENSION IF NOT EXISTS btree_gist;   -- used by the imports exclusion constraint (5.9)

CREATE FUNCTION app_set_updated_at() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN NEW.updated_at := now(); RETURN NEW; END $$;

-- Number of keys in a JSON object; -1 when the value is not an object (so a CHECK can reject it).
CREATE FUNCTION app_jsonb_key_count(j jsonb) RETURNS integer LANGUAGE sql IMMUTABLE PARALLEL SAFE AS $$
  SELECT CASE WHEN jsonb_typeof(j) = 'object' THEN (SELECT count(*)::integer FROM jsonb_object_keys(j)) ELSE -1 END
$$;

CREATE FUNCTION app_forbid_update() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN RAISE EXCEPTION '% is append-only', TG_TABLE_NAME USING ERRCODE = '42501'; END $$;

The updated_at triggers are attached in migration 0008 to the 20 tables that have an updated_at column: users, teams, team_memberships, sites, site_settings, goals, funnels, segments, annotations, shared_links, email_report_subscriptions, spike_notifications, spike_notification_recipients, imports, license_tiers, licenses, purchases, usage_monthly, usage_site_monthly, exchange_rates.

Migration 0003 additionally creates the function app_sites_forbid_script_id_change and its trigger on sites (5.5.1). Trigger name: trg_<table>_updated_at, BEFORE UPDATE ... FOR EACH ROW EXECUTE FUNCTION app_set_updated_at().

5.3 Identity: users, user_recovery_codes, auth_sessions, email_tokens #

Authentication flows, password policy, lockout thresholds and token lifetimes are owned by Section 11; this section only fixes storage.

5.3.1 users #

Purpose: one row per human account. Soft-deleted via deleted_at, then anonymized in place by user-anonymize 30 days later (the row is never hard-deleted, so financial and audit references stay valid).

CREATE TABLE users (
  id                    uuid PRIMARY KEY DEFAULT uuidv7(),
  email                 text NOT NULL,                  -- lowercase, trimmed, 3..254 chars
  email_verified_at     timestamptz,                    -- NULL until the verify_email token is consumed
  password_hash         text NOT NULL,                  -- argon2id PHC string; the literal '!' for anonymized users (matches nothing)
  name                  text NOT NULL,                  -- 1..100 chars
  locale                text NOT NULL DEFAULT 'en',     -- UI language tag; supported values per Section 4
  theme_preference      text NOT NULL DEFAULT 'system', -- light | dark | system; source of truth for the UI theme (Section 11)
  preferences           jsonb NOT NULL DEFAULT '{}'::jsonb, -- per-user UI preferences; key pinned_sites = array of site UUIDs pinned to the top of the site list (max 20, Section 9.19)
  last_team_id          uuid,                           -- last selected team (Section 11.14.2); FK added in migration 0002 once teams exists; NULL when the team is deleted
  timezone              text,                           -- IANA display preference; NULL = use the browser's
  totp_secret_encrypted text,                           -- AES-256-GCM text format of Section 17.7.2 (v1.<kid>.<base64url(nonce || ciphertext || tag)>, purpose totp-encryption, associated data "users.totp_secret_encrypted:<id>"); a not-yet-confirmed secret is held in Valkey, never here
  totp_enabled_at       timestamptz,
  totp_last_used_step   bigint,                         -- last accepted 30-second TOTP step; rejects replay
  last_login_at         timestamptz,
  suspended_at          timestamptz,                    -- set by the operator console (Section 15.17.3); login is refused with 403 account_suspended
  deleted_at            timestamptz,                    -- soft-delete request time
  anonymized_at         timestamptz,                    -- set by user-anonymize
  created_at            timestamptz NOT NULL DEFAULT now(),
  updated_at            timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT users_email_check  CHECK (email = lower(btrim(email)) AND char_length(email) BETWEEN 3 AND 254 AND email ~ '^[^@[:space:]]+@[^@[:space:]]+$'),
  CONSTRAINT users_name_check   CHECK (char_length(name) BETWEEN 1 AND 100),
  CONSTRAINT users_locale_check CHECK (locale ~ '^[a-z]{2,3}(-[A-Za-z0-9]{2,8})*$'),
  CONSTRAINT users_theme_check  CHECK (theme_preference IN ('system','light','dark')),
  CONSTRAINT users_preferences_check CHECK (jsonb_typeof(preferences) = 'object' AND pg_column_size(preferences) <= 8192
    AND (NOT preferences ? 'pinned_sites' OR (jsonb_typeof(preferences->'pinned_sites') = 'array' AND jsonb_array_length(preferences->'pinned_sites') <= 20))),
  CONSTRAINT users_totp_check   CHECK ((totp_secret_encrypted IS NULL) = (totp_enabled_at IS NULL)),
  CONSTRAINT users_anonymized_check CHECK (anonymized_at IS NULL OR deleted_at IS NOT NULL)
);
CREATE UNIQUE INDEX users_email_key ON users (email);
CREATE INDEX users_pending_anonymization_idx ON users (deleted_at) WHERE deleted_at IS NOT NULL AND anonymized_at IS NULL;

Notes:

• Email uniqueness covers soft-deleted users until anonymization (the address stays reserved for 30 days, which also blocks a re-signup that would confuse an undo).
• No is_super_admin column. Super-admin status is computed per request and requires all of: email_verified_at IS NOT NULL set by consuming a verify_email token (never by the no-SMTP shortcut), totp_enabled_at IS NOT NULL (otherwise operator routes return 403 admin_2fa_required), and email in the case-insensitive set parsed from env SUPER_ADMIN_EMAILS (hosted edition only; ignored when EDITION=selfhosted). See 5.12.
• preferences.pinned_sites holds the user's pinned sites as site UUIDs (at most 20, 5.16). Routes: POST /api/app/v1/me/pinned-sites with { "domain" } pins a site (idempotent, 200 also when already pinned; the user must currently have access to it) and DELETE /api/app/v1/me/pinned-sites/{domain} unpins it (204). There is no foreign key: the service removes an id when the user loses access to the site or the site is deleted, and ignores unknown ids when reading. Exceeding 20 returns 409 limit_reached with limit_name: pinned_sites.
• Login lockout state is not stored in Postgres: failed-attempt counters and locks live in Valkey with a 15-minute lifetime (Section 11.8).
• suspended_at is written only by the operator console; a suspended user cannot log in and has their sessions deleted (Section 15.17.3).
• While deleted_at IS NOT NULL AND anonymized_at IS NULL, a login yields only the restore-only session of Section 11.13.3; existing sessions were deleted at the request.
• Anonymization sets email = 'deleted-' || id || '@deleted.invalid', name = 'Deleted user', password_hash = '!', clears TOTP columns, timezone, preferences, locale→'en', and deletes the user's auth_sessions, user_recovery_codes, email_tokens, team_memberships, site_guests, personal segments, personal annotations, email_report_subscriptions and spike_notification_recipients rows with this user_id (the api_keys they created were already deleted when the deletion was requested, Section 11.16.5), and sets licenses.owner_email_snapshot to NULL for licenses of teams they owned. Rows that merely record authorship (created_by_user_id, audit_log.actor_user_id) keep pointing at the anonymized row.

5.3.2 user_recovery_codes #

Purpose: single-use 2FA recovery codes (count, format and regeneration rules per Section 11). Regenerating deletes all existing rows for the user first.

CREATE TABLE user_recovery_codes (
  id         uuid PRIMARY KEY DEFAULT uuidv7(),
  user_id    uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  code_hash  text NOT NULL,                  -- lowercase hex SHA-256 of the normalized code (uppercase, hyphens removed)
  used_at    timestamptz,
  created_at timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT user_recovery_codes_hash_check CHECK (code_hash ~ '^[0-9a-f]{64}$')
);
CREATE UNIQUE INDEX user_recovery_codes_user_hash_key ON user_recovery_codes (user_id, code_hash);

5.3.3 auth_sessions #

Purpose: dashboard login sessions. Postgres is the source of truth; Valkey caches resolved sessions (Section 3). Logout and "sign out other devices" delete rows (no revoked_at). The CSRF token is not stored: it is derived as an HMAC of the session id keyed with the csrf key derived from SECRET_KEY_BASE (Section 17.7), so there is no CSRF column. Impersonation sessions (impersonator_user_id set) are hidden from the target user's session list, are excluded from "sign out other devices" and from the per-user session cap, and are re-validated against the operator's current super-admin status on every request (Sections 11.6 and 15.17).

CREATE TABLE auth_sessions (
  id                     uuid PRIMARY KEY DEFAULT uuidv7(),
  user_id                uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  token_hash             bytea NOT NULL,        -- SHA-256 of the 32-byte random cookie token
  previous_token_hash    bytea,                 -- hash of the cookie token before the last rotation; accepted until previous_valid_until so concurrent requests survive a rotation (Section 11.6.3)
  previous_valid_until   timestamptz,
  rotated_at             timestamptz,           -- time of the last token rotation
  device_label           text,                  -- "Chrome 141 on macOS 15", derived at login, <= 200 chars; the raw UA and the IP are not stored
  country                char(2),               -- ISO 3166-1 alpha-2 resolved from the IP at login for the session list; the IP itself is never stored
  two_factor_verified_at timestamptz,           -- NULL when the user has no 2FA or this session has not passed it yet
  reauth_at              timestamptz,           -- last successful step-up re-authentication (password, plus TOTP code when 2FA is on); sensitive actions require it within the window of Section 11 / 15.17.2
  impersonator_user_id   uuid REFERENCES users(id) ON DELETE SET NULL,  -- operator console read-only impersonation (Section 15)
  created_at             timestamptz NOT NULL DEFAULT now(),
  last_seen_at           timestamptz NOT NULL DEFAULT now(),  -- written at most once per 5 minutes per session
  expires_at             timestamptz NOT NULL,  -- sliding expiry; lifetime per Section 11
  absolute_expires_at    timestamptz NOT NULL,  -- hard cap; lifetime per Section 11
  CONSTRAINT auth_sessions_token_check  CHECK (octet_length(token_hash) = 32),
  CONSTRAINT auth_sessions_expiry_check CHECK (expires_at <= absolute_expires_at),
  CONSTRAINT auth_sessions_label_check  CHECK (device_label IS NULL OR char_length(device_label) <= 200),
  CONSTRAINT auth_sessions_previous_check CHECK ((previous_token_hash IS NULL) = (previous_valid_until IS NULL) AND (previous_token_hash IS NULL OR octet_length(previous_token_hash) = 32)),
  CONSTRAINT auth_sessions_country_check CHECK (country IS NULL OR country ~ '^[A-Z]{2}$')
);
CREATE UNIQUE INDEX auth_sessions_token_hash_key ON auth_sessions (token_hash);
CREATE UNIQUE INDEX auth_sessions_previous_token_hash_key ON auth_sessions (previous_token_hash) WHERE previous_token_hash IS NOT NULL;
CREATE INDEX auth_sessions_user_idx ON auth_sessions (user_id);
CREATE INDEX auth_sessions_expires_idx ON auth_sessions (expires_at);

5.3.4 email_tokens #

Purpose: single-use tokens for email verification, password reset and email change. Only the hash is stored; the raw token travels in the emailed link.

CREATE TABLE email_tokens (
  id          uuid PRIMARY KEY DEFAULT uuidv7(),
  user_id     uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  kind        text NOT NULL,
  token_hash  bytea NOT NULL,                 -- HMAC-SHA-256 (key purpose email-token-hmac, Section 17.7) of 32 random bytes
  new_email   text,                           -- only for change_email; lowercase
  expires_at  timestamptz NOT NULL,           -- lifetime per Section 11
  used_at     timestamptz,
  created_at  timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT email_tokens_kind_check CHECK (kind IN ('verify_email','password_reset','change_email')),
  CONSTRAINT email_tokens_hash_check    CHECK (octet_length(token_hash) = 32),
  CONSTRAINT email_tokens_new_email_check CHECK ((kind = 'change_email') = (new_email IS NOT NULL) AND (new_email IS NULL OR new_email = lower(btrim(new_email))))
);
CREATE UNIQUE INDEX email_tokens_hash_key ON email_tokens (token_hash);
CREATE INDEX email_tokens_user_kind_idx ON email_tokens (user_id, kind, created_at DESC);

Issuing a new token of the same kind for a user deletes that user's older unused tokens of that kind in the same transaction, so at most one live token per (user, kind) exists.

5.4 Teams: teams, team_memberships #

5.4.1 teams #

Purpose: the tenant. Every site belongs to exactly one team; every license belongs to a team. Signup creates one personal team (is_personal = true). Deletion is scheduled with a 7-day undo window (Section 11) and executed by team-purge.

CREATE TABLE teams (
  id                  uuid PRIMARY KEY DEFAULT uuidv7(),
  name                text NOT NULL,                   -- 1..100 chars
  is_personal         boolean NOT NULL DEFAULT false,
  created_by_user_id  uuid REFERENCES users(id) ON DELETE SET NULL,
  require_2fa         boolean NOT NULL DEFAULT false,  -- owner-enforced 2FA for all members (Section 11)
  stripe_customer_id  text,                            -- hosted only; created lazily at first checkout
  deleted_at          timestamptz,                     -- deletion requested at (start of the 7-day undo window)
  purge_after         timestamptz,                     -- deleted_at + 7 days; team-purge hard-deletes the team after this instant
  suspended_at        timestamptz,                     -- set by the operator console (Section 15.17.4); suspended teams get ingestion drops and read-only access
  suspended_reason    text,                            -- 1..500 chars, required together with suspended_at
  created_at          timestamptz NOT NULL DEFAULT now(),
  updated_at          timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT teams_name_check     CHECK (char_length(name) BETWEEN 1 AND 100),
  CONSTRAINT teams_deletion_check CHECK ((deleted_at IS NULL) = (purge_after IS NULL)),
  CONSTRAINT teams_suspension_check CHECK ((suspended_at IS NULL) = (suspended_reason IS NULL) AND (suspended_reason IS NULL OR char_length(suspended_reason) BETWEEN 1 AND 500))
);
CREATE UNIQUE INDEX teams_personal_per_user_key ON teams (created_by_user_id) WHERE is_personal;
CREATE UNIQUE INDEX teams_stripe_customer_key   ON teams (stripe_customer_id) WHERE stripe_customer_id IS NOT NULL;
CREATE INDEX teams_pending_deletion_idx         ON teams (purge_after) WHERE purge_after IS NOT NULL;

While purge_after IS NOT NULL the team is "pending deletion": members get 409 team_pending_deletion on mutations, API keys of the team are rejected as in Section 11.14.5, and ingestion treats every site of the team as an unknown domain and drops events (still 202, Section 8; there is no site "paused" state). Cancelling the undo clears both columns. The team owner is not a column: it is the single team_memberships row with role = 'owner' (5.4.2), which keeps the deferred one-owner trigger the only source of truth; Section 11 derives the owner from it.

5.4.2 team_memberships #

Purpose: user ↔ team with a role (permission matrix in Section 11).

CREATE TABLE team_memberships (
  id                 uuid PRIMARY KEY DEFAULT uuidv7(),
  team_id            uuid NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
  user_id            uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  role               text NOT NULL,
  invited_by_user_id uuid REFERENCES users(id) ON DELETE SET NULL,
  created_at         timestamptz NOT NULL DEFAULT now(),
  updated_at         timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT team_memberships_role_check CHECK (role IN ('owner','admin','editor','viewer'))
);
CREATE UNIQUE INDEX team_memberships_team_user_key ON team_memberships (team_id, user_id);
CREATE UNIQUE INDEX team_memberships_one_owner_key ON team_memberships (team_id) WHERE role = 'owner';
CREATE INDEX team_memberships_user_idx ON team_memberships (user_id);

-- "Exactly one owner" is verified at COMMIT so that ownership transfer (demote old owner, promote new owner) works inside one transaction.
CREATE FUNCTION app_assert_team_has_owner() RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE tid uuid := COALESCE(NEW.team_id, OLD.team_id);
BEGIN
  IF EXISTS (SELECT 1 FROM teams WHERE id = tid)
     AND (SELECT count(*) FROM team_memberships WHERE team_id = tid AND role = 'owner') <> 1 THEN
    RAISE EXCEPTION 'team % must have exactly one owner', tid USING ERRCODE = '23514';
  END IF;
  RETURN NULL;
END $$;
CREATE CONSTRAINT TRIGGER team_memberships_owner_check
  AFTER INSERT OR UPDATE OR DELETE ON team_memberships
  DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION app_assert_team_has_owner();

The team and its owner membership are always inserted in one transaction. The service maps SQLSTATE 23514 from this trigger to 409 owner_required.

5.5 Sites and Access: sites, site_settings, site_guests, invitations, site_domain_reservations #

Decision on site settings: identity and lifecycle live on sites (including timezone, because every report, the realtime view and all date bucketing need it); every tunable toggle lives in the 1:1 table site_settings. "Site settings" elsewhere in this document means the union of the two rows. They are always created together in one transaction, and the ingestion cache (Section 8) loads sites ⨝ site_settings plus the site's exclusions in a single refresh. Sites are addressed by domain in the SPA and the dashboard API (/sites/:domain/..., /api/app/v1/sites/{domain}/..., Section 4); id is internal.

5.5.1 sites #

CREATE TABLE sites (
  id                 uuid PRIMARY KEY DEFAULT uuidv7(),     -- also the ClickHouse site_id
  team_id            uuid NOT NULL REFERENCES teams(id) ON DELETE RESTRICT,
  domain             text NOT NULL,                         -- public site identifier in all APIs; normalized (see notes)
  script_id          text NOT NULL,                         -- 12-char lowercase Crockford base32 id used in the per-site script URL (Section 7.2.1); generated at insert, immutable
  proxy_key          text,                                  -- 32-char lowercase Crockford base32 (160 bits); NULL = no first-party proxy configured (Section 7.15.4)
  timezone           text NOT NULL DEFAULT 'UTC',           -- IANA name, validated against the runtime tz database
  first_event_at     timestamptz,                           -- set once by ingestion on the first accepted event (drives the "waiting for first pageview" state)
  created_by_user_id uuid REFERENCES users(id) ON DELETE SET NULL,
  created_at         timestamptz NOT NULL DEFAULT now(),
  updated_at         timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT sites_domain_check   CHECK (domain = lower(domain) AND domain !~ '^www\.' AND char_length(domain) BETWEEN 1 AND 253
                                         AND domain ~ '^([a-z0-9]([a-z0-9-]{0,61}[a-z0-9])?\.)*[a-z0-9]([a-z0-9-]{0,61}[a-z0-9])?$'),
  CONSTRAINT sites_timezone_check CHECK (timezone ~ '^[A-Za-z][A-Za-z0-9_+/-]{0,63}$'),
  CONSTRAINT sites_script_id_check CHECK (script_id ~ '^[0-9a-hjkmnp-tv-z]{12}$'),
  CONSTRAINT sites_proxy_key_check CHECK (proxy_key IS NULL OR proxy_key ~ '^[0-9a-hjkmnp-tv-z]{32}$')
);
CREATE UNIQUE INDEX sites_domain_key ON sites (domain);
CREATE UNIQUE INDEX sites_script_id_key ON sites (script_id);
CREATE INDEX sites_team_idx ON sites (team_id, created_at);

CREATE FUNCTION app_sites_forbid_script_id_change() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  IF NEW.script_id IS DISTINCT FROM OLD.script_id THEN
    RAISE EXCEPTION 'sites.script_id is immutable' USING ERRCODE = '42501';
  END IF;
  RETURN NEW;
END $$;
CREATE TRIGGER trg_sites_script_id_immutable BEFORE UPDATE ON sites FOR EACH ROW EXECUTE FUNCTION app_sites_forbid_script_id_change();

Notes:

• Domain normalization (single rule for all sections). domain is stored lowercase, in punycode for internationalized names, without scheme, port, path, trailing dot or a leading www.. The service strips one leading www. on create and update, and ingestion strips it (and any port) from the tracker-supplied domain before the lookup (Section 8.6), so www.example.com and example.com are the same site; other subdomains are different sites. Uniqueness is installation-wide on the normalized value, across all teams. There is no port: local development uses localhost. Validation messages and the domain_taken 409 are in Section 4.4.3 and Section 9 (UI).
• timezone default is UTC; the create-site UI pre-fills the browser's IANA zone (Section 9). A value is valid only when it is a member of the runtime's supported IANA zone list (Intl.supportedValuesOf('timeZone')) or UTC, canonicalized to the zone's primary name; UTC-offset strings such as +05:00 and unknown aliases are rejected, because ClickHouse rejects them in toStartOfDay(ts, tz) (the TimeZone primitive of Section 4.8 states the same rule). Changing the timezone re-buckets reports immediately; no stored data changes.
• script_id is generated from a CSPRNG at insert and never changes (a trigger rejects updates), so the tracker snippet stays valid across domain changes.
• proxy_key authorizes first-party proxies to pass the visitor's address in X-Quietstat-Client-IP (Section 8.7). Only a key that matches this value, compared in constant time, makes the header trusted; a request with an invalid key has the header ignored and is rate-limited as a normal client, so no elevated limit is ever granted without a valid key. The key is stored in plaintext by design (documented exception, 5.1: the settings page must be able to show it again), is never returned to roles below site-settings write, and is never logged. Generation and rotation: Section 7.15.4.
• Changing domain is allowed for owners and admins (action site.domain.change, Section 11.19.2); events and history stay attached because ClickHouse is keyed by the UUID. The old domain stays reserved to the same team for 30 days (5.5.5); the tracker snippet must be updated by the user, and events still sent for the old domain are dropped as an unknown domain.
• Domain release rule. When a site is deleted or its domain is changed, the old domain is released to other teams exactly 30 days later (reserved_until, 5.5.5); until then only the same team can claim it again. The ClickHouse purge job finishing earlier does not shorten the reservation. A deleted site's domain is therefore never claimable by another team within 30 days, and events for it are dropped as an unknown domain throughout.
• team_id is RESTRICT on purpose: deleting a team never cascades into sites silently. The team-purge job deletes each site through the normal site-delete service (which enqueues the ClickHouse purge, 5.14 and Section 6.9) and only then deletes the team.
• Site deletion (any path) runs in one transaction: call freezeSiteUsage(team_id, site_id) (Section 15.9.4) so the team keeps the usage already counted, delete the sites row (children cascade), upsert the domain reservation (5.5.5) and enqueue the pg-boss job site-purge for that site_id. Sites are hard-deleted at once; there is no paused or pending-deletion site state (a site whose team is pending deletion is treated as unknown by ingestion, 5.4.1).

5.5.2 site_settings #

CREATE TABLE site_settings (
  site_id                      uuid PRIMARY KEY REFERENCES sites(id) ON DELETE CASCADE,
  -- Tracker feature toggles. They drive the generated snippet (Section 7); ingestion never rejects an event because a toggle is off.
  track_outbound_links         boolean NOT NULL DEFAULT true,
  track_file_downloads         boolean NOT NULL DEFAULT true,
  file_download_extensions     text[],                       -- NULL = the default list of Section 7.5.6
  track_form_submissions       boolean NOT NULL DEFAULT false,
  track_404_pages              boolean NOT NULL DEFAULT false,
  track_hash_routing           boolean NOT NULL DEFAULT false,
  -- Traffic quality
  datacenter_filter_enabled    boolean NOT NULL DEFAULT true,
  allowed_hostnames            text[] NOT NULL DEFAULT '{}',  -- empty = the site domain and all its subdomains
  -- Visibility
  public_dashboard             boolean NOT NULL DEFAULT false,
  -- Revenue
  reporting_currency           char(3) NOT NULL DEFAULT 'USD',
  created_at                   timestamptz NOT NULL DEFAULT now(),
  updated_at                   timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT site_settings_extensions_check CHECK (
    file_download_extensions IS NULL OR (
      cardinality(file_download_extensions) BETWEEN 1 AND 50
      AND array_to_string(file_download_extensions, ',') ~ '^[a-z0-9]{1,10}(,[a-z0-9]{1,10})*$')),
  CONSTRAINT site_settings_hostnames_check CHECK (
    cardinality(allowed_hostnames) <= 50
    AND (cardinality(allowed_hostnames) = 0
         OR array_to_string(allowed_hostnames, ',') ~ '^([a-z0-9]([a-z0-9-]{0,61}[a-z0-9])?\.)*[a-z0-9]([a-z0-9-]{0,61}[a-z0-9])?(,([a-z0-9]([a-z0-9-]{0,61}[a-z0-9])?\.)*[a-z0-9]([a-z0-9-]{0,61}[a-z0-9])?)*$')),
  CONSTRAINT site_settings_currency_check CHECK (reporting_currency ~ '^[A-Z]{3}$')
);

The tracking-configuration API of Section 7 reads and writes exactly the tracker columns above (PATCH with any subset); there is no JSON configuration document. Defaults rationale: outbound links and downloads default on because they are the two lowest-risk, highest-value toggles; form submissions, 404 and hash routing change what is collected or how pages are keyed, so they default off.

5.5.3 site_guests #

Purpose: per-site access for people outside the team (agency clients). Roles guest_viewer (read dashboards) and guest_editor (read plus goals, funnels, segments, annotations; exact matrix in Section 11). A guest sees only the sites they are attached to.

CREATE TABLE site_guests (
  id                 uuid PRIMARY KEY DEFAULT uuidv7(),
  site_id            uuid NOT NULL REFERENCES sites(id) ON DELETE CASCADE,
  user_id            uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  role               text NOT NULL,
  invited_by_user_id uuid REFERENCES users(id) ON DELETE SET NULL,
  created_at         timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT site_guests_role_check CHECK (role IN ('guest_viewer','guest_editor'))
);
CREATE UNIQUE INDEX site_guests_site_user_key ON site_guests (site_id, user_id);
CREATE INDEX site_guests_user_idx ON site_guests (user_id);

A user who is already a member of the site's team cannot also be a guest of it (service check, 409 member_exists).

5.5.4 invitations #

Purpose: pending invitations of four kinds: team_member (join a team with a team role), site_guest (become a guest on one site), ownership_transfer (become the team owner) and site_transfer (take over a site and choose the destination team on acceptance). On acceptance the service performs the action of the kind (inserts the team_memberships or site_guests row, transfers ownership or the site) and sets accepted_at.

CREATE TABLE invitations (
  id                 uuid PRIMARY KEY DEFAULT uuidv7(),
  kind               text NOT NULL,
  team_id            uuid NOT NULL REFERENCES teams(id) ON DELETE CASCADE,   -- the inviting team (for site kinds: the site's current team)
  site_id            uuid REFERENCES sites(id) ON DELETE CASCADE,
  email              text NOT NULL,                         -- lowercase invitee address
  role               text,                                  -- role granted; NULL for site_transfer
  token_hash         bytea NOT NULL,                        -- HMAC-SHA-256 (key purpose email-token-hmac, Section 17.7) of the 32-byte emailed token
  invited_by_user_id uuid REFERENCES users(id) ON DELETE SET NULL,
  expires_at         timestamptz NOT NULL DEFAULT now() + interval '7 days',
  last_sent_at       timestamptz NOT NULL DEFAULT now(),    -- last time the email was (re)sent; drives the resend throttle
  resend_count       smallint NOT NULL DEFAULT 0,
  accepted_at        timestamptz,
  created_at         timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT invitations_kind_check  CHECK (kind IN ('team_member','site_guest','ownership_transfer','site_transfer')),
  CONSTRAINT invitations_email_check CHECK (email = lower(btrim(email)) AND char_length(email) BETWEEN 3 AND 254),
  CONSTRAINT invitations_token_check CHECK (octet_length(token_hash) = 32),
  CONSTRAINT invitations_resend_check CHECK (resend_count >= 0),
  CONSTRAINT invitations_shape_check CHECK (
    (kind = 'team_member'        AND site_id IS NULL     AND role IN ('admin','editor','viewer')) OR
    (kind = 'site_guest'         AND site_id IS NOT NULL AND role IN ('guest_viewer','guest_editor')) OR
    (kind = 'ownership_transfer' AND site_id IS NULL     AND role = 'owner') OR
    (kind = 'site_transfer'      AND site_id IS NOT NULL AND role IS NULL))
);
CREATE UNIQUE INDEX invitations_token_hash_key ON invitations (token_hash);
CREATE UNIQUE INDEX invitations_pending_team_key  ON invitations (team_id, email) WHERE kind = 'team_member' AND accepted_at IS NULL;
CREATE UNIQUE INDEX invitations_pending_site_key  ON invitations (site_id, email) WHERE kind IN ('site_guest','site_transfer') AND accepted_at IS NULL;
CREATE UNIQUE INDEX invitations_pending_owner_key ON invitations (team_id) WHERE kind = 'ownership_transfer' AND accepted_at IS NULL;
CREATE UNIQUE INDEX invitations_pending_site_transfer_key ON invitations (site_id) WHERE kind = 'site_transfer' AND accepted_at IS NULL;
CREATE INDEX invitations_email_idx ON invitations (email);

team_id is always set (for guest and site-transfer invitations it is the site's team, so team admins can list and revoke them). The owner role is only carried by ownership_transfer invitations, so the owner role can never be invited directly; ownership changes only through transfer (Section 11). At most one pending ownership transfer exists per team and one pending site transfer per site. Re-inviting an email with a pending invitation replaces it (delete + insert) so the old link dies; resending keeps the row, issues a new token and bumps last_sent_at and resend_count (throttled per Section 17.6). Revoking deletes the row.

5.5.5 site_domain_reservations #

Purpose: stops a released domain from being claimed by another team while pages still carry the old tracker snippet (which would otherwise deliver the old owner's traffic to the new claimant).

CREATE TABLE site_domain_reservations (
  domain         text PRIMARY KEY,                           -- normalized domain, same rule as sites.domain
  team_id        uuid NOT NULL,                              -- the team the domain is reserved for; no FK so the record survives team deletion
  released_at    timestamptz NOT NULL DEFAULT now(),
  reserved_until timestamptz NOT NULL,                       -- released_at + 30 days
  CONSTRAINT site_domain_reservations_domain_check CHECK (domain = lower(domain) AND domain !~ '^www\.' AND char_length(domain) BETWEEN 1 AND 253),
  CONSTRAINT site_domain_reservations_window_check CHECK (reserved_until > released_at)
);
CREATE INDEX site_domain_reservations_until_idx ON site_domain_reservations (reserved_until);

Rules: when a site's domain changes, or the site is deleted, the previous domain is upserted here in the same transaction with reserved_until = now() + interval '30 days'. Creating a site or changing a domain to a value with an unexpired reservation held by a different team returns 409 domain_taken; the reserving team may take the domain again at any time (the reservation row is deleted in that transaction). Events for a reserved domain that has no site are dropped like any unknown domain. Expired rows are ignored by the check and deleted by site-orphan-reconcile (5.14).

5.6 Site Configuration: goals, funnels, funnel_steps, custom_property_allowlist, segments, annotations #

Semantics of goal matching, funnel evaluation and revenue belong to Sections 9, 10 and 13; this section fixes storage and integrity. Column names are the API field names of Section 10 (the 4.1 boundary rule forbids hand-renaming between column and field).

5.6.1 goals #

Purpose: a conversion definition. Three types: event (custom event by name, optionally with revenue), pageview (path pattern), scroll (path pattern plus minimum scroll depth). Up to three property conditions can narrow event and pageview goals (not scroll goals). Auto-collected events are ordinary custom event names: Outbound Link: Click and File Download carry the property url; 404 and Form: Submission carry the property path (Section 7).

CREATE TABLE goals (
  id                  uuid PRIMARY KEY DEFAULT uuidv7(),
  site_id             uuid NOT NULL REFERENCES sites(id) ON DELETE CASCADE,
  type                text NOT NULL,
  display_name        text NOT NULL,                       -- 1..120 chars, unique per site case-insensitively
  event_name          text,                                -- event goals: the custom event name
  page_path           text,                                -- pageview/scroll goals: path pattern, '*' = one segment, '**' = any depth; starts with '/' or '**'
  scroll_threshold    smallint,                            -- scroll goals: 1..100 (percent)
  property_conditions jsonb NOT NULL DEFAULT '[]'::jsonb,  -- [{"key":"plan","value":"pro"}, ...]; 0..3 entries, sorted by key; all values strings
  revenue_currency    char(3),                             -- event goals only: marks a revenue goal and sets its display currency
  created_by_user_id  uuid REFERENCES users(id) ON DELETE SET NULL,
  created_at          timestamptz NOT NULL DEFAULT now(),
  updated_at          timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT goals_type_check   CHECK (type IN ('event','pageview','scroll')),
  CONSTRAINT goals_name_check   CHECK (char_length(display_name) BETWEEN 1 AND 120),
  CONSTRAINT goals_shape_check  CHECK (
    (type = 'event'    AND event_name IS NOT NULL AND page_path IS NULL     AND scroll_threshold IS NULL) OR
    (type = 'pageview' AND event_name IS NULL     AND page_path IS NOT NULL AND scroll_threshold IS NULL AND revenue_currency IS NULL) OR
    (type = 'scroll'   AND event_name IS NULL     AND page_path IS NOT NULL AND scroll_threshold BETWEEN 1 AND 100 AND revenue_currency IS NULL)),
  CONSTRAINT goals_event_name_check CHECK (event_name IS NULL OR (char_length(event_name) BETWEEN 1 AND 120 AND event_name NOT IN ('pageview','engagement'))),
  CONSTRAINT goals_page_path_check  CHECK (page_path IS NULL OR ((page_path LIKE '/%' OR page_path LIKE '**%') AND char_length(page_path) <= 2000)),
  CONSTRAINT goals_conditions_check CHECK (jsonb_typeof(property_conditions) = 'array' AND jsonb_array_length(property_conditions) <= 3),
  CONSTRAINT goals_currency_check   CHECK (revenue_currency IS NULL OR revenue_currency ~ '^[A-Z]{3}$')
);
CREATE UNIQUE INDEX goals_site_name_key ON goals (site_id, lower(display_name));
CREATE UNIQUE INDEX goals_identity_key ON goals (site_id, type, COALESCE(event_name,''), COALESCE(page_path,''), COALESCE(scroll_threshold,0), property_conditions);