Gigih_Palatangara/STRAPS_LOCALHOST
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
main
STRAPS_LOCALHOST/DOKUMENTASI_PRISMA_FOLDER.md

17 KiB
Raw Permalink Blame History

Dokumentasi Folder prisma/

Gambaran Umum

Folder prisma/ berisi semua konfigurasi dan migration files untuk database management menggunakan Prisma ORM. Folder ini adalah "blueprint" dari database schema aplikasi.

📁 Struktur Direktori

prisma/
├── migrations/                                    # Database Migration History
│   ├── 20251228085634_add_users_model/
│   │   └── migration.sql                          # Initial database schema
│   ├── 20251228091450_add_menu_client_assignment/
│   │   └── migration.sql                          # Menu assignment feature
│   └── migration_lock.toml                        # Migration lock file
├── schema.prisma                                  # Database Schema Definition
└── seed.ts                                        # Database Seeder Script

📄 File Utama

schema.prisma ⭐

Fungsi: Schema definition file - Blueprint utama database

Location: /prisma/schema.prisma

Size: ~2.6 KB

Generator Configuration:

generator client {
  provider = "prisma-client"
  output   = "../app/generated/client"
}
  • Generate Prisma Client TypeScript
  • Output ke app/generated/client/ untuk import di aplikasi

Datasource Configuration:

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}
  • Database: PostgreSQL
  • Connection string dari environment variable DATABASE_URL

📊 Database Models

1. Model users 👥

Fungsi: Menyimpan data pengguna (Coach & Client)

Fields:

Field Type Description Constraint
id String User ID unik Primary Key, default: cuid()
name String Nama lengkap VARCHAR, NOT NULL
role String Role pengguna VARCHAR, "COACH" atau "CLIENT"
coach_id String? ID coach (untuk client) Foreign Key → users.id
created_at DateTime? Waktu registrasi Default: now()

Relations:

  • coach → Self-relation ke users (Many-to-One)
    • Client memiliki 1 coach
  • clients → Self-relation ke users (One-to-Many)
    • Coach memiliki banyak clients
  • created_menus → training_menus[] (One-to-Many)
    • Coach dapat membuat banyak menu
  • assigned_menus → training_menus[] (One-to-Many)
    • Client dapat memiliki banyak assigned menus
  • recaps → user_recaps[] (One-to-Many)
    • User dapat memiliki banyak workout recaps
  • activity_logs → activity_logs[] (One-to-Many)
    • User dapat memiliki banyak activity logs

Indexes:

  • ix_users_id on id (Primary)
  • ix_users_coach_id on coach_id (Query optimization)

ID Format:

  • Coach: C00001, C00002, etc.
  • Client: U00001, U00002, etc.

Business Logic:

// Coach can have multiple clients
Coach (C00001)
  └─> Clients: [U00001, U00002]

// Client belongs to one coach
Client (U00001)
  └─> Coach: C00001

2. Model activity_logs 📝

Fungsi: Log aktivitas real-time (Standing/Sitting/Fall detection)

Fields:

Field Type Description
id Int Auto-increment ID (Primary Key)
timestamp DateTime? Waktu log dibuat
status String? "Standing", "Sitting", "Fall Detected"
confidence String? Confidence score dari XGBoost
details Json? Additional metadata
user_id String? Foreign Key → users.id

Relations:

  • user → users (Many-to-One)

Indexes:

  • ix_activity_logs_id on id
  • ix_activity_logs_user_id on user_id

Use Case:

  • Real-time monitoring di /client/monitor
  • Historical activity tracking
  • Fall detection alerts

Example Data:

{
	"id": 1,
	"timestamp": "2025-12-28T10:30:00Z",
	"status": "Standing",
	"confidence": "0.95",
	"details": {
		"exercise": "bicep_curl",
		"reps": 5
	},
	"user_id": "U00001"
}

3. Model training_menus 📋

Fungsi: Menyimpan workout programs/menus yang dibuat coach

Fields:

