g14wxz/commerce-database-architect

Database architecture skills, docs, and rules for high-demand multi-tenant commerce platforms (PostgreSQL source of truth, Neo4j as derived GraphRAG projection, transactional outbox, RLS-based tenant isolation). Includes live schema introspection workflow via Postgres MCP.

Quality

97%

Does it follow best practices?

Impact

Pending

No eval scenarios have been run

Securityby

Passed

No known issues

Row Level Security Templates

Name: g14wxz/commerce-database-architect
Rating: 97.27272727272728 (1 reviews)
Author: g14wxz

Tenant isolation via PostgreSQL RLS. Skills cite this file as the canonical implementation. Copy as-is for new tenant-scoped tables.

The tenant context pattern

The application sets the tenant context once per request, immediately after authentication and before any business query:

SET LOCAL app.tenant_id = '42';
SET LOCAL app.user_id = '123';
SET LOCAL app.actor_role = 'tenant_user';   -- 'tenant_user' | 'tenant_admin' | 'platform_admin'

SET LOCAL is critical — the value is scoped to the current transaction and cannot leak across requests via connection pooling.

The application uses a connection-pool-aware wrapper that injects this SET into every request transaction. Never issue queries without it.

The standard policy

Apply this template to every tenant-scoped table:

ALTER TABLE catalog_products ENABLE ROW LEVEL SECURITY;
ALTER TABLE catalog_products FORCE ROW LEVEL SECURITY;
-- FORCE is critical: without it, the table owner bypasses RLS

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

USING controls what's visible to SELECT/UPDATE/DELETE. WITH CHECK controls what INSERT/UPDATE rows are allowed to have. Both are required — omitting WITH CHECK allows tenant ID forgery on insert.

Platform admin escape hatch

Some operations (cross-tenant analytics, support tooling) need cross-tenant access. Carve out a separate role with explicit, audited bypass:

CREATE ROLE platform_admin NOINHERIT;

CREATE POLICY catalog_products_platform_admin_bypass
  ON catalog_products
  AS PERMISSIVE
  FOR SELECT
  TO platform_admin
  USING (true);

The application middleware logs every platform_admin query separately and requires SSO + step-up auth.

Tenant + role-based fine-grained access

When tenant users have varying permissions inside their own tenant:

CREATE POLICY catalog_products_tenant_user_select
  ON catalog_products
  FOR SELECT
  USING (
    tenant_id = current_setting('app.tenant_id')::bigint
    AND (
      current_setting('app.actor_role') = 'tenant_admin'
      OR EXISTS (
        SELECT 1
          FROM identity_role_grants g
         WHERE g.tenant_id = current_setting('app.tenant_id')::bigint
           AND g.user_id = current_setting('app.user_id')::bigint
           AND g.permission = 'catalog.read'
      )
    )
  );

Composite indexes must support these queries. Add a covering index on identity_role_grants (tenant_id, user_id, permission).

Soft-delete + RLS

Soft-deleted rows should not appear in default queries but should remain visible to admins for audit. Combine RLS with a partial unique index:

CREATE UNIQUE INDEX catalog_products_active_sku_key
  ON catalog_products (tenant_id, sku)
  WHERE deleted_at IS NULL;

CREATE POLICY catalog_products_hide_deleted
  ON catalog_products
  FOR SELECT
  USING (
    deleted_at IS NULL
    OR current_setting('app.actor_role') IN ('tenant_admin', 'platform_admin')
  );

Two policies on the same table are AND-combined for the SELECT command, so both tenant_isolation and hide_deleted apply.

Cross-tenant FK enforcement

Composite FKs that include tenant_id make cross-tenant references impossible at the database level:

ALTER TABLE sales_order_items
  ADD CONSTRAINT sales_order_items_product_fk
    FOREIGN KEY (tenant_id, product_id)
    REFERENCES catalog_products (tenant_id, id)
    ON DELETE RESTRICT;

This requires catalog_products to have (tenant_id, id) as a UNIQUE constraint:

ALTER TABLE catalog_products
  ADD CONSTRAINT catalog_products_tenant_id_key UNIQUE (tenant_id, id);

The composite unique is already implied by the PK + the tenant_id column, but PostgreSQL needs an explicit UNIQUE for FK targeting.

Testing RLS

Mandatory tests for every tenant-scoped table:

-- Setup: two tenants, each with one product
INSERT INTO tenancy_tenants (id, name) VALUES (1, 'A'), (2, 'B');
INSERT INTO catalog_products (tenant_id, sku, name) VALUES
  (1, 'SKU-1', 'A product'),
  (2, 'SKU-2', 'B product');

-- Test 1: tenant 1 sees only its product
SET LOCAL app.tenant_id = '1';
SELECT count(*) FROM catalog_products;  -- expect 1

-- Test 2: tenant 1 cannot insert as tenant 2
SET LOCAL app.tenant_id = '1';
INSERT INTO catalog_products (tenant_id, sku, name) VALUES (2, 'X', 'Y');
-- expect: ERROR — new row violates row-level security policy

-- Test 3: tenant 1 cannot update tenant 2's product (UPDATE returns 0)
SET LOCAL app.tenant_id = '1';
UPDATE catalog_products SET name = 'hacked' WHERE id = (
  SELECT id FROM catalog_products WHERE sku = 'SKU-2'  -- visible to admin only
);

-- Test 4: missing tenant context fails closed
RESET app.tenant_id;
SELECT count(*) FROM catalog_products;
-- expect: ERROR — current_setting('app.tenant_id') returns NULL

If the production DB role bypasses RLS via superuser or BYPASSRLS, the policies are silently ignored. Always run RLS tests as a non-bypass role.

Common mistakes (don't)

Forgetting FORCE ROW LEVEL SECURITY. Without it, the table owner bypasses policies — and the migration runner is usually the owner.
Forgetting WITH CHECK. Without it, INSERT can forge tenant_id.
Casting current_setting('app.tenant_id') once and reusing. Each policy must cast at evaluation time.
Setting app.tenant_id outside SET LOCAL. Connection-pooled sessions leak the tenant context across requests — catastrophic.
Trusting frontend filtering. RLS is the backstop; never the only layer.