Janady13/missionmatch / db/migrations/0057_tasks_and_timezone.sql

Source identity

Field	Recorded value
Source type	GitHub source
Original record	Janady13/missionmatch / db/migrations/0057_tasks_and_timezone.sql
Date	Not specified in source
Repository	Janady13/missionmatch
Revision	720d47fae476b0d38477ae4401697d372b1ea2bc
Branch	feature/opportunity-to-outcome
Path or archive member	db/migrations/0057_tasks_and_timezone.sql
Original SHA-256	184efeb47eb8bfa22874e6253001c32d5a2716e6cff1338ec8a70251a063b771
Git blob SHA	ee60ded6fc0653e16ed3500a94ab45d84d603011
Reading-copy SHA-256	184efeb47eb8bfa22874e6253001c32d5a2716e6cff1338ec8a70251a063b771

Original source ↗

Every recorded occurrence

These 2 records contain identical original source bytes. Their paths and revisions remain part of the archive.

Original record	Branch or archive	Revision / identifier	Original location
Janady13/missionmatch / db/migrations/0057_tasks_and_timezone.sql	feature/opportunity-to-outcome	720d47fae476b0d38477ae4401697d372b1ea2bc	Open original
Janady13/missionmatch / db/migrations/0057_tasks_and_timezone.sql	feature/aioe-upgrade	2178e7298d0e5d9af83afa12402fd587f0ba6ed8	Open original

Recovered source transcript

Download complete source text ↓165 lines · 8.3 KB

-- +goose Up
-- Gives the organization a timezone and makes the task queue survive its owner.
--
-- Four defects, each reproduced against a live database first.
--
-- A. Reopening a completed task was impossible. tasks_completed_chk requires completed_at to be NULL
--    unless the status is done, and nothing cleared it, so UPDATE tasks SET status='open' failed with a
--    constraint violation on a row the user had every right to reopen.
--
-- B. Paging was not deterministic. The queue orders by due_at, and tasks sharing a due date or all
--    lacking one have no tiebreak, so two pages could show the same task twice and skip another.
--
-- C. There was no timezone anywhere in the schema, and this one is the widest. Reproduced:
--
--      an 8pm Eastern log is UTC 2026-07-21
--      UTC day says 2026-07-21 but the recruiter worked 2026-07-20
--
--    Every daily number for a US firm would attribute evening work to the next day. That is wrong on the
--    task queue today, and it would be wrong on every rollup Phase 17 computes, which is why it is fixed
--    here rather than discovered there.
--
-- D. Deleting a recruiter deleted their open tasks. Unlike an activity, a task is work that still needs
--    doing, so cascading it destroys the work rather than the record of it. Reproduced: three open tasks
--    became zero.

-- ---------------------------------------------------------------------------
-- C. One timezone per organization.
--
-- Per organization rather than per user on purpose. A staffing firm operates on one business day: if two
-- recruiters in different zones saw different "today" numbers for the same desk, neither could be
-- reconciled against the other or against an invoice. Per user display preferences can be layered later
-- without changing what a day MEANS.
--
-- The default is America/New_York rather than UTC because this product serves US federal contracting and
-- UTC is wrong for every user of it. A wrong default that is obviously wrong beats one that looks right.
ALTER TABLE organizations ADD COLUMN time_zone text NOT NULL DEFAULT 'America/New_York';

-- Validated by trigger rather than CHECK: the set of zone names lives in a catalog view, so the test is
-- not immutable and cannot appear in a constraint. Attempting the cast is the only honest validation,
-- because it asks the same question the query will ask later.
-- +goose StatementBegin
CREATE OR REPLACE FUNCTION rm_validate_time_zone() RETURNS trigger AS $$
BEGIN
    IF NEW.time_zone IS NULL OR btrim(NEW.time_zone) = '' THEN
        RAISE EXCEPTION 'a time zone is required' USING ERRCODE = '22023';
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_timezone_names WHERE name = NEW.time_zone) THEN
        RAISE EXCEPTION '% is not a known IANA time zone', NEW.time_zone USING ERRCODE = '22023';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- +goose StatementEnd

CREATE TRIGGER organizations_validate_time_zone
    BEFORE INSERT OR UPDATE OF time_zone ON organizations
    FOR EACH ROW EXECUTE FUNCTION rm_validate_time_zone();

-- rm_org_tz returns the calling tenant's zone, for day bucketing in any query.
--
-- STABLE, not IMMUTABLE: it reads a table, and marking it immutable would let the planner cache a value
-- across a change. Falls back to UTC only when there is no tenant context, which is a state that returns
-- no rows anyway.
-- +goose StatementBegin
CREATE OR REPLACE FUNCTION rm_org_tz() RETURNS text AS $$
    SELECT COALESCE(
        (SELECT time_zone FROM organizations
          WHERE id = NULLIF(current_setting('app.current_org_id', true), '')::uuid),
        'UTC');
$$ LANGUAGE sql STABLE;
-- +goose StatementEnd

