PoC: Prisma Multi-Schema with PostgreSQL Views

Proof of concept for the Viero platform's IDP-to-product data integration.

Status: Validated. All 6 tests passing. Seeds idempotent. Date: 2026-03-30 Related: ADR-002 (Product-IDP Data Integration)

---

Table of Contents

1. What This Proves
2. Architecture
3. File Structure
4. How It Works
5. Running the PoC
6. Test Results
7. Recipes
8. Production Requirements
9. Known Caveats
10. FAQ
11. Review Findings

---

1. What This Proves

Products (fuel-logistics, fleet-cards) can read IDP user data with native Prisma relations and JOINs, without:

• Direct access to IDP tables (products see PostgreSQL VIEWs, not tables)
• Migration conflicts (Prisma Migrate ignores view blocks)
• Manual sync of field definitions (generator reads IDP schema, uses allowlist)
• Exposing sensitive fields (password, PIN, lockout state excluded by allowlist)
• Per-product placeholder replacement (all products share idp_views schema)

---

2. Architecture

PostgreSQL database: viero_platform
+--------------------------------------------------+
|  idp schema (IDP owns, migrates)                 |
|  +---------+  +---------+  +------+              |
|  | User    |  | Tenant  |  | Role |  ... more    |
|  | (TABLE) |  | (TABLE) |  |(TABLE)|             |
|  +---------+  +---------+  +------+              |
|  (has password, pin, lockout, tokens, etc.)       |
+--------------------------------------------------+
        |            |           |
        | PostgreSQL VIEWs (safe fields only)
        v            v           v
+--------------------------------------------------+
|  idp_views schema (shared, read-only)            |
|  +---------+  +-----------+  +--------+          |
|  | IdpUser |  | IdpTenant |  | IdpRole|          |
|  | (VIEW)  |  | (VIEW)    |  | (VIEW) |          |
|  +---------+  +-----------+  +--------+          |
+--------------------------------------------------+
        ^
        | Prisma @relation
+--------------------------------------------------+
|  fuel_logistics schema (Product owns, migrates)  |
|  +---------+  +--------+                        |
|  | Driver  |  | Order  |                        |
|  | (TABLE) |  | (TABLE)|                        |
|  +---------+  +--------+                        |
+--------------------------------------------------+

Three schemas, one database:

Schema	Owner	Contains	Prisma keyword
idp	IDP backend	Real tables (User, Tenant, Role, etc.)	model
idp_views	Generated (shared)	Read-only VIEWs (IdpUser, IdpTenant, IdpRole)	view
fuel_logistics	Product backend	Product tables (Driver, Order, etc.)	model

---

3. File Structure

poc-multischema/
|
+-- idp/                                    THE IDP BACKEND
|   |
|   +-- prisma/schema/
|   |   +-- _config.prisma                  Prisma config: schemas = ["idp"]
|   |   +-- models.prisma                   SOURCE OF TRUTH: model User, Tenant, Role
|   |
|   +-- prisma.config.ts                    Prisma 7 datasource URL config
|   |
|   +-- scripts/
|   |   +-- generate-shared-views.ts        THE GENERATOR: reads models, outputs views
|   |                                       Uses ALLOWLIST (only listed fields exposed)
|   |                                       Run: pnpm generate:shared
|   |
|   +-- packages/idp-views/                 GENERATED OUTPUT (becomes git submodule)
|   |   +-- idp-views.prisma               Prisma views with @@schema("idp_views")
|   |   +-- create-idp-views.sql           SQL to create PostgreSQL VIEWs
|   |   +-- add-relations.sh               Products run this to inject relations
|   |
|   +-- src/seed.ts                         Seeds 2 tenants, 4 users, 1 role
|   +-- .env                               DATABASE_URL
|   +-- package.json
|
+-- product/                                A PRODUCT BACKEND (e.g., fuel-logistics)
|   |
|   +-- idp-views/ -> submodule             Points to idp/packages/idp-views
|   |                                       Products NEVER edit files in here
|   |
|   +-- idp-relations.conf                  Product's relations config
|   |                                       Format: ViewName:relation_definition
|   |                                       Example: IdpUser:drivers Driver[]
|   |
|   +-- prisma/schema/
|   |   +-- _config.prisma                  schemas = ["fuel_logistics", "idp_views"]
|   |   +-- models.prisma                   Product tables: model Driver, Order
|   |   +-- idp-views.prisma               GENERATED by add-relations.sh
|   |                                       Submodule source + product relations
|   |
|   +-- prisma.config.ts                    Prisma 7 datasource URL config
|   +-- src/seed.ts                         Seeds 3 drivers, 9 orders
|   +-- src/test-queries.ts                 6 cross-schema query tests
|   +-- .env                               DATABASE_URL
|   +-- package.json
|
+-- README.md

