mtp-docs/adr/0002-database-per-client.html at main · paramhq/mtp-docs · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1" />
<title>ADR-0002 · Database per Client</title>
<link rel="preconnect" href="https://fonts.googleapis.com" />
<link rel="preconnect" href="https://fonts.gstatic.com" crossorigin />
<link href="https://fonts.googleapis.com/css2?family=Inter:wght@400;500;600;700&family=Fraunces:opsz,wght@9..144,500;9..144,600&family=JetBrains+Mono:wght@400;500&display=swap" rel="stylesheet" />
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/prism/1.29.0/themes/prism-tomorrow.min.css" />
<link rel="stylesheet" href="../assets/styles.css" />
</head>
<body>
<div class="layout">
  <aside class="sidebar">
    <a href="../index.html" class="brand">
      <span class="brand-mark">MTP</span>
      <span class="brand-text">Multi-Tenant Platform</span>
    </a>
    <nav>
      <div class="nav-group">
        <h4>Foundations</h4>
        <ul>
          <li><a href="../index.html"><span class="nav-num">00</span>Overview</a></li>
          <li><a href="../docs/01-findings.html"><span class="nav-num">01</span>Findings</a></li>
          <li><a href="../docs/02-architecture.html"><span class="nav-num">02</span>Architecture</a></li>
          <li><a href="../docs/10-tenancy-model.html"><span class="nav-num">10</span>Tenancy Model</a></li>
        </ul>
      </div>
      <div class="nav-group">
        <h4>Validation</h4>
        <ul>
          <li><a href="../docs/03-code-sketch.html"><span class="nav-num">03</span>Code Sketch</a></li>
          <li><a href="../docs/04-ui-rule-config.html"><span class="nav-num">04</span>UI Rule Config</a></li>
          <li><a href="../docs/05-result-pattern.html"><span class="nav-num">05</span>Result Pattern</a></li>
          <li><a href="../docs/06-reconciliation.html"><span class="nav-num">06</span>Reconciliation</a></li>
        </ul>
      </div>
      <div class="nav-group">
        <h4>Tenant Surface</h4>
        <ul>
          <li><a href="../docs/07-ui-schema.html"><span class="nav-num">07</span>UI Schema</a></li>
          <li><a href="../docs/08-i18n-l10n.html"><span class="nav-num">08</span>i18n / l10n</a></li>
          <li><a href="../docs/09-extension-fields.html"><span class="nav-num">09</span>Extension Fields</a></li>
        </ul>
      </div>
      <div class="nav-group">
        <h4>Frontend</h4>
        <ul>
          <li><a href="../mockups/classic.html"><span class="nav-num">UI1</span>AppShell &mdash; Classic</a></li>
          <li><a href="../mockups/zoho.html"><span class="nav-num">UI2</span>AppShell &mdash; 2-Stage</a></li>
          <li><a href="../mockups/exam-setup.html"><span class="nav-num">UI3</span>AppShell &mdash; Exam Setup</a></li>
          <li><a href="../mockups/ux-showcase-1.html"><span class="nav-num">UI4</span>AppShell &mdash; UX Showcase</a></li>
          <li><a href="../mockups/ux-showcase-2.html"><span class="nav-num">UI5</span>AppShell &mdash; UX Showcase 2</a></li>
          <li><a href="../mockups/ux-showcase-3.html"><span class="nav-num">UI6</span>AppShell &mdash; UX Showcase 3</a></li>
          <li><a href="../mockups/ux-showcase-4.html"><span class="nav-num">UI8</span>AppShell &mdash; UX Showcase 4</a></li>
          <li><a href="../mockups/ux-catalog.html"><span class="nav-num">UI7</span>UX Pattern Catalog</a></li>
          <li><a href="../mockups/notes.html"><span class="nav-num">UIN</span>AppShell Notes</a></li>
        </ul>
      </div>
      <div class="nav-group">
        <h4>Decisions</h4>
        <ul>
          <li><a href="0001-hybrid-validation-strategy.html"><span class="nav-num">ADR-1</span>Hybrid Validation</a></li>
          <li><a href="0002-database-per-client.html" class="active"><span class="nav-num">ADR-2</span>DB-per-Client</a></li>
          <li><a href="0003-frontend-stack.html"><span class="nav-num">ADR-3</span>Frontend Stack</a></li>
          <li><a href="0004-authorization-permission-model.html"><span class="nav-num">ADR-4</span>Authz &amp; Permissions</a></li>
          <li><a href="0005-repo-topology.html"><span class="nav-num">ADR-5</span>Repo Topology</a></li>
          <li><a href="0006-no-module-federation.html"><span class="nav-num">ADR-6</span>No Module Federation</a></li>
          <li><a href="0007-ui-stack-radix-cssmodules.html"><span class="nav-num">ADR-7</span>UI Stack &mdash; Radix</a></li>
          <li><a href="0008-mtp-web-folder-structure.html"><span class="nav-num">ADR-8</span>mtp-web Folder Structure</a></li>
          <li><a href="0009-hr-payroll-three-aggregates.html"><span class="nav-num">ADR-9</span>HR-Payroll &mdash; 3 Aggregates, 1 Module</a></li>
        </ul>
      </div>
      <div class="nav-group">
        <h4>GNUMS &mdash; Conventions</h4>
        <ul>
          <li><a href="../gnums/module-catalog.html"><span class="nav-num">MOD</span>Module Catalog</a></li>
          <li><a href="../gnums/all-modules-inventory.html"><span class="nav-num">REF</span>Procedure Inventory</a></li>
          <li><a href="../gnums/org-hierarchy.html"><span class="nav-num">ORG</span>Org Hierarchy</a></li>
          <li><a href="../gnums/hierarchy-comparison.html"><span class="nav-num">CMP</span>Hierarchy Comparison</a></li>
        </ul>
      </div>
      <div class="nav-group">
        <h4>GNUMS &mdash; Modules</h4>
        <ul>
          <li><a href="../gnums/methodology.html"><span class="nav-num">HOW</span>Study Methodology</a></li>
          <li><a href="../gnums/accounting/scope.html"><span class="nav-num">ACC</span>Accounting &mdash; Scope</a></li>
          <li><a href="../gnums/staff/scope.html"><span class="nav-num">STF</span>Staff &mdash; Scope</a></li>
          <li><a href="../gnums/hr-payroll/scope.html"><span class="nav-num">HRP</span>HR-Payroll &mdash; Scope</a></li>
          <li><a href="../gnums/exam/scope.html"><span class="nav-num">EXM</span>Exam &mdash; Scope</a></li>
          <li><a href="../gnums/exam/strategy.html"><span class="nav-num">PLAY</span>Exam &mdash; Strategy</a></li>
          <li><a href="../gnums/student/scope.html"><span class="nav-num">STU</span>Student &mdash; Scope</a></li>
          <li><a href="../gnums/student/strategy.html"><span class="nav-num">SPL</span>Student &mdash; Strategy</a></li>
          <li><a href="../gnums/student/cluster-01-core-identity.html"><span class="nav-num">SC1</span>Student / Core Identity</a></li>
          <li><a href="../gnums/student/cluster-02-subordinate-entities.html"><span class="nav-num">SC2</span>Student / Subordinate Entities</a></li>
          <li><a href="../gnums/student/cluster-03-master-config.html"><span class="nav-num">SC3</span>Student / Master &amp; Config</a></li>
          <li><a href="../gnums/student/cluster-04-certificate-workflow.html"><span class="nav-num">SC4</span>Student / Certificate Workflow</a></li>
          <li><a href="../gnums/student/cluster-05-bvm-activities.html"><span class="nav-num">SC5</span>Student / NCC NSS Yoga</a></li>
          <li><a href="../gnums/student/cluster-06-mentoring.html"><span class="nav-num">SC6</span>Student / Mentoring</a></li>
        </ul>
      </div>
    </nav>
  </aside>

  <main>
    <span class="eyebrow">ADR-0002</span>
    <h1>Database-per-client tenancy isolation</h1>
    <p class="lede">
      <span class="status-pill status-accepted">Accepted</span>
      &nbsp;·&nbsp; Each client owns its PostgreSQL database. Single API + single frontend
      deployment. Client identified by JWT claim or HTTP header; middleware resolves the
      per-client connection string at request time.
    </p>

    <h2>Context</h2>
    <p>
      The platform serves multiple client organisations. Each client may have distinct compliance,
      backup, customisation, and operational profiles. Three multi-tenancy patterns were
      considered:
    </p>
    <ol>
      <li><strong>Shared DB + tenant column</strong> — one schema, every table has <code>TenantId</code> FK. Row-level security enforces isolation.</li>
      <li><strong>Schema-per-tenant</strong> — one DB, separate Postgres schema per client.</li>
      <li><strong>Database-per-tenant</strong> — one Postgres database per client. Process-boundary isolation.</li>
    </ol>

    <h2>Decision</h2>
    <p>Adopt <strong>database-per-tenant</strong> with shared application deployment:</p>
    <ul>
      <li>Each client provisioned with its own Postgres database.</li>
      <li>Single ASP.NET Core Web API and single React frontend deployment serve all clients.</li>
      <li>Subdomain per client (<code>&lt;client&gt;.app.example.com</code>) routes to the same deployment via DNS wildcard.</li>
      <li>Client identity travels on every request as a JWT claim (<code>client_code</code>) or HTTP header (<code>X-Client-Code</code>). Subdomain hostname is a fallback / consistency check.</li>
      <li>A central <code>PlatformControl</code> Postgres database holds the <code>Client</code> registry mapping <code>ClientCode</code> → connection string.</li>
      <li>Middleware (<code>ClientContextMiddleware</code>) populates a scoped <code>IClientContext</code> on every request.</li>
      <li>Service layer obtains per-request DB connections via <code>IClientDbFactory</code>, which resolves the connection string from the registry.</li>
    </ul>
    <p>Detailed design lives in <a href="../docs/10-tenancy-model.html">page 10 · Tenancy Model</a>.</p>

    <h2>Consequences</h2>

    <h3>Easier</h3>
    <ul>
      <li><strong>Hard isolation.</strong> Different DB = different process boundary. Zero risk of leakage via a missing <code>WHERE</code> clause.</li>
      <li><strong>Per-client backup / restore.</strong> One client recovered without touching others.</li>
      <li><strong>Compliance posture.</strong> "Your data is in its own database" is easy to audit.</li>
      <li><strong>Per-client extension flexibility.</strong> If a single client needs a custom index or computed column, it stays scoped.</li>
      <li><strong>Schema migrations</strong> roll forward per client; canary on one before rolling to all.</li>
      <li><strong>No <code>TenantId</code> columns.</strong> Domain schema is cleaner; queries are simpler.</li>
      <li><strong>No row-level security policies.</strong> Postgres RLS is powerful but adds surface area; we don't need it.</li>
    </ul>

    <h3>Harder</h3>
    <ul>
      <li><strong>Cross-client analytics.</strong> No single SQL query spans clients. Use Postgres FDW, API-tier aggregation, or ETL into a warehouse.</li>
      <li><strong>Connection-string registry.</strong> One more operational concern; <code>PlatformControl</code> is now a critical dependency.</li>
      <li><strong>Migration tooling.</strong> Must replay against N databases. Per-client status table needed.</li>
      <li><strong>Connection pool sprawl.</strong> Npgsql pools per connection string. Many clients × deep pools = pressure on Postgres <code>max_connections</code>. May need PgBouncer.</li>
      <li><strong>Schema drift risk.</strong> Per-client customisations possible — must be policed (we forbid them except via config tables inside the client DB).</li>
      <li><strong>Provisioning complexity.</strong> Creating a client = create DB + run migrations + seed defaults + DNS + cert. Needs tooling.</li>
    </ul>

    <h2>Alternatives considered</h2>
    <table>
      <thead><tr><th>Alternative</th><th>Why rejected</th></tr></thead>
      <tbody>
        <tr>
          <td>Shared DB + <code>TenantId</code> column + RLS</td>
          <td>One missing predicate = data leak. RLS works but adds complexity. Backup/restore granularity poor. Per-client customisation impossible. No process-level isolation.</td>
        </tr>
        <tr>
          <td>Schema-per-tenant (one DB, N schemas)</td>
          <td>Marginal isolation gain over shared. Still one DB-wide <code>max_connections</code>. Backup/restore still all-or-nothing for the DB. Confusing search_path manipulation per request.</td>
        </tr>
        <tr>
          <td>Database-per-tenant <em>per server</em> (one RDS instance per client)</td>
          <td>Operational overkill. Cost-prohibitive at low client counts. Defer until a single client's load justifies it.</td>
        </tr>
        <tr>
          <td>Hybrid (shared for most, dedicated DB for premium)</td>
          <td>Two patterns to maintain. Worst of both worlds for now. Could be added later if a premium tier emerges.</td>
        </tr>
      </tbody>
    </table>

    <h2>Operational rules</h2>
    <ol>
      <li>API code never hardcodes a client code or connection string.</li>
      <li>Service-layer code never opens a connection without going through <code>IClientDbFactory</code>.</li>
      <li>No table inside a client DB has a <code>ClientId</code> column. The DB itself is the discriminator.</li>
      <li><code>PlatformControl</code> DB holds zero business data — operational metadata only.</li>
      <li>JWT/header/subdomain mismatch → reject with 400. No silent preference of one source.</li>
      <li>Integration tests provision and tear down disposable client DBs.</li>
    </ol>

    <h2>References</h2>
    <ul>
      <li><a href="../docs/10-tenancy-model.html">10 · Tenancy Model</a> — full design</li>
      <li><a href="../memory/project_mtp_tenancy_model.md">memory: tenancy model</a> — operational rules</li>
      <li><a href="../memory/project_mtp_pascalcase_db.md">memory: PascalCase DB</a> — schema naming convention</li>
    </ul>

    <div class="doc-footer">
      <a href="0001-hybrid-validation-strategy.html">
        <span class="label">← Previous</span>
        <span class="title">ADR-0001 · Hybrid Validation</span>
      </a>
      <a href="0003-frontend-stack.html" class="next">
        <span class="label">Next</span>
        <span class="title">ADR-0003 · Frontend Stack →</span>
      </a>
    </div>
  </main>
</div>
<script src="https://cdn.jsdelivr.net/npm/mermaid@10/dist/mermaid.min.js"></script>
<script>mermaid.initialize({ startOnLoad: true, theme: 'default' });</script>
</body>
</html>