The mobile app and the web apps share one Supabase database. Most database changes live in utopiaspace-core, but the attendance, Post OT, and leave calculation changes are written in the mobile repository, under supabase/migrations.
This matters when you look for a rule: if a database function about clock-in, missed clock-outs, or Post OT is not in core, it is probably here.
Not in core's baseline. Confirm each one is live before relying on it.
| Migration | What it does |
|---|---|
20260904090000_align_hourly_attendance_reset_policy | Makes the hourly missed clock-out check use the same 4:00 AM / 16-hour deadline as the app. |
20260905090000_fix_missed_clock_out_on_completed_attendance | A session with a clock-out is never marked as a missed clock-out. |
20260909100000_post_ot_early_call_in | Early call-in declaration, manager review, and roster write-back for Post OT. |
20260910090000_repair_post_ot_early_call_in_upgrade | Repairs databases that ran an earlier draft of the early call-in work. |
20260910103000_warm_post_ot_early_call_roster | Lets the app load the roster for the early call-in check before the first punch. |
20260910120000_notify_post_ot_manager_review_ready | Notifies the manager when a Post OT item is ready to review. |
20260923090000_post_ot_raw_punch_and_early_arrival_notice | Post OT counts from the actual clock-in. Retires the early call-in review and adds the early arrival notice. See Overtime. |
Already part of core's baseline.
| Area | What was added |
|---|---|
| Attendance | Clock location columns, two sessions per day, the session ID from the phone, geofence, the 4:00 AM / 16-hour reset deadline, sync status and monitoring, missed clock-out corrections, the 07:00 reminder, and clearing the missed flag on a late clock-out. |
| Post OT | Post OT claims, raw OT minutes, the claimable hours fix, matching the request method to OT Treatment, and the automatic Post OT claim when a missed clock-out correction is approved. |
| Leave | The General Leave calculation (cascade v1, then v2), the one-request-per-date rule, and eligibility by working shift. |
Lessons recorded in the mobile repository's design notes.
pg_get_functiondef. Production can hold changes that no file in the repository contains, and replacing it would undo them.
create table if not exists skips a table that already exists, including its old constraints. It is not a safe way to upgrade a table.
create or replace function only replaces a function with the same argument list. A new argument creates a second function, which can make calls ambiguous.
Test against a disposable database, never the shared development or production database. The Post OT tests run the real migration in an in-memory Postgres (PGlite).
Notification code inside a trigger must never make the main action fail. A clock-in must still succeed if its notification cannot be written.
When a function changes, regenerate core's database types after it is deployed.