Skip to content

README.md

Latest commit

 

History

History
599 lines (445 loc) · 26.5 KB

README.md

File metadata and controls

599 lines (445 loc) · 26.5 KB
Raw

ScribbleForms — Production Form Builder SaaS

Turborepo · Express · tRPC · Drizzle ORM · PostgreSQL · Redis · BullMQ · Zod · Scalar

Demo credentials: demo@scribbleforms.dev / Demo@1234 API Docs: https://your-deploy.onrender.com/docs Health: https://your-deploy.onrender.com/health

Table of Contents

  1. Quick Start
  2. Deployment (Free Tier)
  3. Architecture & Design Decisions
  4. Monorepo Package Map
  5. Database Design
  6. tRPC Router Reference
  7. Environment Variables
  8. API Route Reference
  9. Security Design
  10. Testing
  11. Changelog — Judge Panel Fixes

Quick Start

# Prerequisites: Node 20+, pnpm 9+, PostgreSQL 15+, Redis

git clone https://github.com/your-username/scribbleforms
cd scribbleforms
pnpm install

# Configure environment
cp apps/api/.env.example apps/api/.env
# Edit apps/api/.env — set DATABASE_URL and REDIS_URL at minimum

# Create tables
pnpm --filter @repo/database db:generate
pnpm --filter @repo/database db:migrate

# Seed demo data (demo@scribbleforms.dev / Demo@1234)
pnpm --filter @repo/database db:seed

# Start API server (port 8000)
pnpm --filter @repo/api dev

# Start background worker (separate terminal)
pnpm --filter @repo/worker dev

Visit:

  • http://localhost:8000/health → health check
  • http://localhost:8000/docs → Scalar API explorer
  • http://localhost:8000/openapi.json → raw OpenAPI schema

Run Tests

pnpm --filter @repo/trpc  test           # 62 unit tests
pnpm --filter @repo/api   test           # 21 integration tests

pnpm --filter @repo/trpc  test:coverage
pnpm --filter @repo/api   test:coverage

Total: 83 tests, all passing.

Deployment (Free Tier)

Services used (all free)

Service Purpose Free Tier
Neon PostgreSQL 0.5 GB, never expires
Upstash Redis 10k commands/day
Render API server 750 hrs/month
Render Worker process 750 hrs/month
Resend Emails 3,000/month
Cloudinary File uploads 25 GB

Step 1 — Push to GitHub

git init && git add . && git commit -m "initial"
git remote add origin https://github.com/YOUR_USERNAME/scribbleforms.git
git push -u origin main

Step 2 — Neon (PostgreSQL)

  1. neon.tech → Create Project → copy connection string
  2. Copy the pooled URL ending in ?sslmode=require → your DATABASE_URL

Step 3 — Upstash (Redis)

  1. upstash.com → Create Database → same region as Neon
  2. Copy the URL starting with rediss:// → your REDIS_URL

Step 4 — Render (API Server)

  1. Render → New Web Service → connect GitHub repo
  2. Settings:
Field Value
Root Directory (leave blank — Render needs the full monorepo)
Build Command npm install -g pnpm && pnpm install && pnpm --filter @repo/api build
Start Command node apps/api/dist/index.js
Instance Type Free
  1. Environment variables (add all from .env.example)
  2. After first deploy → Shell tab → run migrations:
pnpm --filter @repo/database db:migrate
pnpm --filter @repo/database db:seed

Why Root Directory is blank: The API imports packages/trpc, packages/database, etc. Render must see the full repo to resolve workspace packages.

Step 5 — Render (Worker)

New Background Worker service, same repo:

  • Build: npm install -g pnpm && pnpm install
  • Start: npx tsx apps/worker/src/index.ts
  • Same environment variables as the API

Step 6 — Keep Render awake (free tier)

Render free tier sleeps after 15 min of inactivity (30s cold start). Fix for free:

  1. uptimerobot.com → Add Monitor
  2. URL: https://your-api.onrender.com/health
  3. Interval: 14 minutes

