Multi-Tenant SaaS Starter

Category: Backend & APIs

Get this pack →

This page is generated from the Air Pipe marketplace. Browse it live to install into your organization.

A production-shaped multi-tenant backend over Postgres: API-key authentication, strict per-tenant data isolation, plan-based limits, and usage metering — the things a real SaaS needs. Point Air Pipe at a Postgres connection string and you have a tenant-aware API.

This is the pack that proves the backend claim. Every request authenticates with an API key that resolves to exactly one tenant, and every query is scoped to that tenant — cross-tenant access is structurally impossible.

---

What's included

File	Purpose
schema.sql	Tenants, API keys (sha256-hashed), projects, usage events, plan limits
auth.yml	The x-api-key → tenant resolution middleware (+ /auth/whoami)
tenants.yml	Create tenants, issue / list / revoke API keys
projects.yml	Tenant-scoped CRUD with plan-limit enforcement + metering
usage.yml	Per-tenant usage & plan summary
seed.yml	Creates the schema and loads two demo tenants with ready-to-use keys

---

Endpoints

All tenant-scoped endpoints authenticate with the header x-api-key: mtk_....

Method	Path	Auth	Description
POST	/api/seed	—	Create schema + demo data (returns demo keys)
POST	/tenants	— *	Create a tenant; returns its first API key (once)
GET	/auth/whoami	key	Resolve the key to its tenant + plan
POST	/tenants/keys	key	Issue another API key (returned once)
GET	/tenants/keys/list	key	List the tenant's keys (prefix only)
POST	/tenants/keys/revoke	key	Revoke one of the tenant's keys
GET	/projects	key	List this tenant's projects
POST	/projects/create	key	Create a project (enforces plan limit, meters usage)
POST	/projects/get	key	Get a project (404 if not this tenant's)
PUT	/projects/update	key	Update a project (tenant-scoped)
DELETE	/projects/delete	key	Delete a project (tenant-scoped)
GET	/usage	key	Plan, limit, projects used, metered actions

* POST /tenants is left open so the demo works out of the box. In production, gate it behind a platform-admin credential — it provisions billable tenants.

---

How it works

API keys. A key looks like mtk_<48 hex chars>. On creation it's generated inside Postgres (gen_random_bytes), the raw value is returned once, and only its sha256 hash is stored. Authentication looks the key up by sha256(presented_key) — fast and deterministic (bcrypt is for passwords; it's salted and can't be queried by).

Tenant isolation. Every protected endpoint starts with one ResolveTenant action:

- name: ResolveTenant
  query: |
    SELECT t.id AS tenant_id, t.plan
    FROM api_keys k JOIN tenants t ON t.id = k.tenant_id
    WHERE k.key_hash = encode(digest($1, 'sha256'), 'hex')
      AND k.revoked_at IS NULL;
  params: [ a|headers::x-api-key| ]
  assert: { http_code_on_error: 401, tests: [ { value: count(), is_equal_to: 1 } ] }
  post_transforms: [ { extract_value: "[0]" } ]

Every subsequent query then filters by tenant_id = a|ResolveTenant::tenant_id|::uuid. There is no code path that reads a tenant id from the request body — it only ever comes from the validated key — so one tenant cannot reach another's rows.

Plan limits & metering. plan_limits maps each plan to a project cap (free = 3, pro = 50, enterprise = unlimited). POST /projects/create checks the cap (402 when exceeded) and writes a usage_events row; GET /usage aggregates it.

---

Setup

1. Provision Postgres (any: local Docker, RDS, Cloud SQL, Supabase, Neon). The pgcrypto extension is required for key generation + hashing; the seed enables it (CREATE EXTENSION IF NOT EXISTS pgcrypto), or enable it yourself.
2. Set the managed variable DATABASE_URL to your connection string.
3. Create the schema — either psql $DATABASE_URL -f schema.sql, or deploy seed.yml and call POST /api/seed (which also loads demo data):

curl -X POST https://your-airpipe-host/api/seed
# → { "tenants": 2, "api_keys": 2, "projects": 3,
#     "demo_api_keys": { "acme (free plan)": "mtk_acme_demo_key_a1a1a1a1a1a1",
#                        "globex (pro plan)": "mtk_globex_demo_key_b2b2b2b2b2" } }

4. Deploy the configs. Each name: is unique, so all files deploy together.

---

Quick start: the isolation demo

BASE=https://your-airpipe-host
ACME=mtk_acme_demo_key_a1a1a1a1a1a1       # free plan
GLOBEX=mtk_globex_demo_key_b2b2b2b2b2     # pro plan

# Each key only ever sees its own tenant's projects
curl $BASE/projects -H "x-api-key: $ACME"     # Acme's 2 projects
curl $BASE/projects -H "x-api-key: $GLOBEX"   # Globex's 1 project

# Grab one of Globex's project ids, then try to read it as Acme → 404, not the row
GP=$(curl -s $BASE/projects -H "x-api-key: $GLOBEX" | jq -r '.data.ListProjects.data[0].id')
curl -i -X POST $BASE/projects/get -H "x-api-key: $ACME" \
  -H 'content-type: application/json' -d "{\"id\":\"$GP\"}"   # HTTP 404

# Plan limit: free = 3 projects. Acme has 2 → one more works, the next is 402.
curl -X POST $BASE/projects/create -H "x-api-key: $ACME" \
  -H 'content-type: application/json' -d '{"name":"Third"}'   # 200
curl -i -X POST $BASE/projects/create -H "x-api-key: $ACME" \
  -H 'content-type: application/json' -d '{"name":"Fourth"}'  # HTTP 402

# Usage + plan summary
curl $BASE/usage -H "x-api-key: $ACME"
# → { "plan": "free", "max_projects": 3, "projects_used": 3,
#     "usage_by_action": { "project.create": 3 } }

Onboard a new tenant

curl -X POST $BASE/tenants -H 'content-type: application/json' \
  -d '{"name":"Startup Co","plan":"free"}'
# → { "name": "Startup Co", "slug": "startup-co-3f9a2c", "plan": "free",
#     "api_key": "mtk_…", "key_id": "…" }   ← store api_key now; it's shown once

Rotate keys

# issue a second key (e.g. for CI), then revoke the old one
curl -X POST $BASE/tenants/keys -H "x-api-key: $ACME" \
  -H 'content-type: application/json' -d '{"name":"ci-pipeline"}'   # returns a new mtk_… once
curl -X POST $BASE/tenants/keys/revoke -H "x-api-key: $ACME" \
  -H 'content-type: application/json' -d '{"key_id":"<old-key-id>"}'

---

Notes

• Keys are shown once. Only the sha256 hash is stored; there's no endpoint that returns a raw key after creation. GET /tenants/keys/list shows the prefix and metadata only.
• Revocation is instant. A revoked key (revoked_at set) fails the ResolveTenant lookup → 401 on the next request.
• Cross-tenant operations 404, never 403-with-data. Update/delete/revoke are all scoped by tenant_id, so a foreign id simply matches zero rows.
• Add your own resource. projects is the template — copy projects.yml, rename the table, keep the ResolveTenant first-action + tenant_id = …::uuid scoping, and you have another fully-isolated resource.
• Add user auth (JWT). API keys cover service-to-service. For end-user sessions, layer the JWT validation pattern from the REST API Starter pack on top, carrying the tenant id in a claim.
• Plans. Edit plan_limits to change caps or add plans; meter more actions by inserting into usage_events from any endpoint.

Configuration

auth.yml

name: MultiTenantAuth

docs: true

# API-key tenant resolution — the middleware every tenant-scoped endpoint starts with.
#
# Clients send their key in the `x-api-key` header. The key is looked up by its
# sha256 hash (only the hash is stored), the matching tenant is resolved, and every
# subsequent query is scoped with `a|ResolveTenant::tenant_id|`. A missing, unknown,
# or revoked key resolves to zero rows → 401.
#
# To protect any endpoint: copy the `ResolveTenant` action as the first action and
# add `run_when_succeeded: { actions: [ResolveTenant], http_code_on_error: 401 }`
# to every subsequent action, scoping each query by `a|ResolveTenant::tenant_id|`.
#
# Required managed variable:
#   DATABASE_URL — Postgres connection string (pgcrypto extension enabled)

global:
  databases:
    main:
      driver: postgres
      conn_string: "a|ap_var::DATABASE_URL|"

interfaces:

  # GET /auth/whoami
  # Resolve the caller's API key to its tenant and plan. Useful as a health/identity
  # check and as the canonical example of the ResolveTenant middleware.
  # Header: x-api-key: mtk_...
  auth/whoami:
    output: http
    summary: Who am I
    description: Resolve the x-api-key header to its tenant, plan, and key identity.
    tags: [auth]
    response_example:
      tenant_id: 6f1e...
      tenant_name: Acme Inc
      slug: acme
      plan: free
      key_name: default

    actions:
      - name: ResolveTenant
        database: main
        hide_data_on_error: true
        query: |
          SELECT
            t.id   AS tenant_id,
            t.name AS tenant_name,
            t.slug,
            t.plan,
            k.id   AS key_id,
            k.name AS key_name
          FROM api_keys k
          JOIN tenants t ON t.id = k.tenant_id
          WHERE k.key_hash = encode(digest($1, 'sha256'), 'hex')
            AND k.revoked_at IS NULL;
        params:
          - a|headers::x-api-key|
        assert:
          http_code_on_error: 401
          error_message: "Missing, invalid, or revoked API key"
          tests:
            - value: count()
              is_equal_to: 1
        post_transforms:
          - extract_value: "[0]"

      # Record usage of the key (best-effort; does not affect the response).
      - name: TouchKey
        run_when_succeeded:
          actions: [ResolveTenant]
          http_code_on_error: 401
        database: main
        hide_data_on_success: true
        query: |
          UPDATE api_keys SET last_used_at = NOW() WHERE id = $1::uuid;
        params:
          - a|ResolveTenant::key_id|

projects.yml

name: MultiTenantProjects

docs: true

# Tenant-scoped resource CRUD. Every query filters by the tenant resolved from the
# API key, so a tenant can only ever see and modify its own rows — cross-tenant
# access is structurally impossible, not just checked.

global:
  databases:
    main:
      driver: postgres
      conn_string: "a|ap_var::DATABASE_URL|"

interfaces:

  # GET /projects
  # List the calling tenant's projects only.
  # Header: x-api-key: mtk_...
  projects:
    output: http
    summary: List projects
    description: List the calling tenant's projects (scoped by API key).
    tags: [projects]
    response_example:
      - id: a1b2...
        name: Website redesign
        status: active
        created_at: "2024-01-01T12:00:00Z"

    actions:
      - name: ResolveTenant
        database: main
        hide_data_on_error: true
        query: |
          SELECT t.id AS tenant_id, t.plan
          FROM api_keys k JOIN tenants t ON t.id = k.tenant_id
          WHERE k.key_hash = encode(digest($1, 'sha256'), 'hex')
            AND k.revoked_at IS NULL;
        params:
          - a|headers::x-api-key|
        assert:
          http_code_on_error: 401
          error_message: "Missing, invalid, or revoked API key"
          tests:
            - value: count()
              is_equal_to: 1
        post_transforms:
          - extract_value: "[0]"

      - name: ListProjects
        run_when_succeeded:
          actions: [ResolveTenant]
          http_code_on_error: 401
        database: main
        query: |
          SELECT id, name, status, created_at, updated_at
          FROM projects
          WHERE tenant_id = $1::uuid
          ORDER BY created_at DESC;
        params:
          - a|ResolveTenant::tenant_id|

  # POST /projects/create
  # Create a project. Enforces the plan's project limit and meters usage.
  # Header: x-api-key: mtk_...   Body: { "name": "Website redesign" }
  projects/create:
    output: http
    method: POST
    summary: Create project
    description: >
      Create a project for the calling tenant. Rejected with 402 if the plan's
      project limit is reached. Records a usage event.
    tags: [projects]
    request_example:
      name: Website redesign
    response_example:
      id: a1b2...
      name: Website redesign
      status: active
      created_at: "2024-01-01T12:00:00Z"

    actions:
      - name: ResolveTenant
        database: main
        hide_data_on_error: true
        query: |
          SELECT t.id AS tenant_id, t.plan
          FROM api_keys k JOIN tenants t ON t.id = k.tenant_id
          WHERE k.key_hash = encode(digest($1, 'sha256'), 'hex')
            AND k.revoked_at IS NULL;
        params:
          - a|headers::x-api-key|
        assert:
          http_code_on_error: 401
          error_message: "Missing, invalid, or revoked API key"
          tests:
            - value: count()
              is_equal_to: 1
        post_transforms:
          - extract_value: "[0]"

      - name: ValidateBody
        run_when_succeeded:
          actions: [ResolveTenant]
          http_code_on_error: 401
        input: a|body|
        hide_data_on_success: true
        assert:
          http_code_on_error: 400
          tests:
            - value: name
              is_not_null: true
              is_not_empty: true

      # Enforce the plan limit. `allowed` is true when the plan is unlimited
      # (max = -1) or the tenant is under its cap.
      - name: CheckLimit
        run_when_succeeded:
          actions: [ValidateBody]
          http_code_on_error: 400
        database: main
        hide_data_on_success: true
        query: |
          SELECT (
            pl.max_projects = -1
            OR (SELECT COUNT(*) FROM projects WHERE tenant_id = $1::uuid) < pl.max_projects
          ) AS allowed
          FROM plan_limits pl
          WHERE pl.plan = $2;
        params:
          - a|ResolveTenant::tenant_id|
          - a|ResolveTenant::plan|
        assert:
          http_code_on_error: 402
          error_message: "Project limit reached for your plan — upgrade to add more"
          tests:
            - value: "[0].allowed"
              is_equal_to: true

      - name: CreateProject
        run_when_succeeded:
          actions: [CheckLimit]
          http_code_on_error: 402
        database: main
        query: |
          INSERT INTO projects (tenant_id, name)
          VALUES ($1::uuid, $2)
          RETURNING id, name, status, created_at;
        params:
          - a|ResolveTenant::tenant_id|
          - a|ValidateBody::name|
        post_transforms:
          - extract_value: "[0]"

      # Meter the action (append-only).
      - name: MeterUsage
        run_when_succeeded: [CreateProject]
        database: main
        hide_data_on_success: true
        query: |
          INSERT INTO usage_events (tenant_id, action) VALUES ($1::uuid, 'project.create');
        params:
          - a|ResolveTenant::tenant_id|

  # POST /projects/get
  # Fetch one project by id — only if it belongs to the calling tenant.
  # A key from another tenant gets 404, never the row.
  # Header: x-api-key: mtk_...   Body: { "id": "a1b2..." }
  projects/get:
    output: http
    method: POST
    summary: Get project
    description: Fetch a project by id, scoped to the calling tenant (404 otherwise).
    tags: [projects]
    request_example:
      id: a1b2...
    response_example:
      id: a1b2...
      name: Website redesign
      status: active
      created_at: "2024-01-01T12:00:00Z"
      updated_at: "2024-01-01T12:00:00Z"

    actions:
      - name: ResolveTenant
        database: main
        hide_data_on_error: true
        query: |
          SELECT t.id AS tenant_id
          FROM api_keys k JOIN tenants t ON t.id = k.tenant_id
          WHERE k.key_hash = encode(digest($1, 'sha256'), 'hex')
            AND k.revoked_at IS NULL;
        params:
          - a|headers::x-api-key|
        assert:
          http_code_on_error: 401
          error_message: "Missing, invalid, or revoked API key"
          tests:
            - value: count()
              is_equal_to: 1
        post_transforms:
          - extract_value: "[0]"

      - name: ValidateBody
        run_when_succeeded:
          actions: [ResolveTenant]
          http_code_on_error: 401
        input: a|body|
        hide_data_on_success: true
        assert:
          http_code_on_error: 400
          tests:
            - value: id
              is_not_null: true

      - name: GetProject
        run_when_succeeded:
          actions: [ValidateBody]
          http_code_on_error: 400
        database: main
        query: |
          SELECT id, name, status, created_at, updated_at
          FROM projects
          WHERE id = $1::uuid AND tenant_id = $2::uuid;
        params:
          - a|ValidateBody::id|
          - a|ResolveTenant::tenant_id|
        assert:
          http_code_on_error: 404
          error_message: "Project not found"
          tests:
            - value: count()
              is_equal_to: 1
        post_transforms:
          - extract_value: "[0]"

  # PUT /projects/update
  # Update a project's name and/or status — only within the calling tenant.
  # Header: x-api-key: mtk_...   Body: { "id": "...", "name": "...", "status": "archived" }
  projects/update:
    output: http
    method: PUT
    summary: Update project
    description: Update a project's name/status, scoped to the calling tenant.
    tags: [projects]
    request_example:
      id: a1b2...
      name: Website redesign v2
      status: archived
    response_example:
      id: a1b2...
      name: Website redesign v2
      status: archived
      updated_at: "2024-01-02T09:00:00Z"

    actions:
      - name: ResolveTenant
        database: main
        hide_data_on_error: true
        query: |
          SELECT t.id AS tenant_id
          FROM api_keys k JOIN tenants t ON t.id = k.tenant_id
          WHERE k.key_hash = encode(digest($1, 'sha256'), 'hex')
            AND k.revoked_at IS NULL;
        params:
          - a|headers::x-api-key|
        assert:
          http_code_on_error: 401
          error_message: "Missing, invalid, or revoked API key"
          tests:
            - value: count()
              is_equal_to: 1
        post_transforms:
          - extract_value: "[0]"

      - name: ValidateBody
        run_when_succeeded:
          actions: [ResolveTenant]
          http_code_on_error: 401
        input: a|body|
        hide_data_on_success: true
        assert:
          http_code_on_error: 400
          tests:
            - value: id
              is_not_null: true

      - name: UpdateProject
        run_when_succeeded:
          actions: [ValidateBody]
          http_code_on_error: 400
        database: main
        query: |
          UPDATE projects
          SET
            name       = COALESCE($3, name),
            status     = COALESCE($4, status),
            updated_at = NOW()
          WHERE id = $1::uuid AND tenant_id = $2::uuid
          RETURNING id, name, status, updated_at;
        params:
          - a|ValidateBody::id|
          - a|ResolveTenant::tenant_id|
          - a|ValidateBody::name->default(null)|
          - a|ValidateBody::status->default(null)|
        assert:
          http_code_on_error: 404
          error_message: "Project not found"
          tests:
            - value: count()
              is_equal_to: 1
        post_transforms:
          - extract_value: "[0]"

  # DELETE /projects/delete
  # Delete a project — only within the calling tenant.
  # Header: x-api-key: mtk_...   Body: { "id": "a1b2..." }
  projects/delete:
    output: http
    method: DELETE
    summary: Delete project
    description: Delete a project, scoped to the calling tenant.
    tags: [projects]
    request_example:
      id: a1b2...
    response_example:
      id: a1b2...

    actions:
      - name: ResolveTenant
        database: main
        hide_data_on_error: true
        query: |
          SELECT t.id AS tenant_id
          FROM api_keys k JOIN tenants t ON t.id = k.tenant_id
          WHERE k.key_hash = encode(digest($1, 'sha256'), 'hex')
            AND k.revoked_at IS NULL;
        params:
          - a|headers::x-api-key|
        assert:
          http_code_on_error: 401
          error_message: "Missing, invalid, or revoked API key"
          tests:
            - value: count()
              is_equal_to: 1
        post_transforms:
          - extract_value: "[0]"

      - name: ValidateBody
        run_when_succeeded:
          actions: [ResolveTenant]
          http_code_on_error: 401
        input: a|body|
        hide_data_on_success: true
        assert:
          http_code_on_error: 400
          tests:
            - value: id
              is_not_null: true

      - name: DeleteProject
        run_when_succeeded:
          actions: [ValidateBody]
          http_code_on_error: 400
        database: main
        query: |
          DELETE FROM projects
          WHERE id = $1::uuid AND tenant_id = $2::uuid
          RETURNING id;
        params:
          - a|ValidateBody::id|
          - a|ResolveTenant::tenant_id|
        assert:
          http_code_on_error: 404
          error_message: "Project not found"
          tests:
            - value: count()
              is_equal_to: 1
        post_transforms:
          - extract_value: "[0]"

seed.yml

name: MultiTenantSaasSeed
description: Creates all tables (idempotent) and loads two sample tenants with known demo API keys, projects, and usage. Safe to re-run — clears tenant data before inserting.

docs: true

# Required managed variable:
#   DATABASE_URL — Postgres connection string (pgcrypto enabled)

global:
  databases:
    main:
      driver: postgres
      conn_string: "a|ap_var::DATABASE_URL|"

interfaces:

  # POST /api/seed
  # Creates the schema and loads demo data. Returns the two demo API keys so you
  # can start calling the tenant-scoped endpoints immediately.
  api/seed:
    output: http
    method: POST

    actions:
      - name: CreateSchema
        database: main
        hide_data_on_success: true
        query: |
          CREATE EXTENSION IF NOT EXISTS pgcrypto;

          CREATE TABLE IF NOT EXISTS tenants (
            id         UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
            name       TEXT         NOT NULL,
            slug       TEXT         UNIQUE NOT NULL,
            plan       TEXT         NOT NULL DEFAULT 'free' CHECK (plan IN ('free', 'pro', 'enterprise')),
            created_at TIMESTAMPTZ  NOT NULL DEFAULT NOW()
          );

          CREATE TABLE IF NOT EXISTS api_keys (
            id           UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
            tenant_id    UUID         NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
            name         TEXT         NOT NULL DEFAULT 'default',
            key_prefix   TEXT         NOT NULL,
            key_hash     TEXT         UNIQUE NOT NULL,
            last_used_at TIMESTAMPTZ,
            revoked_at   TIMESTAMPTZ,
            created_at   TIMESTAMPTZ  NOT NULL DEFAULT NOW()
          );

          CREATE TABLE IF NOT EXISTS projects (
            id         UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
            tenant_id  UUID         NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
            name       TEXT         NOT NULL,
            status     TEXT         NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'archived')),
            created_at TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
            updated_at TIMESTAMPTZ  NOT NULL DEFAULT NOW()
          );

          CREATE TABLE IF NOT EXISTS usage_events (
            id         BIGSERIAL    PRIMARY KEY,
            tenant_id  UUID         NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
            action     TEXT         NOT NULL,
            quantity   INT          NOT NULL DEFAULT 1,
            created_at TIMESTAMPTZ  NOT NULL DEFAULT NOW()
          );

          CREATE TABLE IF NOT EXISTS plan_limits (
            plan         TEXT  PRIMARY KEY,
            max_projects INT   NOT NULL
          );

          CREATE INDEX IF NOT EXISTS idx_api_keys_hash   ON api_keys     (key_hash);
          CREATE INDEX IF NOT EXISTS idx_api_keys_tenant ON api_keys     (tenant_id);
          CREATE INDEX IF NOT EXISTS idx_projects_tenant ON projects     (tenant_id);
          CREATE INDEX IF NOT EXISTS idx_usage_tenant    ON usage_events (tenant_id, created_at DESC);

      - name: SeedPlanLimits
        run_when_succeeded: [CreateSchema]
        database: main
        hide_data_on_success: true
        query: |
          INSERT INTO plan_limits (plan, max_projects) VALUES
            ('free', 3), ('pro', 50), ('enterprise', -1)
          ON CONFLICT (plan) DO UPDATE SET max_projects = EXCLUDED.max_projects;

      - name: ClearTables
        run_when_succeeded: [SeedPlanLimits]
        database: main
        hide_data_on_success: true
        query: |
          TRUNCATE usage_events, projects, api_keys, tenants RESTART IDENTITY CASCADE;

      - name: InsertTenants
        run_when_succeeded: [ClearTables]
        database: main
        hide_data_on_success: true
        query: |
          INSERT INTO tenants (name, slug, plan) VALUES
            ('Acme Inc',      'acme',   'free'),
            ('Globex Corp',   'globex', 'pro');

      # Store only the sha256 hash of each known demo key.
      - name: InsertKeys
        run_when_succeeded: [InsertTenants]
        database: main
        hide_data_on_success: true
        query: |
          INSERT INTO api_keys (tenant_id, name, key_prefix, key_hash)
          SELECT t.id, 'demo', left(v.raw, 12), encode(digest(v.raw, 'sha256'), 'hex')
          FROM (VALUES
            ('acme',   'mtk_acme_demo_key_a1a1a1a1a1a1'),
            ('globex', 'mtk_globex_demo_key_b2b2b2b2b2')
          ) AS v(slug, raw)
          JOIN tenants t ON t.slug = v.slug;

      - name: InsertProjects
        run_when_succeeded: [InsertKeys]
        database: main
        hide_data_on_success: true
        query: |
          INSERT INTO projects (tenant_id, name, status)
          SELECT t.id, v.name, v.status
          FROM (VALUES
            ('acme',   'Website redesign', 'active'),
            ('acme',   'Mobile app',       'active'),
            ('globex', 'Data pipeline',    'active')
          ) AS v(slug, name, status)
          JOIN tenants t ON t.slug = v.slug;

      - name: InsertUsage
        run_when_succeeded: [InsertProjects]
        database: main
        hide_data_on_success: true
        query: |
          INSERT INTO usage_events (tenant_id, action)
          SELECT tenant_id, 'project.create' FROM projects;

      - name: SeedSummary
        run_when_succeeded: [InsertUsage]
        database: main
        query: |
          SELECT
            (SELECT COUNT(*) FROM tenants)  AS tenants,
            (SELECT COUNT(*) FROM api_keys) AS api_keys,
            (SELECT COUNT(*) FROM projects) AS projects,
            json_build_object(
              'acme (free plan)',   'mtk_acme_demo_key_a1a1a1a1a1a1',
              'globex (pro plan)',  'mtk_globex_demo_key_b2b2b2b2b2'
            ) AS demo_api_keys;
        post_transforms:
          - extract_value: "[0]"

