Cline rules

1# Database
2 
3## Connection Details
4 
5### Docker Connection (from host)
6```bash
7docker exec -it sapi3d-db psql -U sapi3d -d sapi3d
8```
9 
10### Application Connection
11```
12postgresql+asyncpg://sapi3d:sapi3d_password@db:5432/sapi3d
13```
14 
15### Credentials
16- **User**: `sapi3d`
17- **Password**: `sapi3d_password`
18- **Database**: `sapi3d`
19- **Port**: `5432`
20 
21## Database Tables (11 Total)
22 
23### Core Tables
241. **users** - User accounts and profiles
252. **roles** - User roles (admin, user, guest)
263. **user_roles** - Many-to-many relationship
27 
28### Session & Activity
294. **sessions** - User session tracking
305. **devices** - Device information per session
316. **locations** - User location tracking in 3D space
32 
33### Gamification
347. **achievements** - Achievement definitions
358. **user_achievements** - User achievement progress
36 
37### Performance
389. **metrics** - Performance metrics per session
3910. **metric_snapshots** - Time-series metric data
40 
41### System
4211. **system_logs** - Application logging
43 
44## Key Relationships
45 
46```
47users ←→ user_roles ←→ roles
48users → sessions → devices
49users → sessions → locations
50users → sessions → metrics → metric_snapshots
51users ←→ user_achievements ←→ achievements
52```
53 
54## Quick Commands
55 
56### Access Database
57```bash
58docker exec -it sapi3d-db psql -U sapi3d -d sapi3d
59```
60 
61### List Tables
62```sql
63\dt
64```
65 
66### View Table Structure
67```sql
68\d users
69\d sessions
70```
71 
72### Check Connections
73```sql
74SELECT * FROM pg_stat_activity WHERE datname = 'sapi3d';
75```
76 
77## Detailed Documentation
78 
79→ Complete schema: `DATABASE_IMPLEMENTATION.md`
80→ ERD diagram: `docs/architecture.md#database-schema`
81→ Model definitions: `backend/app/models/`
82