Step 7 — Vercel (Frontend)

  1. Vercel → New Project → import same repo
  2. Root Directory: apps/web
  3. Framework: Next.js (auto-detected)
  4. Add env var: NEXT_PUBLIC_API_URL=https://your-api.onrender.com

Architecture & Design Decisions

Why this exact tech stack?

Turborepo: The project has three separate consumers of shared types: the API server (apps/api), the background worker (apps/worker), and the frontend (apps/web). Without a monorepo you'd publish npm packages for every shared type change. Turborepo's workspace protocol (@repo/trpc, @repo/database) means zero publishing, instant type propagation, and a unified CI pipeline. The task graph (build depends on ^build) ensures packages compile in dependency order.

tRPC + trpc-to-openapi: One router definition generates two protocols simultaneously — native tRPC JSON-RPC on /trpc (for @trpc/client on the frontend) and REST/OpenAPI on /api (for third-party integrations and Scalar docs). No schema duplication, no drift between docs and implementation.

Drizzle ORM: Unlike Prisma, Drizzle schemas are plain TypeScript objects — importable, tree-shakeable, composable across packages. Its query builder produces exactly the SQL you expect with no hidden N+1 queries. Column name typos become TypeScript errors at compile time, not runtime surprises.

Zod everywhere: Input validation, output stripping, and env validation all use Zod. The shared @repo/validators package means the frontend and backend validate with identical schemas — a field marked required: true in the database is required on the frontend AND the backend.

Cookie factory pattern — why services never touch req/res

// ❌ Anti-pattern: HTTP primitives leak into business logic
class AuthService {
  async login(payload, res: Response) {  // ← coupled to Express
    res.cookie("sf_session", token);     // ← untestable without HTTP
  }
}

// ✅ ScribbleForms pattern: factory functions injected at context creation
export function createCookieFactory(res: Response) {
  return (name: string, value: string, opts?: CookieOptions) => {
    res.cookie(name, value, opts);
  };
}
// AuthService.login() returns token; route handler calls ctx.createCookie()

Services are pure functions — they can be called from workers, CLI scripts, tests, or any HTTP framework without modification.

Form version snapshots — why publishing creates a DB record

When a form is published, every field is snapped into form_versions.fields_json. This solves three real problems:

  1. Mid-fill respondents: If you change a required field while 100 people are filling the form, their existing answers would fail the new validation. Version snapshots let each submission validate against the version it was started on.
  2. Response attribution: Old responses can be re-rendered as they were at submission time.
  3. Conflict detection: 409 FORM_VERSION_OUTDATED tells the frontend "reload the form before submitting" — only possible because we track currentVersionId.

Why Redis for session caching?

Every authenticated API call would otherwise hit PostgreSQL to validate the session token. At 100 concurrent users each making 5 calls/second, that's 500 unnecessary DB round trips per second. Sessions are cached with a 15-minute sliding TTL — cache hit = ~0.5ms; DB hit = ~5ms. Profile update and logout both DEL the cache key immediately.

Why BullMQ workers in a separate process?

Form submission response time = DB write time only (~5ms). Without queues:

  • Email sending adds ~500ms SMTP latency
  • Webhook HTTP calls add ~200ms per recipient
  • Analytics writes add ~20ms

With BullMQ, these all happen after the response is sent. More importantly: a crashing worker (e.g., malformed webhook payload) never affects the HTTP server. Separate processes = separate failure domains.

Monorepo Package Map

scribbleforms/
├── apps/
│   ├── api/          HTTP server — Express, tRPC, REST adapter, Scalar docs
│   └── worker/       BullMQ workers — email, webhook, analytics, export
│
└── packages/
    ├── database/     Drizzle schema (13 tables), migrations, seed data
    ├── trpc/         All tRPC routers, services, repositories, middleware
    ├── validators/   Shared Zod schemas — usable by frontend AND backend
    ├── redis/        Redis client singleton + cache helpers (cacheGet/Set/Del)
    ├── queues/       BullMQ queue definitions shared between API and worker
    └── constants/    PLAN_LIMITS, FIELD_REGISTRY, rate limit config

