BPPP/smb-online
2
0
Fork 0
Code Issues 15 Pull Requests Actions Packages Projects Releases Wiki Activity

Booking schema + tenancy-safe data access layer #15

New Issue
Closed
opened 2026-07-23 14:07:41 +02:00 by mivanchenko · 1 comment
mivanchenko commented 2026-07-23 14:07:41 +02:00
Owner
Copy Link
Copy Source

Parent

#14

What to build

The foundational booking schema and a single tenancy-safe data-access module. This ticket has no
user-facing UI — it's the plumbing every other booking ticket builds on: resources, services,
and a bookings table wired for correct double-booking protection, plus the users /
password_reset_tokens tables and the clients column additions the rest of the module needs.

  • New resources table (resource_id pk, client_id, name, active). One row per bookable
    unit (staff/chair); single-provider clients get exactly one row, but the schema doesn't assume
    single-resource.
  • New services table (service_id pk, client_id, name, duration_minutes, price,
    active).
  • bookings gains a resource_id foreign key and a during generated tstzrange column (from
    start_time/end_time) with a Postgres EXCLUDE USING gist (resource_id WITH =, during WITH &&) constraint — the single source of truth for "no double-booking." bookings.status gains
    pending as a valid value.
  • New users table (user_id pk, client_id, email unique, password_hash, timestamps).
  • New password_reset_tokens table (token, user_id, expires_at, used_at).
  • clients gains: slug (unique, public URL identifier), timezone (default 'Europe/Berlin'),
    auto_confirm (boolean, default true), ics_token (per-client secret for the calendar feed).
  • A new data-access module is the only place raw SQL runs against these tables. Every function
    requires (or resolves from session/token) client_id and injects the filter itself — nothing
    above this layer ever writes WHERE client_id = ... by hand. Functions needed at minimum:
    create/read resource, create/read service, create booking (respecting the exclusion constraint),
    read bookings for a client, update booking (for the reschedule path later), create user, read
    user by email scoped to client, create/consume password reset token.

