Schedule PostgreSQL Schema

Full PostgreSQL schema design for Schedule Core collections, including DaysStructure and Recurrent Templates. Part of the RTDB → PostgreSQL migration.

Context

The Schedule module currently stores all data in Firebase Realtime Database (RTDB) across ~15 top-level collections. RTDB's deeply nested JSON structure served initial development well but creates pain at scale:

No joins — reading a week's schedule requires fan-out reads across WeeklySchedule, ScheduleDrafts, ManualShifts, OpenShifts, WeeklyScheduleSettings, and more.
No aggregation — labor cost projections, shift counts, and schedule state are pre-computed and stored as denormalized copies (ScheduleDates, allPositions).
No constraints — data integrity is enforced only in application code, leading to orphaned records and inconsistent state.

This schema is the PostgreSQL replacement. All 13 tables are designed to be created in a single migration.

Key Design Decisions

1. Flatten the deeply nested RTDB structure

RTDB stores shifts as WeeklySchedule/{companyId}/{date}/{employeeId}/{positionId}/{subcategoryId}/{shiftKey} — 6 levels deep. In PostgreSQL this becomes a single shifts table with those path segments as columns.

2. Unify WeeklySchedule / ScheduleDrafts / ManualShifts

All three have identical shift structure. Instead of 3 tables, use a single shifts table with a status enum (draft, published, manual) to distinguish lifecycle state. This eliminates data duplication and simplifies queries.

3. Use composite indexes over separate tables for settings

WeeklyScheduleSettings is a bag of position-ID sets. Model as a schedule_week_positions join table rather than JSONB, enabling efficient queries.

4. `BIGSERIAL` for PKs, keep RTDB keys as reference

All writes go through Cloud Functions (single backend), so there's no distributed ID generation need. BIGSERIAL (auto-increment BIGINT) is preferred over UUID:

8 bytes vs 16 bytes — smaller indexes, faster joins
Sequential inserts are B-tree friendly (no page splits)
Simpler to debug (shift #42 vs 3a7f-...)

RTDB push keys stored in a legacy_id column where needed for migration/backfill.

Table Definitions

1. `schedule_weeks`

The anchor entity — one row per company per week.

CREATE TABLE schedule_weeks (
  id              BIGSERIAL PRIMARY KEY,
  company_id      TEXT NOT NULL,                    -- RTDB company push key
  week_start      DATE NOT NULL,                    -- Monday of the week
  closed_days     DATE[] NOT NULL DEFAULT '{}',     -- dates marked closed during generation
  created_by      TEXT NOT NULL,
  updated_by      TEXT NOT NULL,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),

  UNIQUE (company_id, week_start)
);

CREATE INDEX idx_schedule_weeks_company ON schedule_weeks (company_id, week_start);

Maps from: WeeklyScheduleSettings/{companyId}/{weekStart} (the closedDays field + anchor for position tracking).

2. `schedule_week_positions`

Tracks which positions are generated/published/manual for a given week. Replaces generatedPositions, publishedPositions, manuallyScheduledPositions, allPositions from WeeklyScheduleSettings.

CREATE TYPE position_status AS ENUM ('generated', 'published', 'manual');

CREATE TABLE schedule_week_positions (
  id                BIGSERIAL PRIMARY KEY,
  schedule_week_id  BIGINT NOT NULL REFERENCES schedule_weeks(id) ON DELETE CASCADE,
  position_id       TEXT NOT NULL,                  -- RTDB position push key
  status            position_status NOT NULL,
  created_by        TEXT NOT NULL,
  updated_by        TEXT NOT NULL,
  created_at        TIMESTAMPTZ NOT NULL DEFAULT now(),

  UNIQUE (schedule_week_id, position_id, status)
);

CREATE INDEX idx_swp_week ON schedule_week_positions (schedule_week_id);
CREATE INDEX idx_swp_status ON schedule_week_positions (schedule_week_id, status);

Derived state: scheduleState is computed from this table:

Any row with status = 'published' → 'posted'
Else any row with status = 'generated' → 'draft'
Else any row with status = 'manual' → 'manual'

