# Multi-tenancy architecture

How AGS Vocab serves many schools out of one Laravel + MySQL deployment.

> **Status: Phase 0 complete.** Schema, scoping, Filament admin, OTP +
> Sanctum auth, and isolation tests are all in place. Subdomain DNS,
> self-service signup, billing, and dedicated-DB graduation are
> follow-up phases (1-4).

---

## 1. Why this architecture

We chose **single-DB shared-schema with a discriminator column** (`school_id`
on every tenant-owned table) over physically-separated databases per
tenant.

**Reasoning** (as discussed during architecture decision):

- Lower ops burden — one migration set, one backup target, one log stream
- Cross-school analytics (AGS HQ dashboard, leaderboards, kudos
  reconciliation) is one query, not N joins across DBs
- Easier migration FROM single-tenant (which is what we had at the
  start) — adding `school_id` is a column add; converting to per-DB
  would have been rebuilding the world
- An escape hatch exists for premium customers needing hard isolation
  (`schools.tenancy_strategy = 'dedicated_db'`); details in §10

**Mitigation for the obvious risk** — every Eloquent query MUST be filtered
by `school_id`. We address this by:

- Global query scopes attached via traits (`BelongsToSchool` /
  `BelongsToSchoolOrShared`) so application code doesn't have to
  remember
- Explicit `Model::withoutTenancy()` macro for HQ super-admin tools that
  legitimately span schools — the explicit-ness makes audit easy
- Feature tests (`tests/Feature/MultiTenancyTest.php`) that exercise the
  cross-tenant leak paths

---

## 2. Schema

### 2.1 The tenant root

**`schools`** — one row per tenant.

| Column | Purpose |
|---|---|
| `id` | PK |
| `slug` | URL-safe identifier ("all-gifted") — used in admin URLs |
| `name` | Display name |
| `subdomain` | unique nullable; e.g. "acme" for `acme.agsvocab.com` |
| `custom_domain` | unique nullable; premium: customer-owned host |
| `status` | onboarding / **active** / trial_expired / suspended / archived |
| `subscription_tier` | trial / basic / premium / enterprise |
| `tenancy_strategy` | **shared_db** (default) / dedicated_db (Phase 4) |
| `tenant_db_name` | nullable; populated when strategy is dedicated_db |
| `trial_ends_at`, `contact_email`, `contact_phone` | onboarding meta |
| `timezone`, `locale` | per-school i18n |
| `is_ags_branding_locked` | controls the AGS attribution badge — see §6 |
| `meta` | JSON catch-all |

### 2.2 The membership pivot

**`school_users`** — many-to-many between users and schools.

| Column | Purpose |
|---|---|
| `school_id`, `user_id` | composite UNIQUE |
| `role` | school_admin / teacher / **learner** |
| `status` | pending / **active** / inactive / removed |
| `invited_by`, `invited_at`, `joined_at`, `removed_at` | onboarding audit |
| `meta` | JSON per-membership flags |

A user can belong to multiple schools (learner transfers; HQ admins
auditing). Each membership has its own role and status.

### 2.3 `school_id` on tenant-owned tables

| Behaviour | Tables | Constraint |
|---|---|---|
| **Always required** (NOT NULL + ON DELETE CASCADE) — every row belongs to exactly one tenant. | `test_sessions`, `responses`, `ability_estimates`, `kudo_events`, `life_events`, `otp_codes`, `pronunciations` | NOT NULL, CASCADE |
| **Shared-library or tenant-private** (NULL = AGS global; non-null = tenant-private) | `words`, `questions`, `configs` | NULL allowed, SET NULL on school delete |
| **User special-case** (NULL = HQ super-admin) | `users` | NULL allowed, SET NULL on school delete |
| **Sanctum tokens** (NULL pre-tenancy or super-admin) | `personal_access_tokens` | NULL allowed, CASCADE on school delete |

### 2.4 Forward-compat columns on `users`

- `external_id` — nullable, unique. Future hook for centralising identity
  in an `account.allgifted.com` service. NULL today; not used.
