Database

tripplan.ing uses a single SQLite database for all data, accessed through Drizzle ORM. In production this is Cloudflare D1; locally it's better-sqlite3. The schema contains 30+ tables organized into two families: platform tables (global) and event tables (scoped by event_id).

Schema families

Platform tables

These store global platform state — operators, events, organizations, and audit logs. They have no event_id column.

Table	Purpose	Key fields
platform_users	Operator accounts	email, status
platform_roles	Operator role assignments	userId, role (super_admin/admin)
platform_events	Event metadata and lifecycle	slug, status, adminEmail, primaryDomain
platform_event_domains	Hostname-to-event mapping	eventId, hostname, isPrimary
platform_organizations	Organization grouping	name, slug
platform_org_members	Org membership	orgId, userId
platform_audit_logs	Operator action tracking	actorEmail, action, targetType

Event tables

All event-scoped tables include event_id as a required column. Every query must filter by it.

Registration & payments:

Table	Purpose
rsvps	Party/group registrations (contact email, status, dietary, custom fields)
attendees	Individual people within an RSVP (name, tier, optional email, add-on selections)
payments	Payment records (Stripe/PayPal/manual, amount, status, refund tracking)
payment_items	Line items within a payment (attendee + expense mapping)

Schedule:

Table	Purpose
schedule_days	Day containers (date, label, sort order)
schedule_items	Agenda items (time, title, location, category, rich content)
schedule_item_permissions	Per-item access control (email or group)

Content & media:

Table	Purpose
content_sections	Homepage blocks (type, body, images, links, visibility)
content_section_permissions	Per-section access control
photos	Gallery entries (R2 key, filename, caption, uploader)
documents	File entries (R2 key, title, auth requirement, sort order)
document_permissions	Per-document access control (email or group)
announcements	Email blasts and site banners (delivery type, recipients, status)

Engagement:

Table	Purpose
polls	Poll definitions (type: availability/single/multiple, status, anonymous)
poll_options	Options within a poll
poll_votes	Individual votes (voter email, option, optional "other" text)

Access & people:

Table	Purpose
access_requests	Sign-up requests from non-allowed users (status: pending/approved/denied)
groups	Named groups for organizing people (color, description)
group_members	Email-to-group associations

Configuration:

Table	Purpose
settings	Event config overrides (single row per event, all fields nullable)
pricing_tiers	Attendee types with multipliers (Adult 1.0, Child 0.5, Infant 0)
add_ons	Billable items (per-attendee or flat, optional, with tier-specific pricing)
custom_fields	Dynamic RSVP form fields (text, select, checkbox, number)
custom_field_options	Options for select-type custom fields

Drizzle ORM patterns

Table definitions

Tables are defined using Drizzle's SQLite helpers:

import { sqliteTable, text, integer, index, uniqueIndex } from 'drizzle-orm/sqlite-core';

export const rsvps = sqliteTable(
  'rsvps',
  {
    id: text('id').primaryKey(),
    eventId: text('event_id').notNull(),
    contactEmail: text('contact_email').notNull(),
    contactName: text('contact_name').notNull(),
    status: text('status', { enum: ['confirmed', 'tentative', 'declined'] })
      .notNull()
      .default('confirmed'),
    notes: text('notes'),
    customFields: text('custom_fields'),  // JSON string
    createdAt: text('created_at').notNull().$defaultFn(() => new Date().toISOString()),
    deletedAt: text('deleted_at')  // Soft-delete support
  },
  (table) => [
    index('rsvps_event_id_idx').on(table.eventId),
    index('rsvps_contact_email_idx').on(table.contactEmail),
    index('rsvps_event_email_idx').on(table.eventId, table.contactEmail)
  ]
);

Key conventions:

• IDs: Always text('id').primaryKey() — generated with crypto.randomUUID()
• Timestamps: Stored as ISO text strings, not integers
• Enums: Defined inline with text('col', { enum: [...] })
• JSON fields: Stored as text, parsed/serialized in application code
• Soft deletes: deletedAt column (used by RSVPs)
• Indexes: Defined in the third argument as an array

Queries

Always scope by event_id for event tables:

import { eq, and } from 'drizzle-orm';
import { getRuntimeEnv } from '$lib/server/runtime/index.js';
import { rsvps } from '$lib/server/db/schema';

const env = await getRuntimeEnv(platform);
const confirmed = await env.db
  .select()
  .from(rsvps)
  .where(and(
    eq(rsvps.eventId, eventId),
    eq(rsvps.status, 'confirmed')
  ));

Inserts

await env.db.insert(rsvps).values({
  id: crypto.randomUUID(),
  eventId,
  contactEmail: email,
  contactName: name,
  status: 'confirmed',
  createdAt: new Date().toISOString()
});

Updates

await env.db
  .update(rsvps)
  .set({ status: 'declined', updatedAt: new Date().toISOString() })
  .where(and(eq(rsvps.eventId, eventId), eq(rsvps.id, rsvpId)));

Joins

const rsvpsWithAttendees = await env.db
  .select()
  .from(rsvps)
  .leftJoin(attendees, eq(rsvps.id, attendees.rsvpId))
  .where(eq(rsvps.eventId, eventId));

Migration workflow

Drizzle Kit generates migrations from schema changes:

# 1. Edit schema.ts
# 2. Generate a migration file
npm run db:generate

# 3. Apply to local SQLite
npm run db:migrate:local

# 4. Apply to remote D1 (production)
npm run db:migrate:remote

Migrations live in apps/event-site/drizzle/ as numbered SQL files. The CI pipeline applies migrations before deploying.

Migration tips

• Adding a column: Make it nullable or provide a default. D1 doesn't support ALTER TABLE ... ADD COLUMN ... NOT NULL without a default.
• Renaming a column: Create a new column, migrate data, drop old column (three separate migrations).
• Adding an index: Safe to do in a single migration.
• Dropping a table: Ensure no foreign keys reference it.

JSON field patterns

Several tables store structured data as JSON text:

Table.Column	Contains
rsvps.customFields	Record<string, string | boolean | number>
attendees.customFields	Same as above
attendees.selectedAddOnIds	string[] (add-on IDs)
settings.allowedEmails	string[]
settings.adminEmails	string[]
settings.paymentMethods	('stripe' | 'paypal')[]
add_ons.tierPrices	Record<tierId, amountCents>
content_sections.galleryImageKeys	string[]
announcements.recipientEmails	string[]

Parse with JSON.parse() and validate the shape in application code. Always handle null (column not set) gracefully.

Permission model

Three tables implement fine-grained access control using a consistent pattern:

// All permission tables share this shape
{
  id: text('id').primaryKey(),
  eventId: text('event_id').notNull(),
  [parentId]: text('parent_id').notNull().references(...),
  permissionType: text('permission_type', { enum: ['email', 'group'] }).notNull(),
  permissionValue: text('permission_value').notNull(),  // email address or group ID
  createdBy: text('created_by').notNull(),
  createdAt: text('created_at').notNull()
}

Used by: document_permissions, schedule_item_permissions, content_section_permissions.

To check access: query permissions for the item, then check if the user's email matches directly or via group membership.

Related pages

• Architecture — how the database fits into the runtime abstraction
• Data Models — TypeScript interfaces that map to these tables
• Code Patterns — Drizzle import conventions and query patterns