The allPositions array from RTDB is simply SELECT DISTINCT position_id FROM schedule_week_positions WHERE schedule_week_id = ?.

3. `shifts`

Unified table for all shift data (replaces WeeklySchedule, ScheduleDrafts, ManualShifts).

CREATE TYPE shift_status AS ENUM ('draft', 'published', 'manual');

CREATE TABLE shifts (
  id              BIGSERIAL PRIMARY KEY,
  company_id      TEXT NOT NULL,
  date            DATE NOT NULL,
  employee_id     TEXT NOT NULL,                    -- RTDB employee push key
  position_id     TEXT NOT NULL,                    -- FK conceptually -> Companies/.../jobs
  subcategory_id  TEXT NOT NULL,                    -- FK conceptually -> .../subcategories
  status          shift_status NOT NULL,

  start_minutes   SMALLINT NOT NULL,                -- minutes from midnight (0-1439)
  end_minutes     SMALLINT,                         -- null if open-ended
  legacy_id       TEXT NOT NULL,                     -- UUID v4 shift identifier from RTDB
  on_hold         BOOLEAN NOT NULL DEFAULT false,
  note            TEXT,
  is_manual       BOOLEAN,
  is_recurrent    BOOLEAN,

  created_by      TEXT NOT NULL,
  updated_by      TEXT NOT NULL,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),

  UNIQUE (company_id, date, employee_id, position_id, subcategory_id, status, start_minutes)
);

-- Primary query: all shifts for a company in a date range with a given status
CREATE INDEX idx_shifts_company_date_status ON shifts (company_id, date, status);
-- Employee-centric queries (user shifts endpoint)
CREATE INDEX idx_shifts_employee_date ON shifts (employee_id, date);
-- Position-centric queries (delete/unpublish by position)
CREATE INDEX idx_shifts_position ON shifts (company_id, position_id, date);

Maps from: WeeklySchedule, ScheduleDrafts, ManualShifts — all three with identical leaf structure.

4. `open_shifts`

One row per employee slot. When claimed, the row is deleted and a regular shift is created instead.

CREATE TYPE open_shift_period AS ENUM ('morning', 'evening');

CREATE TABLE open_shifts (
  id                BIGSERIAL PRIMARY KEY,
  company_id        TEXT NOT NULL,
  date              DATE NOT NULL,a
  position_id       TEXT NOT NULL,
  subcategory_id    TEXT NOT NULL,
  start_minutes     SMALLINT NOT NULL,
  end_minutes       SMALLINT,
  period            open_shift_period,
  on_hold           BOOLEAN NOT NULL DEFAULT false,
  created_by        TEXT NOT NULL,
  updated_by        TEXT NOT NULL,
  created_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at        TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_open_shifts_company_date ON open_shifts (company_id, date);

Maps from: OpenShifts/{companyId}/{date}/{positionId}/{subcategoryId}/{shiftKey} — each numberOfEmployees + numberOfEmployeesHold counter is expanded into individual rows with on_hold flag.

5. `schedule_modifications`

Recommendation: Replace this table with shifts_history, open_shifts_history, and schedule_week_positions_history tables that capture full before/after row state on every change. This gives richer data (point-in-time reconstruction, implicit operation type from old/new values) and eliminates the custom action enum. Conflict detection still works by querying shifts_history WHERE company_id = ? AND changed_at > ?. Batch business events (publish, unpublish, position delete) would remain in schedule_audit_log.

Undo / restore support: With history on all three tables, an accidentally deleted schedule can be fully reconstructed — query each history table for rows where the operation was a delete, filter by company and date range, and re-insert the original state.

CREATE TYPE schedule_mod_action AS ENUM (
  'shift_add', 'shift_delete', 'shift_update', 'shift_move', 'shift_note',
  'open_shift_create', 'open_shift_update', 'open_shift_assign',
  'open_shift_delete', 'open_shift_delete_all',
  'position_delete', 'publish', 'unpublish'
);

CREATE TABLE schedule_modifications (
  id              BIGSERIAL PRIMARY KEY,
  company_id      TEXT NOT NULL,
  shift_id        BIGINT REFERENCES shifts(id) ON DELETE SET NULL,  -- null for batch actions (publish, unpublish, position_delete)
  created_by      TEXT NOT NULL,
  action          schedule_mod_action NOT NULL,
  position_ids    TEXT[],                            -- only on publish/unpublish actions
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),

  legacy_id       TEXT                               -- RTDB push key for migration
);

