# CNLP MIS — Database Schema V1

## Status
Approved working technical foundation for the browser-based trial.

## Core architectural rule
Machines, materials, operations, rates, profiles, pricing rules, and production parameters are data-driven. Adding a normal new physical machine should not require redesigning the quotation database.

## Main relationship path

Customer → Quote → Quote Item → Quantity Option → Components + Route Steps → Layout Results → Cost Lines → Pricing → Job Order → Actual Production

## Extensibility

- `machines` stores each physical asset.
- `machine_types` classifies the asset.
- `operations` describes what production work is being performed.
- `machine_operation_profiles` connects machines to operations.
- `machine_operation_profile_versions` preserves speed, efficiency, setup, cleanup, handling, and machine-specific parameters over time.
- `machine_capabilities` allows max width, board thickness, supported modes, and future machine limits to be added without schema changes.
- `work_centers` covers manual processes such as Sintra cutting, assembly, packing, and installation.
- `material_parameter_definitions` + `material_parameter_values` handle category-specific material parameters.
- `quote_layout_results.result_json` preserves exact roll/sheet nesting output for reproducibility.
- `quote_cost_lines` is the standardized output consumed by the pricing engine.
- `quote_overrides` and `audit_log` preserve changes, reasons, users, and approvals.

## Financial precision

Use PostgreSQL `NUMERIC`, never floating point, for money, rates, percentages, dimensions used in costing, and quantity calculations.

## Quote numbering

`quote_no` is a simple numeric sequence, matching the agreed design. Database primary keys remain separate from customer-facing quote numbers.

## Four quantity options

`quote_quantity_options` enforces option numbers 1–4 per quotation item. Each option has independent layouts, cost lines, and pricing. Never scale one option's price mechanically from another option.

## Snapshot rule

Every calculated quotation must snapshot:

- material cost and supplier basis;
- machine cost rate;
- profile speed and efficiency;
- setup/change/cleanup allowances;
- coverage/waste values;
- nesting parameters/results;
- markup/discount/tax values.

Admin edits affect future calculations only unless an authorized user deliberately recalculates an old quotation.

## Current seeded production profiles

- JV100 Sticker/PVC 6 Pass: 20.00 m²/hr × 85% = 17.00 m²/hr.
- JV100 Tarpaulin 4 Pass: 27.40 m²/hr × 85% = 23.29 m²/hr.
- CG-130AR Standard Contour: 0.30 m/sec × 40% = 0.12 m/sec.
- MEFU Roll Lamination: 1.50 m/min × 70% = 1.05 m/min.
- MEFU Rigid Sintra Mounting: 0.45 m/min × 70% = 0.315 m/min; 45 sec/piece handling.

## Current seeded material defaults

- NJ100R Removable Gloss 54 in × 164 ft: ₱3,930/roll.
- Glossy 90 mic Standard 54 in × 164 ft: ₱2,480/roll.
- Sintra 3 mm 48 × 96 in: ₱645/sheet.

## Not hard-coded

The seed file only supplies current trial data. All of these records remain editable in Admin.

## Next implementation milestone

1. Create PostgreSQL migration runner.
2. Apply `cnlp_mis_schema_v1.sql`.
3. Apply `cnlp_mis_seed_trial_v1.sql`.
4. Build API models/services around Customers, Materials, Machines, Operations, Quotes, Quantity Options, Cost Lines, and Pricing.
5. Implement the calculation modules separately: geometry, material, ink, machine time, labor, cost aggregation, pricing.
6. Build Admin CRUD screens so machines/materials/profiles can be added or edited without code changes.