Designing a Multi-Tenant Video Platform Schema with PostgreSQL RLS

Six months ago a media partner asked us to white-label the trend feed for their own newsroom, and that single request broke the assumption our entire backend rested on: one database, one set of tables, one tenant. For years ViralVidVault ran on SQLite in WAL mode behind LiteSpeed and Cloudflare Workers, tracking viral videos across European markets. That stack is genuinely great for a single-tenant, read-heavy discovery site. It is the wrong tool the moment three partners each want an isolated dashboard over the same crawl pipeline, each with its own retention window and its own GDPR data-subject requests to honour. That is a PostgreSQL problem now, and the schema is where multi-tenancy is won or lost. Everything else — the caching layer, the edge workers, the API — is downstream of getting the tables right.

Three isolation models, and why the middle one won

Before writing a single CREATE TABLE, you have to pick how tenants are separated. There are three honest options, and the internet loves to argue about them without stating scale.

• Database-per-tenant. Each partner gets a physically separate database. Isolation is airtight, per-tenant data residency is trivial, and a pg_dump is a clean export for a leaving customer. But every schema migration now runs N times, connection pools multiply, and any cross-tenant question ("which video trended in five markets this week?") becomes an application-level fan-out. This shines at thousands of tenants with strict compliance walls — and it is overkill at dozens.
• Schema-per-tenant. One database, a Postgres schema namespace per tenant, search_path juggled per request. It looks tidy until you have hundreds of schemas and pg_dump takes an hour walking metadata, and until a migration has to loop over every schema anyway.
• Shared tables with a tenant_id column plus Row-Level Security. One set of tables, one migration, trivial cross-tenant analytics. The famous risk is that a single forgotten WHERE tenant_id = ? leaks another partner's data. The fix is to stop relying on the application to remember that clause and push enforcement into the database.

We are at dozens of tenants, not thousands. But the argument that actually settled it was the catalogue itself: we crawl viral videos once for all of Europe. Duplicating that catalogue into a database-per-tenant would waste storage and, worse, waste our crawl and API quota re-fetching the same YouTube and TikTok metadata. A shared catalogue with per-tenant curation on top is not just cheaper — it is the whole point of a discovery platform. So: shared schema, tenant_id, and RLS as the seatbelt.

The core schema

The key design decision is what is global and what is tenant-scoped. The video catalogue is global and deduplicated — it belongs to no tenant. Curation, users, and analytics events are tenant data. That split is the spine of everything below.

-- Tenants: each white-label partner / newsroom.
CREATE TABLE tenant (
    id            bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    slug          text NOT NULL UNIQUE,
    display_name  text NOT NULL,
    region        text NOT NULL DEFAULT 'eu-central',   -- data residency hint
    created_at    timestamptz NOT NULL DEFAULT now()
);

-- Global, deduplicated video catalogue. Crawled ONCE for everyone.
CREATE TABLE video (
    id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    platform     text   NOT NULL,          -- youtube | tiktok | reels
    external_id  text   NOT NULL,
    title        text   NOT NULL,
    lang         text,
    country      text,                      -- origin market
    published_at timestamptz,
    UNIQUE (platform, external_id)
);

-- Per-tenant curation of the shared catalogue. This table IS tenant data.
CREATE TABLE tenant_video (
    tenant_id    bigint NOT NULL REFERENCES tenant(id),
    video_id     bigint NOT NULL REFERENCES video(id),
    curated_rank int    NOT NULL,
    added_by     bigint,
    added_at     timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (tenant_id, video_id)
);

CREATE TABLE app_user (
    id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tenant_id    bigint NOT NULL REFERENCES tenant(id),
    email        text   NOT NULL,
    display_name text   NOT NULL,
    ip_hash      bytea,
    erased_at    timestamptz,
    UNIQUE (tenant_id, email)
);

-- Analytics events, range-partitioned by month (see the maintenance job).
CREATE TABLE video_event (
    tenant_id     bigint      NOT NULL,
    video_id      bigint      NOT NULL,
    actor_user_id bigint,
    kind          text        NOT NULL,     -- impression | play | share
    created_at    timestamptz NOT NULL DEFAULT now()
) PARTITION BY RANGE (created_at);