tenants.yml

name: MultiTenantTenants

docs: true

global:
  databases:
    main:
      driver: postgres
      conn_string: "a|ap_var::DATABASE_URL|"

interfaces:

  # POST /tenants
  # Provision a new tenant and return its first API key. The raw key is shown
  # ONCE here and never again — only its sha256 hash is stored.
  # Body: { "name": "Acme Inc", "plan": "free" }
  #
  # In production, gate this behind a platform-admin credential (it creates
  # billable tenants). Left open here so the demo works out of the box.
  tenants:
    output: http
    method: POST
    summary: Create tenant
    description: >
      Create a tenant and issue its first API key. Plan defaults to free.
      The returned api_key is shown once — store it securely.
    tags: [tenants]
    request_example:
      name: Acme Inc
      plan: free
    response_example:
      id: 6f1e...
      name: Acme Inc
      slug: acme-inc-3f9a2c
      plan: free
      api_key: mtk_3274e9475c557a9eeeeab8dda083917f...
      key_id: 9b2c...

    actions:
      - name: ValidateBody
        input: a|body|
        hide_data_on_success: true
        assert:
          http_code_on_error: 400
          tests:
            - value: name
              is_not_null: true
              is_not_empty: true

      - name: CreateTenant
        run_when_succeeded:
          actions: [ValidateBody]
          http_code_on_error: 400
        database: main
        hide_data_on_success: true
        # Slug is derived from the name with a short random suffix to guarantee uniqueness.
        query: |
          INSERT INTO tenants (name, slug, plan)
          VALUES (
            $1,
            lower(regexp_replace(trim($1), '[^a-zA-Z0-9]+', '-', 'g')) || '-' || substr(md5(random()::text), 1, 6),
            COALESCE($2, 'free')
          )
          RETURNING id, name, slug, plan, created_at;
        params:
          - a|ValidateBody::name|
          - a|ValidateBody::plan->default(null)|
        post_transforms:
          - extract_value: "[0]"

      # Generate the key inside Postgres so the raw value never has to be passed
      # in; return the raw key once and store only its sha256 hash.
      - name: CreateFirstKey
        run_when_succeeded: [CreateTenant]
        database: main
        hide_data_on_success: true
        query: |
          WITH gen AS (
            SELECT 'mtk_' || encode(gen_random_bytes(24), 'hex') AS raw
          ),
          ins AS (
            INSERT INTO api_keys (tenant_id, name, key_prefix, key_hash)
            SELECT $1::uuid, 'default', left(raw, 12), encode(digest(raw, 'sha256'), 'hex')
            FROM gen
            RETURNING id, key_prefix
          )
          SELECT ins.id AS key_id, ins.key_prefix, gen.raw AS api_key
          FROM ins, gen;
        params:
          - a|CreateTenant::id|
        post_transforms:
          - extract_value: "[0]"

      - name: Result
        run_when_succeeded: [CreateFirstKey]
        input: a|CreateTenant|
        post_transforms:
          - add_attribute:
              api_key: a|CreateFirstKey::api_key|
              key_id: a|CreateFirstKey::key_id|

  # POST /tenants/keys
  # Issue an additional API key for the authenticated tenant. Returned once.
  # Header: x-api-key: mtk_...   Body: { "name": "ci-pipeline" }
  tenants/keys:
    output: http
    method: POST
    summary: Issue API key
    description: Create a new API key for the calling tenant. The raw key is returned once.
    tags: [tenants]
    request_example:
      name: ci-pipeline
    response_example:
      key_id: 9b2c...
      name: ci-pipeline
      key_prefix: mtk_3274e947
      api_key: mtk_3274e9475c557a9eeeeab8dda083917f...

    actions:
      - name: ResolveTenant
        database: main
        hide_data_on_error: true
        query: |
          SELECT t.id AS tenant_id, t.plan
          FROM api_keys k JOIN tenants t ON t.id = k.tenant_id
          WHERE k.key_hash = encode(digest($1, 'sha256'), 'hex')
            AND k.revoked_at IS NULL;
        params:
          - a|headers::x-api-key|
        assert:
          http_code_on_error: 401
          error_message: "Missing, invalid, or revoked API key"
          tests:
            - value: count()
              is_equal_to: 1
        post_transforms:
          - extract_value: "[0]"

      - name: IssueKey
        run_when_succeeded:
          actions: [ResolveTenant]
          http_code_on_error: 401
        database: main
        query: |
          WITH gen AS (
            SELECT 'mtk_' || encode(gen_random_bytes(24), 'hex') AS raw
          ),
          ins AS (
            INSERT INTO api_keys (tenant_id, name, key_prefix, key_hash)
            SELECT $1::uuid, COALESCE($2, 'default'), left(raw, 12), encode(digest(raw, 'sha256'), 'hex')
            FROM gen
            RETURNING id, name, key_prefix
          )
          SELECT ins.id AS key_id, ins.name, ins.key_prefix, gen.raw AS api_key
          FROM ins, gen;
        params:
          - a|ResolveTenant::tenant_id|
          - a|body::name->default(null)|
        post_transforms:
          - extract_value: "[0]"

  # GET /tenants/keys/list
  # List the calling tenant's API keys (prefix and metadata only — never the raw key).
  # Header: x-api-key: mtk_...
  tenants/keys/list:
    output: http
    summary: List API keys
    description: List the calling tenant's keys. Raw key values are never returned.
    tags: [tenants]
    response_example:
      - key_id: 9b2c...
        name: default
        key_prefix: mtk_3274e947
        revoked: false
        last_used_at: "2024-01-01T12:00:00Z"

    actions:
      - name: ResolveTenant
        database: main
        hide_data_on_error: true
        query: |
          SELECT t.id AS tenant_id
          FROM api_keys k JOIN tenants t ON t.id = k.tenant_id
          WHERE k.key_hash = encode(digest($1, 'sha256'), 'hex')
            AND k.revoked_at IS NULL;
        params:
          - a|headers::x-api-key|
        assert:
          http_code_on_error: 401
          error_message: "Missing, invalid, or revoked API key"
          tests:
            - value: count()
              is_equal_to: 1
        post_transforms:
          - extract_value: "[0]"

      - name: ListKeys
        run_when_succeeded:
          actions: [ResolveTenant]
          http_code_on_error: 401
        database: main
        query: |
          SELECT id AS key_id, name, key_prefix,
                 (revoked_at IS NOT NULL) AS revoked,
                 last_used_at, created_at
          FROM api_keys
          WHERE tenant_id = $1::uuid
          ORDER BY created_at DESC;
        params:
          - a|ResolveTenant::tenant_id|

  # POST /tenants/keys/revoke
  # Revoke one of the calling tenant's keys. A tenant can only revoke its own keys.
  # Header: x-api-key: mtk_...   Body: { "key_id": "9b2c..." }
  tenants/keys/revoke:
    output: http
    method: POST
    summary: Revoke API key
    description: Revoke a key belonging to the calling tenant. Returns 404 if the key isn't theirs.
    tags: [tenants]
    request_example:
      key_id: 9b2c...
    response_example:
      key_id: 9b2c...
      revoked_at: "2024-01-02T09:00:00Z"

    actions:
      - name: ResolveTenant
        database: main
        hide_data_on_error: true
        query: |
          SELECT t.id AS tenant_id
          FROM api_keys k JOIN tenants t ON t.id = k.tenant_id
          WHERE k.key_hash = encode(digest($1, 'sha256'), 'hex')
            AND k.revoked_at IS NULL;
        params:
          - a|headers::x-api-key|
        assert:
          http_code_on_error: 401
          error_message: "Missing, invalid, or revoked API key"
          tests:
            - value: count()
              is_equal_to: 1
        post_transforms:
          - extract_value: "[0]"

      - name: ValidateBody
        run_when_succeeded:
          actions: [ResolveTenant]
          http_code_on_error: 401
        input: a|body|
        hide_data_on_success: true
        assert:
          http_code_on_error: 400
          tests:
            - value: key_id
              is_not_null: true

      - name: RevokeKey
        run_when_succeeded:
          actions: [ValidateBody]
          http_code_on_error: 400
        database: main
        # Scoped by tenant_id, so a tenant cannot revoke another tenant's key.
        query: |
          UPDATE api_keys
          SET revoked_at = NOW()
          WHERE id = $1::uuid AND tenant_id = $2::uuid AND revoked_at IS NULL
          RETURNING id AS key_id, revoked_at;
        params:
          - a|ValidateBody::key_id|
          - a|ResolveTenant::tenant_id|
        assert:
          http_code_on_error: 404
          error_message: "Key not found for this tenant (or already revoked)"
          tests:
            - value: count()
              is_equal_to: 1
        post_transforms:
          - extract_value: "[0]"

