Skip to content

STORY-F-007: Organisation + Office + User schemas + architecture tests

Epic: Core Data Priority: Must Have Story Points: 3 Status: Not Started Assigned To: Unassigned Created: 2026-04-17 Sprint: 2

User Story

As an Org Admin, I want the public.organisations, public.offices, public.users schemas live with full fields, relationships, and soft-delete semantics, so that multi-tenancy is real from Sprint 2 onwards and every subsequent feature has the right foundational data model.

Description

Background

Architecture data.md §Core entities specifies the exact shape of public.organisations, public.users, public.offices. F-006 landed a minimal User to make phx.gen.auth work; this story expands it to the full production shape, adds Organisation + Office, and lays the architecture tests that prevent regressions (every schema has org_id, deleted_at, UUID PK, timestamps).

Scope

In scope:

  • Migration priv/repo/migrations/YYYYMMDD_create_public_core_tables.exs:
  • public.organisations — id UUID, name, slug UNIQUE, abn, industry_profile_id (FK nullable — target table lands in F-015), subscription_tier DEFAULT 'tier1', settings JSONB DEFAULT '{}', irap_classified BOOLEAN DEFAULT false, inserted_at, updated_at, deleted_at
  • public.offices — id UUID, org_id UUID NOT NULL, name, location JSONB (address, lat/lon), timezone VARCHAR, inserted_at, updated_at, deleted_at
  • public.users (expand F-006 schema) — add: role VARCHAR NOT NULL ('director'|'admin'|'staff'|'payroll'|'supervisor'|'worker'|'client_contact'), mfa_enabled BOOLEAN, mfa_method VARCHAR nullable, fido2_credentials JSONB DEFAULT '[]', last_login_at, last_login_ip INET, session_timeout_seconds INTEGER nullable, locked_until TIMESTAMPTZ nullable, email_citext GENERATED ALWAYS AS (LOWER(email)) STORED
  • public.user_offices — pivot: user_id UUID, office_id UUID, is_primary BOOLEAN, PRIMARY KEY (user_id, office_id)
  • Ecto schemas: Finnest.Core.Organisation, Finnest.Core.Office, update Finnest.Core.Users.User, new Finnest.Core.UserOffice
  • Indexes per data.md spec: leading org_id on every operational index; partial WHERE deleted_at IS NULL on active-record queries; unique (org_id, email_citext) WHERE deleted_at IS NULL on users
  • Context modules: Finnest.Core.Organisations, Finnest.Core.Offices with minimal CRUD (create/1, get/1, get!/1, update/2, soft_delete/1, list/1)
  • Cloak vault — Finnest.Core.Vault module using {:cloak, "~> 1.1"} + {:cloak_ecto, "~> 1.2"} with AES-256-GCM; Binary Ecto type for encrypted columns; env var FINNEST_CLOAK_KEY_V1 (seeded in F-005)
  • Apply Cloak to users.mfa_secret_encrypted (backfill F-006 plaintext if any)
  • Architecture tests in test/architecture/:
  • tenant_org_id_test.exs — iterate every Ecto.Schema module under Finnest.*; assert each has :org_id field OR is in allowlist (Organisation, IdMapping, future reference-data schemas)
  • tenant_query_raises_test.exs — Finnest.Core.Tenant.clear(); assert_raise RuntimeError, fn -> Finnest.Repo.all(Finnest.Core.Organisation) end (stub until F-008 implements prepare_query — this test is placeholder, fully enforced after F-008)
  • uuid_primary_key_test.exs — every schema's @primary_key is {:id, :binary_id, autogenerate: true}
  • soft_delete_column_test.exs — every schema has :deleted_at field (allowlist for exceptions)

Out of scope:

  • The Repo.prepare_query full implementation (F-008)
  • Industry profile linking (F-015 adds target table)
  • User role-based authorisation plugs (deferred)
  • DSAR anonymisation cascade (PRD E1.7 AC8 — Migration Phase 1)

Technical Notes

  • citext extension must be enabled — migration execute "CREATE EXTENSION IF NOT EXISTS citext"
  • Email index: unique on (org_id, email_citext) WHERE deleted_at IS NULL — partial index, tenant-scoped
  • role validation: @valid_roles ~w(director admin staff payroll supervisor worker client_contact) in changeset
  • Soft delete: context soft_delete/1 sets deleted_at: DateTime.utc_now(); default queries filter WHERE deleted_at IS NULL
  • The industry_profile_id FK references a table that doesn't exist yet (F-015) — create with references: false + add real FK in F-015 migration
  • Architecture test pattern: for schema <- :application.get_key(:finnest_core, :modules) |> elem(1), function_exported?(schema, :__schema__, 1), do: ...

Dependencies

  • Blocked by: STORY-F-003 (Repo), STORY-F-006 (User base schema)
  • Blocks: STORY-F-008 (prepare_query uses these schemas to test)

Acceptance Criteria

  • Migration runs cleanly (mix ecto.migrate); rollback works (mix ecto.rollback) — CQ-11 reversible
  • public.organisations created with all specified columns; unique slug index active
  • public.offices created; public.user_offices pivot with composite PK
  • public.users expanded; email_citext generated column works
  • Ecto schemas compile; Repo.insert(%Organisation{...}) works for a valid org
  • Cloak vault operational: write a User with mfa_secret_encrypted, confirm stored as BYTEA ciphertext, read back correctly
  • Key FINNEST_CLOAK_KEY_V1 absent at boot → clear error message (not silent NPE)
  • Architecture test tenant_org_id_test.exs passes — asserts every schema has org_id (with minimal allowlist)
  • Architecture test uuid_primary_key_test.exs passes — all schemas use binary_id
  • Architecture test soft_delete_column_test.exs passes — all schemas have deleted_at
  • Tenant-query-raises test is wired (even if it's still a stub awaiting F-008)
  • Multi-tenant integration test: insert Org A with user_a, Org B with user_b; context Users.list(org_a.id) returns only user_a; context Users.list(org_b.id) returns only user_b
  • mix format, credo --strict, dialyzer, mix boundary all green

Testing Requirements

  • Unit tests for each context function (create/get/update/soft_delete/list happy + error paths)
  • Changeset validation tests: invalid role rejected, email format validated, required fields enforced
  • Architecture tests described above
  • Integration: full flow — create org → create office → create user → assign to office with is_primary: true → soft delete user → confirm list excludes

References