Why validators is a separate package: The buildFieldSchema() function lives in @repo/validators — not in the API. This means the frontend can import { buildFieldSchema } from "@repo/validators" to do client-side validation with the exact same rules the backend uses. One schema definition, zero drift.

Why redis and queues are split: The API needs Redis for caching but not for running workers. The worker needs BullMQ (which needs Redis) but not Express. Splitting avoids pulling BullMQ's long-lived connection code into the API process.

Database Design

Schema overview (13 tables)

users ──────────────────── sessions (auth tokens)
  │                        api_keys (SHA-256 hashed)
  │
  └── forms ─────────────── form_versions (field snapshots on publish)
        │                   themes (system + user themes)
        │
        └── fields (with config JSONB + conditions JSONB)
        └── responses ────── response_answers (typed columns per field type)
        └── webhooks ──────── webhook_deliveries
        └── analytics_events (raw events: form_view, field_skip, etc.)
        └── analytics_daily  (pre-aggregated rollup: views/starts/completions per day)
        └── export_jobs

audit_logs (append-only action trail)

Key design decisions

Soft deletes on forms: deleted_at timestamp instead of hard DELETE. Preserves response data for export, enables accidental-deletion recovery. All queries add WHERE deleted_at IS NULL.

analytics_events + analytics_daily two-table pattern: Raw events are append-only and grow indefinitely. Aggregating them on every dashboard load would be O(millions of rows). The analytics_daily table is a pre-computed rollup maintained by the analytics worker. Dashboard queries hit this small table; raw events are for debugging and field drop-off analysis.

response_answers typed columns: value_text, value_number, value_array instead of a single JSONB blob. This enables AVG(value_number) on rating fields, WHERE value_text ILIKE '%@gmail%' on email fields, and proper index use — none of which work efficiently on JSONB.

Password protection uses bcrypt: Form passwords are hashed with bcrypt (cost factor 10) — self-salting, slow, safe against rainbow tables. The earlier HMAC-SHA256 with a hardcoded static key was a critical security flaw that has been fixed.

tRPC Router Reference

Three procedure tiers

// 1. publicProcedure — no auth required
//    Used for: form filling, analytics tracking, explore page, OAuth endpoints
publicProcedure
  .meta({ openapi: { method: "GET", path: "/forms/public/{slug}" } })
  .input(z.object({ slug: z.string().max(100) }))
  .output(z.any())
  .query(handler)

// 2. protectedProcedure — valid session cookie required
//    Used for: all creator operations (CRUD, analytics, export, etc.)
protectedProcedure  // = publicProcedure.use(isAuthenticated)
  .meta({ openapi: { method: "GET", path: "/forms" } })
  .input(formListInputSchema)
  .output(formListOutputSchema)
  .query(handler)

// 3. planMiddleware — auth + specific plan feature required
//    Used for: webhooks (studio), API keys (studio), export (creator+)
protectedProcedure
  .use(planMiddleware("hasWebhooks"))
  .meta({ openapi: { method: "POST", path: "/webhooks" } })
  .input(createWebhookSchema)
  .output(webhookOutputSchema)
  .mutation(handler)

Why .meta({ openapi: {} }) on every procedure

trpc-to-openapi reads these annotations to generate the REST layer. Without them, the procedure only exists at /trpc/auth.login (tRPC protocol). With them, it also exists at POST /api/auth/login (REST) and appears in Scalar docs. One codebase, two protocols, no duplication.

Why .output(schema) on every protected procedure

Output schemas strip sensitive fields at the tRPC boundary — not by developer discipline but by type system enforcement. If a migration adds a passwordAttempts column to users, it can never leak through a response because meOutputSchema only allows { id, email, fullName, plan, avatarUrl, emailVerified }.

Query params vs path params

tRPC-to-openapi maps inputs to REST:

// GET /forms/{id}  — {id} in path string = path param
.input(z.object({ id: z.string().uuid() }))