usage.yml

name: MultiTenantUsage

docs: true

global:
  databases:
    main:
      driver: postgres
      conn_string: "a|ap_var::DATABASE_URL|"

interfaces:

  # GET /usage
  # Usage + plan summary for the calling tenant: plan, project limit, projects used,
  # and metered actions to date.
  # Header: x-api-key: mtk_...
  usage:
    output: http
    summary: Usage summary
    description: Per-tenant plan, limits, project count, and metered usage totals.
    tags: [usage]
    response_example:
      plan: free
      max_projects: 3
      projects_used: 2
      usage_by_action:
        project.create: 2

    actions:
      - name: ResolveTenant
        database: main
        hide_data_on_error: true
        query: |
          SELECT t.id AS tenant_id, t.plan
          FROM api_keys k JOIN tenants t ON t.id = k.tenant_id
          WHERE k.key_hash = encode(digest($1, 'sha256'), 'hex')
            AND k.revoked_at IS NULL;
        params:
          - a|headers::x-api-key|
        assert:
          http_code_on_error: 401
          error_message: "Missing, invalid, or revoked API key"
          tests:
            - value: count()
              is_equal_to: 1
        post_transforms:
          - extract_value: "[0]"

      - name: Usage
        run_when_succeeded:
          actions: [ResolveTenant]
          http_code_on_error: 401
        database: main
        query: |
          SELECT
            $2                                                              AS plan,
            (SELECT max_projects FROM plan_limits WHERE plan = $2)          AS max_projects,
            (SELECT COUNT(*) FROM projects WHERE tenant_id = $1::uuid)            AS projects_used,
            COALESCE(
              (SELECT json_object_agg(action, total)
               FROM (
                 SELECT action, SUM(quantity) AS total
                 FROM usage_events
                 WHERE tenant_id = $1::uuid
                 GROUP BY action
               ) s),
              '{}'::json
            )                                                              AS usage_by_action;
        params:
          - a|ResolveTenant::tenant_id|
          - a|ResolveTenant::plan|
        post_transforms:
          - extract_value: "[0]"