Field Type Description
id Int Auto-increment ID (Primary Key)
name String? Nama menu (e.g., "Upper Body Day 1")
exercises Json? Array of exercise objects
created_at DateTime? Waktu pembuatan
author_id String? Foreign Key → users.id (Coach yang buat)
client_id String? Foreign Key → users.id (Client assigned)

Relations:

  • author → users (Many-to-One, relation: "CreatedMenus")
    • Menu dibuat oleh 1 coach
  • assigned_client → users (Many-to-One, relation: "AssignedMenus")
    • Menu ditugaskan kepada 1 client
  • user_recaps → user_recaps[] (One-to-Many)
    • Menu bisa memiliki banyak recap results

Indexes:

  • ix_training_menus_id on id
  • ix_training_menus_name on name
  • ix_training_menus_author_id on author_id

JSON Structure untuk exercises field:

[
	{
		"name": "Bicep Curl",
		"set_index": 1,
		"reps": 10,
		"weight": 15,
		"rest": 60
	},
	{
		"name": "Hammer Curl",
		"set_index": 1,
		"reps": 12,
		"weight": 12,
		"rest": 60
	}
]

Business Logic:

// Coach creates menu
Coach (C00001)
  └─> Creates Menu (id: 1, "Upper Body")
      └─> Assigns to Client (U00001)

// Client sees assigned menu
Client (U00001)
  └─> Assigned Menu: "Upper Body" (id: 1)

4. Model user_recaps 📈

Fungsi: Menyimpan hasil latihan (training recap) setelah workout selesai

Fields:

Field Type Description
id Int Auto-increment ID (Primary Key)
menu_id Int? Foreign Key → training_menus.id
user_id String? Foreign Key → users.id
summary Json? Workout summary data
completed_at DateTime? Waktu workout selesai

Relations:

  • training_menus → training_menus (Many-to-One)
    • Recap terkait dengan 1 menu
  • user → users (Many-to-One)
    • Recap milik 1 user

Indexes:

  • ix_user_recaps_id on id
  • ix_user_recaps_user_id on user_id

JSON Structure untuk summary field:

{
	"completed": true,
	"exercises": [
		{
			"name": "Bicep Curl",
			"set_index": 1,
			"reps": 10,
			"weight": 15,
			"rest": 60
		}
	],
	"timestamp": "2025-12-28T11:00:00Z",
	"results": [
		{
			"name": "Bicep Curl",
			"set": 1,
			"reps": 10,
			"weight": 15,
			"score": 12.5,
			"repDetails": [
				{
					"rep": 1,
					"score": 8.2,
					"feedback": "Perfect"
				},
				{
					"rep": 2,
					"score": 14.5,
					"feedback": "Elbow moving forward"
				}
			]
		}
	]
}

Key Features di Summary:

  • ✅ Overall workout stats
  • ✅ Per-set average form scores
  • ✅ Per-rep breakdown dengan:
    • Rep number
    • MAE score
    • Specific feedback text

🔄 Database Relationships Diagram