---

4. How It Works

Step 1: IDP Defines Tables

idp/prisma/schema/models.prisma is the source of truth:

model User {
  id           String     @id @default(uuid(7))
  email        String?
  firstName    String?
  password     String     // NEVER exposed
  pin          String?    // NEVER exposed
  userTypes    UserType[]
  isActive     Boolean    @default(true)
  tenantId     String?
  // ...
  @@schema("idp")
}

Step 2: Generator Outputs Views Using Allowlist

pnpm generate:shared in the IDP runs generate-shared-views.ts:

1. Recursively finds all .prisma files in the schema directory
2. Parses models using a brace-counting parser
3. For each model in EXPOSED_MODELS, includes ONLY the explicitly listed fields
4. New fields added to the IDP model are hidden by default
5. Outputs idp-views.prisma + create-idp-views.sql with @@schema("idp_views")
6. Validates output with prisma format

Allowlist config:

const EXPOSED_MODELS = {
  User: {
    viewName: 'IdpUser',
    fields: ['id', 'email', 'firstName', 'lastName', 'mobileNumber',
             'userTypes', 'isActive', 'tenantId', 'avatarUrl',
             'createdAt', 'updatedAt'],
    // NOT exposed: password, pin, refreshTokenVersion, failedLoginAttempts,
    // lockedUntil, shouldChangePassword, lastPasswordChangeAt, lastLoginAt, metadata
  },
  // ...
};

Step 3: Product Adds Submodule

git submodule add <idp-repo>/packages/idp-views idp-views

The submodule contains the generated .prisma file, SQL file, and the add-relations.sh script.

Step 4: Product Runs add-relations.sh

./idp-views/add-relations.sh

This copies idp-views/idp-views.prisma into prisma/schema/idp-views.prisma, injecting relations from idp-relations.conf between the PRODUCT_RELATIONS_START/END markers.

Step 5: Product Creates PostgreSQL VIEWs

cat idp-views/create-idp-views.sql | psql -U <user> -d <db>

Creates VIEWs in the idp_views schema:

CREATE SCHEMA IF NOT EXISTS "idp_views";

CREATE VIEW "idp_views"."IdpUser" AS
SELECT id, email, "firstName", "lastName", "mobileNumber",
       "userTypes", "isActive", "tenantId", "avatarUrl",
       "createdAt", "updatedAt"
FROM "idp"."User";

Step 6: Product Queries with Native Prisma

// Cross-schema JOIN via include
const drivers = await prisma.driver.findMany({
  include: { idpUser: true },
});

// Filter by IDP field
const results = await prisma.driver.findMany({
  where: { idpUser: { firstName: { contains: 'Mohammed', mode: 'insensitive' } } },
  include: { idpUser: true },
});

// Pagination with cross-schema data
const page = await prisma.driver.findMany({
  skip: 0, take: 20,
  include: { idpUser: { select: { firstName: true, lastName: true } } },
});

---

5. Running the PoC

Prerequisites

• Docker, Node.js 20+, pnpm

Start Database

docker run -d --name poc-multischema-pg \
  -e POSTGRES_USER=viero -e POSTGRES_PASSWORD=viero \
  -e POSTGRES_DB=viero_platform -p 5434:5432 postgres:16

sleep 3

docker exec poc-multischema-pg psql -U viero -d viero_platform \
  -c "CREATE SCHEMA IF NOT EXISTS idp; CREATE SCHEMA IF NOT EXISTS fuel_logistics; CREATE SCHEMA IF NOT EXISTS idp_views;"

Setup IDP

cd idp
pnpm install
pnpm prisma migrate dev --name init
pnpm prisma generate
pnpm seed
pnpm generate:shared

Setup Product

cd product
ln -s ../idp/packages/idp-views idp-views   # git submodule in production
./idp-views/add-relations.sh
cat idp-views/create-idp-views.sql | docker exec -i poc-multischema-pg psql -U viero -d viero_platform
pnpm install
pnpm prisma db push
pnpm prisma generate
pnpm seed
pnpm test

Cleanup

docker stop poc-multischema-pg && docker rm poc-multischema-pg

---

6. Test Results

