Database Schema & RLS
Relevant source files
The following files were used as context for generating this wiki page:
- db/migrations_combined.sql
- supabase/migrations/20251128000000_init.sql
- supabase/migrations/20251220000000_rbac_schema.sql
- supabase/migrations/20260121000000_ensure_alignment.sql
- db/migrations/20251208000001_rbac_implementation.sql
- db/migrations/20250228000001_pos_weight_shrinkage_loyalty_consolidated.json
The DRX CRM database is a multi-tenant PostgreSQL system designed for privacy-first retail operations. It utilizes a centralized schema with strict per-business isolation enforced through Row Level Security (RLS) and a granular Role-Based Access Control (RBAC) system. The architecture supports complex inventory tracking (including weight-based cannabis products), customer credit management, and AI-driven business insights.
The schema is built to ensure that data remains siloed between different business entities while allowing Super Admins system-wide oversight. Every sensitive operation—from inventory adjustments to price overrides—is captured in an immutable audit trail to maintain compliance and operational integrity.
Sources: db/migrations_combined.sql:431-432, supabase/migrations/20251128000000_init.sql:1-5
Core Data Architecture
The database is organized into several modules: Business Management, Inventory, Sales, Customers, and Credit. Most tables include a business_id foreign key to facilitate tenant isolation.
Entity Relationship Overview
The following diagram illustrates the primary relationships between the business profile and its operational modules.
Primary Data Models
| Table | Purpose | Key Fields |
|---|---|---|
business_profile | High-level tenant identity. | id, owner_id, business_name, is_active |
inventory | Product catalog and stock levels. | sku, quantity_on_hand, unit_cost, selling_price |
customers | CRM records and compliance data. | first_name, last_name, total_purchases, med_card_number |
sales | Transaction header records. | order_number, total_amount, payment_method, order_status |
user_roles | Tenant-specific user permissions. | user_id, business_id, role |
Sources: db/migrations_combined.sql:11-150, supabase/migrations/20260121000000_ensure_alignment.sql:47-55
Role-Based Access Control (RBAC)
The system implements a hierarchical RBAC model using custom PostgreSQL types and permission mapping tables.
Role Hierarchy and Permissions
The app_role enum defines the available roles, ranging from cashier to super_admin.
RBAC Implementation Components
- Enums:
app_role('cashier', 'supervisor', 'manager', 'super_admin'). - Permissions Table:
app_permissionsstores granular action codes likesales.voidorinventory.manage. - Mapping Table:
role_permissionslinks roles to specific permission codes. - Enforcement Functions:
check_permission(required_permission)validates if the current authenticated user has the necessary rights.
Sources: supabase/migrations/20251220000000_rbac_schema.sql:14-31, 126-146
Row Level Security (RLS)
RLS is the primary mechanism for multi-tenant isolation. It ensures that users can only interact with data belonging to their specific business_id.
Security Helper Functions
The system uses several security-definer functions to evaluate access safely:
is_admin(user_id): Checks if a user is a system-wide Super Admin.has_business_access(user_id, business_id): Verifies if a user has an active role within a specific business.get_user_business_id(user_id): Retrieves the primary business context for a user.
Sources: db/migrations_combined.sql:454-486, supabase/migrations/20251128000000_init.sql:454-486
Policy Enforcement Examples
Policies are applied to every table to restrict SELECT, INSERT, UPDATE, and DELETE operations.
-- Inventory View Policy
CREATE POLICY "Users can view inventory for their businesses"
ON inventory FOR SELECT
USING (
has_business_access(auth.uid(), business_id)
OR is_admin(auth.uid())
);
-- Inventory Management Policy (with RBAC)
CREATE POLICY "Managers can insert inventory"
ON inventory FOR INSERT
WITH CHECK (check_permission(auth.uid(), business_id, 'inventory.manage'));
Sources: db/migrations_combined.sql:518-531, db/migrations/20251208000001_rbac_implementation.sql:151-153
Weight-Based Inventory & Sales
To support cannabis and bulk retail, the schema includes specialized fields and functions for handling fractional quantities and weight-based pricing.
Weight Tracking Extensions
The inventory table is extended to track grams specifically:
inventory_in_grams: Decimal tracking of mass.price_per_gram: Tiered or flat pricing.allow_fractional_weight: Boolean toggle for retail precision.
Sources: db/migrations/20250228000001_pos_weight_shrinkage_loyalty_consolidated.json:Step 4
POS Checkout Logic (Enhanced)
A specialized RPC function process_weight_based_checkout manages atomic transactions involving weight deductions, loyalty discount application, and inventory logging.
Audit & Compliance
The schema enforces a "write-once, read-many" philosophy for sensitive events via the system_audit_log and inventory_log tables.
Audit Log Structure
| Field | Type | Description |
|---|---|---|
action | VARCHAR | The type of event (e.g., 'pos_checkout', 'shrinkage_recorded'). |
old_values | JSONB | State of the record before the change. |
new_values | JSONB | State of the record after the change. |
user_id | UUID | The staff member who performed the action. |
Sources: db/migrations_combined.sql:415-425, db/migrations/20250228000001_pos_weight_shrinkage_loyalty_consolidated.json:Step 9
Inventory Traceability
Every stock movement (Sale, Intake, Shrinkage, or Adjustment) is recorded in the stock_movements table, providing a complete historical ledger of every gram/unit in the system.
Sources: db/migrations/20250228000001_pos_weight_shrinkage_loyalty_consolidated.json:Step 10
Summary
The DRX CRM database schema utilizes PostgreSQL's robust security features to deliver a multi-tenant environment suitable for regulated industries. By combining RLS for data isolation with a granular RBAC layer and specialized RPC functions for atomic transactions, the system ensures data integrity, compliance, and operational speed.