-- Conflict detection query: modifications after a timestamp
CREATE INDEX idx_smod_company_ts ON schedule_modifications (company_id, created_at);
CREATE INDEX idx_smod_shift ON schedule_modifications (shift_id) WHERE shift_id IS NOT NULL;

Maps from: ScheduleModifications/{companyId}/{weekStart}/{modificationId}

6. `schedule_audit_log`

CREATE TYPE audit_action AS ENUM ('create', 'publish', 'unpublish', 'delete');

CREATE TABLE schedule_audit_log (
  id                    BIGSERIAL PRIMARY KEY,
  company_id            TEXT NOT NULL,
  week_start            DATE NOT NULL,
  created_by            TEXT NOT NULL,
  action                audit_action NOT NULL,
  created_positions     TEXT[],                      -- on 'create'
  published_positions   TEXT[],                      -- on publish/unpublish/delete
  positions_to_publish  TEXT[],                      -- on 'publish'
  unpublished_positions TEXT[],                      -- on 'unpublish'
  deleted_positions     TEXT[],                      -- on 'delete'
  is_manual             BOOLEAN,                     -- on 'publish'
  created_at            TIMESTAMPTZ NOT NULL DEFAULT now(),

  legacy_id             TEXT
);

CREATE INDEX idx_audit_company_week ON schedule_audit_log (company_id, week_start);

Maps from: ScheduleAuditLog/{companyId}/{weekStart}/{auditId}

7. `weekly_logs`

CREATE TABLE weekly_logs (
  company_id      TEXT NOT NULL,
  date            DATE NOT NULL,
  text            TEXT NOT NULL,
  created_by      TEXT NOT NULL,
  updated_by        TEXT NOT NULL,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),

  PRIMARY KEY (company_id, date)
);

Maps from: WeeklyLog/{companyId}/{date}

8. `day_periods`

Period configuration (morning/evening) per company per day of week. Part of Companies/{companyId}/daysStructure.

CREATE TYPE day_of_week AS ENUM (
  'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday'
);
CREATE TYPE period_type AS ENUM ('morning', 'evening');

CREATE TABLE day_periods (
  id              BIGSERIAL PRIMARY KEY,
  company_id      TEXT NOT NULL,
  day             day_of_week NOT NULL,
  period          period_type NOT NULL,
  start_minutes   SMALLINT NOT NULL,               -- minutes from midnight (morning default 300, evening default 840)
  revenue         NUMERIC NOT NULL DEFAULT 0,
  per_client      NUMERIC NOT NULL DEFAULT 0,
  created_by      TEXT NOT NULL,
  updated_by      TEXT NOT NULL,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),

  UNIQUE (company_id, day, period)
);

CREATE INDEX idx_day_periods_company ON day_periods (company_id);

Maps from: Companies/{companyId}/daysStructure/{DayOfWeek}/morning and .../evening

9. `day_structure_shifts`

Shift templates per company per day per position. Each row is a shift time slot with staffing requirements per subcategory.

CREATE TABLE day_structure_shifts (
  id              BIGSERIAL PRIMARY KEY,
  company_id      TEXT NOT NULL,
  day             day_of_week NOT NULL,
  position_id     TEXT NOT NULL,                   -- FK conceptually -> Companies/.../jobs
  start_minutes   SMALLINT NOT NULL,               -- minutes from midnight
  end_minutes     SMALLINT,                        -- null if open-ended
  legacy_id       TEXT,                            -- RTDB push key for migration
  created_by      TEXT NOT NULL,
  updated_by      TEXT NOT NULL,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),

  UNIQUE (company_id, day, position_id, start_minutes)
);

