HatoBMS Database Schema Documentation
🏗️ Production-Ready Database Setup
Your database has been configured with the following production-ready features:
📊 Core Tables
1. Users
- User authentication and profile management
- Sync capabilities with offline support
- Device tracking for multi-device sync
2. Enterprises & Workspaces
- Multi-tenant architecture
- Role-based access control (admin, member, viewer)
- Workspace isolation for data security
3. Business Data Tables
- Areas: Location/area management
- Customers: Customer relationship management
- Suppliers: Supplier management
- Inventory: Stock management with advanced features
- Transactions: Sales, purchases, adjustments
- Transaction Items: Detailed transaction line items
🔄 Sync Features
Offline-First Architecture
- Sync Version Tracking: Each record has a sync version for conflict resolution
- Soft Deletes: Records are marked as deleted, not physically removed
- Device Sync: Track sync status per device
- Conflict Resolution: Automatic conflict resolution with customizable strategies
Sync Tables
sync_log: Tracks all changes for synchronizationdevice_sync: Manages device sync statusaudit_log: Comprehensive audit trail
🔒 Security Features
Row Level Security (RLS)
- All tables protected with RLS policies
- Workspace-based data isolation
- Role-based permissions
- User can only access their workspace data
Authentication Integration
- Supabase Auth integration
- JWT token validation
- Secure API access
Indexes
- Sync version indexes for efficient sync queries
- Workspace-based indexes for data filtering
- Foreign key indexes for join performance
- Composite indexes for complex queries
Database Functions
increment_sync_version(): Auto-increment sync versionslog_sync_change(): Track changes for syncget_sync_changes(): Fetch changes since versionresolve_sync_conflict(): Handle sync conflictsupdate_device_sync_status(): Update device sync state
📱 Flutter Integration
Models
- UserModel: Enhanced with sync fields
- WorkspaceModel: Multi-tenant support
- InventoryModel: Advanced stock management
- CustomerModel: CRM features
- TransactionModel: Complete transaction tracking
Sync Service
- SyncService: Complete offline/online sync
- Conflict Resolution: Automatic conflict handling
- Batch Operations: Efficient data sync
- Device Management: Multi-device sync support
🚀 Deployment
Quick Deploy
./scripts/deploy-database.sh
Manual Steps
- Login to Supabase:
supabase login
- Link Project:
supabase link --project-ref YOUR_PROJECT_ID
- Push Migrations:
supabase db push --include-all
- Verify: Check Supabase dashboard
Environment Variables
Update your .env files with:
VITE_SUPABASE_URL=https://YOUR_PROJECT_ID.supabase.co
VITE_SUPABASE_PUBLISHABLE_KEY=YOUR_ANON_KEY
VITE_SUPABASE_PROJECT_ID=YOUR_PROJECT_ID
🔧 Post-Deployment Checklist
✅ Database Verification
✅ Application Testing
✅ Security Check
📊 Monitoring & Maintenance
Database Health
- Monitor slow queries in Supabase dashboard
- Check connection pool usage
- Review storage usage trends
- Monitor sync performance
Sync Health
- Track sync conflicts and resolution
- Monitor device sync status
- Review audit logs for suspicious activity
- Optimize sync frequency based on usage
Data Integrity
- Regular backup verification
- Monitor foreign key constraints
- Check for orphaned records
- Validate sync version consistency
🆘 Troubleshooting
Common Issues
- Sync Conflicts: Check
sync_log table for conflict patterns
- Performance Issues: Review query plans and indexes
- RLS Errors: Verify user permissions and workspace access
- Connection Limits: Monitor database connection usage
Debug Queries
-- Check sync status
SELECT * FROM device_sync WHERE device_id = 'YOUR_DEVICE_ID';
-- View recent changes
SELECT * FROM sync_log ORDER BY created_at DESC LIMIT 10;
-- Monitor performance
SELECT * FROM pg_stat_statements ORDER BY total_time DESC;
📚 Additional Resources