Drizzle and Effect 4

The bootstrap tables live in the public namespace and are exported from backend/src/db/schemas/index.ts for Drizzle Kit.

Outbound webhook scheduling lives in a separate pgboss schema managed by pg-boss at backend bootstrap, before requests are accepted. Drizzle Kit remains scoped to application tables in public. The database role needs schema/object creation privileges. The queue uses the same PG settings, password secret and verified CA as the application, with a separate pool of up to three connections per process. Budget 13 connections per API process: five Database, five separate MailDatabase, and three pg-boss connections, plus headroom for deployment overlap and operators. A repository-level fromDrizzle adapter captures the transaction's Effect context so business writes, webhook_outbox inserts and pg-boss jobs commit or roll back together on the reserved connection. Queue settlement and outbox updates are transactional too. Four scoped fibers per API process consume the shared queue; shutdown closes the pool. See outbound webhooks for retries, leases, scaling and operational SQL.

Application tables include:

• person: UUID ID, firstName, lastName, avatar, unique required email, gender, birthDate, structured address object, phoneNumber, required clubId, typed attributes and creation timestamp.
• person_attribute: per-club attribute definitions with a unique key, label, fixed type and choice options.
• club: UUID ID, required name, avatar, description, structured address object, globally unique hostedSlug, onboardingId, nullable merchantProfileId and creation timestamp. Multiple clubs can select the same merchant profile.
• onboarding: short text ID, clubId, identifying name and description, hosted enablement, font and primary color, ordered questions, after-approval configuration, and revision. A club can publish multiple onboardings below its shared hosted slug. See onboarding.
• reservation_category: club-scoped type configuration with name, description, conversation flag, session requirement, and nullable default check-in/check-out minute offsets.
• reservation_resource: named club resources owned by exactly one reservation category; names are unique case-insensitively within a club.
• reservation_session: weekly UTC session definitions and one-off custom sessions, each with an optional member capacity.
• reservation_session_guest_person, reservation_session_guest_plan: a session's optional guest list. Deleting the session, person or plan removes the entry.
• reservation_waitlist_entry: FIFO member entries for full session occurrences, including the selected resource and materialized occurrence dates.
• reservation_block: club/type-scoped unavailable time ranges with an optional reason.
• reservation: club/type/customer booking, requested window (null for a pending or canceled request awaiting a staff proposal), nullable staff-proposed alternative window with its free flag, nullable session reference and check-in/check-out overrides, guests, status, creation timestamp, and exactly one resource. The resource must belong to the reservation's type; its displayed name is read from the resource, so renaming a resource renames it on every reservation.
• reservation_message: ordered customer/professional messages for conversation-enabled reservations.
• reservation_chat_staff: per-staff conversation participation, last-read time, and archive state. A row is created when that staff account first replies to the reservation chat.
• application: a club-scoped immutable onboarding snapshot with applicant name, verified email, structured applicant address, expiring salted email challenge, ordered answers, progress, review status, optional rejection note, finalization-reminder schedule and count, timestamps, revision, and a nullable unique person reference populated only on acceptance. See applications.
• plan: UUID ID, required name, optional description, nonnegative integer amount, currency, monthly/yearly interval, subscription/addon type, active state, creation timestamp and the optional successor plan that replaced it for new subscribers after a split.
• merchant_profile: local identity, sandbox/platform and connected account IDs, configuration, payment and payout readiness, and last-check timestamp.
• merchant_plan: merchant + plan mapping to a connected Product and Price.
• plan_price: every connected Price created for a plan, with its amount, currency and interval, so renewals on historical or subscriber-specific Prices can be validated.
• stripe_customer: person + connected account mapping to a Customer, with compound uniqueness preventing contradictory person/Customer associations within an account.
• guest_invitation: club-scoped guest email, 12/24/48-hour access duration, inviting member or staff account, hashed single-use token, link expiry and acceptance time.
• guest_access: one accepted invitation's temporary access grant, including the guest person, club, inviter kind and nullable current inviter reference, start, expiry and optional revocation. The latest grant determines whether a person is a current or former guest. See guests.
• checkout_attempt: durable person, club, merchant/account, plan/Price, request and session references, state and creation timestamp.
• membership: person, plan, club/account, subscription, subscription item, Price, paid-period timestamps, calendar expiration date, creation timestamp, and an optional scheduled replacement Price with the time it is applied.
• invoice_projection: account + public invoice ID, owning club and the provider snapshot. It is a local copy of Stripe data and is deleted with its club.
• invoice_job: account-qualified scan, invoice and webhook jobs with cursor and durable retry state.

