Customer support SaaS with database-enforced tenant isolation.
SupportFlow is a multi-tenant customer support platform built around PostgreSQL RLS, Security Definer RPCs, transactional workflows, realtime authorization, and server-side validation.
The Vulnerability of Frontend Tenant Isolation
A customer support system appears straightforward until multiple organizations share the same database infrastructure.
- Which organization owns this ticket?
- Which specific users are allowed to see it?
- Which users have permission to transition ticket status or priority?
- How is every state transition audited without race conditions?
- How do realtime notifications reach only authorized subscribers?
- Why a frontend check alone is a critical security vulnerability.
Make tenant isolation and workflow integrity properties of the database and transaction model, not conventions developers are expected to remember.
System Overview & Capabilities
SupportFlow provides three distinct operational roles enforced through database rules.
Customer Role
- Create tickets
- Track ticket status
- Upload attachments
- Close resolved tickets
Agent Role
- Triage & respond
- Status state transitions
- Tenant-scoped collaboration
- Monitor SLA clocks
Admin Role
- Assign agent workload
- Manage priority & SLA
- Organization settings
- Database-aggregated analytics
Data Flow & Infrastructure
Supabase Auth
↓
Next.js Authorization
↓
PostgreSQL Tenant Context (current_organization_id)
↓
RLS-Protected Reads
↓
Security Definer RPC Mutations
↓
Transactional Workflow (Atomic Lock & Mutate)
↓
Audit Events + Realtime WebSocket DeliveryShared Database + Shared Schema + Organization Discriminator
Enforcing authorization below the application layer
SupportFlow uses a shared database, shared schema, and organization_id discriminator model.
The application never trusts a tenant ID sent from the browser. Instead, tenant context is derived directly from the authenticated session inside PostgreSQL via current_organization_id() and current_role().
Reads are bounded by RLS policies; mutations are executed inside atomic Security Definer RPCs that lock target records, validate roles, update workflow state, and log immutable audit entries in a single transaction.
In a shared database, the data layer itself should understand who owns the data and what operations a user is allowed to perform.
Engineering Details
Database-Level Authorization & RLS
Every business table has Row-Level Security active with policies filtering by verified tenant context.
- Reads constrained by organization_id = current_organization_id().
- Customer queries restricted to tickets where customer_id = auth.uid().
- RLS serves as an unbypassable second line of defense behind server middleware.
Transactional Security Definer Mutations
Core operations (create_ticket, assign_ticket, update_ticket_status, add_ticket_comment) run as PostgreSQL RPCs.
- Resolves acting tenant and validates role permissions.
- Acquires row-level lock on target ticket to prevent concurrent race conditions.
- Updates ticket state, calculates SLA timestamps, and records an audit log row atomically.
Authorized Private Realtime Topics
Realtime comments and updates use private WebSocket broadcast channels verified by database policies.
Comment Inserted
↓
PostgreSQL Trigger
↓
realtime.send()
↓
ticket:<UUID>:comments
↓
realtime.messages RLS Policy Check
↓
Authorized Subscriber WebSocket DeliveryTenant-Scoped Private Storage & 60s Signed URLs
Ticket attachments reside in a private bucket ('ticket-attachments') with 5 MB maximum size and MIME allowlists.
- Storage path structure: organization_id / ticket_id / uuid-filename.
- Downloads require server-generated 60-second signed URLs.
- Zero public attachment buckets.
SLA Response Engine & Database Aggregated Analytics
Continuous elapsed-time response targets computed directly inside PostgreSQL.
| Priority Tier | Target First Response SLA |
|---|---|
| Urgent | 4 hours |
| High | 24 hours |
| Medium | 48 hours |
| Low | 72 hours |
System Centerpiece & Inspection
Inspect the live execution state, benchmarks, security boundaries, and architectural guarantees.
| Ticket ID | Org ID | Title | Status | Visibility |
|---|---|---|---|---|
#TK-101 | ORG_01 | Billing invoice mismatch | In Progress | ✓ ALLOWED (Org Match) |
#TK-102 | ORG_01 | SSO SAML configuration | Open | ✓ ALLOWED (Org Match) |
#TK-201 | ORG_02 | API webhook rate limiting | Resolved | ✕ FILTERED (RLS) |
#TK-202 | ORG_02 | Database migration error | Open | ✕ FILTERED (RLS) |
-- Evaluated inside PostgreSQL kernel
CREATE POLICY "tenant_ticket_isolation" ON tickets
FOR ALL TO authenticated
USING (
organization_id = current_organization_id()
AND (
current_role() IN ('admin', 'agent')
OR (current_role() = 'customer' AND customer_id = auth.uid())
)
);
-- Active Session State:
-- current_organization_id() = 'ORG_01'
-- current_role() = 'agent'
-- Result: Zero cross-tenant data leakage possibleRigorous Verification Evidence
SLA logic, Zod validation, role state machines
Live Supabase RLS & RPC isolation verified
Simultaneous Customer, Admin, and Agent contexts
Defensive search_path, atomic transactions
Active across all 7 business tables
Multi-Role Verified Ticket Lifecycle
Full workflow tested with simultaneous browser contexts in Playwright.
Customer creates ticket via create_ticket RPC with file attachment
Admin assigns agent workload; SLA clock begins
Agent changes status to In Progress and sends private reply comment
Customer receives reply immediately via authorized Realtime WebSocket
Database trigger records first_agent_response_at and verifies SLA compliance
Agent marks ticket Resolved; Customer closes ticket; audit trail completed
Full state progression completed atomically with zero cross-tenant leakage.
Engineering Capabilities Proven
Disciplined Technical Claims
- SupportFlow does not claim enterprise-scale load, millions of users, 99.99% uptime, or billing infrastructure.
- The SLA engine measures continuous elapsed time rather than custom business-hours calendars.
- The team roster lookup requires server-side service_role access to retrieve emails from auth.users (deliberate architectural trade-off).
Engineering Retrospective
Multi-tenancy is not just an application routing concern. In a shared database, the data layer itself should understand who owns the data and what operations a user is allowed to perform.
RLS as an Unbypassable Guardrail
Even if an application handler has a logic flaw, row-level security prevents cross-tenant data leakage at the query level.
Atomic RPCs Prevent Partial Failures
Wrapping status changes, audit logging, and SLA recalculation in a single stored procedure eliminates multi-step network failure inconsistencies.