Project CRUD

Full lifecycle management for construction projects — create, list, view, update, and archive.

Create Project

Admins create projects via a dialog that collects:

• Name (required, 1-255 chars)
• Client (free text)
• Business Unit (dropdown, fetched from org’s business units)
• Location (address string, max 500 chars)
• Beacon ID (external reference)
• Award Date / Completion Date (YYYY-MM-DD)
• Value (amount + 3-char currency code, default AUD)
• Initial Stages (optional, created inline during project creation)

Flow

1. User clicks “New Project” in the projects table
2. Dialog opens, business units loaded via useQuery
3. User fills form, submits
4. Server Action validates with Zod, calls POST /v1/orgs/{orgId}/projects
5. Go handler verifies admin role, creates project with CreateProject sqlc query
6. If stages provided, inserts each via AddProjectStage
7. Returns full project detail
8. Client navigates to /org-dashboard/{orgId}/projects/{projectId}

List Projects

The default dashboard view. Admins see all projects in the org; non-admins see only projects they are assigned to.

Query Strategy

Admin:     ListProjectsByOrgID     + CountProjectsByOrgID
Non-admin: ListProjectsByUserAssignment + CountProjectsByUserAssignment

The user assignment queries use INNER JOIN project_members to filter to assigned projects only.

Filters

Table Columns

Name, Client, Status (badge), Business Unit, Award Date, Completion Date, Member Count.

Row click navigates to the project detail page.

View Project Detail

Fetches the project with its nested stages via the GetProjectByID query, with a LEFT JOIN business_units for the unit name. The detail carries no member list: the Members tab reads GET …/members when it is opened, and person pickers read GET …/members/options.

Tabs

• Overview — All project metadata in a dl grid. Inline edit form toggles between view and edit modes.
• Stages — Append-only stage list with “Add Stage” form. See Project Stages.
• Members — Member list with roles and permissions, loaded when the tab opens. See Project Members.

Update Project

Partial updates via PUT /v1/orgs/{orgId}/projects/{projectId}. Uses the COALESCE pattern in SQL:

UPDATE projects
SET name = COALESCE(sqlc.narg('name'), name),
    geo_location = COALESCE(sqlc.narg('geo_location'), geo_location),
    -- ... all optional fields
WHERE id = @id AND org_id = @org_id AND deleted_at IS NULL
RETURNING *;

Only fields included in the request body are updated. Omitted fields retain their current values.

Inline Edit Flow

1. User clicks “Edit” on the Overview tab
2. View switches to a form pre-filled with current values
3. User modifies fields and clicks “Save Changes”
4. Server Action sends only changed fields to PUT endpoint
5. Go handler validates admin role, runs UpdateProject query
6. Page refreshes with updated data

Archive Project

Soft-delete via DELETE /v1/orgs/{orgId}/projects/{projectId}:

1. User clicks “Archive” button, confirms via browser dialog
2. Server Action calls DELETE endpoint
3. Go handler sets deleted_at = NOW() via SoftDeleteProject query
4. User redirected back to projects list
5. Archived projects excluded from default list (filtered by deleted_at IS NULL)

Archived projects can be viewed via the status=archived filter.

API Endpoints

Security

• Admin-only mutations — Create, update, and delete require master_admin or organisational_manager role.
• Tenant isolation — All queries include AND org_id = $N. Handler verifies JWT org_id matches URL {orgId}.
• Non-admin access — Detail endpoint checks IsUserProjectMember before returning data.
• Soft-delete — No data is permanently removed. deleted_at IS NULL filter applied to all active queries.
• COALESCE updates — Prevents accidental field overwriting during partial updates.

Key Files

What a new project gets automatically

Creating a project performs several best-effort seeds. None of them can fail project creation — each logs a warning and continues.

The join QR

Previously the QR only existed once someone visited Field Users and clicked Show QR code — QR Manager derives the surface from the presence of a field_join_tokens row, so a project nobody had opened Field Users for had no join QR at all.

The seeded token never expires (it uses the same 9999-12-31 sentinel as a manually generated permanent QR) and carries a 7-character invite code, so a field user can either scan or type it. Its internal row label is Project join — auto-created; QR Manager’s displayed label is always Join This Project.

The QR belongs to the project, not to a person. created_by records the admin who created the project, taken from the verified session. It is only a record: the column is nullable and its foreign key to users is ON DELETE SET NULL. Deleting or deactivating that person (or deleting their organisation) never deletes, hides or invalidates the token, so a printed sign keeps working and QR Manager keeps showing the same QR. If the session carries no usable user id, the token is still seeded, with no creator recorded.

Generating another QR does not break this one. QR Manager shows the most recently created token that is neither revoked nor expired, so a later manual generation takes over the display, but the seeded token is not revoked and stays fully redeemable. A sign printed from it keeps working.

Expiry is part of that check for a reason. A time-limited token that has lapsed is skipped rather than displayed: token validation requires expires_at > NOW(), so an expired row would otherwise render a QR that scans and then fails — and, because the newest match wins, it would hide the permanent token sitting behind it.

Existing projects were backfilled by a one-off migration, which gave one permanent token to every live, non-deleted project in an active org that held no active token. Projects in an org with no users were skipped, since at the time created_by could not be empty.