Two choices deserve a comment. First, tenant_video has a composite primary key of (tenant_id, video_id) — the same physical video can be curated by many tenants, and the leading tenant_id is exactly the column RLS and every dashboard query will filter on. Second, video_event carries a denormalised tenant_id even though you could derive it by joining through tenant_video. Do not derive it. RLS policies must be able to filter each row cheaply, and partition-local queries must prune without a join. Denormalising one bigint is the price of both.

Row-Level Security is the seatbelt, not the app code

Here is the failure mode that keeps me up at night: an engineer writes SELECT * FROM app_user WHERE email = $1 for a password-reset flow, forgets the tenant predicate, and now partner A can trigger a reset for partner B's user. Code review catches most of these. "Most" is not a compliance posture. RLS moves the guarantee from every developer, forever to the database, once.

-- The application connects as this role. It must NOT be a superuser and must
-- NOT have BYPASSRLS, or policies are silently ignored.
CREATE ROLE vault_app LOGIN PASSWORD '...';
GRANT SELECT, INSERT, UPDATE, DELETE
    ON tenant_video, app_user, video_event TO vault_app;
GRANT SELECT ON video, tenant TO vault_app;

ALTER TABLE tenant_video ENABLE ROW LEVEL SECURITY;
ALTER TABLE app_user     ENABLE ROW LEVEL SECURITY;
ALTER TABLE video_event  ENABLE ROW LEVEL SECURITY;

-- FORCE makes policies apply even to the table owner.
ALTER TABLE tenant_video FORCE ROW LEVEL SECURITY;
ALTER TABLE app_user     FORCE ROW LEVEL SECURITY;
ALTER TABLE video_event  FORCE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON tenant_video
    USING      (tenant_id = current_setting('app.tenant_id')::bigint)
    WITH CHECK (tenant_id = current_setting('app.tenant_id')::bigint);

CREATE POLICY tenant_isolation ON app_user
    USING      (tenant_id = current_setting('app.tenant_id')::bigint)
    WITH CHECK (tenant_id = current_setting('app.tenant_id')::bigint);

CREATE POLICY tenant_isolation ON video_event
    USING      (tenant_id = current_setting('app.tenant_id')::bigint)
    WITH CHECK (tenant_id = current_setting('app.tenant_id')::bigint);

Three things earn their keep here. FORCE ROW LEVEL SECURITY closes the loophole where the table owner bypasses policies by default. The USING clause filters what a query can read; the separate WITH CHECK clause stops a tenant from writing a row stamped with someone else's tenant_id — without it, an INSERT ... tenant_id = 99 from tenant 42 would sail through. And notice I used current_setting('app.tenant_id') without the missing-ok second argument: if the session variable was never set, the cast throws and the query fails closed. A query that errors is a bug report. A query that silently returns every tenant's rows is a breach.

Binding the tenant to every connection in PHP 8.4

RLS reads app.tenant_id from the session, so something has to set it — correctly — on every request. The trap is connection pooling. If you SET app.tenant_id = 42 at the session level and your pooler (PgBouncer in transaction mode, which we run) hands that physical connection to the next request without resetting it, tenant 42's context bleeds into tenant 7's query. The fix is SET LOCAL semantics: scope the setting to a transaction so it is discarded on commit or rollback. set_config(name, value, true) is the parameterised, injection-safe way to do exactly that.

<?php
declare(strict_types=1);

final class TenantConnection
{
    public function __construct(private \PDO $pdo) {}

    /**
     * Run a unit of work bound to one tenant. The id is set with local
     * (transaction) scope so it can never leak across pooled connections
     * running under PgBouncer transaction mode.
     */
    public function forTenant(int $tenantId, callable $work): mixed
    {
        $this->pdo->beginTransaction();
        try {
            $stmt = $this->pdo->prepare('SELECT set_config(?, ?, true)');
            $stmt->execute(['app.tenant_id', (string) $tenantId]);

            $result = $work($this->pdo);
            $this->pdo->commit();
            return $result;
        } catch (\Throwable $e) {
            $this->pdo->rollBack();
            throw $e;
        }
    }
}

// Usage: note there is NO tenant_id in the query. RLS supplies it.
$repo = new TenantConnection($pdo);
$topVideos = $repo->forTenant(42, static function (\PDO $db): array {
    $stmt = $db->query(
        'SELECT v.title, v.platform, tv.curated_rank
           FROM tenant_video tv
           JOIN video v ON v.id = tv.video_id
          ORDER BY tv.curated_rank
          LIMIT 20'
    );
    return $stmt->fetchAll(\PDO::FETCH_ASSOC);
});