CREATE INDEX idx_dss_company_day ON day_structure_shifts (company_id, day);
CREATE INDEX idx_dss_company_position ON day_structure_shifts (company_id, position_id);

Maps from: Companies/{companyId}/daysStructure/{DayOfWeek}/shifts/{positionId}[index] — the start, end, key fields.

10. `day_structure_staffing`

Per-subcategory staffing requirements for each shift template. The dynamic {subcategoryId} keys on the shift item become rows here.

CREATE TABLE day_structure_staffing (
  id                      BIGSERIAL PRIMARY KEY,
  day_structure_shift_id  BIGINT NOT NULL REFERENCES day_structure_shifts(id) ON DELETE CASCADE,
  subcategory_id          TEXT NOT NULL,
  required                SMALLINT NOT NULL DEFAULT 0,
  hold                    SMALLINT NOT NULL DEFAULT 0,
  created_by              TEXT NOT NULL,
  updated_by              TEXT NOT NULL,
  created_at              TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at              TIMESTAMPTZ NOT NULL DEFAULT now(),

  UNIQUE (day_structure_shift_id, subcategory_id)
);

CREATE INDEX idx_dss_staffing_shift ON day_structure_staffing (day_structure_shift_id);

Maps from: The dynamic {subcategoryId}: number | { required, hold } keys on DayStructureShiftItem. When the value is a plain number, it maps to required = number, hold = 0. When it's { required, hold }, both fields are populated.

11. `recurrent_templates`

Per-employee rotating schedule templates. One row per employee.

CREATE TABLE recurrent_templates (
  id              BIGSERIAL PRIMARY KEY,
  company_id      TEXT NOT NULL,
  employee_id     TEXT NOT NULL,                   -- RTDB employee push key
  period_weeks    SMALLINT NOT NULL DEFAULT 1,     -- rotation period in weeks
  starting_week   DATE NOT NULL,                   -- rotation anchor Monday
  created_by      TEXT NOT NULL,
  updated_by      TEXT NOT NULL,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),

  UNIQUE (company_id, employee_id)
);

CREATE INDEX idx_rt_company ON recurrent_templates (company_id);

Maps from: Templates/{companyId}/{employeeId} — the period and startingWeek fields.

12. `recurrent_template_weeks`

One row per week in the rotation.

CREATE TABLE recurrent_template_weeks (
  id                    BIGSERIAL PRIMARY KEY,
  recurrent_template_id BIGINT NOT NULL REFERENCES recurrent_templates(id) ON DELETE CASCADE,
  week_index            SMALLINT NOT NULL,         -- 0-based index within rotation
  is_complete           BOOLEAN NOT NULL DEFAULT false,
  created_by            TEXT NOT NULL,
  updated_by            TEXT NOT NULL,

  UNIQUE (recurrent_template_id, week_index)
);

CREATE INDEX idx_rtw_template ON recurrent_template_weeks (recurrent_template_id);

Maps from: Templates/{companyId}/{employeeId}/weeks/{weekId} — the weekIndex and isComplete fields.

13. `recurrent_template_shifts`

Individual shift entries within a template week, per day.

CREATE TYPE recurrent_shift_mode AS ENUM ('shift', 'period', 'day_off');

CREATE TABLE recurrent_template_shifts (
  id                          BIGSERIAL PRIMARY KEY,
  recurrent_template_week_id  BIGINT NOT NULL REFERENCES recurrent_template_weeks(id) ON DELETE CASCADE,
  day                         day_of_week NOT NULL,
  position_id                 TEXT NOT NULL,
  subcategory_id              TEXT NOT NULL,
  on_hold                     BOOLEAN NOT NULL DEFAULT false,

  -- Mutually exclusive mode
  mode                        recurrent_shift_mode NOT NULL,
  selected_shift              TEXT,                   -- set when mode = 'shift'
  selected_period             period_type,            -- set when mode = 'period'
  -- mode = 'day_off' needs no extra field

  legacy_id                   TEXT,                   -- RTDB key for migration
  created_by                  TEXT NOT NULL,
  updated_by                  TEXT NOT NULL,

  CONSTRAINT chk_mode CHECK (
    (mode = 'shift' AND selected_shift IS NOT NULL AND selected_period IS NULL) OR
    (mode = 'period' AND selected_period IS NOT NULL AND selected_shift IS NULL) OR
    (mode = 'day_off' AND selected_shift IS NULL AND selected_period IS NULL)
  )
);

