Skip to content

STORY-F-009: V2Repo MyXQL connection to admin_central + admin_atslive (read-only)

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

User Story

As a Lead Developer (Strangler Fig migration operator), I want Finnest able to read admin_central and admin_atslive MySQL databases via a dedicated read-only V2Repo Ecto repo, so that Sprint 2's Week 2 decision gate passes on the MyXQL path, and subsequent migration stories (Sprint 3+) can safely read v2 data without risk of accidental writes.

Description

Background

ADR-010-F mandates Strangler Fig migration from ASG Central v2 MySQL databases. The architecture (AR-05, DA-01, DA-02) requires a read-only Ecto repo for v2 databases — enforced at connection level (DB user has SELECT only) AND Ecto level (read_only: true).

This story proves the path works. It's the single highest-risk item in the Week 2 decision gate — if MyXQL can't reliably connect to admin_central through its Australian DNS with correct credentials, the entire Elixir Direct plan stalls.

Scope

In scope:

  • Add {:myxql, "~> 0.7"} to finnest_core deps
  • Finnest.V2Repo module — use Ecto.Repo, otp_app: :finnest_core, adapter: Ecto.Adapters.MyXQL, read_only: true
  • Finnest.V2AtsRepo module (or single V2Repo with two dynamic connections — evaluate in spike) — reads admin_atslive
  • config/runtime.exs wiring both repos; env vars FINNEST_V2_CENTRAL_URL, FINNEST_V2_ATSLIVE_URL; secrets from Bitwarden via bws
  • Add both repos to the Finnest.Core.Application supervision tree (start order: after primary Repo)
  • Read-only DB credentials — ops provisions a MySQL user with SELECT ONLY on all tables in admin_central + admin_atslive; password in Bitwarden SM
  • Sample read-only schemas: Finnest.V2.Candidate (mirrors admin_central.candidates with explicit fields per DA-02 — not wildcard), Finnest.V2.JobOrder, Finnest.V2.Office — just enough to prove joins work
  • Architecture test v2repo_read_only_test.exs: attempt Finnest.V2Repo.insert/update/delete → returns {:error, :read_only} (Ecto level) or raises (preferred — test both)
  • Prepare_query hook (F-008) must skip V2Repo queries — the hook is only registered on primary Finnest.Repo, not V2Repo. Confirm.
  • Add connection health check to /ready endpoint (F-004) — extends to verify V2Repo reachable too
  • Document credential provisioning steps in CLAUDE.md: how to get V2 read-only user from ops + add to Bitwarden

Out of scope:

  • Full schema mirrors (only enough for spike; real mirrors land in Migration Phase 1+ per ADR-010-F)
  • Migration workers / Oban jobs that read V2 and write Finnest (Sprint 3+ or post-go-live Migration Phase 1)
  • ID mapping table population (lands with first real migration worker)
  • Admin_central write-back (PRD E6.1 — lands with Scout placement flow in Scout+Verify go-live)

Technical Notes

  • MyXQL vs MyCompass: use MyXQL (maintained by Plataformatec/DockYard team; better Elixir integration)
  • Connection pooling: 5 connections per repo is sufficient for spike; tune at Phase 2 if needed
  • admin_central is hosted somewhere (likely RDS MySQL or on-prem). Get hostname + port + creds from ops. If TLS required, configure ssl: true + ssl_opts: [verify: :verify_peer, cacerts: ...] (SE-15 requires TLS for internal DB connections)
  • Read-only enforcement at Ecto layer: read_only: true in Repo config prevents insert/1, update/1, delete/1 calls from compiling through. Belt-and-braces with DB-level SELECT grant.
  • Explicit schema fields (DA-02): every Finnest.V2.* schema declares each field explicitly with correct type. No field :any_field, :any wildcards.
  • The V2 databases have INT primary keys; Finnest uses UUIDs — id_mappings table bridge is Phase 1 concern, not this story
  • Connection string format: mysql://readonly_user:password@host:3306/admin_central
  • If admin_central goes offline during a Finnest request, the request should not hang — use short connection timeouts (connect_timeout: 5_000, queue_target: 50, queue_interval: 1_000) and graceful fallback

Dependencies

  • Blocked by: STORY-F-003 (Repo + supervision), STORY-F-005 (Bitwarden secrets)
  • Blocks: Migration Phase 1 stories (not in Phase 0 scope)

Acceptance Criteria

  • {:myxql, ...} added to deps; mix deps.get succeeds
  • Finnest.V2Repo module defined with read_only: true
  • Finnest.V2AtsRepo module (or dynamic connection to atslive) defined
  • Both repos added to supervision tree; app starts cleanly with V2Repo disabled (V2 DB creds absent in dev) AND enabled (creds present)
  • Spike: Finnest.V2Repo.aggregate(Finnest.V2.Candidate, :count) returns a non-nil integer against real admin_central (proves MyXQL connection works)
  • Spike: Finnest.V2AtsRepo.get(Finnest.V2.Candidate, 1) returns a candidate record OR nil if id 1 doesn't exist (proves atslive connection works)
  • Attempt Finnest.V2Repo.insert(%Finnest.V2.Candidate{...}) → compile error OR runtime error with clear "read_only" message
  • DB user confirmed SELECT only via SHOW GRANTS — documented in sprint report
  • TLS connection working if required by admin_central/atslive (SE-15)
  • Connection timeout configured — admin_central down → request fails in <5s, not hangs
  • Architecture test v2repo_read_only_test.exs passes
  • /ready endpoint returns 200 when all 3 repos (Repo, V2Repo, V2AtsRepo) reachable; 503 if any down
  • Primary Repo prepare_query does NOT apply to V2Repo queries — confirmed via targeted test
  • Documentation update: CLAUDE.md section on "V2 database access" with credential provisioning steps

Testing Requirements

  • Unit: MockV2Repo for non-integration tests (Ecto.Adapters.Test)
  • Integration: real connection to staging copy of admin_central (ops provides; local dev may skip)
  • Error: bad credentials → clear error; no silent retry storm
  • Security: deliberate V2Repo.insert attempt → rejected; documented in PR
  • Performance: 100 sequential V2Repo.get/2 calls complete in <5s total on integration

References