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 and a workflow tool can't do. Point Air Pipe at a Postgres connection string and you have a tenant-aware API.

This is the "Air Pipe is a real backend, not a workflow tool" pack. 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);