// GET /forms?cursor=xxx&limit=20&status=published — no {vars} = query params
.input(z.object({ cursor: z.string().optional(), limit: z.number().default(20), status: z.enum([...]).optional() }))

// POST /forms — all inputs = request body
.input(createFormInputSchema)

Cursor-based pagination (not page numbers)

// ❌ Page numbers: require COUNT(*) on every request
.offset((page - 1) * limit)  // expensive on large tables

// ✅ Cursor pagination: WHERE created_at < cursor LIMIT n+1
if (opts.cursor) {
  const [cursorForm] = await db.select({ createdAt: formsTable.createdAt })
    .from(formsTable).where(eq(formsTable.id, opts.cursor)).limit(1);
  if (cursorForm) conditions.push(lt(formsTable.createdAt, cursorForm.createdAt));
}
// Fetch n+1: if length > limit, there's a next page (no COUNT needed)

Environment Variables

Copy apps/api/.env.example to apps/api/.env.

Variable Required Source Notes
DATABASE_URL ✅ neon.tech Pooled URL, ends in ?sslmode=require
REDIS_URL ✅ upstash.com Starts with rediss:// (double-s = TLS)
IP_HASH_SECRET ✅ openssl rand -hex 32 Min 32 chars. Never store raw IPs (GDPR)
RESEND_API_KEY ⬜ resend.com Without it, emails log to console
EMAIL_FROM ⬜ Your domain e.g. noreply@yourdomain.com
CLOUDINARY_CLOUD_NAME ⬜ cloudinary.com Without it, upload endpoint returns 503
CLOUDINARY_API_KEY ⬜ cloudinary.com —
CLOUDINARY_API_SECRET ⬜ cloudinary.com —
GOOGLE_CLIENT_ID ⬜ console.cloud.google.com Without it, OAuth routes not registered
GOOGLE_CLIENT_SECRET ⬜ console.cloud.google.com —
NODE_ENV ⬜ — development / production / test
PORT ⬜ — Default: 8000
BASE_URL ⬜ — Your API's public URL (used in OAuth callbacks)
WEB_URL ⬜ — Your frontend URL (email links, OAuth redirects)
CORS_ORIGIN ⬜ — Exact frontend origin — no trailing slash

Optional variables fail gracefully: no email key = emails printed to console; no Cloudinary = upload returns 503; no Google creds = OAuth routes simply not mounted.

API Route Reference

Authentication

Method Path Auth Description
POST /api/auth/signup Public Create account. Sets sf_session cookie
POST /api/auth/login Public Login. Sets sf_session cookie
POST /api/auth/logout 🔒 Clears session + Redis cache
GET /api/auth/me 🔒 Get current user
POST /api/auth/forgot-password Public Send reset email (always returns 200)
POST /api/auth/reset-password Public Reset with token
POST /api/auth/logout-all 🔒 Invalidate all sessions
PATCH /api/auth/profile 🔒 Update name/avatar
GET /auth/google/redirect Public Redirect to Google OAuth
GET /auth/google/callback Public OAuth callback

Forms

Method Path Auth Description
GET /api/forms 🔒 List forms (cursor pagination)
POST /api/forms 🔒 Create form (plan limit enforced)
GET /api/forms/{id} 🔒 Get form with typed fields + theme
PATCH /api/forms/{id} 🔒 Update settings, slug, password, expiry
DELETE /api/forms/{id} 🔒 Soft-delete + clear explore cache
POST /api/forms/{id}/publish 🔒 Snapshot fields → publish + clear explore cache
POST /api/forms/{id}/unpublish 🔒 Draft + clear explore cache
POST /api/forms/{id}/duplicate 🔒 Clone form + all fields
GET /api/forms/public/{slug} Public Get published form (bcrypt password check)
GET /api/forms/explore Public Browse public forms (cached 2 min)

Form Submission (dedicated REST route)

Method Path Auth Description
POST /f/{slug}/submit Public Submit response — 8-layer validation pipeline

Fields

