Skip to main content

29. Contractor 201-file details

Date: 2026-06-13

Status​

Accepted

Context​

Onboarding collects a full Philippine "201 file" per contractor via a Google Form: legal name parts, date of birth, marital status, full home/bank addresses, bank account name + full account number, e-wallet handles (GCash/PayMaya/PayPal), education history, spouse, dependents, and even swag sizes. The contractors table already carries the operational subset the app reads on every contractor screen (display name, email, phone, address, emergency contact, local_bank_name, bank_account_last4, Wise fields). The remaining ~25 fields had no home, so the form data was being lost on import.

Two questions had to be settled: where to put ~25 mostly-static, sensitive fields, and whether to store the full bank account number (CLAUDE.md's guardrail is "never store full bank accounts — mask to last 4").

Decision​

  • A dedicated 1:1 table, not flat columns or JSONB. Migration 0024 adds contractor_201_details (one row per contractor, contractor_id unique, on delete cascade). This keeps the hot contractors row lean (it is selected on nearly every screen), isolates sensitive family/financial PII behind its own RLS, and avoids the untyped/unindexed weakness of a JSONB blob for a record this sensitive. Operational fields stay on contractors; only the 201-only extras live here.
  • Same security posture as contractors. RLS on. Admins (profiles) get full access scoped to their company; a contractor may select their own 201 row; there is no contractor self-update (writes go through service-role server actions, per ADR-0004 / migration 0009). The audit trigger (app.audit_row) and set_updated_at are attached, matching the other PII/financial tables (migration 0004).
  • Full bank account number is stored here, deliberately overriding the mask-to-last-4 default. The owner funds payouts and needs the full number; contractors.bank_account_last4 is retained as the safe value for general display. The full number lives only in this admin/self-restricted table — never logged, never surfaced on contractor-facing list views. If column-level encryption is later required, bank_account_number is the single column to wrap.
  • Importer + faithful mapping. scripts/import-201-file.mjs matches each form row to an existing contractor by email (personal or the form's "Email Address" column), then upserts the extras keyed on contractor_id (idempotent). It never creates contractors or overwrites their operational columns. Dry-run by default; --commit to write. Source CSVs live under the gitignored /data/ (third-party PII — never committed).

Consequences​

  • The complete 201 file is queryable per contractor, with the full account number available to admins for funding and last-4 still the default for display.
  • Source-data quirks were imported faithfully rather than silently "corrected" (e.g. one DOB entered as a 2025 date, one "degree" field containing a year, one "name on bank statement" field answered "Yes"). These are flagged for human cleanup; the importer is re-runnable once fixed.
  • A new 1:1 extension-table pattern now exists for contractor PII; future onboarding fields of this nature belong here, not bolted onto contractors.
  • src/db/types.ts gains the contractor_201_details types; regenerate after further schema changes.