#	Test	What It Validates
1	List drivers + user info	include: { idpUser: true } JOINs across schemas
2	Search by name "Mohammed"	where: { idpUser: { firstName: { contains } } } filters through VIEWs
3	Filter by tenant	Multi-tenant data correctly scoped
4	3-way nested include	Driver + IdpUser + Orders in one query
5	Read-only check	Prisma view behavior verification
6	Pagination	skip/take with cross-schema include + count

---

7. Recipes

Recipe 1: Add a Field to IDP User (Exposed to Products)

1. IDP: edit prisma/schema/models.prisma       -> add the field
2. IDP: edit scripts/generate-shared-views.ts   -> add field name to EXPOSED_MODELS.User.fields
3. IDP: pnpm prisma migrate dev --name <name>   -> migrate the table
4. IDP: pnpm generate:shared                    -> regenerate views
5. IDP: commit everything (migration + packages/idp-views/)
6. Product: pull submodule (cd idp-views && git pull)
7. Product: ./idp-views/add-relations.sh        -> regenerate local views
8. Product: re-run the SQL (create-idp-views.sql) -> update PostgreSQL VIEWs
9. Product: pnpm prisma generate                -> update Prisma client types

Recipe 2: Add a Sensitive Field (NOT Exposed)

1. IDP: edit prisma/schema/models.prisma       -> add the field
2. IDP: pnpm prisma migrate dev --name <name>
3. IDP: pnpm generate:shared                   -> field NOT in allowlist, NOT in output
4. Products: nothing to do. VIEWs unchanged.

Recipe 3: Add a New Product Table

1. Product: edit prisma/schema/models.prisma   -> add model with @@schema("fuel_logistics")
2. Product: pnpm prisma migrate dev --name <name>
IDP not involved.

Recipe 4: Add a Relation from Product to IDP View

1. Product: edit prisma/schema/models.prisma
   -> add idpUserId String and idpUser IdpUser @relation(...)
2. Product: edit idp-relations.conf
   -> add line: IdpUser:vehicles Vehicle[]
3. Product: ./idp-views/add-relations.sh
4. Product: pnpm prisma migrate dev --name <name>
5. Product: pnpm prisma generate

Recipe 5: Onboard a New Product Backend

1. Create new repo
2. git submodule add <idp-repo>/packages/idp-views idp-views
3. Create prisma/schema/_config.prisma:
   schemas = ["<your_schema>", "idp_views"], previewFeatures = ["views"]
4. Create idp-relations.conf with your relations
5. ./idp-views/add-relations.sh
6. Run SQL: psql -f idp-views/create-idp-views.sql (VIEWs shared, already exist if another product set them up)
7. Create product schema: CREATE SCHEMA IF NOT EXISTS "<your_schema>"
8. pnpm prisma migrate dev --name init
9. pnpm prisma generate

Recipe 6: Add a New IDP Model for Products to See

1. IDP: model already exists in models.prisma
2. IDP: edit scripts/generate-shared-views.ts -> add to EXPOSED_MODELS with viewName + fields
3. IDP: pnpm generate:shared
4. Products: pull submodule, run add-relations.sh, run SQL, regenerate

Recipe 7: Add a New Enum Value

1. IDP: edit prisma/schema/models.prisma -> add value to enum
2. IDP: pnpm prisma migrate dev
3. IDP: pnpm generate:shared
4. Products: pull submodule, run add-relations.sh, pnpm prisma generate
IMPORTANT: Deploy IDP first. If a user has the new type before products know about it,
Prisma may fail to parse the value.

---

8. Production Requirements

These are NOT in the PoC but required before production.

Database Role Isolation

Without this, products can bypass VIEWs and query idp."User" directly (including passwords).

CREATE ROLE fuel_app WITH LOGIN PASSWORD '...';

-- Product can access its own schema + shared views
GRANT USAGE ON SCHEMA fuel_logistics TO fuel_app;
GRANT ALL ON ALL TABLES IN SCHEMA fuel_logistics TO fuel_app;
GRANT USAGE ON SCHEMA idp_views TO fuel_app;
GRANT SELECT ON ALL TABLES IN SCHEMA idp_views TO fuel_app;

-- Product CANNOT access idp schema
REVOKE ALL ON SCHEMA idp FROM fuel_app;

PgBouncer (Connection Pooling)

Multiple services sharing one PostgreSQL need a connection pooler. Without it, 10 services x 3 replicas x 15 connections = 450 connections (PostgreSQL default max: 100).

Row-Level Security (Tenant Isolation)

VIEWs expose all tenants. Application-level filtering works but is not a security boundary. For defense in depth, add RLS policies or tenant-scoped VIEWs.

Deployment Ordering

