# Database strategies

Packstub Tenancy supports two models of tenant data separation, selected by one config key — and they compose, so a single app can run both at once:

- **`dedicated`** (the default) — one database per tenant. Isolation is the connection switch itself: stancl/tenancy points the runtime at the tenant's own database, tenant tables carry no `tenant_id` column, and a tenant's data can be backed up, restored, exported, or deleted as one database.
- **`shared`** — many tenants per database. Isolation is a `tenant_id` relationship scope, applied automatically by Filament — optionally hardened with [PostgreSQL Row-Level Security](#row-level-security-database-enforced-isolation-postgresql) so the database itself enforces it. Rows live in the central database (or in shared *shard* databases spread across servers), tenants are ready the moment they sign up, and a new tenant costs one row instead of one database.

```php
// config/packstub-tenancy.php
'database_strategy' => 'dedicated', // or 'shared'
```

```php
// or fluently, per panel
use Packstub\Tenancy\TenancyPlugin;

TenancyPlugin::make()->databaseStrategy('shared');
```

Individual tenants can override the app-wide strategy at creation with an `isolation_mode` attribute — that is the [hybrid model](#hybrid-fleets-isolation_mode-per-tenant): a shared app that still gives an enterprise customer a private database, or a dedicated app with a lightweight shared free tier.

## Choosing a strategy

Both models are first-class; they optimize for different things.

| | `dedicated` | `shared` |
|---|---|---|
| Isolation mechanism | connection switch (structural) | `tenant_id` scope (query-level) |
| Signup cost | database create + migrate (queued) | one row — ready instantly |
| Per-tenant backup / export / delete | dump one database | row-level queries |
| Schema migrations | once per tenant (`tenants:migrate`) | once per database |
| Tenant count sweet spot | tens to thousands | thousands and up |
| Horizontal scaling | pool of database **servers** | pool of shared **shard databases** |
| Database-enforced isolation | inherent (separate databases) | optional — [PostgreSQL RLS](#row-level-security-database-enforced-isolation-postgresql) |
| Great for | B2B SaaS, compliance, data residency | high-volume signups, free tiers, B2C |

Rules of thumb:

- Choose **dedicated** when tenants are businesses that care where their data lives, when per-tenant backup/restore matters, or when you never want isolation to depend on every query being scoped correctly.
- Choose **shared** when signups are high-volume and low-friction, when thousands of mostly-small tenants would mean thousands of databases, or when your team already runs the classic `tenant_id` model comfortably.
- Choose **both** (hybrid) when your pricing tiers map to isolation — shared for the free tier, a private database for the enterprise plan.

## The dedicated strategy (default)

This is the model the rest of the documentation describes: signup queues the `CreateDatabase → MigrateDatabase → MarkTenantReady` pipeline, the waiting room shows progress, and stancl's `DatabaseTenancyBootstrapper` switches the runtime connection per request. Nothing changes for existing installs — `dedicated` is the shipped default, and Filament's automatic resource scoping stays **off** because the connection switch already isolates every query.

Setup is covered by [Installation](https://packstub.dev/docs/filament-tenancy/installation) and [Quickstart](https://packstub.dev/docs/filament-tenancy/quickstart); spreading tenant databases across servers is [Horizontal scaling](https://packstub.dev/docs/filament-tenancy/horizontal-scaling).

## The shared strategy

### 1. Flip the strategy

```php
// config/packstub-tenancy.php
'database_strategy' => 'shared',
```

From this moment, every new tenant is created **database-less**: the plugin stamps `isolation_mode = 'shared'` and stancl's `create_database => false` flag on the tenant, skips the database pipeline entirely, and marks the tenant ready through a queued `MarkTenantReady`-only pipeline. No `CreateDatabase`, no per-tenant migrations — signups complete in the time it takes the queue to turn around.

At runtime the plugin routes stancl's bootstrappers per tenant: a shared tenant with no shard connection skips `DatabaseTenancyBootstrapper`, so queries stay on the central connection where the rows actually live. (Cache, filesystem, and other bootstrappers you configured keep running — only the database switch is dropped, and only for tenants that have no database to switch to.)

### 2. Give tenant-owned tables a `tenant_id`

Tenant-owned tables live in your **central** migrations and carry a foreign key to `tenants`:

```php
Schema::create('projects', function (Blueprint $table) {
    $table->id();
    $table->string('tenant_id');
    $table->foreign('tenant_id')->references('id')->on('tenants')->cascadeOnDelete();
    $table->string('name');
    $table->timestamps();

    $table->index('tenant_id');
});
```

Two notes:

- The shipped `tenants` table uses **string (UUID) keys** — stancl's convention — so the foreign key column is a `string`, not an unsigned integer. If you published the migrations with an integer tenant key, match it.
- `cascadeOnDelete()` is doing real work here: under the shared strategy, deleting a tenant does **not** drop a database, so the cascade (or your own cleanup listener) is what removes the tenant's rows. See [Deleting shared tenants](#deleting-shared-tenants).

Each tenant-owned model then defines the relationship back to the tenant:

```php
use Packstub\Tenancy\Models\Tenant;

class Project extends Model
{
    public function tenant(): BelongsTo
    {
        return $this->belongsTo(Tenant::class);
    }
}
```

### 3. Let Filament scope everything

Under the shared strategy, `scope_resources_to_tenant` defaults to **true**: the plugin calls Filament's `Resource::scopeToTenant()`, and every resource query is automatically constrained to the current tenant through the ownership relationship (`whereBelongsTo($tenant)`), while created records are automatically associated with it. If your relationship is not named after the tenant model (`tenant()` for the shipped `Tenant` model), point Filament at it:

```php
// config/packstub-tenancy.php
'ownership_relationship' => 'organization',
```

Filament's scoping covers panel resources. Everything that runs *outside* a panel query — raw SQL, reports, artisan commands, queued jobs — must scope by `tenant_id` itself, exactly as in any single-database tenancy app. If you want belt-and-braces, add a global scope to your tenant-owned models keyed off `tenant()` / Filament's current tenant; the plugin doesn't impose one.

### 4. Done — unless you want more than one database

That's the whole setup for the single-database form: tenants sign up into the central database, Filament isolates them by scope, and there is nothing to provision, pool, or migrate per tenant. When one database stops being enough, add shards:

## Sharded shared: many tenants per database, many databases

The [database pool](https://packstub.dev/docs/filament-tenancy/horizontal-scaling) works under both strategies — it just places different things. Under `shared`, each pool member is a **pre-provisioned shard database**: an ordinary connection in `config/database.php` whose `database` already exists and is already migrated. New tenants are load-balanced across shards with the same strategies (`least-tenants`, `round-robin`, `weighted`), and the placement is persisted on the tenant as `tenancy_db_connection` **plus** `tenancy_db_name` — with those two attributes set, stancl's stock `DatabaseTenancyBootstrapper` connects each request to the right shard, and inside the shard the `tenant_id` scope isolates as before.

### 1. Provision the shards

Each shard is a normal database you create and migrate yourself — shards hold the same schema as your central tenant-owned tables:

```sql
CREATE DATABASE tenant_shard_1;
```

```php
// config/database.php
'shard_1' => [
    'driver' => 'mysql',
    'host' => env('SHARD_1_HOST', '10.0.1.10'),
    'port' => env('SHARD_1_PORT', 3306),
    'database' => 'tenant_shard_1',   // must exist — validation enforces it
    'username' => env('SHARD_1_USERNAME'),
    'password' => env('SHARD_1_PASSWORD'),
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
    'strict' => true,
],
```

Run your tenant-owned migrations against every shard whenever they change — shards migrate like databases, not like tenants:

```bash
php artisan migrate --database=shard_1 --path=database/migrations/shared
php artisan migrate --database=shard_2 --path=database/migrations/shared
```

(Keeping shard-schema migrations in their own directory, e.g. `database/migrations/shared`, makes this a clean loop — and keeps central-only tables out of the shards.)

### 2. Enroll them in the pool

```php
'database_pool' => [
    'enabled' => true,
    'connections' => ['shard_1', 'shard_2'],
    'strategy' => 'least-tenants',
    'weights' => [],
],
```

Boot validation checks every member as usual, plus one shared-strategy rule: each member must have a `database` configured — that name is what gets persisted per tenant as `tenancy_db_name`.

`tenants:pool` shows the tenant distribution across shards exactly as it does across servers, and `tenants:pool --check` probes shard **reachability** instead of `CREATE DATABASE` privileges — nothing is ever created on a shard, so the placement user needs no admin rights.

### 3. Scale out the same way

Adding shard capacity is the same runbook as adding a server in dedicated mode: create + migrate the new shard database, add its connection, append the name to the pool, deploy everywhere (queue workers too), and `least-tenants` fills it first. Placement is sticky — existing tenants never move on their own — and a tenant can be pinned to a specific shard by creating it with an explicit `tenancy_db_connection` + `tenancy_db_name`.

Note that the shard user does need day-to-day query privileges on its shard, and every app host (web and workers) needs the shard connections defined — the same fleet-wide config rule as the dedicated pool.

## Row-Level Security: database-enforced isolation (PostgreSQL)

Because the shared strategy puts a `tenant_id` on every tenant-owned row, it unlocks a third layer most `tenant_id` apps never get around to: **PostgreSQL Row-Level Security**. With RLS enabled, isolation is enforced by the database itself — a query that forgets its scope (a raw report, a hand-written join, a debugging session in Tinker) physically cannot return another tenant's rows, because Postgres filters them out before the app ever sees them.

The layers complement each other rather than compete:

- **Filament's relationship scope** (on by default under the shared strategy) drives the UI: it constrains resource queries *and* associates newly created records with the current tenant.
- **RLS policies** are the backstop: whatever the query looks like, the database only yields rows whose `tenant_id` matches the current tenant.

stancl/tenancy v4 ships the entire RLS toolchain, and the shared strategy composes with it directly — shared tenants keep every bootstrapper you configure (the plugin only removes the database *switch*), which is exactly the seam RLS plugs into.

### 1. Configure the RLS user and session variable

```php
// config/tenancy.php
'rls' => [
    'manager' => Stancl\Tenancy\RLS\PolicyManagers\TableRLSManager::class,
    'user' => [
        'username' => env('TENANCY_RLS_USERNAME'),
        'password' => env('TENANCY_RLS_PASSWORD'),
    ],
    // Must be namespaced ('app.', 'my.', …) — the global namespace is
    // reserved for server configuration.
    'session_variable_name' => 'app.current_tenant',
],
```

This is **one** database user for *all* tenants — a deliberately non-owner, non-superuser role that RLS policies actually apply to (table owners and superusers bypass RLS). Your central connection keeps using the privileged user for migrations and central-only tables.

### 2. Generate the policies

```bash
php artisan tenants:rls
```

The command creates the RLS user if it doesn't exist and generates a policy for **every table related to the `tenants` table** — including indirectly related ones. The default `TableRLSManager` walks foreign keys, so `comments → posts → tenants` gets a policy that scopes comments through their post's tenant:

```sql
CREATE POLICY posts_rls_policy ON posts USING (
    tenant_id::text = current_setting('app.current_tenant')
);
```

(Prefer declaring RLS-protected models explicitly? Swap in `TraitRLSManager` and mark models with stancl's `RLSModel` concern.)

### 3. Swap the bootstrapper

```php
// config/tenancy.php — a pure shared + RLS app
'bootstrappers' => [
    Stancl\Tenancy\Bootstrappers\PostgresRLSBootstrapper::class,
    // ...cache/filesystem bootstrappers as needed
],
```

On tenancy initialization, `PostgresRLSBootstrapper` reconnects as the RLS user and runs `SET app.current_tenant = '<tenant key>'`; on end, it resets the variable and returns to the central connection. From that moment every query in tenant context is filtered by the database, no matter who wrote it.

### Caveats worth knowing

- **PostgreSQL, single-database form.** Stancl's stock RLS bootstrapper builds the tenant connection from the *central* connection, so it targets the shared strategy's single-database form. RLS on **shards** is possible but bring-your-own: create the RLS user and policies per shard and register a small custom bootstrapper that applies the shard connection + RLS credentials together.
- **Hybrid fleets need explicit routing.** In a mixed app, dedicated tenants must get `DatabaseTenancyBootstrapper` and shared tenants `PostgresRLSBootstrapper` — set your own router and the plugin steps aside (it never overwrites an app-owned closure):

  ```php
  use Stancl\Tenancy\Bootstrappers\DatabaseTenancyBootstrapper;
  use Stancl\Tenancy\Bootstrappers\PostgresRLSBootstrapper;

  tenancy()->getBootstrappersUsing = fn ($tenant) => $tenant?->isolation_mode === 'dedicated'
      ? [DatabaseTenancyBootstrapper::class]
      : [PostgresRLSBootstrapper::class];
  ```

- **Connection poolers.** The tenant key lives in a Postgres *session* variable. Behind a transaction-pooling PgBouncer, session state doesn't stick to your connection — use session pooling for the RLS connection (or route it around the pooler).
- **Keep Filament's scoping on.** RLS filters reads and blocks cross-tenant writes, but it doesn't *assign* `tenant_id` to new records — Filament's ownership relationship does that on create. They're better together.

## Hybrid fleets: `isolation_mode` per tenant

The app-wide strategy is a default, not a straitjacket. Any tenant can be created with the other model:

```php
use Packstub\Tenancy\Models\Tenant;

// A private database for an enterprise tenant inside a SHARED app —
// the full provisioning pipeline runs, just for this tenant:
Tenant::create([
    'name' => 'Enterprise Co',
    'slug' => 'enterprise',
    'isolation_mode' => 'dedicated',
]);

// A shared-model tenant inside a DEDICATED app — database-less, instantly ready:
Tenant::create([
    'name' => 'Free Tier Co',
    'slug' => 'free-tier',
    'isolation_mode' => 'shared',
]);
```

`isolation_mode` persists via stancl's VirtualColumn (`data` column) — no schema change needed — and the plugin routes everything off it and the `create_database` flag it implies: the provisioning pipeline, pool placement, bootstrapper selection, and deletion behavior all follow the tenant, not the app default.

Two placement notes for hybrids:

- In a **shared** app, a `dedicated` tenant is *not* placed by the pool (pool members are shards, not `CREATE DATABASE` servers). Give it an explicit `tenancy_db_connection` pin pointing at a server connection — the same pin data-residency tenants use — or let it follow stancl's `template_tenant_connection`.
- In a **dedicated** app, a `shared` tenant lives in the central database (there is no shard pool to place it on).

Combined with pins, this covers the full spectrum in one app: free tier in the central database, standard tier on shards, enterprise on a private database, regulated customers on a private database on their own server.

## Deleting shared tenants

Deleting a dedicated tenant drops its database. Deleting a shared tenant deliberately does **not** touch the shared database — stancl's `DeleteDatabase` job skips database-less tenants, which is exactly what protects a shard holding hundreds of other tenants.

That means the tenant's *rows* are your responsibility. The clean options:

- `cascadeOnDelete()` foreign keys from every tenant-owned table to `tenants` (single-database form — the cascade lives where the rows live), or
- a `deleting` observer / `TenantDeleted` listener that purges rows by `tenant_id` — required for sharded setups, where the rows live in a different database than the `tenants` row:

```php
use Stancl\Tenancy\Events\TenantDeleted;

Event::listen(TenantDeleted::class, function (TenantDeleted $event) {
    $tenant = $event->tenant;

    if ($tenant->getInternal('create_database') !== false) {
        return; // dedicated tenant — DeleteDatabase already dropped its DB
    }

    $connection = $tenant->getInternal('db_connection'); // null = central
    DB::connection($connection)->table('projects')->where('tenant_id', $tenant->getTenantKey())->delete();
    // ...one line per tenant-owned table, or your own cascade helper
});
```

## Moving a tenant between modes

Because shared-model tables carry `tenant_id` everywhere, a "promotion" is a data move, not a schema change — a `tenant_id` scope is harmless in a database that contains one tenant.

**Shared → dedicated** (customer upgraded to a private database):

1. Provision an empty database (on a dedicated server if required) and run the shared-schema migrations against it.
2. Copy the tenant's rows (`WHERE tenant_id = ...`, every tenant-owned table) from the shard/central into it.
3. Update the tenant: set `isolation_mode = 'dedicated'`, `tenancy_db_connection` to the new server connection, `tenancy_db_name` to the new database, and unset `create_database` (or leave it `false` and keep the bootstrapper routing — with connection + name set, the runtime connects to the private database either way).
4. Verify, then delete the old rows from the shard.

**Dedicated → shared** is the mirror image (copy rows into a shard, set `isolation_mode = 'shared'`, `create_database = false`, point connection + name at the shard, drop the old private database yourself).

Both are manual, windowed operations by design — the plugin never moves data behind your back, the same policy as [moving a tenant between servers](https://packstub.dev/docs/filament-tenancy/horizontal-scaling#moving-a-tenant-between-servers).

## Testing your setup

The package's own suites are templates for yours:

- `tests/Feature/DatabaseStrategyTest.php` — strategy defaults, per-tenant `isolation_mode` overrides, scoping defaults.
- `tests/Feature/SharedDatabaseStrategyTest.php` — shared tenants staying on the central connection, hybrid dedicated tenants keeping the full bootstrapper stack.
- `tests/Feature/SharedDatabasePoolTest.php` — shard placement, two tenants sharing one database at runtime, deletion leaving the shard intact, `tenants:pool --check` under the shared strategy.

The pattern: set `packstub-tenancy.database_strategy` (in `defineEnvironment`, so boot-time wiring sees it), create tenants through your real signup path, then assert on `isolation_mode`, `getInternal('create_database')`, `getInternal('db_connection')` / `getInternal('db_name')`, and — for runtime behavior — `DB::connection()->getDatabaseName()` inside `tenancy()->initialize($tenant)`.

## FAQ

**Does switching the strategy affect existing tenants?**
No. The strategy is applied at tenant *creation* — existing tenants keep their `isolation_mode`, connections, and databases. Switching the config only changes what new signups get, so you can migrate a fleet gradually (or never).

**Can I use the shared strategy without Filament's resource scoping?**
Yes — set `scope_resources_to_tenant => false` explicitly and bring your own isolation (global scopes, RLS policies). The strategy still gives you database-less signups, bootstrapper routing, and shard placement; you own the query-level isolation.

**Do shared tenants still fire stancl events and initialize tenancy?**
Yes. Filament's resolved tenant is bridged into `tenancy()->initialize()` as always, so `tenant()`, tenant-aware cache/filesystem bootstrappers, and your own listeners work in both strategies. Only the database connection switch is skipped, and only for tenants without a database.

**Can shards and the central database share a server?**
Yes — shards are just connections. A common starting topology is central + `shard_1` on one server, with new shards added on new servers as you grow.

**What about PostgreSQL Row-Level Security?**
Fully supported — stancl v4 ships the toolchain (`tenants:rls`, `PostgresRLSBootstrapper`, FK-walking policy generation) and the shared strategy plugs straight into it, giving you database-enforced isolation on top of Filament's scoping. See [Row-Level Security](#row-level-security-database-enforced-isolation-postgresql).

## Next steps

- Setting up from scratch? [Installation](https://packstub.dev/docs/filament-tenancy/installation) → flip `database_strategy` before your first signup.
- Spreading dedicated tenants across servers? [Horizontal scaling](https://packstub.dev/docs/filament-tenancy/horizontal-scaling).
- The complete key-by-key reference? [Configuration](https://packstub.dev/docs/filament-tenancy/configuration#database_strategy).