Acceptance criteria

  • Migration adds all new tables/columns described above, applied the same way existing schema
    changes in this repo are applied (backoffice/db/init.sql convention).
  • The EXCLUDE constraint rejects an INSERT of an overlapping booking for the same
    resource_id (proven by a test, not just by inspection).
  • A test proves that a query/write scoped to client A's client_id cannot read or write client
    B's resources/services/bookings/users, even when IDs are guessed.
  • No route handler or script outside the new data-access module contains raw SQL against these
    tables (this ticket doesn't need to add routes — just the module and its tests).
  • password_reset_tokens supports single-use semantics (consuming a token invalidates it).

Blocked by

None — can start immediately.

## Parent #14 ## What to build The foundational booking schema and a single tenancy-safe data-access module. This ticket has no user-facing UI — it's the plumbing every other booking ticket builds on: `resources`, `services`, and a `bookings` table wired for correct double-booking protection, plus the `users` / `password_reset_tokens` tables and the `clients` column additions the rest of the module needs. - New `resources` table (`resource_id` pk, `client_id`, `name`, `active`). One row per bookable unit (staff/chair); single-provider clients get exactly one row, but the schema doesn't assume single-resource. - New `services` table (`service_id` pk, `client_id`, `name`, `duration_minutes`, `price`, `active`). - `bookings` gains a `resource_id` foreign key and a `during` generated `tstzrange` column (from `start_time`/`end_time`) with a Postgres `EXCLUDE USING gist (resource_id WITH =, during WITH &&)` constraint — the single source of truth for "no double-booking." `bookings.status` gains `pending` as a valid value. - New `users` table (`user_id` pk, `client_id`, `email` unique, `password_hash`, timestamps). - New `password_reset_tokens` table (`token`, `user_id`, `expires_at`, `used_at`). - `clients` gains: `slug` (unique, public URL identifier), `timezone` (default `'Europe/Berlin'`), `auto_confirm` (boolean, default true), `ics_token` (per-client secret for the calendar feed). - A new data-access module is the *only* place raw SQL runs against these tables. Every function requires (or resolves from session/token) `client_id` and injects the filter itself — nothing above this layer ever writes `WHERE client_id = ...` by hand. Functions needed at minimum: create/read resource, create/read service, create booking (respecting the exclusion constraint), read bookings for a client, update booking (for the reschedule path later), create user, read user by email scoped to client, create/consume password reset token. ## Acceptance criteria - [ ] Migration adds all new tables/columns described above, applied the same way existing schema changes in this repo are applied (`backoffice/db/init.sql` convention). - [ ] The `EXCLUDE` constraint rejects an `INSERT` of an overlapping booking for the same `resource_id` (proven by a test, not just by inspection). - [ ] A test proves that a query/write scoped to client A's `client_id` cannot read or write client B's resources/services/bookings/users, even when IDs are guessed. - [ ] No route handler or script outside the new data-access module contains raw SQL against these tables (this ticket doesn't need to add routes — just the module and its tests). - [ ] `password_reset_tokens` supports single-use semantics (consuming a token invalidates it). ## Blocked by None — can start immediately.
mivanchenko added the bookingready-for-agentenhancement labels 2026-07-23 14:07:41 +02:00
mivanchenko added a new dependency 2026-07-23 14:09:17 +02:00
#22 Per-client ICS calendar feed
mivanchenko added a new dependency 2026-07-23 14:09:18 +02:00
#24 New-client onboarding automation update
mivanchenko commented 2026-07-23 14:33:53 +02:00
Author
Owner
Copy Link
Copy Source

Implemented in b5c0fc8.

  • Schema (backoffice/db/init.sql): resources, services, users, password_reset_tokens tables; bookings gains resource_id + a generated during tstzrange column with a btree_gist EXCLUDE USING gist (resource_id WITH =, during WITH &&) constraint (double-booking guard, proven by test); clients gains slug, timezone, auto_confirm, ics_token. Verified init.sql re-applies cleanly on top of the existing production schema (had to switch the trigger-install loop to CREATE OR REPLACE TRIGGER so it doesn't abort before reaching the new tables' triggers on redeploy).
  • Data access (backoffice/app/booking_db.py): the only place raw SQL runs against these tables. Every function takes client_id and injects the tenant filter itself. create_booking/update_booking also verify resource_id belongs to that client_id before writing, so a guessed cross-tenant resource_id is rejected (UnknownResource), not silently accepted.
  • Tests (backoffice/app/tests/, 23 tests): run against a real throwaway Postgres 16 container (matching prod), no mocking, per #14's testing decision -- exercise the exclusion constraint, cross-tenant read/write isolation (including guessed IDs), and password-reset single-use semantics.

All acceptance criteria met. Closing.

Implemented in b5c0fc8. - **Schema** (`backoffice/db/init.sql`): `resources`, `services`, `users`, `password_reset_tokens` tables; `bookings` gains `resource_id` + a generated `during tstzrange` column with a `btree_gist` `EXCLUDE USING gist (resource_id WITH =, during WITH &&)` constraint (double-booking guard, proven by test); `clients` gains `slug`, `timezone`, `auto_confirm`, `ics_token`. Verified `init.sql` re-applies cleanly on top of the existing production schema (had to switch the trigger-install loop to `CREATE OR REPLACE TRIGGER` so it doesn't abort before reaching the new tables' triggers on redeploy). - **Data access** (`backoffice/app/booking_db.py`): the only place raw SQL runs against these tables. Every function takes `client_id` and injects the tenant filter itself. `create_booking`/`update_booking` also verify `resource_id` belongs to that `client_id` before writing, so a guessed cross-tenant resource_id is rejected (`UnknownResource`), not silently accepted. - **Tests** (`backoffice/app/tests/`, 23 tests): run against a real throwaway Postgres 16 container (matching prod), no mocking, per #14's testing decision -- exercise the exclusion constraint, cross-tenant read/write isolation (including guessed IDs), and password-reset single-use semantics. All acceptance criteria met. Closing.
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
booking
Booking / Easy!Appointments
 bug
Something is broken
 crm
Back office / CRM
 enhancement
New feature or request
 infra
Infrastructure
 n8n
n8n workflows
 needs-info
Waiting on reporter for more information
 needs-triage
Maintainer needs to evaluate this issue
 ops
Operational / runtime
 ready-for-agent
Fully specified, ready for an AFK agent
 ready-for-human
Requires human implementation
 security
Security / secrets hardening
 tech-debt
Refactor / debt
 wontfix
Will not be actioned
No Label booking enhancement ready-for-agent
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
Administrator mivanchenko
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Blocks
#16 Availability engine + booking API (create/cancel/reschedule)
BPPP/smb-online
#19 Owner accounts: auth, password reset, CRM dashboard visibility
BPPP/smb-online
#21 Owner settings: services, hours/buffer, auto-confirm, notify channel
BPPP/smb-online
#22 Per-client ICS calendar feed
BPPP/smb-online
#24 New-client onboarding automation update
BPPP/smb-online
Reference: BPPP/smb-online#15
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?