GRANT EXECUTE ON FUNCTION rm_org_tz() TO missionmatch_app;

-- Migration 0046 narrowed the app role's write access on organizations to a named column list, so a new
-- column is readable but NOT writable until it is granted explicitly. Verified: without this the API
-- returns "permission denied for table organizations" on a timezone change. updated_at is included
-- because the write stamps it, and a grant that covers the column but not its timestamp fails in exactly
-- the same way. Naming both keeps the narrowing intact rather than widening back to the whole table.
GRANT UPDATE (time_zone, updated_at) ON organizations TO missionmatch_app;

-- ---------------------------------------------------------------------------
-- D. A task outlives its assignee, unassigned rather than deleted.
--
-- An activity is a record of what happened and a task is work that still needs doing, so the right
-- behaviour differs: history keeps the departed person's name, a queue hands the work to nobody and shows
-- it, loudly, until a human picks it up.
ALTER TABLE tasks ALTER COLUMN assigned_to DROP NOT NULL;
ALTER TABLE tasks DROP CONSTRAINT tasks_organization_id_assigned_to_fkey;
-- The column list is required. A bare SET NULL on a composite key nulls organization_id too, which is
-- NOT NULL, so deleting a user would abort. Migration 0053 repaired twenty three constraints that made
-- exactly that mistake.
ALTER TABLE tasks ADD CONSTRAINT tasks_assigned_to_fkey
    FOREIGN KEY (organization_id, assigned_to) REFERENCES users (organization_id, id)
    ON DELETE SET NULL (assigned_to);

-- Unassigned open work is the queue a manager has to see. Without this index it is a sequential scan on
-- the one read that matters most after someone leaves.
CREATE INDEX idx_tasks_unassigned ON tasks (organization_id, due_at)
    WHERE status = 'open' AND assigned_to IS NULL;

-- ---------------------------------------------------------------------------
-- A. completed_at is maintained by the database, so reopening works from any path.
--
-- Setting it here rather than asking every caller to remember means the constraint can never be violated
-- by a correct status change, and the timestamp is the database clock rather than whichever node happened
-- to serve the request.
-- +goose StatementBegin
CREATE OR REPLACE FUNCTION rm_task_completion() RETURNS trigger AS $$
BEGIN
    IF NEW.status = 'done' THEN
        -- Preserve an explicitly supplied completion time, which matters when a task is closed out
        -- retroactively, and otherwise stamp the moment it was closed.
        NEW.completed_at := COALESCE(NEW.completed_at, now());
    ELSE
        -- Reopening or cancelling clears it. Leaving a stale timestamp would violate the constraint and,
        -- worse, would make a reopened task look finished in any report that reads the column.
        NEW.completed_at := NULL;
    END IF;
    NEW.updated_at := now();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- +goose StatementEnd

CREATE TRIGGER tasks_completion
    BEFORE INSERT OR UPDATE ON tasks
    FOR EACH ROW EXECUTE FUNCTION rm_task_completion();

-- +goose Down
DROP TRIGGER IF EXISTS tasks_completion ON tasks;
DROP FUNCTION IF EXISTS rm_task_completion();
DROP INDEX IF EXISTS idx_tasks_unassigned;
ALTER TABLE tasks DROP CONSTRAINT IF EXISTS tasks_assigned_to_fkey;
-- +goose StatementBegin
-- REFUSE rather than delete. The up made assigned_to nullable and added idx_tasks_unassigned to serve
-- the unassigned queue, so an unassigned task is a first class product state that the application
-- authored, not a migration artifact. Deleting them to satisfy the restored NOT NULL destroys real work,
-- including tasks created long before this migration ran. A down migration may drop what its up added; it
-- may not delete rows the application created.
--
-- The operator decides what those tasks should become. Naming the count makes that decision possible;
-- silently emptying the queue does not.
DO $$
DECLARE n bigint;
BEGIN
    SELECT count(*) INTO n FROM tasks WHERE assigned_to IS NULL;
    IF n > 0 THEN
        RAISE EXCEPTION
            'refusing to roll back: % unassigned task(s) exist and this rollback would delete them. '
            'Assign or close them first, then retry.', n;
    END IF;
END
$$;
-- +goose StatementEnd
ALTER TABLE tasks ALTER COLUMN assigned_to SET NOT NULL;
ALTER TABLE tasks ADD CONSTRAINT tasks_organization_id_assigned_to_fkey
    FOREIGN KEY (organization_id, assigned_to) REFERENCES users (organization_id, id) ON DELETE CASCADE;
DROP FUNCTION IF EXISTS rm_org_tz();
DROP TRIGGER IF EXISTS organizations_validate_time_zone ON organizations;
DROP FUNCTION IF EXISTS rm_validate_time_zone();
REVOKE UPDATE (time_zone, updated_at) ON organizations FROM missionmatch_app;

ALTER TABLE organizations DROP COLUMN IF EXISTS time_zone;