The query joining tenant_video to the global video table has no tenant_id predicate anywhere in the SQL. That is the payoff: application code becomes shorter and safer at the same time, because the isolation clause lives in one policy instead of scattered across a hundred hand-written WHEREs. Every read and write inside forTenant is fenced. Forget to wrap something in forTenant, and the query throws instead of leaking — again, fail closed.

Analytics that don't collapse under a billion rows

video_event is the table that grows without bound. Across every tenant, every impression and play at the edge lands here. A single flat table hits index bloat and painful VACUUM runs within a year. Declarative range partitioning by month keeps each child table small, lets the planner prune to the months a query actually touches, and — the part that matters for us — turns data deletion into a metadata operation.

#!/usr/bin/env python3
"""Create next month's video_event partition and drop expired ones.
Run daily from cron; fully idempotent."""
from __future__ import annotations
import datetime as dt
import psycopg

RETENTION_MONTHS = 14  # GDPR: analytics kept 14 months, then the partition is dropped

def month_bounds(d: dt.date) -> tuple[dt.date, dt.date]:
    start = d.replace(day=1)
    nxt = (start + dt.timedelta(days=32)).replace(day=1)
    return start, nxt

def main() -> None:
    today = dt.date.today()
    _, next_start = month_bounds(today)
    p_start, p_end = month_bounds(next_start)
    name = f"video_event_{p_start:%Y_%m}"

    with psycopg.connect("postgresql:///vault", autocommit=True) as conn:
        conn.execute(f"""
            CREATE TABLE IF NOT EXISTS {name}
            PARTITION OF video_event
            FOR VALUES FROM ('{p_start}') TO ('{p_end}')
        """)
        # BRIN is tiny and perfect for append-only, time-ordered data.
        conn.execute(f"CREATE INDEX IF NOT EXISTS {name}_brin "
                     f"ON {name} USING brin (created_at)")

        cutoff = (today.replace(day=1)
                  - dt.timedelta(days=31 * RETENTION_MONTHS)).replace(day=1)
        rows = conn.execute("""
            SELECT child.relname
              FROM pg_inherits
              JOIN pg_class parent ON parent.oid = pg_inherits.inhparent
              JOIN pg_class child  ON child.oid  = pg_inherits.inhrelid
             WHERE parent.relname = 'video_event'
        """).fetchall()
        for (child_name,) in rows:
            try:
                suffix = child_name.removeprefix("video_event_")
                part_date = dt.datetime.strptime(suffix, "%Y_%m").date()
            except ValueError:
                continue
            if part_date < cutoff:
                conn.execute(f"DROP TABLE IF EXISTS {child_name}")
                print(f"dropped expired partition {child_name}")

if __name__ == "__main__":
    main()

Our Cloudflare Workers batch edge events into a queue that flushes into video_event in bulk COPY-style inserts, so writes always target the current month's partition — sequential, append-only, BRIN-friendly. And the retention story writes itself: dropping a partition is an instant catalog operation that reclaims disk immediately, with none of the dead-tuple churn a DELETE FROM ... WHERE created_at < ... would leave behind. Your retention policy and your partition strategy become the same line of code.

GDPR baked into the tables

European users, European law, no way around it. Two GDPR obligations shape the schema directly: retention (handled above by partition drops) and the Article 17 right to erasure. For erasure we pseudonymise in place rather than hard-delete, because deleting a user's rows outright would silently distort every historical aggregate that already counted them. We null the personal data, keep the anonymous shape, and mark the record done.

package main

import (
    "context"
    "log"
    "time"

    "github.com/jackc/pgx/v5/pgxpool"
)

// eraseSubject fulfils an Article 17 request for one user within one tenant.
// It pseudonymises PII rather than hard-deleting, keeping aggregates intact,
// and is idempotent (safe to retry).
func eraseSubject(ctx context.Context, pool *pgxpool.Pool, tenantID, userID int64) error {
    tx, err := pool.Begin(ctx)
    if err != nil {
        return err
    }
    defer tx.Rollback(ctx)

    if _, err := tx.Exec(ctx,
        "SELECT set_config('app.tenant_id', $1::text, true)", tenantID); err != nil {
        return err
    }

    // RLS guarantees the worker can only touch rows for this tenant.
    if _, err := tx.Exec(ctx, `
        UPDATE app_user
           SET email        = 'erased+' || id || '@invalid',
               display_name = 'Erased User',
               ip_hash      = NULL,
               erased_at    = now()
         WHERE id = $1 AND erased_at IS NULL`, userID); err != nil {
        return err
    }

    if _, err := tx.Exec(ctx,
        "UPDATE video_event SET actor_user_id = NULL WHERE actor_user_id = $1",
        userID); err != nil {
        return err
    }

    return tx.Commit(ctx)
}

