Data Models — Backend

Source of truth: backend/migrations/0001..0007_*.sql. This document mirrors the schema as of migration 0007. Run docker exec shiftit-pg psql -U shiftit -d shiftit -c "\dt" against a freshly-migrated dev DB to confirm.

Tenancy & RLS

Every tenant-scoped table has:

  • a tenant_id UUID column with FK to tenants(id) ON DELETE CASCADE,

  • ENABLE ROW LEVEL SECURITY,

  • a policy named <table>_isolation with FOR ALL and WITH CHECK, predicate tenant_id = current_setting('app.tenant_id', true)::uuid,

  • explicit GRANT SELECT, INSERT, UPDATE, DELETE … TO shiftit_app (redundant with default privileges from 0001 but kept for clarity).

The runtime DB pool issues SET ROLE shiftit_app on every physical connection. TenantContext::rls_transaction(&pool) returns a Transaction with app.tenant_id set; queries inside that transaction are RLS-filtered. The connecting user is normally a superuser (would bypass RLS); the SET ROLE hop is what makes the policies bind.

shift_slots and shift_assignments use subquery-based RLS (filter via the parent shifts row’s tenant_id) rather than a denormalised column — fail-closed by construction but a known per-row cost under load (see follow-up: subquery-based RLS).

Tables (by migration)

Migration 0001 — tenants, users

tenants

Column Type Notes

id

UUID PK

gen_random_uuid()

zitadel_org_id

TEXT UNIQUE

Maps to Zitadel Organization id

name

TEXT

Display name

slug

TEXT UNIQUE

URL-safe slug

settings

JSONB

Default: { time_tracking_enabled: false, default_claiming_mode: "open", max_submissions_per_form_per_hour: 10, timezone: "Europe/Berlin" }

created_at

TIMESTAMPTZ

users

Column Type Notes

id

UUID PK

tenant_id

UUID FK → tenants

CASCADE

zitadel_user_id

TEXT

UNIQUE per (tenant_id, zitadel_user_id)

email

TEXT

name

TEXT

role

TEXT CHECK

One of 'admin' \| 'employee' \| 'customer'

created_at

TIMESTAMPTZ

Indexes: users_tenant_idx (tenant_id).

Also creates: shiftit_app NOLOGIN role with all-table DML grants and default privileges so future migrations auto-grant.

Migration 0002 — role_definitions, employee_qualifications, locations

role_definitions — tenant-defined role catalogue with optional self-referencing parent for hierarchy chains.

Column Type Notes

id

UUID PK

tenant_id

UUID FK → tenants

name

TEXT

UNIQUE per (tenant_id, name)

parent_role_id

UUID FK → role_definitions

NULL on root; SET NULL on parent delete

level

INT

Hierarchy ordering; higher = more qualified

created_at

TIMESTAMPTZ

employee_qualifications — m:n users ↔ role_definitions.

Column Type Notes

user_id

UUID FK → users

CASCADE

role_definition_id

UUID FK → role_definitions

CASCADE

tenant_id

UUID FK → tenants

CASCADE

granted_at

TIMESTAMPTZ

PK (user_id, role_definition_id).

locations — tenant venues with optional Nominatim-geocoded coords.

Column Type Notes

id

UUID PK

tenant_id

UUID FK → tenants

name, street, city, postal_code, notes

TEXT

country

TEXT DEFAULT 'DE'

lat, lng

DOUBLE PRECISION

Filled server-side via NominatimClient::geocode if street + city provided

Migration 0003 — events, shifts, shift_slots, shift_assignments

events — date-range parents of shifts.

Column Type Notes

id

UUID PK

tenant_id

UUID FK → tenants

name, description, notes, location_detail

TEXT

location_id

UUID FK → locations

SET NULL on delete

start_datetime, end_datetime

TIMESTAMPTZ

CHECK end > start

status

TEXT CHECK

'draft' \| 'published' \| 'completed' \| 'cancelled'

source

TEXT CHECK

'admin_created' \| 'form_submission'

form_submission_id

UUID

Soft reference (no FK) — set when source is form_submission

created_at

TIMESTAMPTZ

shifts — concrete time-windowed shifts, optionally parented to an event.

Column Type Notes

id

UUID PK

tenant_id

UUID FK → tenants

event_id

UUID FK → events

SET NULL

name

TEXT

start_datetime, end_datetime

TIMESTAMPTZ

CHECK end > start

status

TEXT CHECK

'draft' \| 'open' \| 'filled' \| 'completed'

claiming_mode

TEXT CHECK

'open' \| 'assigned'

created_at

TIMESTAMPTZ

shift_slots — required role + count per shift.

Column Type Notes

id

UUID PK

shift_id

UUID FK → shifts

CASCADE

role_definition_id

UUID FK → role_definitions

required_count

INT DEFAULT 1

CHECK > 0

notes

TEXT

shift_assignments — actual employee assignments to slots.

Column Type Notes

id

UUID PK

shift_slot_id

UUID FK → shift_slots

CASCADE

user_id

UUID FK → users

status

TEXT CHECK

'pending' \| 'confirmed' \| 'completed' \| 'cancelled'

assigned_by

