# ADR-006 — Single Shared Ecto Repo with Schema Ownership

**Date:** 2026-04-26
**Status:** Accepted
**Deciders:** Architecture Team

---

## Context

Multiple umbrella apps need to persist data:
- `mw_audit` → audit events
- `mw_auth` → API keys, token revocation
- `mw_router` → routing rules
- `infra_queue` → dead letter queue records
- `gateway_web` → user preferences, dashboard config

Options for managing database access across apps:

| Option | Description |
|--------|-------------|
| **A. Single shared Repo** | One `Ecto.Repo` in `infra_repo`; all apps reference it |
| **B. Per-app Repo** | Each app defines its own `Ecto.Repo` with its own pool |
| **C. Separate databases** | Each app has its own database |

---

## Decision

**Option A — Single `Ecto.Repo` in `infra_repo`.**

Each umbrella app defines its own Ecto schemas under its own namespace but all apps use
`InfraRepo.Repo` as the repo module.

```
infra_repo/
└── lib/
    └── infra_repo/
        ├── repo.ex              ← Ecto.Repo (one pool, one DB)
        └── schemas/
            ├── audit_event.ex   ← owned by mw_audit
            ├── route_rule.ex    ← owned by mw_router
            ├── api_key.ex       ← owned by mw_auth
            └── dead_letter.ex   ← owned by infra_queue

mw_audit/
└── lib/
    └── mw_audit/
        ├── event.ex             ← defines %MwAudit.Event{} using InfraRepo schema
        └── store.ex             ← calls InfraRepo.Repo.insert(...)
```

Migrations live exclusively in `infra_repo/priv/repo/migrations/`.
Each migration file is prefixed with the owning app: `20260426120000_mw_audit_create_events.exs`.

---

## Rationale

### Why not Option B (Per-app Repo)?

- Each `Ecto.Repo` opens its own connection pool. With 6+ apps each opening pools of 10,
  we exceed MySQL's connection limit on modest infrastructure.
- Migrations become split across multiple apps — no single view of DB schema history.
- Cross-app queries (e.g., audit log viewer joining routing rule metadata) require raw SQL
  or inter-app calls, adding complexity.

### Why not Option C (Separate databases)?

- Referential integrity across entities (audit events referencing routing rule IDs) becomes
  application-enforced rather than DB-enforced.
- Operational overhead: schema migrations across multiple databases, separate backups,
  separate connection credentials.
- Completely unnecessary for a single-node middleware; adds complexity without isolation benefit.

### Why Option A?

- One connection pool, one migration path, one database backup.
- Cross-domain queries are just Ecto queries (e.g., `gateway_web` joins audit + routing data).
- Schema ownership is enforced by convention (migration file prefix + schema namespace),
  not by technical constraint — sufficient for a single-team project.
- Can migrate to Option B later if an individual app needs a dedicated database (e.g., DW
  adapter needs its own MySQL instance) — the `infra_repo` repo can be split at that point.

---

## Schema Ownership Rules

1. Each schema module lives in the app that owns it, not in `infra_repo/schemas/`.
   `infra_repo/schemas/` contains only shared cross-cutting schemas (if any).
2. App `X` must not write to a table owned by app `Y` via raw SQL. Use the owning app's
   context module as the boundary.
3. Migrations are always additive-first. Dropping a column requires a two-phase migration
   (phase 1: ignore in code, phase 2: drop) to enable zero-downtime deploys.
4. Migration naming: `YYYYMMDDHHMMSS_<app>_<description>.exs`

---

## Read Replicas (Future)

When read load grows, `infra_repo` can be extended with a second read-only Repo pointing
to a MySQL read replica:

```elixir
defmodule InfraRepo.ReadRepo do
  use Ecto.Repo, otp_app: :infra_repo, adapter: Ecto.Adapters.MyXQL, read_only: true
end
```

Reporting queries from `gateway_web` and `adapter_dw` are routed to `ReadRepo`.
Write operations remain on the primary `Repo`.

---

## Consequences

### Positive
- One pool, predictable connection count
- Single migration source of truth
- Cross-app joins are straightforward Ecto queries
- Simple backup/restore strategy

### Negative
- All apps are coupled to the same MySQL instance; an outage affects all apps
  — mitigated by high-availability MySQL (replication/proxy)
- Schema changes require coordination across teams (table owned by `mw_audit` but
  `gateway_web` reads it) — mitigated by migration naming convention and code review