A person belongs to exactly one club for life and has many memberships, joined through membership.personId. New seeded and Stripe-backed memberships reference one plan through membership.planId; the column is nullable, so a membership can exist without a plan. Foreign keys enforce these relationships and are indexed. Every table that stores both a person and a club also checks that the person belongs to that club, through a foreign key on (personId, clubId) to person (id, clubId). Deleting a club deletes its people, onboarding and applications; deleting a person deletes their memberships and clears the accepted application's person reference. A referenced plan is archived rather than deleted. Customers are account-qualified, not universal person attributes. Stripe remains the source of truth for Customers, subscriptions and invoices. Billing references (memberships, Checkout attempts, Customers) prevent deletion of their person, club or merchant. Birth and expiration dates are calendar dates (YYYY-MM-DD), without time zones. Avatars hold URLs or storage keys; gender is free-form text. Person, application and club addresses are nullable JSONB objects with line1, line2, city, postalCode, state, and country fields; a CHECK constraint on each table rejects non-object JSON values. Profile details and membership expiration are nullable. A null expiration means no date has been recorded; it does not imply an active or lifetime membership. Plan amounts are stored as nonnegative integer minor currency units. Currency uses a lowercase three-letter code, and interval is month or year. plan.stripeId is not a universal Stripe reference; runtime pricing resolves club → merchant → plan mapping. Editing price fields rotates the connected Stripe Price because Stripe prices are immutable, while deletion archives the Product/Price and removes the mapping. The seeded IÉNA offers are annual (€1,800, €4,500 and €6,300), with no additional tax introduced. Paid-webhook reconciliation is keyed by connected account + subscription, preserves the historical club, and extends only verified paid periods without regression on out-of-order events. The local plan catalog is available through the plans GraphQL query.

attributes is a required JSONB object, defaulting to {}, with a database check rejecting arrays and scalar values. It maps the club's person_attribute keys to typed JSON values (string, number or boolean); see Member attributes.

Text columns limited to a fixed set of values store lowercase snake_case values, enforced by a database check. When GraphQL exposes such a column with another spelling, the backend converts at the API boundary: person_attribute.type stores text, number, date, boolean and choice for TEXT…CHOICE; onboarding.hostedFont stores modern, classic and editorial; housekeeping_checklist_item.category stores after_reservation for afterReservation; and housekeeping_report.condition stores out_of_service for outOfService. JSONB documents such as onboarding questions and floor-plan elements keep their API spelling.

Each email is stored once. person_credential holds only the member's password hash; member login, password reset and activation join person and match lower(btrim(person.email)), which the per-club unique index on person covers, so changing a person's email changes their login. staff_user.email is trimmed and lowercased on every write and unique through lower(email).

First and last names are required. There is no legacy name column or fallback. The administrator-only people query selects only ID, firstName, lastName, and email. Onboarding management and application reviews require club administrator access. Applicant operations require a scoped token; person reads require ownership or administrator access.

Entity tables record a createdAt timestamp, defaulting to the insert time. Link tables, transient rate-limit and queue rows, and tables with their own event time (such as activity.occurredAt or building_entry.enteredAt) do not. GraphQL does not expose these creation timestamps unless a type documents them.

Schema push manages every table exported from the schema index. Other tables in public are left untouched. Upgrading databases created by older versions is not supported: recreate the database instead.