UUID FK → users

NULL when self-claimed

created_at

TIMESTAMPTZ

UNIQUE (shift_slot_id, user_id) — one user per slot.

Migration 0004 — time_entries, overtime_balances, payout_records

time_entries — recorded actuals per shift assignment.

Column Type Notes

id

UUID PK

tenant_id

UUID FK → tenants

user_id

UUID FK → users

shift_assignment_id

UUID FK → shift_assignments

UNIQUE — idempotent on assignment

scheduled_start, scheduled_end

TIMESTAMPTZ

Snapshot of the shift window

actual_hours_minutes

INT

CHECK >= 0

created_at

TIMESTAMPTZ

overtime_balances — running per-user balance.

Column Type Notes

id

UUID PK

tenant_id

UUID FK → tenants

user_id

UUID FK → users

UNIQUE per (tenant_id, user_id)

balance_minutes

INT

Can go negative if payouts exceed accrual

updated_at

TIMESTAMPTZ

payout_records — admin-recorded payouts (subtractions from balance).

Column Type Notes

id

UUID PK

tenant_id

UUID FK → tenants

user_id

UUID FK → users

amount_minutes

INT

CHECK > 0

notes

TEXT

created_by

UUID FK → users

The admin who recorded it

created_at

TIMESTAMPTZ

Migration 0005 — customer_forms, form_submissions, event_documents

customer_forms — admin-defined forms exposed at /f/{slug}.

Column Type Notes

id

UUID PK

tenant_id

UUID FK → tenants

name

TEXT

Internal label

customer_name

TEXT

Displayed customer-facing

assigned_customer_user_id

UUID FK → users

Optional — if set, only that customer’s JWT can submit

default_location_id

UUID FK → locations

SET NULL

slug

UUID UNIQUE

Public URL component; itself a UUID for unguessability

fields

JSONB

[{ id, type, label, required, options? }] — see forms::model::FormField

is_active

BOOLEAN

Soft-disable forms without deleting them

form_submissions — customer-submitted payloads.

Column Type Notes

id

UUID PK

form_id

UUID FK → customer_forms

CASCADE

tenant_id

UUID FK → tenants

submitted_by

UUID FK → users

NULL if anonymous (not currently a path)

data

JSONB

Field-id-keyed map matching customer_forms.fields

status

TEXT CHECK

'new' \| 'reviewed' \| 'converted' \| 'rejected'

event_id

UUID FK → events

Set when admin promotes submission to event

submitted_at

TIMESTAMPTZ

event_documents — files attached to events (uploaded to RustFS).

Column Type Notes

id

UUID PK

event_id

UUID FK → events

CASCADE

tenant_id

UUID FK → tenants

name

TEXT

Display name

file_key

TEXT UNIQUE

S3 key

content_type

TEXT

size_bytes

BIGINT

uploaded_by

UUID FK → users

Migration 0006 — customer_forms_lookup_by_slug function

SECURITY DEFINER SQL function that returns a customer form by slug, bypassing RLS. Used by forms::routes::get_form_for_customer to resolve slug → tenant before the JWT-tenant guard runs. The handler then verifies the JWT’s tenant matches the returned tenant_id. This is the only RLS-bypass in the codebase.

Migration 0007 — notifications, push_subscriptions

notifications — in-app inbox.

Column Type Notes

id

UUID PK

tenant_id

UUID FK → tenants

user_id

UUID FK → users

CASCADE

type

TEXT CHECK

'shift_posted' \| 'shift_assigned' \| 'shift_changed' \| 'event_submitted' \| 'payout_recorded'

payload

JSONB

Type-specific payload (see notifications::model::NotificationPayload)

read_at

TIMESTAMPTZ

NULL = unread

created_at

TIMESTAMPTZ

Indexes: (tenant_id, user_id, created_at DESC) for inbox listing; partial (tenant_id, user_id) WHERE read_at IS NULL for unread badge.

push_subscriptions — Web-Push endpoints.

Column Type Notes

id

UUID PK

user_id

UUID FK → users

CASCADE

tenant_id

UUID FK → tenants

endpoint

TEXT

UNIQUE per (user_id, endpoint)

auth, p256dh

TEXT

Encryption material

Reference relationships

tenants
  ├── users
  │     └── employee_qualifications ─→ role_definitions
  │     └── shift_assignments        ─→ shift_slots ─→ shifts ─→ events ─→ locations
  │     └── time_entries             ─→ shift_assignments
  │     └── overtime_balances
  │     └── payout_records
  │     └── notifications
  │     └── push_subscriptions
  └── customer_forms
        └── form_submissions ─→ events (when promoted)
              └── event_documents

Operator notes

  • Migrations 0001 and 0005 were edited after first apply during foundation work. If you see "migration X was previously applied but has been modified" on cargo run, follow the dev-DB reset in CLAUDE.md.

  • .sqlx/ offline cache is committed. After adding or changing any sqlx query, run cargo sqlx prepare --workspace -- --tests and commit the resulting .sqlx/ changes; CI builds with SQLX_OFFLINE=true.

  • No updated_at columns yet outside overtime_balances. Optimistic concurrency / If-Match headers are a P2 follow-up.