CREATE INDEX idx_rts_week ON recurrent_template_shifts (recurrent_template_week_id);
CREATE INDEX idx_rts_day ON recurrent_template_shifts (recurrent_template_week_id, day);

Maps from: Templates/{companyId}/{employeeId}/weeks/{weekId}/days/{DayOfWeek}/{shiftKey} — the RecurrentShift type with its mutually exclusive selectedShift | selectedPeriod | isDayOff fields, modeled as a mode enum with a CHECK constraint.

Collections NOT Migrated

Collection	Reason
`ScheduleDates`	Denormalized summary for the week slider. Derived from `shifts` with a `GROUP BY` query — no dedicated table needed. See query below.
`ScheduleEditors`	Presence system using `.onDisconnect()`. RTDB-native feature with no clean PostgreSQL equivalent. Stays in RTDB.
`PendingAvailabilities`	Owned by the Requests module. Consumed by a daily cron. Will migrate with the Requests module, not Schedule.
`LaborCostSettings`	Owned by the LaborCost module. Only reference from Schedule is a cleanup null-write on delete. Will migrate with LaborCost.

`ScheduleDates` replacement query

SELECT DISTINCT date,
  BOOL_OR(status = 'published') AS is_posted,
  BOOL_OR(status = 'draft')     AS is_draft,
  BOOL_OR(status = 'manual')    AS is_manual
FROM shifts
WHERE company_id = ? AND date BETWEEN ? AND ?
GROUP BY date;

The existing idx_shifts_company_date_status index makes this fast.

Relationships (ER Overview)

Schedule lifecycle (linked by company_id + date range, no FK):

Other standalone tables (weekly_logs, day_periods) are scoped to a company but have no direct relationship to the lifecycle tables

Summary

#	Table	Maps From (RTDB)
1	`schedule_weeks`	`WeeklyScheduleSettings/{companyId}/{weekStart}`
2	`schedule_week_positions`	`WeeklyScheduleSettings` position arrays
3	`shifts`	`WeeklySchedule` + `ScheduleDrafts` + `ManualShifts` (unified)
4	`open_shifts`	`OpenShifts/{companyId}/{date}/...`
5	`schedule_modifications`	`ScheduleModifications/{companyId}/{weekStart}/...`
6	`schedule_audit_log`	`ScheduleAuditLog/{companyId}/{weekStart}/...`
7	`weekly_logs`	`WeeklyLog/{companyId}/{date}`
8	`day_periods`	`Companies/{companyId}/daysStructure/{Day}/morning\|evening`
9	`day_structure_shifts`	`Companies/{companyId}/daysStructure/{Day}/shifts/{positionId}[i]`
10	`day_structure_staffing`	Dynamic `{subcategoryId}` keys on shift items
11	`recurrent_templates`	`Templates/{companyId}/{employeeId}`
12	`recurrent_template_weeks`	`Templates/.../weeks/{weekId}`
13	`recurrent_template_shifts`	`Templates/.../weeks/{weekId}/days/{Day}/{shiftKey}`

Context​

Key Design Decisions​

1. Flatten the deeply nested RTDB structure​

2. Unify WeeklySchedule / ScheduleDrafts / ManualShifts​

3. Use composite indexes over separate tables for settings​

4. BIGSERIAL for PKs, keep RTDB keys as reference​

Table Definitions​

1. schedule_weeks​

2. schedule_week_positions​

3. shifts​

4. open_shifts​

5. schedule_modifications​

6. schedule_audit_log​

7. weekly_logs​

8. day_periods​

9. day_structure_shifts​

10. day_structure_staffing​

11. recurrent_templates​

12. recurrent_template_weeks​

13. recurrent_template_shifts​

Collections NOT Migrated​

ScheduleDates replacement query​

Relationships (ER Overview)​

Summary​