schema.sql

-- Multi-Tenant SaaS Starter schema (Postgres)
--
-- Run once before deploying, or deploy seed.yml and call POST /api/seed.
-- pgcrypto provides gen_random_bytes (API-key entropy) and digest (sha256 hashing).

CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- A tenant = one customer workspace/organisation. Everything else is scoped to it.
CREATE TABLE IF NOT EXISTS tenants (
  id         UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
  name       TEXT         NOT NULL,
  slug       TEXT         UNIQUE NOT NULL,
  plan       TEXT         NOT NULL DEFAULT 'free' CHECK (plan IN ('free', 'pro', 'enterprise')),
  created_at TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

-- API keys authenticate requests. The raw key is shown ONCE on creation; only a
-- sha256 hash is stored (fast, deterministic lookup — bcrypt can't be queried by).
CREATE TABLE IF NOT EXISTS api_keys (
  id           UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id    UUID         NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  name         TEXT         NOT NULL DEFAULT 'default',
  key_prefix   TEXT         NOT NULL,            -- public, safe to display in listings
  key_hash     TEXT         UNIQUE NOT NULL,     -- sha256 hex of the raw key
  last_used_at TIMESTAMPTZ,
  revoked_at   TIMESTAMPTZ,
  created_at   TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

-- The tenant-scoped resource. Every projects query filters by tenant_id, so a
-- tenant can only ever see its own rows.
CREATE TABLE IF NOT EXISTS projects (
  id         UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id  UUID         NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  name       TEXT         NOT NULL,
  status     TEXT         NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'archived')),
  created_at TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

-- Append-only usage metering. One row per metered action.
CREATE TABLE IF NOT EXISTS usage_events (
  id         BIGSERIAL    PRIMARY KEY,
  tenant_id  UUID         NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  action     TEXT         NOT NULL,
  quantity   INT          NOT NULL DEFAULT 1,
  created_at TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

-- Per-plan limits. max_projects = -1 means unlimited.
CREATE TABLE IF NOT EXISTS plan_limits (
  plan         TEXT  PRIMARY KEY,
  max_projects INT   NOT NULL
);
INSERT INTO plan_limits (plan, max_projects) VALUES
  ('free', 3), ('pro', 50), ('enterprise', -1)
ON CONFLICT (plan) DO NOTHING;

CREATE INDEX IF NOT EXISTS idx_api_keys_hash   ON api_keys     (key_hash);
CREATE INDEX IF NOT EXISTS idx_api_keys_tenant ON api_keys     (tenant_id);
CREATE INDEX IF NOT EXISTS idx_projects_tenant ON projects     (tenant_id);
CREATE INDEX IF NOT EXISTS idx_usage_tenant    ON usage_events (tenant_id, created_at DESC);