Skip to content

Security: eddieburroughs/RegularUpkeep

Security

docs/security.md

RegularUpkeep Security Model

Overview

RegularUpkeep uses Supabase Row Level Security (RLS) to enforce multi-tenant data isolation at the database level. This ensures that users can only access data they are authorized to see, regardless of how the application code behaves.

User Roles

Role Description Access Level
customer Homeowner/property investor Own properties, bookings, payments
tenant Resident of a property Limited; submit issues, view within spend threshold
provider Service company owner/dispatcher Own services, bookings, team management
handyman Field technician Assigned jobs, availability, earnings
admin Platform operations/support Full system access
territory_manager Regional operator Properties/providers within territory
franchisee Territory franchise owner Territory management and revenue

Core Security Functions

is_admin()

Returns TRUE if the current user has the admin role.

SELECT is_admin(); -- Returns boolean

current_user_role()

Returns the role of the currently authenticated user.

SELECT current_user_role(); -- Returns user_role enum

can_access_property(property_id UUID)

Checks if the current user can access a specific property. Returns TRUE if:

  • User is an admin
  • User is a member of the property
  • User is a territory manager/franchisee for the property's territory
  • User is a provider with a booking at the property
  • User is a handyman assigned to a booking at the property
SELECT can_access_property('uuid-here'); -- Returns boolean

in_territory(territory_id UUID)

Checks if the current user manages a specific territory.

SELECT in_territory('uuid-here'); -- Returns boolean

is_provider_team_member(provider_id UUID)

Checks if the current user is on a provider's team (owner or staff).

SELECT is_provider_team_member('uuid-here'); -- Returns boolean

Table Security Policies

Properties

Operation Who Can Access
SELECT Property members, territory managers, admin, providers/handymen with bookings
INSERT Customers, admins, territory managers, franchisees
UPDATE Property owners/managers, admin
DELETE Property owners, admin

Property Members

Operation Who Can Access
SELECT Anyone who can access the property
INSERT Property owners/managers with can_manage_members, admin, or first member
UPDATE Property owners/managers with can_manage_members, admin
DELETE Property owners, admin

Territories

Operation Who Can Access
SELECT Everyone (active territories only)
INSERT Admin only
UPDATE Franchisee (own territory), admin
DELETE Admin only

Provider Team

Operation Who Can Access
SELECT Team members, admin
INSERT Provider owner, team admins, admin
UPDATE Provider owner, team admins, admin
DELETE Provider owner, admin

Property Access Model

Properties are the central entity in RegularUpkeep. All service-related data (bookings, quotes, maintenance, documents) links back to a property.

Member Roles

Role Permissions
owner Full control: manage members, approve quotes, view history, delete property
manager Manage members, approve quotes, view history
tenant Request service (within threshold), view limited history

Spend Threshold

Tenants have an optional spend_threshold_cents field. If a service quote exceeds this amount, the property owner must approve before the booking can proceed.

-- Example: Tenant can approve up to $500
UPDATE property_members
SET spend_threshold_cents = 50000
WHERE user_id = 'tenant-uuid' AND member_role = 'tenant';

Provider Team Model

Providers can have multiple team members with different roles:

Team Role Capabilities
owner Full control, manage team, access all data
admin Manage team, access all data
dispatcher Assign jobs, manage schedule
tech View assigned jobs, update status

Territory Model

Territories define geographic regions for franchising:

  • Boundaries defined by counties, ZIP codes, or polygon geometry
  • Franchisee owns the territory and manages local operations
  • Territory managers oversee provider recruitment and performance
  • Properties auto-assigned to territories based on location

Best Practices

For Developers

  1. Never bypass RLS: Always use the Supabase client with user authentication
  2. Test with different roles: Verify policies work for all user types
  3. Use helper functions: can_access_property() is the canonical access check
  4. Property-first design: New features should link to properties when appropriate

For Adding New Tables

  1. Always enable RLS: ALTER TABLE new_table ENABLE ROW LEVEL SECURITY;
  2. Create explicit policies for SELECT, INSERT, UPDATE, DELETE
  3. Consider property access: Use can_access_property() when relevant
  4. Document policies in this file

For Migrations

  1. Test migrations on a development database first
  2. Include rollback statements where possible
  3. Update this security document when adding new policies
  4. Verify RLS is enabled on all new tables

Audit Trail

The following tables maintain audit information:

  • booking_status_history - All booking status changes
  • *.updated_at triggers - Automatic timestamp on all updates

Future Considerations

  • API key authentication for service accounts
  • Role switching for admin impersonation
  • Detailed permission matrix per feature
  • Audit logging for sensitive operations

There aren’t any published security advisories