Cursor rule

1---
2globs: *.sql
3description: Database design patterns and conventions for PostgreSQL in POS System
4---
5 
6# Database Development Guidelines (PostgreSQL)
7 
8## Schema Design
9 
10### Primary Schema
11The main database schema is defined in [database/init/01_schema.sql](mdc:database/init/01_schema.sql):
12 
13### Core Tables Structure:
141. **users** - Staff and user management with role-based access
152. **categories** - Product categorization with sorting and colors
163. **products** - Menu items with pricing and availability
174. **dining_tables** - Table and seating management
185. **orders** - Order lifecycle with status tracking
196. **order_items** - Individual items within orders
207. **payments** - Payment processing and history
218. **inventory** - Stock management and tracking
229. **order_status_history** - Audit trail for order changes
23 
24## Naming Conventions
25 
26### Table Names
27- Use plural snake_case: `order_items`, `dining_tables`
28- Descriptive names that clearly indicate content
29- Avoid abbreviations unless universally understood
30 
31### Column Names
32- Use snake_case: `created_at`, `order_number`
33- Boolean fields start with `is_`: `is_active`, `is_occupied`
34- Timestamps end with `_at`: `created_at`, `processed_at`
35- Foreign keys follow pattern: `{table_name}_id`
36 
37### Constraint Names
38- Primary keys: `{table}_pkey`
39- Foreign keys: `fk_{table}_{referenced_table}`
40- Unique constraints: `unique_{table}_{column}`
41- Check constraints: `check_{table}_{column}_{description}`
42 
43## Data Types & Standards
44 
45### UUID Primary Keys
46Use UUID v4 for all primary keys:
47```sql
48CREATE TABLE example_table (
49    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
50    -- other columns
51);
52```
53 
54### Timestamps
55Always use `TIMESTAMP WITH TIME ZONE` for time tracking:
56```sql
57created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
58updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
59```
60 
61### Money Values
62Use `DECIMAL(10,2)` for all monetary values:
63```sql
64price DECIMAL(10,2) NOT NULL,
65total_amount DECIMAL(10,2) NOT NULL DEFAULT 0
66```
67 
68### String Fields
69- Use appropriate VARCHAR lengths based on expected content
70- Use TEXT for long content without length restrictions
71- Apply NOT NULL constraints where appropriate
72 
73## Indexing Strategy
74 
75### Performance Indexes
76Critical indexes defined in [database/init/01_schema.sql](mdc:database/init/01_schema.sql):
77 
78```sql
79-- Query optimization indexes
80CREATE INDEX idx_orders_status ON orders(status);
81CREATE INDEX idx_orders_created_at ON orders(created_at);
82CREATE INDEX idx_orders_table_id ON orders(table_id);
83CREATE INDEX idx_order_items_order_id ON order_items(order_id);
84CREATE INDEX idx_products_category_id ON products(category_id);
85CREATE INDEX idx_products_is_available ON products(is_available);
86```
87 
88### Index Guidelines
89- Index foreign key columns for JOIN performance
90- Index columns used in WHERE clauses frequently
91- Index columns used for sorting (ORDER BY)
92- Avoid over-indexing (impacts INSERT/UPDATE performance)
93- Consider composite indexes for multi-column queries
94 
95## Relationships & Constraints
96 
97### Foreign Key Relationships
98Maintain referential integrity with proper CASCADE rules:
99```sql
100-- Orders reference tables and users
101table_id UUID REFERENCES dining_tables(id) ON DELETE SET NULL,
102user_id UUID REFERENCES users(id) ON DELETE SET NULL,
103 
104-- Order items reference orders and products  
105order_id UUID REFERENCES orders(id) ON DELETE CASCADE,
106product_id UUID REFERENCES products(id) ON DELETE CASCADE
107```
108 
109### Check Constraints
110Use CHECK constraints for data validation:
111```sql
112-- Enum-like constraints
113role VARCHAR(20) NOT NULL CHECK (role IN ('admin', 'manager', 'cashier', 'kitchen')),
114status VARCHAR(20) NOT NULL CHECK (status IN ('pending', 'confirmed', 'preparing', 'ready', 'served', 'completed', 'cancelled')),
115 
116-- Business rule constraints
117quantity INTEGER NOT NULL DEFAULT 1 CHECK (quantity > 0),
118price DECIMAL(10,2) NOT NULL CHECK (price >= 0)
119```
120 
121## Triggers & Automation
122 
123### Updated Timestamp Trigger
124Automatic updated_at timestamp management:
125```sql
126CREATE OR REPLACE FUNCTION update_updated_at_column()
127RETURNS TRIGGER AS $$
128BEGIN
129    NEW.updated_at = CURRENT_TIMESTAMP;
130    RETURN NEW;
131END;
132$$ language 'plpgsql';
133 
134-- Apply to relevant tables
135CREATE TRIGGER update_orders_updated_at 
136    BEFORE UPDATE ON orders 
137    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
138```
139 
140## Data Migration Patterns
141 
142### Schema Changes
143For schema modifications:
1441. Create migration scripts with version numbers
1452. Always provide both UP and DOWN migrations
1463. Test migrations on sample data before production
1474. Use transactions for multiple related changes
148 
149### Example Migration Pattern:
150```sql
151-- Migration: 001_add_customer_phone.sql
152BEGIN;
153 
154ALTER TABLE orders ADD COLUMN customer_phone VARCHAR(20);
155CREATE INDEX idx_orders_customer_phone ON orders(customer_phone) 
156    WHERE customer_phone IS NOT NULL;
157 
158-- Rollback plan documented
159-- ALTER TABLE orders DROP COLUMN customer_phone;
160 
161COMMIT;
162```
163 
164## Query Performance Patterns
165 
166### Efficient JOINs
167Use appropriate JOIN types and index foreign keys:
168```sql
169-- Good: Index-optimized JOIN
170SELECT o.*, t.table_number, u.username
171FROM orders o
172LEFT JOIN dining_tables t ON o.table_id = t.id
173LEFT JOIN users u ON o.user_id = u.id
174WHERE o.status = 'pending';
175```
176 
177### Pagination
178Always use LIMIT/OFFSET for large result sets:
179```sql
180SELECT * FROM orders 
181ORDER BY created_at DESC 
182LIMIT $1 OFFSET $2;
183```
184 
185### Parameterized Queries
186Always use parameter placeholders to prevent SQL injection:
187```sql
188-- Good: Parameterized query
189SELECT * FROM products WHERE category_id = $1 AND is_available = $2;
190 
191-- Bad: String concatenation (vulnerable to injection)
192-- SELECT * FROM products WHERE category_id = '" + categoryId + "'
193```
194 
195## Backup & Recovery
196 
197### Data Protection
198- Regular automated backups of critical data
199- Point-in-time recovery capability
200- Test restore procedures regularly
201- Document recovery time objectives (RTO)
202 
203## Security Considerations
204 
205### Access Control
206- Use least-privilege principles for database users
207- Separate users for application vs administrative access
208- Regular password rotation for database accounts
209- Network-level access restrictions
210 
211### Data Encryption
212- Encrypt sensitive data at rest
213- Use SSL/TLS for database connections
214- Consider column-level encryption for PII
215- Audit access to sensitive tables
216 
217## Monitoring & Maintenance
218 
219### Performance Monitoring
220- Monitor slow queries and optimization opportunities
221- Track connection pool usage and limits
222- Monitor disk space and growth trends
223- Set up alerting for critical metrics
224 
225### Regular Maintenance
226- Analyze and vacuum tables regularly
227- Update table statistics for query optimization
228- Monitor and manage database locks
229- Review and optimize indexes based on usage patterns