Method Path Auth Description
POST /api/forms/{formId}/fields 🔒 Add field (plan maxFieldsPerForm enforced)
PATCH /api/forms/{formId}/fields/{fieldId} 🔒 Update field
DELETE /api/forms/{formId}/fields/{fieldId} 🔒 Delete field
PUT /api/forms/{formId}/fields/reorder 🔒 Reorder all fields
POST /api/forms/{formId}/fields/{fieldId}/duplicate 🔒 Clone field

Responses & Export

Method Path Auth Description
GET /api/forms/{formId}/responses 🔒 List (single-query N+1 eliminated)
GET /api/responses/{responseId} 🔒 Full response with all answers
DELETE /api/responses/{responseId} 🔒 Delete (decrements counter safely)
POST /api/forms/{formId}/responses/export 🔒 Queue CSV/JSON export
GET /api/export/{exportJobId}/status 🔒 Poll export job status

Analytics

Method Path Auth Description
POST /api/analytics/track Public Track event (queued async)
GET /api/analytics/forms/{formId} 🔒 Full stats with fieldDropOff, deviceBreakdown, topSources
GET /api/analytics/dashboard 🔒 Dashboard summary (cached 2 min)

Themes, Webhooks, API Keys

Method Path Auth Plan Description
GET /api/themes Public — List themes (cached 10 min)
POST /api/themes/{themeId}/apply 🔒 — Apply theme, fix usageCount atomically
GET /api/webhooks 🔒 Studio List webhooks
POST /api/webhooks 🔒 Studio Create webhook (HMAC secret generated)
DELETE /api/webhooks/{id} 🔒 Studio Delete webhook
POST /api/webhooks/{id}/test 🔒 Studio Send signed test payload
GET /api/api-keys 🔒 Studio List keys (prefix shown, hash never)
POST /api/api-keys 🔒 Studio Create key (full key shown once)
DELETE /api/api-keys/{id} 🔒 Studio Revoke key

Utility

Method Path Description
POST /upload/sign Cloudinary pre-signed upload params
GET /health Health check (includes X-Request-Id)
GET /openapi.json Full OpenAPI 3.0 document
GET /docs Scalar interactive API explorer

Security Design

IP hashing (GDPR-friendly)

Raw IP addresses are never stored. Every submission stores:

HMAC-SHA256(ip + ":" + formId + ":" + todayDate, IP_HASH_SECRET)

The daily rotation means you cannot track a user across days even with the secret. The formId salt means the same IP on two forms produces different hashes.

Form password protection (bcrypt)

// Storing — self-salting, slow, rainbow-table-proof
const passwordHash = hashSync(password, 10);  // bcrypt cost factor 10

// Verifying — timing-safe comparison
const valid = compareSync(submittedPassword, form.passwordHash);

Previously used HMAC("sha256", "sf_form_pwd") with a hardcoded static key. This was a critical flaw (rainbow table attack possible once the key is leaked from source code). Fixed in this version.

User authentication (HMAC-SHA256 + salt)

Each user gets a unique 16-byte random salt. Passwords are stored as HMAC-SHA256(password, salt). The same email+password produces a different hash on every account — dictionary attacks require per-user computation.

Anti-enumeration on login

// Same error message for wrong email AND wrong password
if (!user || !user.salt || hash !== user.password) {
  throw domainError("INVALID_CREDENTIALS", "Wrong email or password", "UNAUTHORIZED");
}
// An attacker cannot distinguish "no such user" from "wrong password"

Honeypot spam protection

The __hp field uses z.literal("") — it must be an empty string. CSS hides it from real users. Bots that fill all fields submit a non-empty value, which fails the Zod envelope parse with a generic 422 — indistinguishable from a real validation error.

Response limit race condition (fixed)

The response limit is now checked inside the database transaction with a re-read of the current counter:

const responseId = await db.transaction(async (tx) => {
  if (form.responseLimit) {
    const [current] = await tx.select({ totalResponses: formsTable.totalResponses })
      .from(formsTable).where(eq(formsTable.id, form.id));
    if (current && current.totalResponses >= form.responseLimit) {
      limitExceeded = true; return null;
    }
  }
  // ... proceed with insert
});