- `global_role` — `'super_admin'` for AGS HQ users (Pamela). NULL for
  ordinary tenant users — their role lives on `school_users.role` instead.

---

## 3. Tenant resolution

The `ResolveSchool` middleware
(`app/Http/Middleware/ResolveSchool.php`) runs on every API request and
every Filament admin request. It tries five resolution strategies in
priority order and binds the winner to `app('current_school')`:

1. **`X-Tenant` header** — explicit tenant ID or slug. Used by Flutter
   mobile and tests, where subdomain routing isn't practical.
2. **Sanctum token claim** — the bearer token's `school_id` column
   (added in Phase 0.6). A learner who signed into Bayview can hit the
   API from any host and stay scoped to Bayview.
3. **Custom domain match** — `schools.custom_domain == request.host`.
   Premium feature.
4. **Subdomain match** — left-most label of the request host (e.g.
   "acme" in `acme.agsvocab.com`) matched against `schools.subdomain`.
5. **Dev/test fallback** — only in `local`/`testing` env, falls back to
   `config('tenancy.default_school_slug')` (default `all-gifted`). In
   production an unresolved tenant returns **404**.

Suspended / archived schools are blocked with **403** unless the user is
a `super_admin`.

Filament's admin panel runs a separate copy of the resolution via
`SyncFilamentTenantToScope` (`app/Http/Middleware/SyncFilamentTenantToScope.php`),
which bridges Filament's own tenant binding (`Filament::getTenant()`) to
`app('current_school')` so the same Eloquent scopes apply in the admin
context.

---

## 4. Scoping

Two traits in `app/Models/Concerns/` apply the right query filter:

### 4.1 `BelongsToSchool`

For tables whose `school_id` must always be non-null
(test_sessions, responses, kudo_events, etc.).

```php
class TestSession extends Model
{
    use BelongsToSchool;
    ...
}
```

What it gives you:
- Global query scope: every query auto-filters by
  `WHERE table.school_id = app('current_school').id`