func main() {
    ctx := context.Background()
    pool, err := pgxpool.New(ctx, "postgres:///vault")
    if err != nil {
        log.Fatal(err)
    }
    defer pool.Close()

    ctx, cancel := context.WithTimeout(ctx, 30*time.Second)
    defer cancel()

    if err := eraseSubject(ctx, pool, 42, 100773); err != nil {
        log.Fatalf("erasure failed: %v", err)
    }
    log.Println("subject erased")
}

The worker sets the tenant context first, so RLS makes it impossible for a mis-routed erasure job to touch the wrong partner's users — the isolation model you built for reads protects your compliance jobs for free. The erased_at IS NULL guard makes retries idempotent. Aggregate analytics keep working because we set actor_user_id to NULL instead of deleting the events; the trend counts stay honest, the person is gone. Back on single-tenant SQLite this was a plain UPDATE too — but there was no tenant boundary to get wrong. Postgres RLS is what makes the same operation safe when many partners share one table.

Indexing and the queries that actually run

RLS silently appends tenant_id = ... to every query, which changes how you index. If your indexes don't lead with tenant_id, the planner can't use them for the policy predicate and you're back to sequential scans. A few rules that survived contact with production:

• Lead composite indexes with tenant_id. tenant_video's primary key already does this; add (tenant_id, curated_rank) for the dashboard's ordered top-N.
• Keep created_at in analytics predicates. Partition pruning only fires when the query filters on the partition key, so WHERE created_at >= now() - interval '7 days' is what keeps a report touching one or two partitions instead of fourteen.
• Use BRIN, not B-tree, on append-only time columns. A B-tree over a billion created_at values is huge; BRIN is a few kilobytes and just as effective when inserts are time-ordered.
• Avoid SELECT * on wide event tables. Name the columns so a covering index can answer the hot dashboard query without touching the heap.
• Test with the app role, never as superuser. Policies are invisible to a superuser, so a query that looks fine in psql can behave completely differently under vault_app. EXPLAIN as the real role.

What I'd tell myself six months ago

Multi-tenancy is a data-modelling problem wearing an application-code costume. The instinct is to reach for middleware that injects tenant_id into every query; the durable answer is to make the database refuse to hand over rows it shouldn't. Enforce isolation with RLS so one forgotten WHERE is a caught error instead of an incident. Denormalise tenant_id onto your analytics table so partitioning and policies stay cheap. Let partitions be your retention policy. And keep the shared catalogue shared — the whole reason a discovery platform exists is that the crawl runs once for everyone. Get those four decisions right in the schema and the rest of the stack, whether it's PHP behind LiteSpeed or Go workers on the edge, becomes almost boring. Boring, in infrastructure that holds European users' data, is exactly what you want.

Comments

[Mads Hansen]

Strong write-up, especially the transaction-local tenant context. One boundary I would add: app.tenant_id is an assertion, not authentication. The wrapper is safe only if the ID is derived from a verified principal-to-tenant membership on a trusted path. If a controller can pass a URL/header tenant ID directly, RLS faithfully grants the wrong tenant. I like binding both app.user_id and app.tenant_id, then expressing membership/role in policy or a security-barrier access view, with negative tests for forged and revoked membership.

The denormalized IDs also need relational integrity. As shown, tenant_video.added_by and video_event.actor_user_id can reference a user from another tenant because there is no tenant-aware FK. A UNIQUE (tenant_id, id) on app_user plus composite FKs (tenant_id, added_by/actor_user_id) prevents cross-tenant references even from privileged ingestion paths that bypass RLS.

Finally, I would be cautious calling pseudonymisation “the person is gone.” Stable erased+<id> values and retained events may remain linkable personal data depending on surrounding data. The erasure workflow needs a field-by-field data map, downstream/cache/back-up handling, and legal-basis/retention decisions—not only an UPDATE.