backend/
├── database/
│   ├── db.js                  # PostgreSQL connection pool & initialization
│   ├── schema.sql             # PostgreSQL schema definition
│   ├── seed-postgres.js       # Data seeding script
│   ├── run-migrations.js      # Migration runner
│   └── migrations/            # Database migration files
├── models/
│   ├── Cycle.js               # Cycle CRUD operations
│   └── Mortality.js           # Mortality CRUD operations
├── routes/
│   ├── cycles.js              # Cycle API routes
│   └── mortality.js           # Mortality API routes
├── server.js                  # Express app entry point
├── startup.sh                 # Startup script for Docker
├── package.json
├── .env                       # Environment configuration
└── README.md

🗄️ Database

Schema

Table: cycles

CREATE TABLE cycles (
    id VARCHAR(50) PRIMARY KEY,
    total_days INTEGER NOT NULL,
    current_day INTEGER NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE,
    chick_in_weight INTEGER,
    doc_in_count INTEGER,
    status VARCHAR(20) NOT NULL CHECK(status IN ('Completed', 'Active', 'Upcoming')),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Table: mortality_records

CREATE TABLE mortality_records (
    id SERIAL PRIMARY KEY,
    cycle_id VARCHAR(50) NOT NULL,
    day INTEGER NOT NULL,
    mortality_count INTEGER NOT NULL DEFAULT 0,
    is_edited BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (cycle_id) REFERENCES cycles(id) ON DELETE CASCADE,
    UNIQUE(cycle_id, day)
);

Indexes

CREATE INDEX idx_mortality_cycle_id ON mortality_records(cycle_id);
CREATE INDEX idx_mortality_day ON mortality_records(day);
CREATE INDEX idx_cycles_status ON cycles(status);
CREATE INDEX idx_cycles_start_date ON cycles(start_date);

See complete schema in database/schema.sql

Initial Data

Database akan terisi otomatis dengan 4 siklus:

Cycle ID	Status	Start Date	End Date	DOC Count
CYCLE-JBW-2025-05-20	Completed	2025-05-20	2025-06-30	20,000
CYCLE-JBW-2025-07-11	Completed	2025-07-11	2025-08-21	20,000
CYCLE-JBW-2025-10-22	Completed	2025-10-22	2025-12-02	20,000
CYCLE-JBW-2025-12-10	Active	2025-12-10	2026-01-20	20,000

📡 API Endpoints

Base URL: http://localhost:5001

Health Check

GET /health

Response:

{
  "status": "ok",
  "timestamp": "2025-12-22T03:02:20.793Z"
}

Cycles API

Get All Cycles

GET /api/cycles

Response:

{
  "success": true,
  "data": [
    {
      "id": "CYCLE-JBW-2025-12-10",
      "totalDays": 42,
      "currentDay": 7,
      "startDate": "2025-12-10",
      "endDate": "2026-01-20",
      "chickInWeight": null,
      "docInCount": 20000,
      "status": "Active",
      "createdAt": "2025-12-22 02:59:17",
      "updatedAt": "2025-12-22 02:59:17"
    }
  ]
}

Get Active Cycle

GET /api/cycles/active

Get Cycle by ID

GET /api/cycles/:id

Example: GET /api/cycles/CYCLE-JBW-2025-12-10

Create Cycle

POST /api/cycles
Content-Type: application/json

{
  "id": "CYCLE-JBW-2025-12-10",
  "totalDays": 42,
  "currentDay": 0,
  "startDate": "2025-12-10",
  "endDate": "2026-01-20",
  "chickInWeight": 42,
  "docInCount": 20000,
  "status": "Active"
}

Update Cycle

PUT /api/cycles/:id
Content-Type: application/json

{
  "totalDays": 42,
  "currentDay": 7,
  "startDate": "2025-12-10",
  "endDate": "2026-01-20",
  "chickInWeight": 42,
  "docInCount": 20000,
  "status": "Active"
}

Delete Cycle

DELETE /api/cycles/:id

Mortality API

Get All Mortality Records for Cycle

GET /api/mortality/:cycleId

Example: GET /api/mortality/CYCLE-JBW-2025-12-10

Response:

{
  "success": true,
  "data": [
    {
      "id": 1,
      "cycleId": "CYCLE-JBW-2025-12-10",
      "day": 0,
      "mortalityCount": 25,
      "isEdited": true,
      "createdAt": "2025-12-22 03:00:00",
      "updatedAt": "2025-12-22 03:00:00"
    }
  ]
}

Get Mortality Record for Specific Day

GET /api/mortality/:cycleId/:day

Example: GET /api/mortality/CYCLE-JBW-2025-12-10/5

Update/Create Mortality Record

PUT /api/mortality/:cycleId/:day
Content-Type: application/json

{
  "mortalityCount": 30
}

Creates new record if doesn't exist
Updates existing record if exists
Sets is_edited flag to true

Delete Mortality Record

DELETE /api/mortality/:cycleId/:day

Removes the mortality record, effectively resetting it to default value.

🔐 Environment Variables

File: .env

PORT=5001
NODE_ENV=development

# PostgreSQL Database Configuration
DB_HOST=localhost
DB_PORT=5432
DB_USER=dashboard_user
DB_PASSWORD=your_secure_password_here
DB_NAME=dashboard_db
DB_SSL=false

# Connection pool settings (optional)
DB_POOL_MIN=2
DB_POOL_MAX=10

Variables

Variable	Description	Default
PORT	Server port	5001
NODE_ENV	Environment (development/production)	development
DB_HOST	PostgreSQL host	localhost
DB_PORT	PostgreSQL port	5432
DB_USER	PostgreSQL user	dashboard_user
DB_PASSWORD	PostgreSQL password	(required)
DB_NAME	Database name	dashboard_db
DB_SSL	Enable SSL connection	false
DB_POOL_MIN	Minimum pool connections	2
DB_POOL_MAX	Maximum pool connections	10

🔧 Development

Database Helper Functions

File: database/db.js

// Get connection pool and helpers
const { pool, query, transaction, toISODate, parseISODate } = require('./database/db');

// Execute query
const result = await query('SELECT * FROM cycles WHERE status = $1', ['Active']);

// Run transaction
await transaction(async (client) => {
  await client.query('UPDATE cycles SET current_day = $1 WHERE id = $2', [
    7,
    'CYCLE-JBW-2025-12-10',
  ]);
  await client.query('INSERT INTO mortality_records ...');
});

// Date helpers
toISODate(new Date()); // Converts Date to YYYY-MM-DD
parseISODate('2025-12-10'); // Converts ISO string to Date

Models

Cycle Model

File: models/Cycle.js

const Cycle = require('./models/Cycle');

// Get all cycles
Cycle.getAll();

// Get cycle by ID
Cycle.getById('CYCLE-JBW-2025-12-10');

// Get active cycle
Cycle.getActive();

// Create new cycle
Cycle.create({
  id: 'CYCLE-JBW-2025-12-10',
  totalDays: 42,
  currentDay: 0,
  startDate: '2025-12-10',
  endDate: '2026-01-20',
  docInCount: 20000,
  status: 'Active',
});

// Update cycle
Cycle.update('CYCLE-JBW-2025-12-10', {
  currentDay: 7,
  // ... other fields
});

// Delete cycle
Cycle.delete('CYCLE-JBW-2025-12-10');

Mortality Model

File: models/Mortality.js

const Mortality = require('./models/Mortality');

// Get all mortality records for a cycle
Mortality.getByCycle('CYCLE-JBW-2025-12-10');

// Get mortality record for specific day
Mortality.getByDay('CYCLE-JBW-2025-12-10', 5);

// Insert or update mortality record
Mortality.upsert('CYCLE-JBW-2025-12-10', 5, 30, true);

// Delete mortality record
Mortality.delete('CYCLE-JBW-2025-12-10', 5);

Request Logging

All requests are logged with timestamp, method, and path:

2025-12-22T03:02:20.793Z - GET /health
2025-12-22T03:02:22.884Z - GET /api/cycles
2025-12-22T03:02:24.985Z - GET /api/cycles/active

SQL queries are also logged in development mode.

CORS Configuration

File: server.js

const corsOptions = {
  origin: ['http://localhost:3001', 'http://localhost:5173'],
  methods: ['GET', 'POST', 'PUT', 'DELETE'],
  credentials: true,
};

Add more origins as needed for different environments.

🧪 Testing API

Using curl

# Health check
curl http://localhost:5001/health

# Get all cycles
curl http://localhost:5001/api/cycles

# Get active cycle
curl http://localhost:5001/api/cycles/active

# Get mortality records
curl http://localhost:5001/api/mortality/CYCLE-JBW-2025-12-10

# Create mortality record
curl -X PUT http://localhost:5001/api/mortality/CYCLE-JBW-2025-12-10/5 \
  -H "Content-Type: application/json" \
  -d '{"mortalityCount": 30}'

# Delete mortality record
curl -X DELETE http://localhost:5001/api/mortality/CYCLE-JBW-2025-12-10/5

Using Postman or Thunder Client

Import the following collection:

{
  "info": {
    "name": "Dashboard Peternakan API",
    "schema": "https://schema.getpostman.com/json/collection/v2.1.0/collection.json"
  },
  "item": [
    {
      "name": "Health Check",
      "request": {
        "method": "GET",
        "url": "http://localhost:5001/health"
      }
    },
    {
      "name": "Get All Cycles",
      "request": {
        "method": "GET",
        "url": "http://localhost:5001/api/cycles"
      }
    }
  ]
}

📝 Database Maintenance

Backup Database

From Docker (Production):

# Run backup script (saves to ./backups/)
./scripts/backup-postgres.sh

Fetch from Production Server:

REMOTE_USER=your-user \
REMOTE_HOST=your-host \
REMOTE_PROJECT_DIR=/path/to/project \
./scripts/fetch-prod-db.sh

Restore Database

Restore to Local:

# This will reset local database and restore from backup
./scripts/restore-postgres-local.sh --reset backups/dashboard_db-YYYYMMDD-HHMMSS.sql.gz

Reset Database

Local Development:

npm run seed  # Runs seed-postgres.js

Docker:

docker-compose down -v  # Remove volumes
docker-compose up -d    # Recreate with fresh data

View Database

Using psql CLI:

# Connect to local database
psql -h localhost -p 5432 -U dashboard_user -d dashboard_db

# SQL commands
\dt                              # Show all tables
\d cycles                        # Show table schema
SELECT * FROM cycles;            # Query data
SELECT * FROM mortality_records;
\q                               # Exit

Using Docker:

docker-compose exec database psql -U dashboard_user -d dashboard_db

Using DBeaver (GUI):

Download from https://dbeaver.io/
Connect to: localhost:5432 (or 15432 for Docker)
Database: dashboard_db
User/Password: from .env file

🚨 Error Handling

All endpoints return consistent error format:

{
  "success": false,
  "error": "Error message here"
}

HTTP Status Codes:

200 - Success
201 - Created
400 - Bad Request (invalid input)
404 - Not Found
500 - Internal Server Error

🔒 Security Notes

Database credentials should be kept in .env (gitignored)
Foreign keys enforce referential integrity
SQL injection is prevented by using parameterized queries ($1, $2, etc.)
CORS is configured for specific origins only
Input validation on all endpoints
Connection pooling manages database connections efficiently
SSL can be enabled for production (set DB_SSL=true)

📚 Additional Resources

🤝 Contributing

When contributing to the backend:

Follow existing code structure
Add error handling for new endpoints
Update this README if adding new features
Test all endpoints before committing
Keep models thin - business logic in models, HTTP in routes

📞 Support

For backend-specific issues:

Check server logs in console
Verify PostgreSQL is running: pg_isready or docker-compose ps
Test database connection: npm run seed
Check port availability: lsof -i :5001
Verify .env configuration (DB_HOST, DB_PORT, credentials)

Backend developed by PT Cipta Pola Solusi Prima - 2025