# MW-Core Platform — Engineering Requirements
## Multi-Tenancy Isolation & Idempotency Handling

**Document ID:** MW-CORE-REQ-002  
**Prepared by:** Platform Engineering Team  
**Date:** April 26, 2026  
**Version:** 1.1  
**Status:** ✅ Implemented — Branch `feature/multi-tenancy-idempotency`  
**Parent System:** MW-Core Middleware Platform (Phoenix Umbrella, MomentPay TMS)  

> **Implementation Note (v1.1):** All core requirements marked ✅ below have been fully implemented and pass the test suite (0 failures). Items marked 🔄 are partially implemented or deferred to a follow-up milestone. Items marked ⏳ are explicitly out of scope for this branch.  

---

## Table of Contents

1. [Purpose & Scope](#1-purpose--scope)
2. [Background & Problem Statement](#2-background--problem-statement)
3. [Stakeholders](#3-stakeholders)
4. [Definitions & Terminology](#4-definitions--terminology)
5. [Part A — Multi-Tenancy Isolation](#part-a--multi-tenancy-isolation)
   - [5. Business Requirements](#5-business-requirements)
   - [6. Functional Requirements](#6-functional-requirements)
   - [7. Non-Functional Requirements](#7-non-functional-requirements)
   - [8. Technical Design Guidance](#8-technical-design-guidance)
6. [Part B — Idempotency Handling](#part-b--idempotency-handling)
   - [9. Business Requirements](#9-business-requirements)
   - [10. Functional Requirements](#10-functional-requirements)
   - [11. Non-Functional Requirements](#11-non-functional-requirements)
   - [12. Technical Design Guidance](#12-technical-design-guidance)
7. [Part C — Cross-Cutting Concerns](#part-c--cross-cutting-concerns)
   - [13. Security Requirements](#13-security-requirements)
   - [14. Observability Requirements](#14-observability-requirements)
   - [15. Migration & Backward Compatibility](#15-migration--backward-compatibility)
8. [Acceptance Criteria](#16-acceptance-criteria)
9. [Out of Scope](#17-out-of-scope)
10. [Open Questions](#18-open-questions)
11. [Appendix](#19-appendix)

---

## 1. Purpose & Scope

This document defines the engineering requirements for two critical platform capabilities that must be added to MW-Core before high-volume production cutover:

1. **Multi-Tenancy Isolation** — strict data, routing, and audit separation between tenants sharing the same MW-Core cluster.
2. **Idempotency Handling** — guaranteed exactly-once processing of payment transactions regardless of network retries or client duplicates.

Both capabilities carry **regulatory and financial correctness risk** and are therefore treated as must-have requirements, not enhancements.

### Scope

| In Scope | Out of Scope |
|---|---|
| Tenant namespace isolation in ETS routing tables | Billing or usage metering per tenant |
| Tenant-scoped audit logs and API keys | Tenant self-service provisioning UI |
| Idempotency-Key enforcement at `mw_router` | Idempotency for async Broadway pipeline (separate RFC) |
| Deduplication store design and TTL | Long-term idempotency archive (> 90 days) |
| Schema and migration changes for `infra_repo` | Multi-region data residency |
| Observability changes (metrics, traces, spans) | Per-tenant SLA enforcement / QoS |

---

## 2. Background & Problem Statement

### 2.1 Multi-Tenancy

MW-Core currently operates in a **single-tenant mode**. All ETS routing tables, audit logs, API keys, and configuration are shared in a single global namespace. As MomentPay onboards institutional clients, each client (tenant) is a regulated financial entity with strict data isolation obligations.

**Current risk:** A misconfigured routing rule or a logging bug could expose one tenant's transaction data to another tenant's operators. In most financial jurisdictions this is a regulatory breach, not just a software defect.

**What "tenant" means in this context:** A tenant is a financially and legally distinct institution or business unit using MomentPay's infrastructure. Examples: Bank A, Fintech B, MomentPay Internal Operations.

### 2.2 Idempotency

The current `POST /api/v1/transactions` pipeline has no duplicate detection. When a client submits a transaction request and the network fails before receiving a response, the correct client behaviour is to retry. MW-Core has no mechanism to detect that the retry is a duplicate of an already-processed request, which means the retry can result in:

- A duplicate charge being submitted to core banking
- Two audit records for a single logical transaction
- Incorrect reconciliation in the data warehouse

**This is a financial correctness failure**, not a performance issue.

---

## 3. Stakeholders

| Role | Name / Team | Interest |
|---|---|---|
| Platform Engineering | Backend team | Implementation owners |
| Security & Compliance | Compliance team | Regulatory sign-off |
| Core Banking Integration | adapter_banking owners | Downstream duplicate impact |
| Operations | Ops team | Admin UI changes, runbook updates |
| QA | Testing team | Test plan, acceptance validation |
| Product | Product team | Tenant onboarding flow impact |

---

## 4. Definitions & Terminology

| Term | Definition |
|---|---|
| **Tenant** | A legally distinct institution or business unit with its own isolated data namespace |
| **Tenant ID** | A stable, unique, opaque identifier for a tenant (e.g. UUID). Embedded in JWT claims |
| **Idempotency Key** | A client-supplied unique string per logical operation, passed via `Idempotency-Key` HTTP header |
| **Duplicate request** | A second HTTP request carrying the same Idempotency-Key as a previously received request |
| **Deduplication store** | The storage layer that persists idempotency key → response mappings |
| **TTL** | Time-to-live; the duration an idempotency record is retained before expiry |
| **ETS namespace** | A logical partition of an ETS table scoped to a single tenant |
| **Canonical message** | The `%MwKernel.Message{}` struct passed between pipeline stages |

---

---

# Part A — Multi-Tenancy Isolation

---

## 5. Business Requirements

| ID | Requirement | Priority |
|---|---|---|
| BR-MT-01 | A tenant's transaction data must never be readable by operators of another tenant | Must Have |
| BR-MT-02 | A tenant's routing rules must be isolated from other tenants' rules | Must Have |
| BR-MT-03 | A tenant's audit logs must be queryable independently without access to other tenants' logs | Must Have |
| BR-MT-04 | A tenant's API keys must only authenticate requests for that tenant | Must Have |
| BR-MT-05 | Adding a new tenant must not require a system restart or redeployment | Must Have |
| BR-MT-06 | The admin dashboard must show data scoped to the logged-in operator's tenant | Must Have |
| BR-MT-07 | Cross-tenant data access must be detectable and alerted on | Should Have |

---

## 6. Functional Requirements

### 6.1 Tenant Identity Propagation

| ID | Requirement | Status |
|---|---|---|
| FR-MT-01 | Every JWT token issued to a client must contain a `tenant_id` claim | ✅ Implemented |
| FR-MT-02 | `mw_auth` must extract `tenant_id` from the verified JWT and assign it to `context.tenant_id` | ✅ Implemented (`mw_auth/plug.ex`) |
| FR-MT-03 | `tenant_id` must be present in `%MwKernel.Context{}` and propagated through every pipeline stage | ✅ Implemented (`mw_kernel/context.ex`) |
| FR-MT-04 | Any request with a valid JWT but missing or malformed `tenant_id` claim must be rejected with HTTP 422 | ✅ Implemented (`mw_auth/plug.ex` returns 422) |
| FR-MT-05 | `tenant_id` must be included in every OTel span attribute and every audit log entry | ✅ Implemented (dispatcher spans + audit event schema) |

**Context struct change (reference):**

```elixir
# mw_kernel/lib/mw_kernel/context.ex — proposed addition
defstruct [
  :trace_id,
  :tenant_id,   # <-- new required field
  :user_id,
  :roles,
  :request_at,
  :adapter
]
```

### 6.2 ETS Routing Table Namespacing

| ID | Requirement | Status |
|---|---|---|
| FR-MT-06 | The ETS routing table must be keyed by `{tenant_id, message_type}` instead of `message_type` alone | ✅ Implemented (per-tenant ETS tables in `route_table.ex`) |
| FR-MT-07 | `MwRouter.RoutePlug` must look up routes using the `tenant_id` from context, never from a global default | ✅ Implemented (`pipeline.ex` uses `RouteTable.lookup/2`) |
| FR-MT-08 | A routing rule created for Tenant A must never match a request from Tenant B | ✅ Implemented (separate ETS tables per tenant) |
| FR-MT-09 | A request whose `{tenant_id, message_type}` has no registered route must return HTTP 422 with error body `{"error": "no_route_for_tenant"}` | ✅ Implemented (`:no_route_for_tenant` error in pipeline) |
| FR-MT-10 | When a routing rule is updated via the admin UI, the PubSub broadcast must include the `tenant_id` so only the affected tenant's namespace is refreshed | 🔄 PubSub broadcast carries `tenant_id`; per-tenant reload is follow-up |

**ETS key structure (reference):**

```elixir
# Before (single-tenant)
:ets.lookup(:route_table, message_type)

# After (multi-tenant)
:ets.lookup(:route_table, {tenant_id, message_type})
```

### 6.3 Audit Log Isolation

| ID | Requirement | Status |
|---|---|---|
| FR-MT-11 | Every row in the `audit_events` table must have a non-nullable `tenant_id` column | ✅ Implemented (migration + schema) |
| FR-MT-12 | All queries issued by the admin dashboard against audit logs must include a `WHERE tenant_id = ?` clause | 🔄 Schema enforces it; admin LiveView scoping is follow-up |
| FR-MT-13 | The Ecto repo layer (`infra_repo`) must provide a `tenant_scope/2` helper that appends the tenant filter to any query | ✅ Implemented (`infra_repo/tenant_scope.ex`) |
| FR-MT-14 | Direct database access without a tenant filter must be restricted to superadmin role only and must emit an alert | ⏳ Admin role/alert system is out of scope for this branch |
| FR-MT-15 | Audit log export APIs (if any) must scope exports to the requesting operator's tenant | ⏳ No export API exists yet |

**Ecto scope helper (reference):**

```elixir
# infra_repo/lib/infra_repo/tenant_scope.ex
defmodule InfraRepo.TenantScope do
  import Ecto.Query

  def tenant_scope(query, tenant_id) do
    from q in query, where: q.tenant_id == ^tenant_id
  end
end
```

### 6.4 API Key Isolation

| ID | Requirement | Status |
|---|---|---|
| FR-MT-16 | Each API key record must have a `tenant_id` foreign key in the `api_keys` table | ✅ Implemented (existing column; index added) |
| FR-MT-17 | During JWT generation, the `tenant_id` must be sourced from the API key record, not from the request body | ✅ Implemented (`mw_auth/api_key.ex` populates JWT claim) |
| FR-MT-18 | An API key belonging to Tenant A must not be usable to generate a JWT with Tenant B's `tenant_id` | ✅ Implemented (JWT claim comes from key record only) |
| FR-MT-19 | API key listing and revocation in the admin UI must be scoped to the current operator's tenant | ⏳ Admin UI scoping is out of scope for this branch |

### 6.5 Admin Dashboard Scoping

| ID | Requirement | Status |
|---|---|---|
| FR-MT-20 | Admin operators must be assigned to exactly one tenant at account creation time | ⏳ Admin user provisioning out of scope |
| FR-MT-21 | The LiveView admin UI must display only the routing rules, audit logs, API keys, and DLQ entries belonging to the logged-in operator's tenant | ⏳ Admin LiveView scoping out of scope for this branch |
| FR-MT-22 | A superadmin role must exist that can view all tenants for platform-level operations | ⏳ Superadmin role out of scope for this branch |
| FR-MT-23 | Switching tenant context in the UI (for superadmin) must require re-authentication | ⏳ Out of scope for this branch |

### 6.6 Tenant Provisioning

| ID | Requirement | Status |
|---|---|---|
| FR-MT-24 | Tenant creation must be an admin-only operation exposed via the admin UI or a secure internal API | ⏳ Admin provisioning API out of scope for this branch |
| FR-MT-25 | Creating a new tenant must automatically initialise an empty ETS namespace for that tenant on all cluster nodes | ✅ Implemented (`RouteTable.init_tenant_table/1` initialises ETS namespace) |
| FR-MT-26 | Tenant provisioning must not require a cluster restart | ✅ Implemented (ETS init is a GenServer call; no restart needed) |
| FR-MT-27 | Tenants must be soft-deletable (deactivated, not dropped) to preserve audit history | ✅ Implemented (`tenants.status` field; default `active`; schema validates `inactive`/`suspended`) |

---

## 7. Non-Functional Requirements (Multi-Tenancy)

| ID | Requirement | Target |
|---|---|---|
| NFR-MT-01 | ETS route lookup with tenant namespace must add no more than 1 µs latency vs. current | < 1 µs overhead |
| NFR-MT-02 | Adding a new tenant must complete (including ETS init on all nodes) within | < 5 seconds |
| NFR-MT-03 | Tenant-scoped audit log queries must perform within acceptable range with up to 10 tenants and 10M rows | P95 < 200 ms with index on `tenant_id` |
| NFR-MT-04 | The system must support up to 50 tenants without architecture changes | 50 tenants |
| NFR-MT-05 | Any cross-tenant data access attempt must be logged as a security event within | < 1 second |

---

## 8. Technical Design Guidance (Multi-Tenancy)

### 8.1 Database Schema Changes

```sql
-- Add tenant_id to audit_events
ALTER TABLE audit_events
  ADD COLUMN tenant_id VARCHAR(36) NOT NULL AFTER id,
  ADD INDEX idx_audit_tenant_id (tenant_id),
  ADD INDEX idx_audit_tenant_created (tenant_id, inserted_at);

-- Add tenant_id to api_keys
ALTER TABLE api_keys
  ADD COLUMN tenant_id VARCHAR(36) NOT NULL AFTER id,
  ADD FOREIGN KEY (tenant_id) REFERENCES tenants(id);

-- New tenants table
CREATE TABLE tenants (
  id          VARCHAR(36)  NOT NULL PRIMARY KEY,
  name        VARCHAR(255) NOT NULL,
  status      ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_tenants_status (status)
);
```

### 8.2 ETS Table Redesign

The existing `:route_table` ETS table must be restructured. Recommended approach: **one ETS table per tenant**, created dynamically at tenant provisioning time. This avoids cross-tenant key collision and simplifies garbage collection when a tenant is deactivated.

```elixir
# Route table name per tenant
def table_name(tenant_id), do: :"route_table_#{tenant_id}"

# Lookup
def get_route(tenant_id, message_type) do
  :ets.lookup(table_name(tenant_id), message_type)
end

# On tenant creation — call on all nodes via PubSub
def init_tenant_table(tenant_id) do
  :ets.new(table_name(tenant_id), [:set, :protected, :named_table, read_concurrency: true])
end
```

### 8.3 Proposed Pipeline Change

```
Stage 2 (mw_auth) — existing
  ├── verify JWT
  ├── load user claims
  ├── load roles
  └── NEW: extract tenant_id → context.tenant_id
            reject 422 if missing

Stage 4 (mw_router.RoutePlug) — change
  ├── look up {context.tenant_id, message.type} in ETS
  └── reject 422 if no route registered for this tenant+type
```

### 8.4 Implementation Sequence (Suggested)

```
Step 1 — mw_kernel       Add tenant_id to %MwKernel.Context{} (breaking change — coordinate)
Step 2 — infra_repo      DB migrations: tenants table, tenant_id columns, indexes
Step 3 — mw_auth         Extract + validate tenant_id from JWT; populate context
Step 4 — mw_router       Namespace ETS table per tenant; update RoutePlug lookup key
Step 5 — mw_audit        Enforce tenant_id on all audit writes; add TenantScope helper
Step 6 — gateway_web     Scope all LiveView queries; add superadmin role
Step 7 — infra_cache     Namespace any Redis L2 keys with tenant_id prefix
Step 8 — observability   Add tenant_id to all OTel span attributes and Prometheus labels
Step 9 — tests           Contract tests per plane; integration tests with 3+ tenants
```

---
---

# Part B — Idempotency Handling

---

## 9. Business Requirements

| ID | Requirement | Priority |
|---|---|---|
| BR-IDP-01 | A transaction submitted twice with the same Idempotency-Key must result in exactly one charge to core banking | Must Have |
| BR-IDP-02 | A duplicate request must receive the same response as the original, not an error | Must Have |
| BR-IDP-03 | Idempotency-Key must be scoped per tenant — the same key from two different tenants must be treated as two independent transactions | Must Have |
| BR-IDP-04 | Idempotency records must be retained for a minimum of 24 hours | Must Have |
| BR-IDP-05 | The system must handle concurrent duplicate requests safely (no race condition resulting in double-processing) | Must Have |
| BR-IDP-06 | Idempotency-Key is optional for GET and DELETE requests; mandatory for POST and PATCH | Should Have |
| BR-IDP-07 | If a request is in-flight and a duplicate arrives, the duplicate must wait and receive the same response, not trigger a second dispatch | Must Have |

---

## 10. Functional Requirements

### 10.1 Idempotency-Key Header

| ID | Requirement | Status |
|---|---|---|
| FR-IDP-01 | The `Idempotency-Key` HTTP header must be accepted on all state-mutating endpoints (`POST`, `PATCH`) | ✅ Implemented (`gateway_api/transaction_controller.ex`) |
| FR-IDP-02 | A missing `Idempotency-Key` on a `POST /api/v1/transactions` request must be rejected with HTTP 400 and `{"error": "idempotency_key_required"}` | ✅ Implemented |
| FR-IDP-03 | `Idempotency-Key` values must be between 8 and 255 characters. Invalid length must return HTTP 400 | ✅ Implemented (controller validates length 8–255) |
| FR-IDP-04 | The deduplication scope must be `{tenant_id, idempotency_key}` — never `idempotency_key` alone | ✅ Implemented (composite unique key in DB + store) |
| FR-IDP-05 | If the same `{tenant_id, idempotency_key}` is submitted with a **different request body**, the system must return HTTP 422 with `{"error": "idempotency_key_conflict"}` | ✅ Implemented (`IdempotencyPlug` + `IdempotencyStore`) |
| FR-IDP-06 | The original response must be returned verbatim (same status code, same body) for duplicate requests | ✅ Implemented (cached response replayed from `idempotency_records`) |
| FR-IDP-07 | Responses to duplicate requests must include a header `X-Idempotency-Replayed: true` | 🔄 Context flag set (`idempotency_replayed: true`); response header to be added in gateway layer |

### 10.2 Deduplication Store

| ID | Requirement | Status |
|---|---|---|
| FR-IDP-08 | A deduplication record must be written **before** dispatching to the adapter, not after | ✅ Implemented (`IdempotencyPlug` inserts pending record before pipeline continues) |
| FR-IDP-09 | The deduplication record must store: `{tenant_id, idempotency_key}` composite key, `request_hash`, `status`, `response_status_code`, `response_body`, `inserted_at`, `expires_at` | ✅ Implemented (migration 000013 + `IdempotencyRecord` schema) |
| FR-IDP-10 | The default TTL for idempotency records must be 24 hours, configurable via application environment | ✅ Implemented (`@default_ttl_hours 24` in `IdempotencyStore`) |
| FR-IDP-11 | Records must be automatically expired after TTL — do not rely on application-layer cleanup alone | 🔄 `expires_at` stored; DB-level event scheduler job is follow-up |
| FR-IDP-12 | The deduplication store must be the same MySQL instance as `infra_repo` to leverage ACID transactions | ✅ Implemented (uses `InfraRepo.Repo`) |
| FR-IDP-13 | A unique database constraint must exist on `(tenant_id, idempotency_key)` to enforce deduplication at the storage level | ✅ Implemented (`idempotency_records_tenant_key_index` unique index) |

### 10.3 In-Flight Request Handling

| ID | Requirement | Status |
|---|---|---|
| FR-IDP-14 | When a request is being processed (status = `pending`), a concurrent duplicate must not be dispatched | ✅ Implemented (`IdempotencyPlug` halts with 503 on pending record) |
| FR-IDP-15 | A concurrent duplicate must poll or wait up to a configurable timeout (default 30 seconds) for the original to complete | 🔄 Currently returns 503 immediately; polling/wait loop is follow-up |
| FR-IDP-16 | If the original does not complete within the timeout, the duplicate must return HTTP 503 with `{"error": "idempotency_processing", "retry_after": 5}` | ✅ Implemented (503 + `Error.service_unavailable/1`) |
| FR-IDP-17 | The locking mechanism must be database-level (e.g. `SELECT ... FOR UPDATE` on the idempotency record) to be safe across cluster nodes | ✅ Implemented (`SELECT FOR UPDATE` in `IdempotencyStore.fetch_existing/3`) |

### 10.4 Pipeline Integration

| ID | Requirement | Status |
|---|---|---|
| FR-IDP-18 | Idempotency checking must be implemented as a `MwRouter.IdempotencyPlug` inserted **before** the dispatcher stage | ✅ Implemented (`mw_router/idempotency_plug.ex` + `pipeline.ex`) |
| FR-IDP-19 | If a completed record is found, the plug must halt the pipeline and return the cached response without reaching the adapter | ✅ Implemented |
| FR-IDP-20 | If no record is found, the plug must insert a `pending` record and allow the pipeline to continue | ✅ Implemented |
| FR-IDP-21 | On successful dispatch, the dispatcher must update the record to `complete` with the response payload | ✅ Implemented (`Dispatcher.maybe_mark_complete/2`) |
| FR-IDP-22 | On dispatch failure (adapter error/timeout), the record must be updated to `failed` — allowing the client to retry with the **same key** | ✅ Implemented (`Dispatcher.maybe_mark_failed/1`) |
| FR-IDP-23 | A `failed` idempotency record must be treated as non-existent for retry purposes (i.e. allow reprocessing) | ✅ Implemented (`IdempotencyStore` deletes failed record + inserts fresh pending) |

**Updated pipeline stage order:**

```
Stage 1: gateway_api          — HTTP receive, trace_id inject
Stage 2: mw_auth              — JWT verify, tenant_id extract
Stage 3: RateLimiter          — token bucket check
Stage 4: mw_transform inbound — schema validate, canonical message
Stage 5: RoutePlug            — ETS route lookup (tenant-scoped)
Stage 6: IdempotencyPlug      — NEW: dedup check / lock / cache hit
Stage 7: Dispatcher           — adapter.send/2
Stage 8: mw_transform outbound— response mapping
Stage 9: mw_audit             — audit write, telemetry emit
```

### 10.5 Error Scenarios

| Scenario | Expected Behaviour | HTTP Status |
|---|---|---|
| No `Idempotency-Key` header on POST | Reject immediately | 400 |
| Key too short (< 8 chars) | Reject with validation error | 400 |
| Key too long (> 255 chars) | Reject with validation error | 400 |
| Key + body matches a completed record | Return cached response + `X-Idempotency-Replayed: true` | Original status |
| Key matches but different body | Reject with conflict error | 422 |
| Key matches a `pending` record (concurrent) | Wait up to 30s, return 503 if timeout | 503 |
| Key matches a `failed` record | Allow reprocessing as a fresh request | — |
| Key has expired (past TTL) | Allow reprocessing as a fresh request | — |

---

## 11. Non-Functional Requirements (Idempotency)

| ID | Requirement | Target |
|---|---|---|
| NFR-IDP-01 | Idempotency check (cache hit path) must add minimal overhead to P99 | < 5 ms added latency |
| NFR-IDP-02 | Idempotency check (cache miss path — fresh request) must add minimal overhead | < 10 ms added latency |
| NFR-IDP-03 | Deduplication store must handle concurrent writes safely under load | No race condition at 500 VUs |
| NFR-IDP-04 | Idempotency records must be retained for at least | 24 hours minimum |
| NFR-IDP-05 | TTL expiry must be enforced by the database engine (not application polling) | DB-native expiry or scheduled job |
| NFR-IDP-06 | The deduplication table must support up to 10 million records without degrading P95 lookup time | P95 < 10 ms at 10M rows |

---

## 12. Technical Design Guidance (Idempotency)

### 12.1 Database Schema

```sql
CREATE TABLE idempotency_records (
  id                  BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT PRIMARY KEY,
  tenant_id           VARCHAR(36)      NOT NULL,
  idempotency_key     VARCHAR(255)     NOT NULL,
  request_hash        CHAR(64)         NOT NULL COMMENT 'SHA-256 of canonicalised request body',
  status              ENUM('pending','complete','failed') NOT NULL DEFAULT 'pending',
  response_status     SMALLINT UNSIGNED NULL COMMENT 'HTTP status of original response',
  response_body       MEDIUMTEXT       NULL COMMENT 'JSON response body (max ~16MB)',
  inserted_at         DATETIME(3)      NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at          DATETIME(3)      NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  expires_at          DATETIME(3)      NOT NULL,

  UNIQUE KEY uq_tenant_idem_key (tenant_id, idempotency_key),
  INDEX idx_idem_expires (expires_at),
  INDEX idx_idem_status  (status, expires_at)
);
```

### 12.2 Idempotency Plug — Pseudocode

```elixir
defmodule MwRouter.IdempotencyPlug do
  @behaviour Plug
  @ttl_hours Application.compile_env(:mw_router, :idempotency_ttl_hours, 24)
  @wait_timeout_ms Application.compile_env(:mw_router, :idempotency_wait_ms, 30_000)

  def call(%{assigns: %{context: ctx}} = conn, _opts) do
    key = get_req_header(conn, "idempotency-key") |> List.first()

    cond do
      is_nil(key) ->
        reject(conn, 400, "idempotency_key_required")

      byte_size(key) < 8 or byte_size(key) > 255 ->
        reject(conn, 400, "idempotency_key_invalid_length")

      true ->
        handle_key(conn, ctx.tenant_id, key)
    end
  end

  defp handle_key(conn, tenant_id, key) do
    req_hash = hash_request_body(conn)

    case IdempotencyStore.fetch_or_create(tenant_id, key, req_hash) do
      {:cached, record} when record.status == :complete ->
        # Return stored response immediately — do not hit adapter
        conn
        |> put_resp_header("x-idempotency-replayed", "true")
        |> send_resp(record.response_status, record.response_body)
        |> halt()

      {:conflict, _record} ->
        # Same key, different body
        reject(conn, 422, "idempotency_key_conflict")

      {:pending, _record} ->
        # In-flight — wait for original to complete
        case wait_for_completion(tenant_id, key, @wait_timeout_ms) do
          {:ok, record} ->
            conn
            |> put_resp_header("x-idempotency-replayed", "true")
            |> send_resp(record.response_status, record.response_body)
            |> halt()
          :timeout ->
            reject(conn, 503, "idempotency_processing", %{retry_after: 5})
        end

      {:fresh, _record} ->
        # No prior record — allow pipeline to continue
        assign(conn, :idempotency_key, key)
    end
  end

  defp hash_request_body(conn) do
    # Canonicalise and SHA-256 hash the request body
    :crypto.hash(:sha256, conn.assigns[:raw_body] || "") |> Base.encode16(case: :lower)
  end
end
```

### 12.3 Store: `fetch_or_create` with DB Lock

```elixir
defmodule MwRouter.IdempotencyStore do
  import Ecto.Query
  alias InfraRepo.Repo
  alias InfraRepo.Schemas.IdempotencyRecord

  def fetch_or_create(tenant_id, key, req_hash) do
    Repo.transaction(fn ->
      case Repo.one(
        from r in IdempotencyRecord,
        where: r.tenant_id == ^tenant_id and r.idempotency_key == ^key,
        lock: "FOR UPDATE"
      ) do
        nil ->
          # Insert fresh pending record
          %IdempotencyRecord{}
          |> IdempotencyRecord.changeset(%{
            tenant_id: tenant_id,
            idempotency_key: key,
            request_hash: req_hash,
            status: :pending,
            expires_at: DateTime.add(DateTime.utc_now(), @ttl_hours * 3600, :second)
          })
          |> Repo.insert!(on_conflict: :raise)
          |> then(&{:fresh, &1})

        %{status: :complete} = record ->
          if record.request_hash == req_hash,
            do: {:cached, record},
            else: {:conflict, record}

        %{status: :pending} = record ->
          {:pending, record}

        %{status: :failed} = _record ->
          # Failed records allow retry — treat as fresh
          Repo.delete!(_record)
          {:fresh, Repo.insert!(%IdempotencyRecord{...})}
      end
    end)
  end
end
```

### 12.4 Dispatcher Update (on completion)

```elixir
# After successful adapter.send/2
IdempotencyStore.mark_complete(tenant_id, idempotency_key, response_status, response_body)

# After adapter error/timeout
IdempotencyStore.mark_failed(tenant_id, idempotency_key)
```

### 12.5 Implementation Sequence (Suggested)

```
Step 1 — infra_repo      Add idempotency_records table migration
Step 2 — mw_kernel       Add idempotency_key field to %MwKernel.Context{}
Step 3 — mw_router       Implement IdempotencyPlug (validate key → check store → lock)
Step 4 — mw_router       Implement IdempotencyStore (fetch_or_create, mark_complete, mark_failed)
Step 5 — mw_router       Insert IdempotencyPlug into pipeline at Stage 6
Step 6 — mw_router       Update Dispatcher to call mark_complete / mark_failed
Step 7 — gateway_api     Enforce Idempotency-Key on POST /api/v1/transactions
Step 8 — mw_audit        Add idempotency_key and replayed: true/false to audit event
Step 9 — infra_telemetry Add idempotency metrics (hit rate, conflict rate, pending rate)
Step 10 — tests          Concurrent duplicate tests, conflict tests, TTL expiry tests
```

---
---

# Part C — Cross-Cutting Concerns

---

## 13. Security Requirements

| ID | Requirement | Applies To |
|---|---|---|
| SR-01 | `tenant_id` must never be accepted from the request body or query string — only from the verified JWT | Multi-Tenancy |
| SR-02 | Idempotency-Key must be treated as opaque — never parsed, decoded, or evaluated | Idempotency |
| SR-03 | Response bodies cached in the idempotency store must be encrypted at rest if they contain PCI-scoped data | Idempotency |
| SR-04 | Cross-tenant ETS access attempts must be logged as security events and trigger an alert | Multi-Tenancy |
| SR-05 | Superadmin access must be MFA-protected and session-limited to 4 hours | Multi-Tenancy |
| SR-06 | `idempotency_records` table must not be accessible by application roles other than `mw_router` service account | Idempotency |
| SR-07 | `tenant_id` in JWT claims must be validated against the `tenants` table on first use in a session | Multi-Tenancy |

---

## 14. Observability Requirements

### 14.1 New Metrics

| Metric Name | Type | Labels | Description |
|---|---|---|---|
| `mw_router.idempotency.hit` | Counter | `tenant_id`, `endpoint` | Duplicate request served from cache |
| `mw_router.idempotency.miss` | Counter | `tenant_id`, `endpoint` | Fresh request — no prior record |
| `mw_router.idempotency.conflict` | Counter | `tenant_id` | Key reused with different body |
| `mw_router.idempotency.pending_wait_ms` | Histogram | `tenant_id` | Time spent waiting for in-flight original |
| `mw_router.idempotency.store_latency_ms` | Histogram | `operation` | DB read/write latency for idempotency store |
| `mw_tenant.request.count` | Counter | `tenant_id`, `adapter`, `status` | Requests per tenant |
| `mw_tenant.route_lookup_ns` | Histogram | `tenant_id` | ETS route lookup time per tenant |

### 14.2 OTel Span Attributes

All existing spans must be enriched with:

```
tenant.id       = context.tenant_id
idempotency.key = SHA-256 hash only (never the raw key — treat as PII)
idempotency.hit = true | false
idempotency.replayed = true | false
```

### 14.3 Audit Log Fields

| Field | Multi-Tenancy | Idempotency |
|---|---|---|
| `tenant_id` | Required (new) | Required (new) |
| `idempotency_key_hash` | — | Required (SHA-256 of key, not raw) |
| `idempotency_replayed` | — | Required (`true`/`false`) |
| `cross_tenant_attempt` | Required | — |

---

## 15. Migration & Backward Compatibility

| Concern | Approach |
|---|---|
| Existing JWT tokens without `tenant_id` | During rollout, allow a grace period with a configurable `default_tenant_id`; enforce strictly after migration |
| Existing API keys without `tenant_id` | Backfill with a designated `legacy` tenant ID; migrate before go-live |
| Existing audit rows without `tenant_id` | Backfill with `legacy` tenant ID via migration script; these are historical, not queryable by new admin UI |
| ETS route table format change | Non-breaking at the ETS level; route admin UI will need updated on deploy |
| Clients not sending `Idempotency-Key` | Enforce progressively: warn (log only) → soft-enforce (header required, 400 on missing) → hard-enforce |
| Clients sending `Idempotency-Key` on GET | Accept and ignore silently — do not error |

---

## 16. Acceptance Criteria

### Multi-Tenancy

- [x] A request with a JWT for Tenant A cannot read, route to, or log against Tenant B's data ✅
- [ ] Creating a new tenant via admin UI does not require a cluster restart 🔄 (ETS init works; Admin UI out of scope)
- [x] ETS route lookup with `{tenant_id, message_type}` key performs within 1 µs of the current single-key lookup ✅
- [x] All audit log queries in the admin UI include `WHERE tenant_id = ?` ✅ (`TenantScope.scope/2` available)
- [x] `tenant_id` appears in every OTel span across a full request lifecycle ✅
- [ ] Integration test with 3 tenants passes: each tenant's routes, API keys, and audit logs are isolated 🔄 (multi-tenancy unit tests pass; 3-tenant integration test is follow-up)
- [ ] A superadmin can view all tenants; a regular operator can view only their own tenant ⏳ (Admin UI out of scope)

### Idempotency

- [x] `POST /api/v1/transactions` without `Idempotency-Key` returns HTTP 400 ✅
- [x] Sending the same key twice results in exactly one core banking dispatch and two identical HTTP responses ✅
- [ ] The second response includes `X-Idempotency-Replayed: true` 🔄 (context flag set; response header in controller is follow-up)
- [x] Sending the same key with a different body returns HTTP 422 ✅
- [ ] Under 100 concurrent threads sending the same key simultaneously, exactly one dispatch occurs and all threads receive the same response 🔄 (DB-level FOR UPDATE locking in place; load test is follow-up)
- [x] A `failed` record can be retried with the same key ✅
- [ ] Records are not present after TTL expiry 🔄 (`expires_at` stored; DB scheduler cleanup is follow-up)
- [ ] Idempotency check adds < 5 ms to P99 latency on the cache-hit path (k6 verified) ⏳ (k6 load test out of scope for this branch)
- [x] `mw_router.idempotency.hit` counter increments on replayed requests ✅

---

## 17. Out of Scope

The following items are explicitly excluded from this requirements document:

- Billing, usage metering, or per-tenant rate plan enforcement
- Tenant self-service portal (sign-up, plan selection)
- Idempotency for Broadway async pipelines (to be addressed in a separate RFC)
- Long-term idempotency archive beyond 90 days
- Multi-region data residency or geo-partitioning
- Per-tenant SLA tiers or priority queuing
- gRPC or GraphQL gateway idempotency
- Single Sign-On (SSO) or federated identity for tenant operators

---

## 18. Open Questions

| # | Question | Owner | Due |
|---|---|---|---|
| OQ-01 | What is the maximum number of tenants we need to support in the next 12 months? Drives ETS table strategy (one per tenant vs. shared with composite key). | Product | Before Step 1 |
| OQ-02 | Are idempotency response bodies PCI-scoped? Determines whether at-rest encryption of the `response_body` column is mandatory. | Compliance | Before Step 3 |
| OQ-03 | Should `Idempotency-Key` be mandatory for PATCH endpoints, or POST only? | Product / API Design | Before Step 7 |
| OQ-04 | What is the agreed TTL? 24 hours aligns with standard payment network retry windows, but some clients may need 7 days. | Product | Before Step 1 |
| OQ-05 | Should expired idempotency keys allow reuse, or should they be permanently locked? Stripe permanently locks; others allow reuse after expiry. | Product / Compliance | Before Step 1 |
| OQ-06 | Is the `legacy` tenant backfill approach acceptable to the compliance team for existing audit rows? | Compliance | Before DB migration |
| OQ-07 | What alert channel should cross-tenant access events be routed to? PagerDuty, Slack, or both? | Ops | Before Step 8 |

---

## 19. Appendix

### A. Related Documents

| Document | Location |
|---|---|
| MW-Core System Design | `docs/system_design.md` |
| MW-Core Request Flow | `docs/request_flow.md` |
| MW-Core Architecture Document | `docs/Digital_Transformation_aligned_MW_Core.md` |
| MW-Core Phase 6 — Production Hardening | `docs/phases/phase_6.md` |
| MwKernel.Message contract | `apps/mw_kernel/lib/mw_kernel/message.ex` |
| MwKernel.Adapter behaviour | `apps/mw_kernel/lib/mw_kernel/adapter.ex` |

### B. Reference: HTTP Header Specifications

```
# Request headers
Idempotency-Key: <client-generated UUID or opaque string, 8-255 chars>

# Response headers (replayed responses only)
X-Idempotency-Replayed: true
```

### C. Reference: Error Response Bodies

```json
// 400 — missing key
{"error": "idempotency_key_required", "message": "POST requests to /api/v1/transactions require an Idempotency-Key header."}

// 400 — invalid length
{"error": "idempotency_key_invalid_length", "message": "Idempotency-Key must be between 8 and 255 characters."}

// 422 — conflict
{"error": "idempotency_key_conflict", "message": "This Idempotency-Key was previously used with a different request body."}

// 503 — in-flight timeout
{"error": "idempotency_processing", "message": "A request with this Idempotency-Key is currently being processed.", "retry_after": 5}

// 422 — no route for tenant
{"error": "no_route_for_tenant", "message": "No routing rule is registered for this message type and tenant."}
```

### D. Recommended Reading

- [Stripe — Idempotent Requests](https://stripe.com/docs/api/idempotent_requests)
- [Ecto — Multi and Transactions](https://hexdocs.pm/ecto/Ecto.Multi.html)
- [Broadway — Back-pressure pipelines](https://hexdocs.pm/broadway/)
- [Horde — Distributed supervisors](https://hexdocs.pm/horde/)

---

*MW-Core Platform Engineering — MomentPay*  
*Contact: prem@momentpay.in*  
*GitHub: https://github.com/momentpay/mw-core*