Menu
Designs or reviews multi-tenant database schemas for tenant isolation, lifecycle tracking, usage auditing, and query safety. Use when designing SaaS schema, reviewing tenant isolation, or adding company or workspace scoping to data models.
Install with Codex or Claude Copy this prompt, paste it into Codex, Claude, or another assistant, and let it review the skill page and install it for you.
Based on SOC occupation classification
Evaluates and enforces layered middleware architecture for authentication, authorization, and usage enforcement on API routes. Use when building request middleware, enforcing plans or feature access, or attaching request context to downstream handlers.
Selects Cloudflare infrastructure services and LLM providers for a feature set based on dependency chains, storage needs, and settled policy. Use when assigning services (D1, KV, R2, Durable Objects, Queues, Workflows, Workers AI, Hyperdrive), evaluating whether a feature needs Workflows or Durable Objects, choosing between LLM providers, or confirming a build follows the Workers-first topology.
Audits request handling pipelines for explicit data flow, visible transformations, and traceability through logs. Use when designing request handling pipelines, reviewing code for debuggability, tracing production issues, or evaluating whether a new developer could follow the flow.
Audits stateful logic and error handling to verify single sources of truth, boundary-layer error handling, and explicit recovery paths. Use when designing APIs or workflows, refactoring stateful logic, or reviewing retries, fallback behavior, or recovery flows.
Traces feature implementation from UI through handlers, middleware, and storage to identify broken wiring, dead paths, and coverage gaps. Use when reviewing implementation completeness, validating frontend-to-backend wiring, or checking route/handler/middleware/storage consistency.
Audits authentication, authorization, and input validation boundaries to verify trust is established once at entry and not re-checked ad hoc throughout business logic. Use when building or reviewing auth, designing middleware, handling user/webhook/third-party input, or performing security-oriented review.
| name | design-tenant-schema |
| description | Designs or reviews multi-tenant database schemas for tenant isolation, lifecycle tracking, usage auditing, and query safety. Use when designing SaaS schema, reviewing tenant isolation, or adding company or workspace scoping to data models. |
Primary roles: engineer, architect
Evaluates a multi-tenant data model for correct tenant scoping, explicit lifecycle state, audit-ready usage tracking, and safe relational patterns — or produces a schema design that satisfies these requirements.
Identify the tenant root. Locate the companies (or workspaces/organizations) table that serves as the tenant boundary. Confirm it includes: ID, name, slug, plan reference, subscription status, and timestamps.
Verify tenant scoping on all entities. For every table that holds user-generated or tenant-specific data:
company_id foreign key exists.company_id (no cross-tenant data leakage).Verify the user model. Check the users table:
Verify the plans table. Check that reference data is structured:
Check schema conventions. Across all tables, verify:
created_at and updated_at timestamps on every table.Verify usage tracking. Inspect the usage/logs table:
Evaluate authorization implications. Review joins and lookups for paths where:
company_id filter.Check migration safety. If schema changes are proposed:
Produce findings. Document the schema assessment with specific gaps and recommendations.
Companies (tenant root):
created_at, updated_atUsers (scoped to company):
company_id foreign key (every user belongs to a company)owner, admin, memberPlans (reference data):
| Convention | Rule |
|---|---|
| Timestamps | Every table gets created_at and updated_at |
| Status columns | CHECK constraints with explicit allowed values |
| Primary keys | TEXT (UUIDs), not auto-increment integers |
| Foreign keys | Always indexed |
| Status columns | Always indexed |
| Soft delete | Via status column, not row deletion, when audit trail matters |
Track API usage per company per billing period:
| Column | Type | Purpose |
|---|---|---|
| id | TEXT (PK) | Usage record ID |
| company_id | TEXT (FK) | Tenant association |
| user_id | TEXT (FK) | Who made the request |
| feature | TEXT | Feature or worker name |
| success | BOOLEAN | Success/failure flag |
| error_message | TEXT | Error details on failure (nullable) |
| provider | TEXT | LLM provider used (nullable) |
| created_at | TIMESTAMP | For billing period queries |
This enables: monitoring queries, usage enforcement, billing reconciliation, and debugging — all from the schema.
company_id. Cross-tenant queries become possible.created_at/updated_at make debugging, auditing, and migration planning difficult.company_id or filter by status will scan the full table.For every query that touches tenant data:
company_id in the WHERE clause?company_id?Produce a schema review or design document containing:
company_id, and whether queries properly filter by it.