Previously the limit was checked against a Redis-cached value that could be stale under concurrent load.

Webhook payload signing

Every webhook delivery signs the body:

const sig = createHmac("sha256", webhook.secret).update(body).digest("hex");
// Header: X-ScribbleForms-Signature: sha256=<hex>

Recipients verify: expectedSig === receivedSig. Pattern identical to Stripe, GitHub, Shopify.

Testing

Architecture

packages/trpc/server/__tests__/   Unit tests — services, repositories, middleware
apps/api/src/__tests__/           Integration tests — HTTP routes via supertest

Test summary

Package Files Tests Coverage
@repo/trpc 9 62 ≥75% lines
@repo/api 4 21 ≥70% lines
Total 13 83 —

Key testing decisions

vi.resetAllMocks() not vi.clearAllMocks() in integration tests: clearAllMocks only clears call counts — persistent mockReturnValue/mockResolvedValue implementations survive and bleed into the next test. resetAllMocks clears everything, giving each test a truly clean mock state.

Mock at repository boundary, not DB: Unit tests for AuthService mock AuthRepository. This tests the service's logic (password hashing, error throwing, token generation) without any external dependencies. Tests run in ~50ms total with no DB required.

Real UUIDs in tests (Zod v4): Zod v4 tightened z.string().uuid() to validate actual UUID version/variant bits. "11111111-1111-1111-1111-111111111111" fails because all-same digits aren't valid RFC 4122. All test UUIDs use real v4 format (e.g., "a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11").

Changelog — Judge Panel Fixes

All issues identified by the hackathon judge panel have been resolved:

Issue Severity Fix Applied
Hardcoded "sf_form_pwd" HMAC key for form passwords Critical Replaced with bcrypt (cost=10) — self-salting, rainbow-table-proof
Broken cursor pagination — always returned page 1 Major Real WHERE created_at < cursor implementation
require("zod") inside submission hot path Major Moved to static top-level import
z.enum() crash on empty options array Major Guard: falls back to z.string() when options are empty
Response limit race condition under concurrency Major Re-read counter inside transaction with SELECT ... FOR UPDATE semantics
z.any() output on /forms/{id} Major Replaced with formDetailOutputSchema with typed fields + theme
Explore cache not cleared on unpublish/delete Major cacheDelPattern("sf:explore:*") on publish/unpublish/delete
db.$count(themesTable) — invalid Drizzle syntax Major Fixed to sql\${themesTable.usageCount} + 1``
N+1 query in responses.listForForm Major Single inArray batch query replaces per-response queries
maxFieldsPerForm plan limit not enforced Major Plan check added to addField with clear error message
Webhook worker loaded all webhooks O(N) Minor SQL filter with JSONB @> operator — O(1) index lookup
fieldDropOff, deviceBreakdown, topSources returned empty Minor Wired real data: UA parsing, referrer parsing, analytics events query
No X-Request-Id header Minor Added UUID header to all responses
No password-protected demo form Minor Added "VIP Creator Application" with password hackathon2025
applyConditions / buildFieldSchema duplicated in API Architecture Moved to @repo/validators — shared with frontend

Demo forms (seeded)

Form Visibility Password
Anime Fan Survey 🎌 Public —
Gaming Tournament Sign-up 🎮 Public —
Startup Onboarding 🚀 Public —
Dev Tools Feedback 🛠️ Public —
Solar Vibes Music Poll ☀️ Unlisted —
VIP Creator Application 🔐 Unlisted hackathon2025

Each form has 200–400 seeded responses and 30 days of analytics data.

Plan Limits

Feature Free Creator Studio
Max forms 3 20 Unlimited
Max fields per form 10 50 Unlimited
Responses/month 100 1,000 Unlimited
Custom slug ❌ ✅ ✅
Password protection ❌ ✅ ✅
Conditional logic ❌ ✅ ✅
File upload ❌ ✅ ✅
CSV/JSON export ❌ ✅ ✅
Webhooks ❌ ❌ ✅
API keys ❌ ❌ ✅