# Drizzle and Effect 4 Automatic schema push, native Effect queries, and generated credentials. Source: https://docs.champagne.dev-ukab.fr/en/database 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](/en/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](/en/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](/en/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](/en/graphql/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](/en/graphql/plans). `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](/en/graphql/person-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 [#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](/en/stripe-connect) for the manifest delivery path. ## Native Effect queries [#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. ```ts 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 [#configuration-and-secrets] | Location | Purpose | | --------------------------- | ------------------------------------------------------------------------------------------------------ | | `config.mk` | Non-secret development settings; override with ignored `config.local.mk`. | | `secrets/postgres_password` | Automatically generated persistent password, mounted as a Docker secret. | | `.env` | Optional externally issued secrets, such as `STRIPE_SECRET_KEY`; never generated database credentials. | `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 [#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-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.