Automatic schema push

Every backend start runs drizzle-kit push before listening. Edit the Drizzle schema and restart the backend; there are no generated SQL files or migration folders to manage. Startup uses --force and automatically resolves unknown rename/create decisions as create, retrying with generated hints. There are no explicit rename hints. Destructive changes are approved without prompting: tables or columns can be dropped and data can be lost, including when a rename is treated as drop-and-create. This development policy also applies to deployed API startup; do not rely on it to preserve data. Automatic decisions are logged. Database errors, malformed CLI responses, repeated unresolved decisions, or exceeding the bounded retry limit still fail startup. Concurrent pushes to the same database, such as two API instances starting together, wait for each other through a PostgreSQL advisory lock instead of applying the same changes twice.

backend/src/bootstrap/seed.ts contains idempotent demo seeding after schema push. It inserts 39 fictional profiles and the five requested UKAB teammates and three plans with stable IDs. All seeded people belong to Ukab Clubs; Vosges and Astel remain available. 27 people have accepted applications, and Ukab Clubs has draft, submitted and rejected candidates. Ukab Clubs contains Yanis Meziane, Julian Cario, Maxime Borde, Chayanne Godard and Hichame Benaddi, using their first name at ukab.fr. Existing email matches retain their IDs. Unrelated custom profiles are preserved. Application progress and profile edits survive restarts. It creates no Stripe membership without an explicit STRIPE_FIXTURE_MANIFEST file. A valid manifest restores Ukab Clubs' merchant and connected catalog, Maxime Borde's Customer, and his verified paid Cercle + Sport subscription period. Vosges and Astel initially have no merchant. Business edits and later merchant selections survive; conflicting fixture references fail explicitly. An unreadable or invalid supplied manifest fails startup. The seed performs no Stripe API calls. Production seeding is off by default; Terraform's demo_seed=true enables it. See Stripe Connect for the manifest delivery path.

Native Effect queries

Database uses drizzle-orm/effect-postgres and @effect/sql-pg. Queries are Effects, not Promise queries wrapped in Effect. One scoped layer owns the pool and releases it on shutdown. GraphQL propagates request cancellation.

const people = Effect.gen(function* () {
  const db = yield* Database;
  return yield* db
    .select()
    .from(person)
    .orderBy(person.firstName, person.lastName, person.id)
    .limit(100);
});

Ordinary statements use PostgreSQL autocommit. Explicit db.transaction(...) remains available when multiple statements must succeed or fail together.

Configuration and secrets

make setup, make dev, and first worktree startup generate a cryptographically random database password if necessary. secrets/ is private and excluded from Git and Docker builds. Subsequent starts preserve it; missing or changed existing credentials are rejected rather than silently rotating access to persisted data.

Compose passes the generated file to PostgreSQL via POSTGRES_PASSWORD_FILE and to the backend via PGPASSWORD_FILE. Host, port, username, and database are wired automatically. You never construct a database URL or put a password in .env. Compose loads optional external API keys from .env only into the backend.

Terraform generates the production password with random_password, supplies the private database endpoint and trusted CA, and injects the password through Scaleway secret environment variables. Production needs no local .env file. Review a Terraform plan before applying changes to existing infrastructure; adopting the generated password rotates the previous database credential.

Versions

Drizzle ORM/Kit are pinned to 1.0.0-rc.5-5935859 and Effect packages to 4.0.0-rc.112. This Drizzle candidate supports Effect's current Schema.TaggedError API without a compatibility patch or Effect 3 adapter.

Member notifications

member_notification stores recipient-scoped inbox entries and email delivery state. notification_preference stores per-club/member/category channel choices; absent rows mean both channels enabled. notification_event retains deduplication keys for scheduled/join milestones, including suppressed notifications. session_cancellation records canceled occurrence instants without removing recurring session definitions. Pending cancellation refunds are derived from canceled occurrence reservations with a payment intent and no refund ID.