1. IDP migrates first (creates/alters tables in idp schema)
2. VIEWs updated (re-run create-idp-views.sql if columns changed)
3. Products deploy (Prisma client expects VIEW columns to match)

Rule: IDP treats VIEW-exposed columns as a public API. Never remove columns; only add.

CI/CD Integration

# IDP CI: regenerate shared after schema changes
- run: pnpm generate:shared
- run: git diff --exit-code packages/idp-views/

# Product CI: sync before generate
- run: git submodule update --init --recursive
- run: ./idp-views/add-relations.sh
- run: pnpm prisma generate

---

9. Known Caveats

views is a Prisma Preview Feature

Requires previewFeatures = ["views"]. Preview features can change between versions. Pin your Prisma version across IDP and all products.

Model-to-View Relations Are Not Officially Documented

@relation from a model to a view works in practice (proven by this PoC) but is not explicitly documented by Prisma. No FK constraint is created at the database level. Referential integrity depends on application logic.

Views Still Expose Mutation Methods at Runtime

Prisma 7's view keyword generates create(), update(), delete() methods on the client at runtime. They fail at the database level (PostgreSQL rejects writes to views). Database role isolation is defense in depth.

Enum Types Across Schemas

The UserType enum lives in the idp schema at the PostgreSQL level. The VIEW passes through the column type. If IDP adds a new enum value before products update their Prisma schema, Prisma may fail to parse the value. Deploy IDP first, then products.

Single Database = Shared Resources

All services share one PostgreSQL instance. Heavy queries from one product can impact others. Monitor with pg_stat_statements. Use PgBouncer and consider read replicas at scale.

---

10. FAQ

Q: Does the product ever need IDP database credentials? No. The product connects with its own role. VIEWs in idp_views execute as the VIEW owner.

Q: What if I add a field to the IDP and forget to add it to the allowlist? It stays hidden. The allowlist is safe-by-default: new fields are not exposed unless explicitly added.

Q: What if I accidentally add password to the allowlist? Code review should catch this. For extra safety, add a CI check: grep -q 'password\|pin' packages/idp-views/idp-views.prisma && exit 1

Q: Can I add indexes on VIEWs? No. VIEWs use the underlying table's indexes. Ensure the IDP maintains indexes on User(id), User(tenantId), etc.

Q: What about materialized views for performance? Simple VIEWs are query rewrites; PostgreSQL sees through them and uses indexes directly. At 100K users this is performant. Switch to materialized views only if measured performance degrades.

Q: Why idp_views schema instead of putting VIEWs in each product's schema? Shared schema means one set of VIEWs for all products. No per-product SQL generation. The submodule file has @@schema("idp_views") hardcoded, no placeholder needed.

Q: Why not just hand-maintain the views file? The real IDP has 15+ models. Hand-maintaining views across multiple products leads to drift and risks leaking sensitive fields. The generator with an allowlist is safer.

Q: What's the migration path if the shared DB becomes a bottleneck?

1. Add read replicas (VIEWs can point at replicas)
2. Use PostgreSQL logical replication to separate databases
3. Replace VIEWs with event-driven sync (product code stays the same; only the backing data changes)

Q: Why does the product need add-relations.sh instead of using the submodule directly? Each product needs different relations on the views (drivers Driver[] vs cards Card[]). Prisma requires both sides of a relation in the same view block. The script copies the submodule file and injects product-specific relations.

---

11. Review Findings

This PoC was reviewed by 8 specialized agents across 3 rounds. Summary of final findings:

Architecture (Confidence: 4/5)

• Sound pattern for shared-DB multi-product platform
• idp_views shared schema eliminates per-product template replacement
• Allowlist approach is security-critical and correctly implemented
• Scales to 50K+ users with proper indexes; PgBouncer needed at 5+ services

Security

• All sensitive fields (password, pin, lockout state, tokens) correctly excluded
• Allowlist is safe-by-default (new fields hidden)
• VIEWs are application-level boundary; DB role isolation needed for production
• No tenant filtering in VIEWs (application-level; RLS recommended for production)

Code Quality

• Generator: brace-counting parser works for Prisma schemas (edge case: } in string literals)
• Sync script: portable bash, validates schema name, handles empty conf files
• Seeds: idempotent (upsert pattern)
• Tests: demo scripts proving the concept (not assertion-based test suite)

Prisma Compatibility

• view + @unique (not @id) is correct
• model-to-view relations work in practice (not officially documented)
• previewFeatures = ["views"] still required in Prisma 7
• multiSchema is GA (no preview flag needed)
• schemas = ["fuel_logistics", "idp_views"] is correct