Database Schema & Migrations
Relevant source files
The following files were used as context for generating this wiki page:
- DEPLOYMENT_GUIDE.md
- scripts/verify_deployment.sql
- supabase/migrations/20251019_backend_hardening.sql
- src/integrations/supabase/types.ts
- supabase/migrations/20251211200310_83416cf1-7905-4d2a-b5ea-36c2b29d32b8.sql
- supabase/migrations/20250823022015_bdd020e5-4b71-4c43-97e4-841eb19bb78e.sql
- supabase/migrations/20251028085528_40615c56-edd5-4617-b466-579f4a563c0b.sql
- supabase/migrations/20251026050005_a9bc8983-4baa-4053-825c-116e733ad580.sql
The database system for Monarch Property Management is built on Supabase (PostgreSQL) and is designed to handle property listings, project management, vendor bidding (RFQs), and financial transactions. The architecture emphasizes security through Row Level Security (RLS) hardening, performance via specialized indexing, and multi-tenant isolation.
The schema evolved through a series of migrations that introduced core business entities, followed by a "Backend Hardening" phase (2025-10-19) which implemented 8 performance indexes, 5 SECURITY DEFINER helper functions, and over 15 RLS policies to ensure data protection.
Sources: DEPLOYMENT_GUIDE.md:12-25, supabase/migrations/20251026050005_a9bc8983-4baa-4053-825c-116e733ad580.sql:1-10
Core Data Model
The system utilizes several interconnected tables to manage the lifecycle of properties and maintenance projects. The primary entities include Properties, Projects, RFQs (Request for Quotations), and Vendors.
Entity Relationships
The following ER diagram illustrates the relationships between the core management entities, specifically focusing on how Projects and RFQs bridge Properties and Vendors.
The diagram shows the hierarchy where Properties serve as the root for Projects and RFQs, which then branch into granular tasks (Milestones) and bidding units (Lots).
Sources: src/integrations/supabase/types.ts, supabase/migrations/20250823022015_bdd020e5-4b71-4c43-97e4-841eb19bb78e.sql:3-19, supabase/migrations/20250928051909_f878a793-e9c2-407f-9b7e-c58eaa64db69.sql:3-15
Key Tables and Structures
| Table Name | Primary Purpose | Key Fields |
|---|---|---|
properties | Physical asset tracking | id, address, property_type, status, owner_id |
projects | Work order management | id, title, status, priority, property_id, assigned_vendor_id |
rfqs | Bidding process control | id, title, deadline, status, tenant_id, document_control |
profiles | User identity and roles | id, full_name, email, role, tenant_id |
vendor_bids | Vendor proposals | id, rfq_id, vendor_id, bid_amount, status |
Sources: src/integrations/supabase/types.ts:742-1240, supabase/migrations/20250823022015_bdd020e5-4b71-4c43-97e4-841eb19bb78e.sql:3-19
Security Architecture & Hardening
A critical component of the database is the security layer, primarily enforced through Postgres Row Level Security (RLS). All public tables are required to have RLS enabled to prevent unauthorized data access.
Security Helper Functions
To simplify RLS policy logic and ensure security, a set of SECURITY DEFINER functions was implemented. These functions run with the privileges of the owner (bypassing RLS internally) to check permissions reliably.
is_admin_user(user_uuid): Checks if a specific user has the 'admin' role in theuser_rolestable.user_has_role(role_name): Verifies if the current authenticated user has a specific role or is an admin.get_user_id(): Safely retrieves the current authenticated user's ID.can_access_room(room_id): Logic for validating if a user is a member of a messaging room.
Sources: scripts/verify_deployment.sql:63-90, supabase/migrations/20251026050005_a9bc8983-4baa-4053-825c-116e733ad580.sql:10-45
RLS Policy Implementation
Policies are categorized by role, ensuring that vendors can only see their assigned projects while admins maintain global visibility.
The flow demonstrates the role-based conditional logic used in USING and WITH CHECK clauses across the database.
Sources: supabase/migrations/20251211200310_83416cf1-7905-4d2a-b5ea-36c2b29d32b8.sql, supabase/migrations/20250823022015_bdd020e5-4b71-4c43-97e4-841eb19bb78e.sql:24-48
Performance Optimization
The 2025-10-19 hardening migration introduced specialized indexes to improve query performance for common lookups, reportedly leading to 20x-100x speed improvements for critical queries.
Specialized Indexes
| Index Name | Target Table | Columns | Purpose |
|---|---|---|---|
idx_vendor_bids_vendor_id | vendor_bids | vendor_id | Faster lookup for vendor proposals |
idx_projects_property_id | projects | property_id | Linked project history per property |
idx_realtime_messages_topic | realtime_messages | topic | Optimized messaging performance |
idx_projects_status | projects | status | Faster dashboard filtering |
idx_leases_tenant_id | leases | tenant_id | Tenant portal performance |
Sources: scripts/verify_deployment.sql:14-30, DEPLOYMENT_GUIDE.md:144-155, supabase/migrations/20251026050005_a9bc8983-4baa-4053-825c-116e733ad580.sql:106-115
Database Logic & RPC Functions
The system uses Pl/pgSQL functions for complex multi-table operations, exposed via PostgREST as RPC (Remote Procedure Call) endpoints. These functions are marked as SECURITY DEFINER to ensure they execute with consistent permissions.
Core Business Functions
create_rfq(p_property_id, p_title, p_description, p_deadline, p_lots)
This function handles the atomic creation of an RFQ header and its associated line items (lots). It enforces that only admins or property managers can initiate the process.
Sources: supabase/migrations/20251028085528_40615c56-edd5-4617-b466-579f4a563c0b.sql:6-43
award_contract(p_rfq_id, p_vendor_id, p_contract_value, p_start_date, p_end_date)
Manages the transition from a bid to an active contract. It performs three operations:
- Generates a unique contract number (Format:
CNT-YYYYMMDD-hash). - Creates a new record in the
contractstable. - Updates the status of the source RFQ to 'awarded'.
Sources: supabase/migrations/20251028085528_40615c56-edd5-4617-b466-579f4a563c0b.sql:111-152
submit_bid(p_rfq_id, p_vendor_id, p_bid_lines, p_notes)
Allows vendors to submit pricing for specific RFQ lots. It validates that the vendor was actually invited before allowing the insertion into bid_lines.
Sources: supabase/migrations/20251028085528_40615c56-edd5-4617-b466-579f4a563c0b.sql:80-109
Verification and Deployment
Migrations are verified using a standardized script (scripts/verify_deployment.sql) that checks for the existence of components and correct security settings.
-- Example verification check for secure functions
SELECT
p.proname as function_name,
CASE WHEN p.prosecdef THEN '✅ SECURITY DEFINER' ELSE '❌ NOT SECURE' END as security_status
FROM pg_proc p
WHERE p.proname IN ('get_user_id', 'is_admin_user', 'user_has_role');
The deployment workflow requires a full database backup before running hardening migrations, followed by executing the verification script to ensure all 8 indexes and 5 secure functions are active.
Sources: scripts/verify_deployment.sql:47-60, DEPLOYMENT_GUIDE.md:50-80
Conclusion
The Monarch Property Management database schema is a hardened, multi-tenant Postgres implementation. Through the use of SECURITY DEFINER helper functions and strict RLS policies, the system ensures data isolation between vendors, tenants, and staff. The inclusion of specialized B-Tree indexes on foreign keys and status columns provides the necessary performance for high-concurrency operations like real-time messaging and large-scale RFQ management.