RLS Policy Testing

Testing Row Level Security policies to ensure data isolation.

Approach

RLS policies should be tested against the actual Supabase database (not mocked):

1. Create test users with different roles
2. Attempt cross-boundary access
3. Verify access is correctly denied

Key Test Cases

Brand Isolation

-- User A has projects with brand "Holiday Inn Express"
-- User A should NOT see specs for brand "Hilton Garden Inn"
SET ROLE authenticated;
SET request.jwt.claim.sub = 'user-a-uuid';

SELECT COUNT(*) FROM pip_ai_spec_sections
WHERE brand_name = 'Hilton Garden Inn';
-- Expected: 0 rows

Project Membership

-- User B is NOT a member of Project X
-- User B should NOT see PIP items for Project X
SET request.jwt.claim.sub = 'user-b-uuid';

SELECT COUNT(*) FROM pip_ai_pip_items
WHERE project_id = 'project-x-uuid';
-- Expected: 0 rows

Admin Access

-- Admin should see all data regardless of membership
SET request.jwt.claim.sub = 'admin-uuid';

SELECT COUNT(*) FROM pip_ai_spec_sections;
-- Expected: all rows

Idempotency Tests

Verify that re-running N8N workflows doesn't create duplicates:

-- Insert spec section twice with same unique key
INSERT INTO pip_ai_spec_sections (spec_upload_id, spec_number, ...)
VALUES ('upload-1', 'EXG2-601', ...)
ON CONFLICT (spec_upload_id, spec_number) DO UPDATE SET ...;

-- Count should be 1
SELECT COUNT(*) FROM pip_ai_spec_sections
WHERE spec_upload_id = 'upload-1' AND spec_number = 'EXG2-601';
-- Expected: 1