- `creating` hook: auto-fills `school_id` from the current container
  binding (so application code doesn't have to remember)
- `school()` BelongsTo relationship
- `Model::withoutTenancy()` escape hatch for HQ super-admin queries

### 4.2 `BelongsToSchoolOrShared`

For tables where `school_id NULL` means "shared / global library"
(words, questions, configs):

```php
class Word extends Model
{
    use BelongsToSchoolOrShared;
    ...
}
```

What it gives you:
- Global query scope:
  `WHERE table.school_id IS NULL OR table.school_id = :current`
- `isShared()` instance check
- `Model::onlyShared()` — just the AGS-curated rows
- `Model::onlyOwn()` — just the current tenant's private items
- `Model::withoutTenancy()` escape hatch

The shared-library design lets every tenant inherit the AGS-curated
~1,900-word bank, while a school can ALSO add its own private content
(e.g. an international school in Singapore enabling Singlish vocab they
hand-curated for their context).

### 4.3 The dev escape: `withoutTenancy()`

Available on every model using either trait. Use it for:
- AGS HQ super-admin views ("how many sessions across ALL schools?")
- Cross-tenant analytics jobs
- Backfill / migration scripts

Always grep for `withoutTenancy(` in code review — it's the only path
that bypasses the safety net, and every use should be deliberate.

---

## 5. Content-sharing model

| Table | Default | Per-school override |
|---|---|---|
| `words` | All AGS-curated (1,900+ rows, `school_id` NULL) | A school can add private words with their `school_id` |
| `questions` | All AGS-generated (2,600+ rows, NULL) | Same — schools can author bespoke items |
| `configs` | AGS defaults for branding/feature flags (NULL) | School admins override per-key from `/admin/{slug}/configs` |

The composite UNIQUE on `configs(school_id, key)` (migration
`2026_05_24_000009`) means each tenant can override any setting while
the AGS default remains untouched. `SiteConfig::all()` merges the two
with the tenant row winning for duplicate keys.

---

## 6. AGS attribution rule

The "Powered by AGS Vocab" badge is **non-removable for ordinary tenants**.

Driven by `School::mustShowAgsAttribution()`:

```php
public function mustShowAgsAttribution(): bool
{
    return $this->is_ags_branding_locked
        || $this->subscription_tier !== self::TIER_ENTERPRISE;
}
```

Returns **true** unless the school is enterprise-tier AND has
`is_ags_branding_locked = false`. Practical effect:

- **All Gifted** (enterprise, branding-lock off) → NO badge. They ARE the brand.
- **Bayview Primary** (trial, branding-locked) → badge required.
- A future enterprise customer paying for white-label can have an admin
  flip `is_ags_branding_locked = false` for them.

Rendered server-side:

- Filament admin: footer render hook
  (`resources/views/partials/ags-attribution.blade.php`) mounted via
  `PanelsRenderHook::FOOTER`. View checks the rule and emits nothing
  if the badge isn't required.
- Flutter app: `GET /api/config` returns `school.must_show_ags_attribution`;
  Flutter renders the footer chip when true. Label text comes from
  `config/tenancy.php → attribution_label`.

UI layers should never read `is_ags_branding_locked` directly — always
go through `mustShowAgsAttribution()`.

---

## 7. Filament admin integration

`AdminPanelProvider` (`app/Providers/Filament/AdminPanelProvider.php`)
enables Filament v3 native tenancy:

```php
$panel
    ->tenant(School::class, slugAttribute: 'slug')
    ->tenantMiddleware([
        SyncFilamentTenantToScope::class,
    ], isPersistent: true)
    ...
```

Effects:

- URLs become `/admin/{school-slug}/...` — e.g. `/admin/all-gifted/configs`,
  `/admin/bayview-primary/words`
- `/admin/login` stays tenant-less; after sign-in the user is redirected
  to their default tenant
- Tenant switcher in the admin sidebar (visible only when the user
  belongs to 2+ schools)
- `User::getTenants(Panel $panel)` returns:
  - Every school for `super_admin` users
  - Active `school_users` memberships for ordinary users
- `User::canAccessTenant(Model $tenant)` gates the URL guard
- Branding (logo, name, font, favicon) is read per-request through
  `tenantConfig()` closures so each tenant's `configs` overrides apply

The `SyncFilamentTenantToScope` middleware binds the Filament-resolved
tenant to `app('current_school')` so the same model traits apply.

---

## 8. OTP + Sanctum auth

**OTP codes** are scoped per tenant. `otp_codes.school_id` is NOT NULL;
the `BelongsToSchool` trait auto-fills it on creation. A learner whose
email is registered at Acme School cannot use Acme's OTP code to sign
into Bayview — they'd request a separate code on Bayview's subdomain.

**Sanctum tokens** carry a `school_id` column
(`personal_access_tokens.school_id`, added in
`2026_05_25_000001_add_school_id_to_personal_access_tokens`). When
`AuthController::verifyOtp` creates a token, it stamps it with the
school the user signed into. The `ResolveSchool` middleware reads this
as resolution step #2 — so any subsequent API request resolves to the
correct tenant even if the host doesn't match a subdomain.

`OtpService::findOrCreateUser` also:
- Stamps `users.school_id` on new users (their "home school")
- Creates a `school_users` pivot row with `role = learner, status = active`

A user who signs in to a SECOND school's subdomain gets a second
`school_users` row added (cross-school transfer pattern).

---

## 9. Tests

`tests/Feature/MultiTenancyTest.php` — 12 tests, 30 assertions, all green.

| Test | What it proves |
|---|---|
| `tenant_scope_filters_sessions_to_current_school` | The global scope works |
| `shared_library_words_visible_to_every_tenant` | Shared-library design works |
| `private_word_for_one_tenant_invisible_to_others` | The critical leak test |
| `without_tenancy_escape_hatch_works_for_hq_admin_queries` | The escape hatch works |
| `only_shared_and_only_own_scope_macros_partition_correctly` | Partitioning math holds |
| `config_overrides_are_per_tenant` | Per-tenant config overrides work |
| `api_request_unknown_subdomain_falls_back_in_dev` | Dev fallback works |
| `api_request_with_x_tenant_header_resolves_to_named_school` | Header resolution works |
| `resolve_school_middleware_404s_when_no_tenant_resolves_and_mode_required` | Production gate works |
| `ags_attribution_rule_is_per_tenant` | AGS branding rule works |
| `super_admin_can_access_every_tenant` | Super-admin escape works |
| `ordinary_user_can_only_access_own_schools` | Ordinary user gate works |

Run them with `php artisan test --filter=MultiTenancyTest`. They use
`DatabaseTransactions` so production data stays untouched.

---

## 10. Future: the dedicated-DB escape (Phase 4)

`schools.tenancy_strategy` is the hook. Today every school is
`shared_db`. To graduate a premium customer to a dedicated database:

1. Provision the new DB and run `php artisan migrate` against it
2. Copy the school's rows from the shared DB to the dedicated DB
3. Set `schools.tenancy_strategy = 'dedicated_db'` and `tenant_db_name = 'vocabile_acme'`
4. Add a `SwitchDatabaseForSchool` middleware that changes the active
   Laravel connection to `tenant_db_name` for that school's requests
5. Soft-delete the school's rows from the shared DB

The shared-DB code path stays untouched for every other tenant. Two
schools could even live on different MySQL servers if needed for data
residency.

---

## 11. How to add a new school

Today (via tinker or seeder):

```php
\App\Models\School::create([
    'slug' => 'oakridge',
    'name' => 'Oakridge Secondary',
    'subdomain' => 'oakridge',
    'status' => 'active',
    'subscription_tier' => 'trial',
    'is_ags_branding_locked' => true,
    'contact_email' => 'admin@oakridge.edu.sg',
]);
```

Then invite the first school_admin manually:

```php
$user = \App\Models\User::firstOrCreate(['email' => 'principal@oakridge.edu.sg'], [...]);
\App\Models\SchoolUser::create([
    'school_id' => $school->id,
    'user_id'   => $user->id,
    'role'      => 'school_admin',
    'status'    => 'active',
    'joined_at' => now(),
]);
```

After Phase 2 (self-service), this becomes a public signup form +
Stripe billing.

---

## 12. Known limitations / Phase 1+ follow-ups

- **Subdomain DNS** isn't wired yet. In dev, the middleware uses the
  default-school fallback. Production needs `*.agsvocab.com` wildcard
  DNS + SSL cert.
- **No self-service signup**. Schools are added manually for now.
- **No Stripe / billing**. `subscription_tier` is set manually.
- **Flutter footer attribution** — the Flutter UI doesn't yet render
  the "Powered by AGS Vocab" chip from the new ConfigController
  payload. Small Flutter change.
- **AGS Math is still single-tenant.** Cross-product identity (one
  account spanning Vocab + Math) is a separate uplift.
- **No data-export tooling** for tenants leaving (PDPA / GDPR
  portability).
- **Admin tenant-switcher UI** uses Filament defaults; might want to
  customise for super-admins moving between many schools.

---

## 13. Migration history

The Phase 0 migrations, in order:

| Migration | Phase | What |
|---|---|---|
| `2026_05_24_000006_create_schools_and_school_users_tables.php` | 0.1 | Tenant root + membership pivot |
| `2026_05_24_000007_add_school_id_to_tenant_tables.php` | 0.3 | `school_id` on 11 tenant-owned tables (nullable) |
| `2026_05_24_000008_backfill_school_id_and_lock_columns.php` | 0.4 | Backfill to school #1, swap FK to CASCADE, NOT NULL where appropriate |
| `2026_05_24_000009_make_configs_key_unique_per_tenant.php` | 0.5 | Composite UNIQUE on `configs(school_id, key)` |
| `2026_05_25_000001_add_school_id_to_personal_access_tokens.php` | 0.6 | Sanctum tokens carry the signed-in tenant |

Pre-tenancy backup: `storage/backups/pre-tenancy-20260524-201545.sql`
(2.0 MB). Restore point if Phase 0 ever needs to be rolled back.