erDiagram
    users ||--o{ users : "coach-client"
    users ||--o{ training_menus : "creates (author)"
    users ||--o{ training_menus : "assigned to (client)"
    users ||--o{ user_recaps : "performs workout"
    users ||--o{ activity_logs : "generates logs"
    training_menus ||--o{ user_recaps : "tracked in recap"

    users {
        string id PK
        string name
        string role
        string coach_id FK
    }

    training_menus {
        int id PK
        string name
        json exercises
        string author_id FK
        string client_id FK
    }

    user_recaps {
        int id PK
        int menu_id FK
        string user_id FK
        json summary
    }

    activity_logs {
        int id PK
        string status
        string user_id FK
    }

📁 Folder migrations/

Gambaran Umum

Folder yang berisi history semua perubahan database schema.

Migration Files:

1. 20251228085634_add_users_model/migration.sql

Tanggal: 28 Desember 2025, 08:56:34

Fungsi: Initial database schema creation

Changes:

  • ✅ Create table users dengan self-referencing foreign key
  • ✅ Create table activity_logs dengan JSONB details
  • ✅ Create table training_menus dengan JSONB exercises
  • ✅ Create table user_recaps dengan JSONB summary
  • ✅ Create indexes untuk optimization:
    • User ID index
    • Coach ID index
    • Menu name index
    • Author ID index
    • User recap user_id index
  • ✅ Add foreign key constraints dengan proper ON DELETE/UPDATE actions

Key SQL:

CREATE TABLE "users" (
    "id" TEXT NOT NULL,
    "name" VARCHAR NOT NULL,
    "role" VARCHAR NOT NULL,
    "coach_id" TEXT,
    "created_at" TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT "users_pkey" PRIMARY KEY ("id")
);

-- Self-referencing foreign key
ALTER TABLE "users"
  ADD CONSTRAINT "users_coach_id_fkey"
  FOREIGN KEY ("coach_id") REFERENCES "users"("id")
  ON DELETE SET NULL ON UPDATE CASCADE;

Size: ~2.7 KB

2. 20251228091450_add_menu_client_assignment/migration.sql

Tanggal: 28 Desember 2025, 09:14:50

Fungsi: (Merged into previous migration)

Status: ⚠️ Skipped/No-op migration

Reason:

  • Original purpose: Add client_id to training_menus
  • Already included in first migration to fix type mismatches
  • File kept untuk preserve migration history order

Content:

-- This migration is skipped because its changes (adding client_id)
-- were manually merged into the previous migration to fix type mismatches
-- This file is kept to preserve migration history order.

migration_lock.toml

Fungsi: Lock file untuk ensure consistent database provider

Content:

# Please do not edit this file manually
# It should be added in your version-control system (e.g., Git)
provider = "postgresql"

Purpose:

  • Mencegah accidental switch ke database provider lain
  • Ensure semua developer/environment menggunakan PostgreSQL
  • Auto-generated oleh Prisma CLI

📄 File seed.ts

Fungsi: Database seeder untuk populate initial test data

Location: /prisma/seed.ts

Size: ~1.6 KB

Seed Data:

2 Coaches:

  1. C00001 - "Coach One"
  2. C00002 - "Coach Two"

3 Clients:

  1. U00001 - "Client One" (assigned to Coach One)
  2. U00002 - "Client Two" (assigned to Coach One)
  3. U00003 - "Client Three" (assigned to Coach Two)

Script Logic:

import { PrismaClient } from "../app/generated/client/client";

const prisma = new PrismaClient();

async function main() {
	// Upsert coaches
	const coach1 = await prisma.users.upsert({
		where: { id: "C00001" },
		update: {},
		create: {
			id: "C00001",
			name: "Coach One",
			role: "COACH",
		},
	});

	// Upsert clients with coach_id
	const client1 = await prisma.users.upsert({
		where: { id: "U00001" },
		update: {},
		create: {
			id: "U00001",
			name: "Client One",
			role: "CLIENT",
			coach_id: coach1.id,
		},
	});
}

Run Seeder:

npx prisma db seed

Use Case:

  • Development/testing data
  • Demo accounts
  • Quick reset database dengan data awal

🛠️ Prisma Commands

Essential Commands:

1. Generate Prisma Client

npx prisma generate
  • Regenerate TypeScript client dari schema
  • Run setelah setiap perubahan schema.prisma
  • Output ke app/generated/client/

2. Create Migration

npx prisma migrate dev --name migration_name
  • Create migration file baru
  • Apply migration ke database
  • Update Prisma Client

3. Apply Migration (Production)

npx prisma migrate deploy
  • Apply pending migrations
  • Untuk production environment
  • Tidak auto-generate client

4. Reset Database

npx prisma migrate reset
  • Drop database
  • Re-run all migrations
  • Run seed script
  • ⚠️ DANGER: Deletes all data!

5. Prisma Studio (GUI)

npx prisma studio
  • Open web GUI untuk browse/edit data
  • URL: http://localhost:5555
  • Visual database management

6. Format Schema

npx prisma format
  • Auto-format schema.prisma
  • Fix indentation dan spacing

7. Validate Schema

npx prisma validate
  • Check schema for errors
  • Verify relations dan syntax

🔑 Key Concepts

Migration Strategy:

  1. Development:

    • Use prisma migrate dev
    • Creates migration + applies + generates client
    • Safe untuk experiments

  2. Production:

    • Use prisma migrate deploy
    • Never use migrate dev in prod
    • Always test migrations di staging first

Schema Best Practices:

  1. Naming Conventions:

    • Tables: snake_case (e.g., training_menus)
    • Fields: snake_case (e.g., coach_id)
    • Relations: camelCase di Prisma model (e.g., assignedClient)

  2. Indexes:

    • Add index untuk foreign keys
    • Add index untuk frequently queried fields
    • Consider composite indexes untuk complex queries

  3. JSONB Usage:

    • ✅ Good for: Flexible nested data (exercises, recap summary)
    • ❌ Avoid for: Searchable/filterable data
    • Use Json type di Prisma, becomes JSONB di PostgreSQL

  4. ID Strategy:

    • Users: Custom strings (C00001, U00001)
    • Other tables: Auto-increment integers
    • Consider UUID untuk distributed systems

📊 Database Size Estimates

Assuming Active Usage:

Table Rows/Month Storage
users ~10 < 1 KB
training_menus ~50 ~5 KB
user_recaps ~500 ~500 KB (with per-rep data)
activity_logs ~10,000 ~1 MB

Total: ~2 MB/month dengan moderate usage

Optimization Tips:

  • Archive old activity_logs after 30 days
  • Compress old recaps
  • Add pagination untuk large queries

🔐 Security Considerations

Implemented:

  • ✅ Foreign key constraints prevent orphaned data
  • ✅ Indexes prevent slow queries (avoid DOS)
  • ✅ ON DELETE SET NULL untuk soft deletes

TODO / Recommendations:

  • Add email field dengan @unique constraint
  • Add password_hash field (currently not in schema!)
  • Add role as enum type instead of string
  • Add soft_delete timestamp instead of hard delete
  • Add row-level security (RLS) di PostgreSQL

🚀 Future Enhancements

Planned Schema Changes:

  1. Add Authentication Fields:
model users {
  email         String   @unique
  password_hash String
  email_verified Boolean @default(false)
}
  1. Add Workout Sessions (untuk track progress over time):
model workout_sessions {
  id          Int      @id @default(autoincrement())
  user_id     String
  menu_id     Int
  started_at  DateTime
  ended_at    DateTime?
  status      String   // "IN_PROGRESS" | "COMPLETED" | "ABANDONED"
}
  1. Add Exercise Library (normalize exercises):
model exercises {
  id          Int      @id @default(autoincrement())
  name        String   @unique
  category    String   // "UPPER" | "LOWER" | "CORE"
  instructions Json?
}
  1. Add Personal Records:
model personal_records {
  id          Int      @id @default(autoincrement())
  user_id     String
  exercise    String
  weight_kg   Float
  reps        Int
  achieved_at DateTime
}

💡 Troubleshooting

Common Issues:

1. Migration Failed

Error: Migration failed to apply

Solution:

# Reset database
npx prisma migrate reset

# Or fix manually
npx prisma db push --skip-generate
npx prisma generate

2. Client Out of Sync

Error: Prisma Client is out of sync with schema

Solution:

npx prisma generate

3. Connection Error

Error: Can't reach database server

Check:

  1. PostgreSQL running? (sudo systemctl status postgresql)
  2. DATABASE_URL correct di .env?
  3. Network/firewall issues?

4. Seed Script Fails

Error: Unique constraint violation

Solution: Data already exists

# Clear database first
npx prisma migrate reset

📚 Resources

Official Docs:

Tutorials:

✅ Checklist Maintenance

  • Schema documented
  • Migrations tracked in Git
  • Seed data available
  • Backup strategy defined
  • Migration rollback tested
  • Production deployment guide
  • Performance benchmarks