# Superport Database Patterns v8.0

## What This Really Is
Database queries and schema patterns. PostgreSQL specific.

## Main Tables
```sql
-- Core entities
equipment (
  id SERIAL PRIMARY KEY,
  serial_number VARCHAR UNIQUE,
  models_id INT REFERENCES models(id),
  companies_id INT REFERENCES companies(id),
  status VARCHAR
)

equipment_history (
  id SERIAL PRIMARY KEY,
  equipments_id INT REFERENCES equipment(id),
  transaction_type CHAR(1), -- 'I'(입고), 'O'(출고)
  warehouses_id INT,
  transacted_at TIMESTAMP
)

maintenance (
  id SERIAL PRIMARY KEY,
  equipment_history_id INT,
  maintenance_type VARCHAR, -- WARRANTY, CONTRACT, INSPECTION
  expiry_date DATE
)
```

## Common Queries
```sql
-- Equipment with latest location
SELECT e.*, w.name as warehouse_name
FROM equipment e
LEFT JOIN LATERAL (
  SELECT warehouses_id 
  FROM equipment_history
  WHERE equipments_id = e.id
  ORDER BY transacted_at DESC
  LIMIT 1
) eh ON true
LEFT JOIN warehouses w ON w.id = eh.warehouses_id;

-- Maintenance due in 30 days
SELECT m.*, e.serial_number
FROM maintenance m
JOIN equipment_history eh ON m.equipment_history_id = eh.id
JOIN equipment e ON eh.equipments_id = e.id
WHERE m.expiry_date <= CURRENT_DATE + INTERVAL '30 days'
  AND m.expiry_date >= CURRENT_DATE;
```

## Indexes
```sql
-- Add these for performance
CREATE INDEX idx_equipment_history_equipments ON equipment_history(equipments_id);
CREATE INDEX idx_equipment_history_transacted ON equipment_history(transacted_at DESC);
CREATE INDEX idx_maintenance_expiry ON maintenance(expiry_date);
```

## Transaction Pattern
```sql
BEGIN;
-- Insert equipment
INSERT INTO equipment (serial_number, models_id) VALUES ($1, $2) RETURNING id;
-- Insert history
INSERT INTO equipment_history (equipments_id, transaction_type, warehouses_id) 
VALUES ($id, 'I', $3);
COMMIT;
```

---
*Backend handles all DB logic. Frontend never touches SQL.*