# Module Design Document: Print Estimation Engine

**Module:** Print Estimation Engine (`print_estimation` PWA)  
**Status:** Forward-looking design — not yet built · **4 blocking RPC issues fixed 2026-07-03 (3 original + 1 advisor-caught param/column collision — see review note)** · 1 non-blocking item (plate-cost FK) still open, tracked in `dbt_pending.md`  
**Author:** Fable 5 (drafting agent), reviewed by Sonnet 5 (orchestrating session)  
**Date:** 2026-07-03  
**Revision:** 1.2 + review annotations + RPC fixes + collision fix

---

## ⚠️ Review note (added post-draft, before this MDD is used) — REQUIRED READING

This draft was explicitly requested to be "leak-proof": no silent assumptions, no confident
prose masking an unimplemented gap. A close read against that standard found the *prose*
sections (§7, §8) are honest and well-reasoned, but **three of the RPC pseudocode blocks in
§5 did not implement what the prose right next to them claimed they implemented.** This was
flagged here rather than silently accepted. **Status as of 2026-07-03: all 3 blocking issues
are fixed directly in §5's pseudocode below** (details in each subsection); the 4th item (worth
fixing, not blocking) remains open — see `dbt_pending.md`.

1. ✅ **FIXED — estimate numbering.** §8.1 describes a locked-counter, gap-free numbering
   scheme, but `fn_estimate_create_and_line_add` (§5.2) took a caller-supplied
   `estimate_no_seed` and used it directly — the estimate number was entirely client-controlled,
   allowing duplicates. **Fix:** the parameter is removed; the RPC now does the actual
   `UPDATE ... RETURNING`-based locked increment against `print_estimation.config` (§4.9) that
   was previously only a comment.

2. ✅ **FIXED — margin role-gate.** §7.5 states the RPC "returns margin/variance fields only if
   `caller_role IN ('owner', 'manager')`" — but `fn_estimate_rollup_costs` (§5.1) had no
   `caller_role` parameter at all; margin was returned unconditionally to any caller. **Fix:**
   the parameter now exists and the RPC actually branches on it. This is still NOT
   cryptographically sound — `caller_role` remains a forgeable client-supplied string until
   ADR-105 (signed JWT) ships, exactly as §7.5/Red-Team-1 always said the interim mitigation
   would be. The fix closes "the gate doesn't exist," not "the gate is weak" — that part of the
   risk is unchanged and still open by design.

3. ✅ **FIXED — ink cost hardcoded to 0.** `fn_estimate_line_add` (§5.3) had
   `v_ink_cost_total := 0.0; -- Placeholder`, silently omitting ink cost from every estimate's
   `material_cost`. **Fix:** the RPC now loops a real `ink_colors` JSONB input, looks up each
   color in `ink_master`, applies the §4.6 flexo formula, and writes the computed breakdown into
   `estimate_line.inks` (a column the schema declared but the original pseudocode never wrote
   to). Two related bugs were caught and fixed while implementing this, both necessary for the
   fix to be actually correct rather than just present: the formula's `transfer_percent` wasn't
   being divided by 100 like every other percent field in this schema (§4.6), and ink volume
   wasn't scaling with `quantity` the way substrate/wastage costs already do, which would have
   understated ink cost on any multi-unit line. This also required adding
   `anilox_volume_bcm`/`transfer_percent` as real config columns (§4.9) — previously "a config
   table (not shown here)."

4. 🔲 **Still open (worth fixing, not blocking)** — the plate-cost rollup query in §5.1
   `LEFT JOIN`s every `estimate_line` against every `plate_cost_config` row matching
   `press_type IS NULL OR press_type = 'default'`, then filters `pcfg.active = true` in the
   `WHERE` clause, which silently turns the `LEFT JOIN` into an `INNER JOIN`. There is no FK
   tying a line to a specific plate config, and no constraint preventing more than one "global"
   active config row from existing — if that happens, every line's plate cost fans out against
   every matching row, silently inflating the total. Not fixed in this pass; tracked separately
   in `dbt_pending.md`.

5. ✅ **FIXED — a 4th blocking bug found while fixing #1–#3 (advisor-caught, not in the
   original 4-point review).** §5.1 and §5.3 both took a parameter literally named
   `estimate_id` — but `estimate_line.estimate_id` is a real column, and §5.1 already declares
   `#variable_conflict use_column`. That means every bare `WHERE estimate_id = estimate_id`
   (§5.1's material-subtotal and plate-cost queries) and `WHERE estimate_id = estimate_id`
   inside §5.3's `MAX(line_number)` subquery resolved **both sides to the same column**,
   collapsing to `col = col` — always true for any non-null row. `fn_estimate_rollup_costs`
   would have summed `material_cost` across **every estimate in the table**, not the one passed
   in, and §5.3's line-numbering would have counted across all estimates too. This would have
   silently undermined the ink-cost fix (#3) and the whole rollup, regardless of how correct the
   per-line math was — exactly the "silently wrong invoice" class Red-Team-2 (§11) discusses in
   the abstract. **Fix:** renamed the parameter to `p_estimate_id` in both RPCs' signatures and
   bodies (the standard fix for this collision class), added the same `use_column` pragma to
   §5.3 defensively (its OUT parameter `material_cost` shares a name with a real column too,
   though the existing `v_`-prefixed-local pattern already avoided live ambiguity there).

**Bottom line:** the *design* — data model, precision/rounding rules, access-model framing,
immutability lifecycle, eFlexo-unknowns handling — was always solid. The RPC pseudocode is now
consistent with its own prose for the 3 originally-blocking points, and a 4th blocking issue
found during the fix (parameter/column name collision) is also closed. Item 4 (plate-cost FK)
remains a known, non-blocking gap; `caller_role` forgeability itself (as opposed to its absence)
is intentionally deferred to ADR-105, not something this pass could or should close.

---

---

## 1. Why This Exists

360DLM (360 Degree Logical Marketing) operates an in-house printing business spanning flexographic (flexo), digital, offset, and signage production, plus fabrication and installation services. **The core problem:** the company has no repeatable, auditable system to compute print job quotations from first principles, nor any mechanism to record actual material consumption against estimates for post-job profitability review.

**Current state:** Quotations are assembled ad-hoc (spreadsheets, email estimates, memory), costing is opaque to the estimator (no substrate/ink/plate pricing visibility), and job margin analysis is impossible — completed jobs have no "actual vs. estimated" variance tracking.

**Why it matters:** Printing is a high-volume, low-margin business in India; a 3% mis-estimation compounds across 100+ jobs/month. The company cannot confidently bid on new work classes (e.g., new substrate types, new press configurations) without guessing on material cost and labor burden. A repeatable estimation engine is the single highest-value missing capability (confirmed by prior market research, §2).

---

## 2. Inspiration vs. Scope

### References Studied (Ideas, Not Code/Secrets)

**eFlexo** (eflexo.in, Indian cloud print ERP for ~1,200 users, pricing from ₹6,970/month):
- Commercial reference showing job-card → delivery-challan → invoice flow.
- **Its estimation algorithm is proprietary and not disclosed** — this is documented as a genuine gap, not assumed below. See §9 (open questions).

**PrintVis** (on MS Dynamics 365 Business Central):
- Time-based costing and material-waste factors; **notably: tracks actual vs. estimated material consumption per job** — the single most valuable, least-copied feature in the surveyed tools. This is the differentiator we prioritize in §4 (data model).

**Ordant** (signage/print workflows):
- Concept: different estimation/production paths per product type (screen-print vs. digital vs. large-format vs. signage vs. vehicle-wrap). Relevant since 360DLM spans several.

**ERPNext/Frappe** (open-source):
- Pattern: immutable document lifecycle (draft → submitted → amended → cancelled). Adopted for §8 (financial integrity).

**Dolibarr** (open-source):
- Pattern: JSONB custom-fields to avoid schema migration every time a new material/parameter type is needed. Considered for material masters but NOT adopted in v1 (keep schema simple; add JSONB in v2 if needed).

### Intentional Out-of-Scope for v1

- **Actual shop-floor data capture** — This MDD proposes estimation only; linking to live press sensors, wastage-weight scales, or ink-viscosity monitors is future work.
- **Scheduling / production sequence optimization** — Job sequencing based on color-family, plate changeover, setup time is a separate PWA concern (see Ordant inspiration); this module does pure estimation.
- **Integration with eFlexo or other commercial tools** — Data bridges, auto-sync, or replacement of legacy tools are decided post-trial (§9).
- **Custom fields JSONB pattern** — v1 uses fixed schema; v2 can add configurable parameter types via JSONB if customers demand flexibility.
- **Multi-currency** — All amounts in INR; multi-currency is out of scope.

---

## 3. Proposed Build Phases

### Phase 1: Core Estimation Engine (Weeks 1–3)
- **Substrate master:** bulk material types (flexo film, vinyl, paper, polyester, etc.), cost per unit/kg, supplier tracking.
- **Ink master:** color/coverage lookup table, cost per mL, consumption calculator (public flexo formula, §2).
- **Plate cost model:** fixed cost | per-color | per-cm² modes.
- **Basic estimate record:** single form, lines for each color/material combination, rollup RPC.
- **DB schema:** `print_estimation` (dedicated schema, separate from `printing` PWA).
- **Screens:** estimate builder (form), material admin UI.
- **Tests:** 50+ spec tests (estimate calc, rounding, edge cases); hub integration; access control.

### Phase 2: Variance Tracking (Weeks 4–5)
- **Actual consumption record:** link to completed job, record actual material used (wastage %, ink mL, plates consumed).
- **Variance RPC:** compare actual vs. estimated, compute margin achieved, surface in reports.
- **Variance dashboard:** per-material trend, per-customer, per-press-type.

### Phase 3: Financial Integration (Week 6)
- **GST invoice linkage:** once estimate is "sent to customer," auto-create invoice row in `finance` schema (RPC call). Invoice sequencing (§8) enforced here.
- **Margin report:** roll-up by customer, by material type, by month — visibility into profitability.

### Phase 4: Refinement (On-Demand)
- **eFlexo trial data capture** (§9) — if eFlexo trial reveals different algorithm, revise.
- **Dolibarr JSONB pattern adoption** — if customers request custom estimation fields.
- **Press-specific parameters** — if multiple press types demand different anilox/transfer curves.

---

## 4. Data Model

### Schema: `print_estimation`

All money and quantity columns use `NUMERIC(precision,scale)` with explicit precision/scale. No FLOAT. All timestamps are UTC in the database; display converts to IST (§6).

---

#### 4.1 `substrate_master`

| Column | Type | Keys | Default | Notes |
|--------|------|------|---------|-------|
| id | BIGSERIAL | PK | | |
| name | VARCHAR(100) | UNIQUE | | e.g. "Flexo Film PVC 150µ", "Vinyl Sheeting", "Offset Coated 130gsm" |
| substrate_type | VARCHAR(50) | | | Enum: flexo_film, vinyl, paper, polyester, canvas, etc. |
| gsm | NUMERIC(8,2) | | NULL | Grams per square meter (if applicable). |
| cost_per_unit | NUMERIC(12,2) | | | Cost in ₹ per single unit (1 roll, 1 sheet, 1 meter, etc.). |
| unit_label | VARCHAR(50) | | "roll" | What is the unit? e.g. "roll", "sheet", "meter", "kg". |
| supplier_id | BIGINT | FK → suppliers | NULL | Optional; links to a supplier master (not in v1 scope if not used). |
| active | BOOLEAN | | true | Soft-delete via flag. |
| created_at | TIMESTAMP | | NOW() | UTC. |
| updated_at | TIMESTAMP | | NOW() | UTC. |

---

#### 4.2 `ink_master`

| Column | Type | Keys | Default | Notes |
|--------|------|------|---------|-------|
| id | BIGSERIAL | PK | | |
| color_name | VARCHAR(100) | | | e.g. "Pantone 186C (Red)", "Process Cyan", "Custom Gold". |
| cost_per_ml | NUMERIC(10,4) | | | Cost in ₹ per milliliter. Stored at paise precision. |
| typical_coverage_percent | NUMERIC(5,2) | | 25.00 | Industry-standard coverage % for this color (e.g., metallics 10–20%, process cyan 25–35%). Estimator can override per-job. |
| active | BOOLEAN | | true | Soft-delete. |
| created_at | TIMESTAMP | | NOW() | UTC. |
| updated_at | TIMESTAMP | | NOW() | UTC. |

---

#### 4.3 `plate_cost_config`

| Column | Type | Keys | Default | Notes |
|--------|------|------|---------|-------|
| id | BIGSERIAL | PK | | |
| press_type | VARCHAR(50) | | | e.g. "flexo_6color", "digital", "offset_4color". Optional grouping; may leave NULL if global. |
| cost_mode | VARCHAR(20) | | "per_color" | Enum: fixed (one price regardless of colors), per_color (cost × num_colors), per_cm2 (cost × engraved area). |
| fixed_cost | NUMERIC(10,2) | | NULL | If mode=fixed, the plate cost in ₹. |
| per_color_cost | NUMERIC(10,2) | | NULL | If mode=per_color, cost per color station in ₹. |
| per_cm2_cost | NUMERIC(12,4) | | NULL | If mode=per_cm2, cost per cm² of engraved area in ₹, stored at decimal precision. |
| active | BOOLEAN | | true | Soft-delete. |
| created_at | TIMESTAMP | | NOW() | UTC. |
| updated_at | TIMESTAMP | | NOW() | UTC. |

---

#### 4.4 `wastage_config`

| Column | Type | Keys | Default | Notes |
|--------|------|------|---------|-------|
| id | BIGSERIAL | PK | | |
| global_wastage_percent | NUMERIC(5,2) | | 5.00 | Default wastage % applied to all new estimates (3–10% typical; site-wide default). |
| material_type | VARCHAR(50) | | NULL | If set, this row applies only to a specific substrate_type (e.g. "vinyl" may have 7%, "paper" 3%). NULL = global default. |
| active | BOOLEAN | | true | Soft-delete. |
| created_at | TIMESTAMP | | NOW() | UTC. |
| updated_at | TIMESTAMP | | NOW() | UTC. |

---

#### 4.5 `estimate` (Master Estimate Record)

| Column | Type | Keys | Default | Notes |
|--------|------|------|---------|-------|
| id | BIGSERIAL | PK | | |
| estimate_no | VARCHAR(50) | UNIQUE | | Customer-facing estimate number, e.g. "EST-2026-0001". Assigned on creation, never reused. |
| state | VARCHAR(20) | | "draft" | Enum: draft, sent, accepted, invoiced, cancelled. Immutable once sent (§8). |
| customer_id | BIGINT | FK → (assumed external CRM) | | Customer identifier. Exact FK depends on integrations; may be NULL in v1. |
| customer_name | VARCHAR(255) | | | Duplicate for offline/email use. |
| job_description | TEXT | | | Brief description of the job (e.g. "Flexo print: 4-color repeat on 1000m roll"). |
| estimated_quantity | NUMERIC(14,2) | | | Total job quantity (meters, pieces, kg — see unit_label in substrate_master). |
| material_subtotal_amount | NUMERIC(12,2) | | | Rolled-up cost of all material (substrate + ink). Sum of estimate_line.material_cost. Paise precision. |
| labor_overhead_amount | NUMERIC(12,2) | | 0.00 | Setup time + operator time + facility burden, in ₹. Admin-entered per estimate (not auto-computed). |
| plate_cost_amount | NUMERIC(12,2) | | 0.00 | Sum of plate costs for all colors. Computed by RPC from plate_cost_config. |
| total_cost_before_gst | NUMERIC(12,2) | | | material + labor + plate. Computed, not entered. |
| gst_percent | NUMERIC(5,2) | | 18.00 | GST rate applicable (usually 18%, but some materials may differ; India-specific). |
| gst_amount | NUMERIC(12,2) | | | Computed as (total_cost_before_gst × gst_percent / 100), rounded per §8. |
| total_cost_with_gst | NUMERIC(12,2) | | | total_cost_before_gst + gst_amount. |
| markup_percent | NUMERIC(5,2) | | NULL | Margin % to apply (if admin wants cost+markup, not cost+fixed-price). |
| quoted_price_before_gst | NUMERIC(12,2) | | | Selling price (before GST). Computed from total_cost_with_gst or total_cost_before_gst × (1 + markup). See §7 (customer-facing safety). |
| quoted_gst_amount | NUMERIC(12,2) | | | GST on quoted_price_before_gst. |
| quoted_price_with_gst | NUMERIC(12,2) | | | Final invoice price to customer. |
| margin_percent_achieved | NUMERIC(5,2) | | NULL | (quoted_price_with_gst − total_cost_with_gst) / quoted_price_with_gst × 100. Computed for reporting; admin-only visibility (§7). |
| notes | TEXT | | "" | Estimator notes (e.g. "Customer prefers matte finish"). |
| created_at | TIMESTAMP | | NOW() | UTC. |
| updated_at | TIMESTAMP | | NOW() | UTC (updated only if state=draft). |
| sent_at | TIMESTAMP | | NULL | When estimate was marked sent; immutability clock starts. |
| invoiced_at | TIMESTAMP | | NULL | When invoice was created (links to finance schema). |

**Immutability rule (§8):** Once state ≠ "draft", no UPDATE on any cost/material field. Creation of a new revision (amendment) is a new row with amended_from_id (below).

---

#### 4.6 `estimate_line`

| Column | Type | Keys | Default | Notes |
|--------|------|------|---------|-------|
| id | BIGSERIAL | PK | | |
| estimate_id | BIGINT | FK → estimate(id) | | |
| line_number | SMALLINT | | | 1, 2, 3… for ordering within estimate. |
| substrate_id | BIGINT | FK → substrate_master(id) | | Material being used. |
| substrate_width_mm | NUMERIC(10,2) | | | Width of substrate in mm. |
| substrate_length_m | NUMERIC(14,2) | | | Length in meters (or total area if non-roll substrate; clarify per substrate_type). |
| substrate_quantity | NUMERIC(14,2) | | | How many of the above (e.g., "3 rolls of 1000m each"). |
| substrate_cost_per_unit | NUMERIC(12,2) | | | Cost per unit at the time of estimation (copy from substrate_master for audit trail; allows historical cost tracking even if master changes). |
| substrate_total_cost | NUMERIC(12,2) | | | (substrate_quantity × substrate_cost_per_unit), rounded per §8. |
| ink_colors_count | SMALLINT | | | Number of color stations / inks used. |
| inks | JSONB | | | Array of {color_id, coverage_percent, cost_per_ml, ink_ml_consumed, cost}. See calc below. |
| wastage_percent_applied | NUMERIC(5,2) | | | (from wastage_config or estimator override). |
| wastage_cost | NUMERIC(12,2) | | | (substrate_total_cost × wastage_percent_applied / 100), rounded per §8. |
| material_cost | NUMERIC(12,2) | | | substrate_total_cost + wastage_cost + sum(ink costs from inks[] array). Final material line-item cost. |
| plate_cost_for_line | NUMERIC(12,2) | | | If this is the only line with this color, the color's plate cost. If shared across multiple lines, allocate pro-rata or leave NULL (parent estimate.plate_cost_amount is the true source). |
| notes | TEXT | | "" | Line-specific notes (e.g. "Metallized ink, +₹50 surcharge"). |
| created_at | TIMESTAMP | | NOW() | UTC. |

**Ink calculation (public flexo formula):**
```
For each color in inks[]:
  ink_consumed_ml = (width_mm × length_m × 1000 × coverage_percent / 100)
                    / (anilox_volume_bcm × transfer_percent)
  cost = ink_consumed_ml × cost_per_ml
```
**Assumptions stored in estimate_line.inks[] (or estimate header if global):**
- `anilox_volume_bcm`: Typical 12–40 BCM (billion cubic microns); estimator enters per press or defaults to site-wide value.
- `transfer_percent`: Typical 20–25%; estimator enters or defaults to site-wide value.
- `coverage_percent`: Per color, from substrate_master default or estimator override.

**Resolved (was an open decision pending eFlexo trial — §9 confirmed eFlexo has no ink-tracking
concept at all to compare against, so this is a 360DLM-original design, not derived from a
competitor default):** `anilox_volume_bcm` and `transfer_percent` are global site-wide defaults,
stored as two additional columns on the `print_estimation.config` singleton row (§4.9) — the same
table that holds the estimate-numbering counter. No per-press override in v1; add a `press_type`-
keyed table later if multiple presses need different anilox/transfer values.

**Formula correction (found while implementing §5.3's ink-cost fix):** the formula above divides
`coverage_percent` by 100 but does not divide `transfer_percent` by 100, even though both are
stored the same way (e.g. `20.00` meaning 20%) — inconsistent with every other percent field in
this schema (gst_percent, wastage_percent, margin_percent all divide by 100 when used in a
ratio). The RPC in §5.3 divides both consistently; treat the formula as written here as
illustrative only, not literal pseudocode.

---

#### 4.7 `actual_consumption` (Variance Tracking, Phase 2)

| Column | Type | Keys | Default | Notes |
|--------|------|------|---------|-------|
| id | BIGSERIAL | PK | | |
| estimate_id | BIGINT | FK → estimate(id) | | Links back to the original estimate. |
| line_number_reference | SMALLINT | | | Which line in the original estimate this applies to. |
| actual_substrate_used_units | NUMERIC(14,2) | | | How many units of substrate actually consumed (vs. estimated). |
| actual_wastage_units | NUMERIC(14,2) | | | Waste actually incurred. |
| actual_wastage_percent_achieved | NUMERIC(5,2) | | | (actual_wastage / (actual_substrate_used + actual_wastage)) × 100. |
| actual_ink_ml_per_color | JSONB | | | Array of {color_name, actual_ml_consumed, estimated_ml, variance_ml}. |
| actual_plate_count | SMALLINT | | | How many plates actually used (vs. estimated). |
| recorded_at | TIMESTAMP | | NOW() | When this actual data was recorded (post-job). |
| recorded_by_employee_id | BIGINT | | | Who recorded (audit trail). |

---

#### 4.8 `estimate_revision` (Immutability Support, §8)

| Column | Type | Keys | Default | Notes |
|--------|------|------|---------|-------|
| id | BIGSERIAL | PK | | |
| original_estimate_id | BIGINT | FK → estimate(id) | | The estimate this is a revision of. |
| revised_estimate_id | BIGINT | FK → estimate(id) | | The new estimate row (state=amended). |
| reason | VARCHAR(255) | | | Why amended: customer change, material restock, etc. |
| created_at | TIMESTAMP | | NOW() | UTC. |

---

#### 4.9 `config` (Singleton — Locked Counter + Site-Wide Defaults)

| Column | Type | Keys | Default | Notes |
|--------|------|------|---------|-------|
| id | SMALLINT | PK | 1 | Single row only (id always 1); enforce with a CHECK or just never insert a second row. |
| next_estimate_no | BIGINT | | 1 | Locked counter for gap-free `estimate_no` assignment (§8.1). Incremented atomically inside `fn_estimate_create_and_line_add` — never write to this column any other way. |
| last_invoice_no | BIGINT | | 0 | Links to `finance.invoice` schema (Phase 3, not v1). |
| anilox_volume_bcm | NUMERIC(8,2) | | | Site-wide default anilox volume (billion cubic microns) for the ink-cost formula (§4.6, §5.3). Must be seeded before any `fn_estimate_line_add` call with ink lines — the RPC errors if NULL. |
| transfer_percent | NUMERIC(5,2) | | | Site-wide default ink transfer % for the same formula. Same NULL-errors-out rule. |

**No DBA direct writes:** per Red-Team 3 (§11), only the locked-counter RPC may update this row — revoke direct UPDATE/DELETE grants on this table from any role except the RPC's `SECURITY DEFINER` owner.

---

### Summary: Rounding & Precision

**Internal representation:**
- Quantity: `NUMERIC(14,2)` (up to ₹99,999,999.99).
- Unit cost (e.g. substrate per kg): `NUMERIC(12,4)` (to preserve paise-level precision in suppliers' quotes).
- Line-item total & all sums: `NUMERIC(12,2)` (final ₹/paise).
- Ink cost per mL: `NUMERIC(10,4)` (paise precision).

**Rounding rule (detailed in RPC, §5):**
1. Compute each line_item.material_cost as a sum: substrate + wastage + ink. Intermediate sums at `NUMERIC(14,4)`.
2. Round line_item.material_cost to `NUMERIC(12,2)` (₹ with 2 decimals) per line.
3. Sum all lines to estimate.material_subtotal_amount (NUMERIC(12,2)).
4. Compute tax: `tax_amount = material_subtotal × (tax_percent / 100)`, intermediate at NUMERIC(14,4), round to 2 decimals.
5. **No cascading re-rounding:** once a line is stored at NUMERIC(12,2), never un-round and re-round.

This prevents silent cumulative drift. See §11 (red-team) for verification.

---

## 5. Remote Procedure Calls (RPCs)

All RPC functions run as `web_anon` by default. **Critical:** where they write to sensitive cost/margin tables, declare `SECURITY DEFINER` and enforce role checks inside (§7). Return type always includes an `ok` boolean to signal transaction success.

---

### 5.1 `fn_estimate_rollup_costs(p_estimate_id, caller_role)`

**Purpose:** Compute material, plate, labor, and tax totals for a given estimate.

**Signature:**
```sql
-- caller_role: client-supplied, per §7.5 Option B. NOT cryptographically verified —
-- an attacker can pass 'owner' and get margin back. Acceptable ONLY as an interim
-- gate until ADR-105 (signed JWT) ships; see Red-Team 1 (§11). Internal callers that
-- don't need margin (e.g. fn_estimate_line_add, §5.3) should pass NULL, not a
-- privileged role string, so they don't accidentally leak margin into a response
-- nobody asked for.
-- Parameter is `p_estimate_id`, NOT `estimate_id` — estimate_line has a real
-- `estimate_id` column, and #variable_conflict use_column below means a bare
-- `estimate_id` in any query would resolve to THAT COLUMN on both sides of a
-- WHERE clause (`WHERE estimate_id = estimate_id` → col = col → always true),
-- silently summing every estimate in the table instead of just this one.
-- Found and fixed 2026-07-03 (advisor review, same class as
-- feedback_plpgsql_return_table_conflict.md but for an INPUT param, not OUT).
fn_estimate_rollup_costs(p_estimate_id BIGINT, caller_role VARCHAR)
RETURNS TABLE (
  ok BOOLEAN,
  error_msg VARCHAR,
  material_subtotal_amount NUMERIC(12,2),
  plate_cost_amount NUMERIC(12,2),
  labor_overhead_amount NUMERIC(12,2),
  total_cost_before_gst NUMERIC(12,2),
  gst_amount NUMERIC(12,2),
  total_cost_with_gst NUMERIC(12,2),
  margin_percent NUMERIC(5,2)  -- NULL unless caller_role IN ('owner','manager') — see above
)
SECURITY DEFINER
AS $$
#variable_conflict use_column  -- Prevent OUT param shadowing (§ critical, feedback_plpgsql_return_table_conflict.md)
DECLARE
  v_estimate_state VARCHAR;
  v_caller_role VARCHAR;
  v_material_subtotal NUMERIC(14,4) := 0;
  v_plate_cost NUMERIC(12,2) := 0;
  v_labor_overhead NUMERIC(12,2) := 0;
  v_gst_rate NUMERIC(5,2);
  v_gst_amount NUMERIC(14,4);
  v_total_before_gst NUMERIC(14,4);
  v_total_with_gst NUMERIC(12,2);
  v_quoted_price NUMERIC(12,2);
  v_margin NUMERIC(5,2);
BEGIN
  -- Fetch estimate, ensure it exists and state is draft/sent
  SELECT state, gst_percent, quoted_price_with_gst
    INTO v_estimate_state, v_gst_rate, v_quoted_price
    FROM print_estimation.estimate
   WHERE id = p_estimate_id;
  
  IF NOT FOUND THEN
    ok := false;
    error_msg := 'Estimate not found';
    RETURN;
  END IF;
  
  -- Sum material costs from all lines
  SELECT COALESCE(SUM(material_cost), 0.0)::NUMERIC(14,4)
    INTO v_material_subtotal
    FROM print_estimation.estimate_line
   WHERE estimate_id = p_estimate_id;
  
  -- Compute plate cost based on plate_cost_config
  SELECT COALESCE(SUM(
    CASE WHEN pcfg.cost_mode = 'fixed' THEN pcfg.fixed_cost
         WHEN pcfg.cost_mode = 'per_color' THEN pcfg.per_color_cost * el.ink_colors_count
         WHEN pcfg.cost_mode = 'per_cm2' THEN pcfg.per_cm2_cost * (el.substrate_width_mm / 10.0 * el.substrate_length_m * 100.0)
         ELSE 0
    END
  ), 0.0)::NUMERIC(12,2)
    INTO v_plate_cost
    FROM print_estimation.estimate_line el
    LEFT JOIN print_estimation.plate_cost_config pcfg ON (pcfg.press_type IS NULL OR pcfg.press_type = 'default')
   WHERE el.estimate_id = p_estimate_id AND pcfg.active = true;
  
  -- Fetch labor/overhead from estimate
  SELECT labor_overhead_amount INTO v_labor_overhead
    FROM print_estimation.estimate
   WHERE id = p_estimate_id;
  
  -- Compute total before GST
  v_total_before_gst := v_material_subtotal + v_plate_cost + v_labor_overhead;
  
  -- Compute GST (round to paise)
  v_gst_amount := (v_total_before_gst * v_gst_rate / 100.0)::NUMERIC(14,4);
  v_gst_amount := ROUND(v_gst_amount, 2);
  
  -- Compute total with GST
  v_total_with_gst := (v_total_before_gst + v_gst_amount)::NUMERIC(12,2);
  
  -- Compute margin (admin-only, checked below)
  IF v_quoted_price > 0 THEN
    v_margin := ((v_quoted_price - v_total_with_gst) / v_quoted_price * 100.0)::NUMERIC(5,2);
  END IF;
  
  ok := true;
  material_subtotal_amount := v_material_subtotal::NUMERIC(12,2);
  plate_cost_amount := v_plate_cost;
  labor_overhead_amount := v_labor_overhead;
  total_cost_before_gst := v_total_before_gst::NUMERIC(12,2);
  gst_amount := v_gst_amount::NUMERIC(12,2);
  total_cost_with_gst := v_total_with_gst;

  -- Gate margin visibility by caller_role (§7.5 Option B, Red-Team 1 §11). This is
  -- the actual conditional the prose always claimed existed — fixed 2026-07-03; the
  -- original draft computed v_margin above but returned it unconditionally,
  -- regardless of caller_role, which is exactly the leak Red-Team 1 describes.
  IF caller_role IN ('owner', 'manager') THEN
    margin_percent := v_margin;
  ELSE
    margin_percent := NULL;
  END IF;
  RETURN;
END;
$$ LANGUAGE plpgsql;
```

**Behavior:**
- Called after any estimate line is added/edited.
- Rolls up material, plate, and labor costs from the estimate_line and estimate headers.
- Applies GST at the configured rate (default 18%).
- Computes margin internally either way (needed for the `IF` check), but only copies it into
  the returned `margin_percent` field when `caller_role IN ('owner', 'manager')` — everyone else
  gets NULL. **Still not cryptographically sound** (`caller_role` is client-supplied and
  forgeable) — this closes the "RPC doesn't even have the parameter" gap the review note flagged,
  not the underlying JWT gap, which is unchanged open-risk pending ADR-105. See §7 (open-risk).
- Returns totals for display and validation.

---

### 5.2 `fn_estimate_create_and_line_add(customer_name, job_description)`

**Purpose:** Create a new estimate in draft state and return its ID.

**Fixed 2026-07-03:** the original signature took a client-supplied `estimate_no_seed` and used
it directly in the number — as written, two callers could pass the same seed and produce
duplicate `estimate_no` values, defeating the gap-free sequencing §8.1 describes. The parameter
is removed entirely; the number now comes only from the locked `print_estimation.config` counter.

**Signature:**
```sql
RETURNS TABLE (
  ok BOOLEAN,
  error_msg VARCHAR,
  estimate_id BIGINT,
  estimate_no VARCHAR
)
SECURITY DEFINER
AS $$
DECLARE
  v_new_id BIGINT;
  v_new_no VARCHAR;
  v_seq_no BIGINT;
BEGIN
  -- Locked-counter assignment (§8.1, §4.9). A single atomic UPDATE...RETURNING
  -- takes the row lock and returns the pre-increment value in one statement —
  -- no separate SELECT...FOR UPDATE round trip needed, and no window where a
  -- second caller could read the same "next" value before it's incremented.
  UPDATE print_estimation.config
     SET next_estimate_no = next_estimate_no + 1
   WHERE id = 1
   RETURNING next_estimate_no - 1 INTO v_seq_no;

  IF NOT FOUND THEN
    ok := false;
    error_msg := 'Estimate counter not initialized — print_estimation.config row (id=1) missing. Seed it once via migration before first use.';
    RETURN;
  END IF;

  v_new_no := 'EST-' || TO_CHAR(NOW(), 'YYYY') || '-' || LPAD(v_seq_no::text, 4, '0');

  INSERT INTO print_estimation.estimate (
    estimate_no, customer_name, job_description, state, created_at
  ) VALUES (
    v_new_no, customer_name, job_description, 'draft', NOW()
  ) RETURNING id INTO v_new_id;
  
  ok := true;
  estimate_id := v_new_id;
  estimate_no := v_new_no;
  RETURN;
END;
$$ LANGUAGE plpgsql;
```

**Migration note:** seed the singleton row once, before this RPC is ever called:
`INSERT INTO print_estimation.config (id) VALUES (1) ON CONFLICT (id) DO NOTHING;` — the same
migration should also set `anilox_volume_bcm`/`transfer_percent` (§4.9, §5.3) so the ink-cost RPC
doesn't error on its first real call.

---

### 5.3 `fn_estimate_line_add(p_estimate_id, substrate_id, width_mm, length_m, quantity, ink_colors, wastage_override)`

**Purpose:** Add a line item to an estimate, compute its material cost, and roll up totals.

**Fixed 2026-07-03:** the original draft hardcoded `v_ink_cost_total := 0.0` — every estimate's
material_cost silently omitted ink cost entirely, a materially wrong number (not a rounding-level
error) for jobs where ink is a significant cost component. `ink_colors` is now documented as the
real input shape: a JSONB array of `{color_id, coverage_percent_override}` (override nullable —
falls back to `ink_master.typical_coverage_percent`). The RPC loops it, applies the public flexo
formula from §4.6, and writes the fully computed per-color breakdown into `estimate_line.inks`
(the JSONB column the schema already declared but the original pseudocode never populated).
Two related correctness fixes made at the same time (both necessary to make the ink-cost fix
actually correct, not just present — see §4.6 for the first):
- `transfer_percent` is now divided by 100 like every other percent field (was inconsistent with
  `coverage_percent` in the illustrative formula).
- Ink volume now scales with `quantity`, matching how `substrate_total_cost` and `wastage_cost`
  already scale with it — the original formula only used `width_mm × length_m` (one unit's
  dimensions), which would have understated ink cost for any line with `quantity > 1`.

**Signature:**
```sql
RETURNS TABLE (
  ok BOOLEAN,
  error_msg VARCHAR,
  line_id BIGINT,
  material_cost NUMERIC(12,2),
  updated_estimate_total NUMERIC(12,2)
)
SECURITY DEFINER
AS $$
#variable_conflict use_column  -- Same reasoning as §5.1: estimate_line.material_cost
  -- and estimate_line.estimate_id both exist as column names that collide with
  -- this function's own OUT/input names — declared explicitly rather than relying
  -- on the (also-safe, since we use v_-prefixed locals) implicit default.
DECLARE
  v_line_id BIGINT;
  v_material_cost NUMERIC(14,4);
  v_substrate_cost_per_unit NUMERIC(12,2);
  v_substrate_total NUMERIC(14,4);
  v_wastage_pct NUMERIC(5,2);
  v_wastage_cost NUMERIC(14,4);
  v_ink_cost_total NUMERIC(14,4) := 0;
  v_anilox_bcm NUMERIC(8,2);
  v_transfer_pct NUMERIC(5,2);
  v_ink_item JSONB;
  v_color_id BIGINT;
  v_coverage_pct NUMERIC(5,2);
  v_cost_per_ml NUMERIC(10,4);
  v_ink_ml NUMERIC(14,4);
  v_ink_cost NUMERIC(14,4);
  v_inks_computed JSONB := '[]'::JSONB;
  v_estimate_total NUMERIC(12,2);
BEGIN
  -- Fetch substrate cost (snapshot for audit trail)
  SELECT cost_per_unit INTO v_substrate_cost_per_unit
    FROM print_estimation.substrate_master
   WHERE id = substrate_id AND active = true;
  
  IF NOT FOUND THEN
    ok := false;
    error_msg := 'Substrate not found or inactive';
    RETURN;
  END IF;
  
  -- Compute substrate total: quantity × cost_per_unit, round per §8
  v_substrate_total := (quantity * v_substrate_cost_per_unit)::NUMERIC(14,4);
  v_substrate_total := ROUND(v_substrate_total, 2);
  
  -- Determine wastage %
  IF wastage_override IS NOT NULL THEN
    v_wastage_pct := wastage_override;
  ELSE
    SELECT global_wastage_percent INTO v_wastage_pct
      FROM print_estimation.wastage_config
     WHERE active = true AND material_type IS NULL
     LIMIT 1;
  END IF;
  
  -- Compute wastage cost
  v_wastage_cost := (v_substrate_total * v_wastage_pct / 100.0)::NUMERIC(14,4);
  v_wastage_cost := ROUND(v_wastage_cost, 2);

  -- Compute ink cost: loop ink_colors JSONB array [{color_id, coverage_percent_override}, ...],
  -- apply the public flexo formula (§4.6, corrected for quantity + consistent %/100 handling).
  SELECT anilox_volume_bcm, transfer_percent
    INTO v_anilox_bcm, v_transfer_pct
    FROM print_estimation.config WHERE id = 1;

  IF (ink_colors IS NOT NULL AND jsonb_array_length(ink_colors) > 0)
     AND (v_anilox_bcm IS NULL OR v_transfer_pct IS NULL) THEN
    ok := false;
    error_msg := 'Ink calc defaults not configured — set print_estimation.config.anilox_volume_bcm and transfer_percent (§4.9) before adding lines with ink colors';
    RETURN;
  END IF;

  IF ink_colors IS NOT NULL THEN
    FOR v_ink_item IN SELECT * FROM jsonb_array_elements(ink_colors)
    LOOP
      v_color_id := (v_ink_item->>'color_id')::BIGINT;

      SELECT cost_per_ml,
             COALESCE((v_ink_item->>'coverage_percent_override')::NUMERIC, typical_coverage_percent)
        INTO v_cost_per_ml, v_coverage_pct
        FROM print_estimation.ink_master
       WHERE id = v_color_id AND active = true;

      IF NOT FOUND THEN
        ok := false;
        error_msg := 'Ink color not found or inactive: id=' || v_color_id;
        RETURN;
      END IF;

      -- ink_ml = (width_mm × length_m × quantity × 1000 × coverage% / 100)
      --          / (anilox_bcm × transfer% / 100)
      v_ink_ml := (width_mm * length_m * quantity * 1000.0 * v_coverage_pct / 100.0)
                  / (v_anilox_bcm * v_transfer_pct / 100.0);
      v_ink_ml := ROUND(v_ink_ml, 4);
      v_ink_cost := ROUND((v_ink_ml * v_cost_per_ml)::NUMERIC, 2);

      v_ink_cost_total := v_ink_cost_total + v_ink_cost;
      v_inks_computed := v_inks_computed || jsonb_build_object(
        'color_id', v_color_id,
        'coverage_percent', v_coverage_pct,
        'cost_per_ml', v_cost_per_ml,
        'ink_ml_consumed', v_ink_ml,
        'cost', v_ink_cost
      );
    END LOOP;
  END IF;
  v_ink_cost_total := ROUND(v_ink_cost_total, 2);
  
  -- Total material cost for this line
  v_material_cost := v_substrate_total + v_wastage_cost + v_ink_cost_total;
  v_material_cost := ROUND(v_material_cost, 2);
  
  -- Insert line (ink_colors_count and inks now both populated — the original
  -- draft declared estimate_line.inks in the schema but never wrote to it)
  INSERT INTO print_estimation.estimate_line (
    estimate_id, line_number, substrate_id, substrate_width_mm, substrate_length_m,
    substrate_quantity, substrate_cost_per_unit, substrate_total_cost,
    ink_colors_count, inks, wastage_percent_applied, wastage_cost, material_cost, created_at
  ) VALUES (
    p_estimate_id,
    (SELECT COALESCE(MAX(line_number), 0) + 1 FROM print_estimation.estimate_line WHERE estimate_id = p_estimate_id),
    substrate_id, width_mm, length_m, quantity, v_substrate_cost_per_unit,
    v_substrate_total::NUMERIC(12,2),
    COALESCE(jsonb_array_length(ink_colors), 0), v_inks_computed,
    v_wastage_pct, v_wastage_cost::NUMERIC(12,2),
    v_material_cost::NUMERIC(12,2), NOW()
  ) RETURNING id INTO v_line_id;
  
  -- Call fn_estimate_rollup_costs to update parent totals. Passes caller_role=NULL —
  -- this call only needs total_cost_with_gst, not margin, so it must NOT pass a
  -- privileged role (§5.1) just to get a number this RPC doesn't even return here.
  SELECT total_cost_with_gst INTO v_estimate_total
    FROM print_estimation.fn_estimate_rollup_costs(p_estimate_id, NULL);
  
  ok := true;
  line_id := v_line_id;
  material_cost := v_material_cost::NUMERIC(12,2);
  updated_estimate_total := v_estimate_total;
  RETURN;
END;
$$ LANGUAGE plpgsql;
```

---

### 5.4 `fn_estimate_send_to_customer(estimate_id)`

**Purpose:** Mark estimate as sent (state="sent"), locking it from further edits, and return customer-safe summary.

**Signature:**
```sql
RETURNS TABLE (
  ok BOOLEAN,
  error_msg VARCHAR,
  estimate_no VARCHAR,
  quoted_price_with_gst NUMERIC(12,2),
  sent_at TIMESTAMP
)
SECURITY DEFINER
AS $$
BEGIN
  UPDATE print_estimation.estimate
    SET state = 'sent', sent_at = NOW(), updated_at = NOW()
   WHERE id = estimate_id AND state = 'draft';
  
  IF NOT FOUND THEN
    ok := false;
    error_msg := 'Estimate not in draft state';
    RETURN;
  END IF;
  
  SELECT estimate_no, quoted_price_with_gst, sent_at
    INTO estimate_no, quoted_price_with_gst, sent_at
    FROM print_estimation.estimate
   WHERE id = estimate_id;
  
  ok := true;
  RETURN;
END;
$$ LANGUAGE plpgsql;
```

---

### 5.5 `fn_record_actual_consumption(estimate_id, actual_data_json)`

**Purpose (Phase 2):** Record actual material consumption after job is complete, compute variance.

**Signature:**
```sql
RETURNS TABLE (
  ok BOOLEAN,
  error_msg VARCHAR,
  variance_percent NUMERIC(5,2),
  actual_vs_estimated_cost_delta NUMERIC(12,2)
)
SECURITY DEFINER
AS $$
DECLARE
  v_data JSONB;
  v_estimated_total NUMERIC(12,2);
  v_actual_total NUMERIC(12,2);
BEGIN
  -- Parse actual_data_json, insert into actual_consumption
  -- Compute variance per material line and aggregate
  -- Return variance % and cost delta (admin-only visibility, enforced by front-end role check)
  -- Detailed implementation deferred to Phase 2.
  
  ok := true;
  variance_percent := 0.0;
  actual_vs_estimated_cost_delta := 0.0;
  RETURN;
END;
$$ LANGUAGE plpgsql;
```

---

## 6. Screens/UX

### 6.1 Estimate Builder Form

**Path:** `/print-estimation/index.html`

**Layout:**
- Header: "New Estimate" / "Edit EST-2026-0042".
- Panel 1: Estimate header (customer name, job description, job quantity, notes).
- Panel 2: Material lines (repeating section).
  - Add Line button.
  - Each line: substrate dropdown, width, length, qty, ink colors, wastage % override (if estimator wants to adjust from default).
  - Display: substrate cost, wastage cost, ink cost (computed real-time via inline JS formula), line total.
- Panel 3: Totals (read-only, updated by RPC on line change).
  - Material subtotal, plate cost, labor/overhead entry field, GST, total cost, margin % (if logged-in user has admin role).
- Panel 4: Quoted price.
  - Admin entry: markup % OR fixed price. Compute final quoted_price from total_cost or apply markup.
  - Display: customer-facing price before/after GST.
- Action buttons: Save (draft), Send to Customer (→ state=sent), Cancel.

**Behavior:**
- Real-time JS validation (no negative costs, qty > 0).
- On substrate change: fetch cost_per_unit, update line subtotals.
- On ink color count change: fetch coverage % defaults, apply flexo formula.
- On Save: POST to `fn_estimate_create_and_line_add` (or update if existing), then `fn_estimate_rollup_costs(p_estimate_id, caller_role)` — the frontend passes the logged-in user's role from `localStorage['session']`, same client-side source as every other role gate on this platform (§7.2).
- On Send to Customer: POST to `fn_estimate_send_to_customer`, then display PDF preview (invoice-like, customer-safe: show description, qty, customer price, not internal costs).

---

### 6.2 Substrate Master Admin Screen

**Path:** `/print-estimation/admin/substrates`

**Layout:**
- Table: id, name, type, GSM, cost_per_unit, unit_label, supplier, active (checkbox toggle).
- Actions per row: Edit (open form), Delete (soft-delete via active flag), View history (price changes).
- Add button: open form to add new substrate.
- Filter: by substrate_type, active/inactive.

**Form (add/edit):**
- Fields: name, substrate_type (dropdown), GSM, cost_per_unit, unit_label, supplier (optional), notes.
- Submit: POST to upsert substrate_master row.

---

### 6.3 Ink Master Admin Screen

**Path:** `/print-estimation/admin/inks`

**Layout:**
- Table: id, color_name, cost_per_ml, typical_coverage_percent, active.
- Actions: Edit, Delete (soft-delete), View history.
- Add button.
- Filter: by color name search.

**Form:**
- Fields: color_name, cost_per_ml, typical_coverage_percent, notes.
- Submit: POST to upsert ink_master.

---

### 6.4 Plate Cost Config Admin Screen

**Path:** `/print-estimation/admin/plate-costs`

**Layout:**
- Table: id, press_type, cost_mode, fixed_cost | per_color_cost | per_cm2_cost, active.
- Actions: Edit, Delete, View.
- Add button.

**Form:**
- Fields: press_type (dropdown, or "Global" for NULL), cost_mode (radio: fixed | per_color | per_cm2).
- Conditionally show input for fixed_cost OR per_color_cost OR per_cm2_cost based on selection.
- Submit: POST to upsert plate_cost_config.

---

### 6.5 Variance Report (Phase 2)

**Path:** `/print-estimation/reports/variance`

**Layout:**
- Date range picker.
- Table: estimate_no, customer, job description, material, estimated_cost, actual_cost, variance_%, margin_achieved.
- Filters: by material type, by customer, by margin (e.g., "show all <10% margin").
- Export to Excel.

**Behavior:**
- Fetches from estimate + actual_consumption, joins and aggregates.
- Admin-only visibility: variance % and actual costs visible only to admin role (enforced on frontend per §7 open-risk).

---

## 7. Data Sensitivity & Access Model

**CRITICAL DESIGN SECTION — NOT TO BE COMPRESSED.**

### 7.1 Data Classification

| Field / Table | Classification | Visibility | Enforcement | Justification |
|---|---|---|---|---|
| estimate.estimate_no, customer_name, job_description | Customer-facing | Public (any logged-in user) | Client-side role check | Safe to share; no cost exposure. |
| estimate_line.substrate_width_mm, length_m, quantity | Customer-facing | Public | Client-side | Production specs, not sensitive. |
| estimate.quoted_price_before_gst, quoted_price_with_gst, quoted_gst_amount | Customer-facing | Customer (via PDF) + admin | Frontend role gate (admin-only editor, PDF printed customer-safe) | Selling price is known to customer; internal cost hidden. |
| substrate_master.cost_per_unit, ink_master.cost_per_ml, plate_cost_config.* | Proprietary costs | Admin only | **OPEN-RISK (see below)** | Supplier rates, margin levers. Exposure = margin compression or copycat pricing. |
| estimate_line.material_cost, substrate_total_cost, ink cost breakdown | Internal costing | Admin only | **OPEN-RISK** | Raw material cost per line; reveals supplier spend. |
| estimate.material_subtotal_amount, labor_overhead_amount, plate_cost_amount, total_cost_before_gst, total_cost_with_gst | Internal costing | Admin only | **OPEN-RISK** | Reveals gross margin. |
| estimate.margin_percent_achieved, fn_estimate_rollup_costs.margin_percent | Profit metrics | Admin only | **OPEN-RISK** | Shows job profitability; strategically sensitive. |
| actual_consumption.* | Variance/variance_percent | Admin only | **OPEN-RISK** | Actual consumption reveals supplier performance, process efficiency, rework costs. |

### 7.2 Current Authorization Architecture

**Session model (inherited from Hub PWA):**
- User logs in via PIN (4–6 digits) → `verify_pin` RPC called client-side → returns role as one of: `{id, name, role}` array entry.
- `localStorage['session']` stores role (and employee ID) client-side.
- No signed JWT; role is **not verified on each API call.**

**Database access model (platform-wide, inherited from 360LM defaults):**
- PostgREST auto-generates REST endpoints for every table.
- All PostgREST calls run as `web_anon` role (anonymous Postgres role).
- `web_anon` holds broad **SELECT/INSERT/UPDATE/DELETE grants** on nearly all schemas, including `print_estimation` (new schema inherits defaults).
- **Implication:** Without explicit revocation, `web_anon` can do `GET /db/substrate_master`, `GET /db/estimate_line?estimate_id=eq.999&select=material_cost`, etc. — **raw material costs are readable by any user via bare PostgREST.**

### 7.3 Proposed Enforcement (Verdict Table)

| Requirement | Status | Mechanism | Notes |
|---|---|---|---|
| Own schema for isolation | **Implemented** | `print_estimation` schema created as standalone (separate from `printing`). | Allows grant-revocation without touching Print Nest PWA. |
| Role-based access to cost fields | **Open-Risk** | Default `web_anon` grant + client-side frontend role check. | **Vulnerable:** `web_anon` has SELECT on substrate_master, estimate_line.material_cost; a determined user can `curl http://api/db/substrate_master` or inspect network requests. Client-side role check is advisory only. |
| Admin-only cost visibility | **Open-Risk (mitigated via workaround)** | `SECURITY DEFINER` RPC `fn_estimate_rollup_costs` returns margin, but cannot verify caller's role (no signed JWT per ADR-105). | Must pass role as RPC argument (client can forge) OR require ADR-105 JWT in Authorization header before returning margin/cost columns. Interim mitigation: frontend role gate + audit logging of cost-field reads. |
| Variance data (actual consumption) isolation | **Open-Risk** | `actual_consumption` table in same schema, default `web_anon` grants apply. | Same as above: `web_anon` can SELECT; raw data is readable without role check. |
| Supplier identity / cost negotiation data | **Not applicable v1** | No supplier PII stored; supplier_id is a nullable FK, not a join. | Deferred if/when supplier master is added. |

### 7.4 Recommended Mitigation (Phase 1.5, Before Production)

**Option A: Revoke `web_anon` grant (Strongest).**
```sql
-- Revoke defaults on cost/margin tables
REVOKE SELECT, INSERT, UPDATE, DELETE ON print_estimation.substrate_master FROM web_anon;
REVOKE SELECT, INSERT, UPDATE, DELETE ON print_estimation.ink_master FROM web_anon;
REVOKE SELECT, INSERT, UPDATE, DELETE ON print_estimation.plate_cost_config FROM web_anon;
REVOKE SELECT, INSERT, UPDATE, DELETE ON print_estimation.estimate_line FROM web_anon;
REVOKE SELECT, INSERT, UPDATE, DELETE ON print_estimation.actual_consumption FROM web_anon;

-- Create a public-safe view for estimate_line (no cost columns)
CREATE VIEW print_estimation.estimate_line_public AS
  SELECT id, estimate_id, line_number, substrate_id, substrate_width_mm,
         substrate_length_m, substrate_quantity, ink_colors_count,
         wastage_percent_applied, notes, created_at
    FROM print_estimation.estimate_line;

GRANT SELECT ON print_estimation.estimate_line_public TO web_anon;

-- Admin-only view (includes cost)
CREATE VIEW print_estimation.estimate_line_admin AS
  SELECT * FROM print_estimation.estimate_line;

-- Grant admin view only to signed-JWT-verified users via RPC
-- (Requires ADR-105 JWT + RPC that checks Authorization header)
```

**Option B: Proxy RPC with Role Validation (Interim, Without ADR-105).**
- All cost reads go through `fn_get_estimate_costs_if_admin(estimate_id, caller_role_arg)`.
- RPC checks `caller_role_arg` against a hardcoded admin list (e.g., `role IN ('owner', 'manager')`).
- **Caveat:** caller_role_arg can be forged by client. Not cryptographically sound until ADR-105 JWT is implemented.

**Option C: Audit Logging (Weakest, Compliance Only).**
- Log all cost-field reads to an `access_log` table (timestamp, ip, user_id, table, columns).
- Detect anomalies (e.g., repeated cost reads by non-admin users) in a monthly audit.
- Does not prevent unauthorized reads; only detects them post-facto.

### 7.5 Design Decision: Verdict

**For v1, adopt Option B (Proxy RPC) with an explicit open-risk note.**

- All cost-field reads go through `fn_estimate_rollup_costs()` RPC (see §5), NOT via bare PostgREST GET.
- RPC accepts `caller_role` as an argument (passed by client JS after `verify_pin`).
- RPC returns margin/variance fields only if `caller_role IN ('owner', 'manager')`.
- Frontend enforces the same role gate (advisory).
- **Document clearly:** this is NOT cryptographically sound; an attacker who reverse-engineers the RPC can pass `caller_role='owner'` and retrieve costs. Proper fix requires ADR-105 JWT.

**Post-v1 (Phase 3, GST invoice linkage):**
- Implement ADR-105 signed JWT (signed on Hub side during PIN verification).
- RPC verifies JWT signature + expiry before returning cost/margin fields.
- Revoke `web_anon` broad grants (Option A above).

---

## 8. Financial Integrity & Immutability

**CRITICAL DESIGN SECTION — NOT TO BE COMPRESSED.**

### 8.1 Invoice Sequencing (GST Compliance, India)

**Requirement:** GST rules demand sequential, unbroken invoice numbering. A cancelled invoice is marked cancelled (state='cancelled'), never deleted or renumbered. Gaps are non-compliant.

**Implementation:**
- `estimate.estimate_no` is assigned on creation via a **locked counter**, NOT a Postgres SEQUENCE.
  - Rationale: SEQUENCES have gaps on rollback or server crash; a cancelled transaction leaves a gap. India's GST audit flags gaps.
  
**Mechanism:** (full table definition and both site-wide default columns in §4.9 — shown here
in the abbreviated counter-only form for the sequencing narrative)
```sql
-- Create a config table to hold counter state (full definition, incl. the
-- anilox_volume_bcm/transfer_percent columns §5.3's ink-cost RPC needs: §4.9)
CREATE TABLE print_estimation.config (
  id SMALLINT PRIMARY KEY DEFAULT 1,
  next_estimate_no BIGINT DEFAULT 1,
  last_invoice_no BIGINT DEFAULT 0,  -- Links to finance.invoice schema
  anilox_volume_bcm NUMERIC(8,2),
  transfer_percent NUMERIC(5,2)
);
INSERT INTO print_estimation.config (id) VALUES (1) ON CONFLICT (id) DO NOTHING;

-- In fn_estimate_create_and_line_add() (§5.2), the actual RPC does:
UPDATE print_estimation.config
  SET next_estimate_no = next_estimate_no + 1
 WHERE id = 1
 RETURNING next_estimate_no - 1 INTO v_seq_no;

-- v_seq_no is the number THIS estimate gets — subtracting 1 from the post-increment
-- value in the same RETURNING clause means the first call ever returns 1, not 2
-- (there is no separate SELECT...FOR UPDATE step; one atomic UPDATE does both the
-- lock and the read, see §5.2 for why).
-- v_seq_no is guaranteed gap-free (locked row, incremented atomically in transaction).
-- Format: 'EST-' || YYYY || '-' || LPAD(v_seq_no, 4, '0') → 'EST-2026-0001', 'EST-2026-0002', …
```

**Cancellation (not deletion):**
```sql
UPDATE print_estimation.estimate
  SET state = 'cancelled', updated_at = NOW()
 WHERE id = estimate_id;

-- Original estimate_no is NOT reused. A new estimate gets the next counter value.
-- Cancelled estimate's number remains in the sequence as a gap-marker.
```

**Confirmed finding (2026-07-03, eFlexo capture — Settings > Jobcard No / Invoice No / Quotation
No):** eFlexo's real numbering config defaults to Indian-financial-year format
(`JOB/2026-27/001`, FY = April–March, not calendar year) **with "Start from 1" selected as the
default New Year Sequence** (the alternative, "Continue Numbers," exists but isn't the default).
This confirms a hint an earlier advisor review raised from the FY-based date-range filters seen
on Job Card/Quotation List — the business likely expects per-FY renumbering, not our current
single never-resetting `EST-<calendar-year>-<counter>` scheme.

**Does this block the fix already made?** No — a single running counter (our current design) is
still GST-compliant (gap-free) regardless of which year convention is used; this is a UX/format
preference, not a financial-integrity requirement. But it's worth a deliberate decision, not a
silent default: **confirm with the user whether `print_estimation.config.next_estimate_no`
should reset to 1 at each Indian FY boundary** (with a `fy_year` column added to `config` so the
counter is keyed per-FY, e.g. `next_estimate_no` becomes per-`(fy_year)` rather than global) to
match the observed market convention, or keep the simpler single-counter design as-is. Not
implemented in this pass — flagged as an open decision in `dbt_pending.md`.

### 8.2 Rounding Rule (Prevent Silent Drift)

**Problem:** If we compute line totals at different precisions, add them, and re-round, cumulative rounding error can silently inflate/deflate the total by a paise or more across 10+ lines.

**Solution:**
1. **Intermediate precision:** All intermediate sums use `NUMERIC(14,4)` (allows paise-level precision plus headroom).
2. **Line-item rounding:** Each `estimate_line.material_cost` is computed and **rounded to NUMERIC(12,2)** (₹/paise) before insertion.
3. **Total aggregation:** Sum the already-rounded line items:
   ```sql
   material_subtotal = SUM(estimate_line.material_cost) -- already NUMERIC(12,2), sum stays whole paise
   ```
4. **Tax rounding:** Compute GST on the already-rounded subtotal:
   ```
   gst_amount = ROUND(material_subtotal * gst_percent / 100, 2)
   ```
5. **Final invoice total:** Sum the already-rounded subtotal and gst_amount (no re-rounding).

**Why this works:** Once a value is rounded to 2 decimals and stored, we never un-round and re-round. The only rounding operation is at the tax-computation step, which is mathematically unavoidable.

**Audit trail:**
- Log the rounding decision in code comments and commit message.
- In estimates.sql migration file, document the rounding rule explicitly.
- Consider adding a `rounding_adjustment_paise` field (0 in most cases, can hold a manual adjustment if GST audit finds a paise-level discrepancy that needs explanation).

### 8.3 Currency Precision

**All monetary fields use `NUMERIC(12,2)` except intermediate calculations (see above):**
- Minimum: ₹0.01 (one paise).
- Maximum: ₹99,999,999.99 (99 crore, well above typical estimate).
- Substrate cost_per_unit: `NUMERIC(12,4)` to preserve paise-level supplier quotes (e.g., ₹45.1234 per kg).
- Final display/invoice: always 2 decimals (₹X.YY).

**No mixed currencies:** All amounts in INR. No conversion.

### 8.4 Immutability (ERPNext Pattern Adaptation)

**Lifecycle:**
```
draft → sent → accepted → invoiced → (variance recorded post-job)
             ↓
          cancelled (any state)
```

**Immutability rules:**
- **state = 'draft':** All fields are mutable (estimator can edit cost, quantity, customer name, etc.).
- **state = 'sent':** estimate_id, estimate_no, customer_name, job_description, all material and cost fields become READ-ONLY. No UPDATE allowed. Any change is a new amended estimate (see below).
- **state = 'accepted' / 'invoiced' / 'cancelled':** All fields read-only. Variance recording (actual consumption) is append-only (new rows in actual_consumption table).

**Amendment (for quote revisions):**
```sql
-- Client requests "send a revised quote for EST-2026-0042"
-- Instead of UPDATE existing estimate:
-- 1. Create a NEW estimate (EST-2026-0125) with amended_from = 2026-0042
-- 2. Copy lines from old estimate
-- 3. Edit lines as needed
-- 4. Mark old estimate state = 'cancelled'
-- 5. Send new one

-- Table: estimate_revision (links old → new)
INSERT INTO print_estimation.estimate_revision (original_estimate_id, revised_estimate_id, reason)
  VALUES (old_id, new_id, 'Customer requested price reduction on color separation');
```

**GST compliance:**
- A cancelled estimate's number is NOT reused (no invoice was issued, but the estimate number is reserved in the sequence as a marker).
- When a new invoice is issued from an amended estimate, it gets a new invoice number (in finance schema, not this schema; see Phase 3).

---

## 9. Open Questions — Resolved via eFlexo Trial Capture (2026-07-03)

Resolved by capturing real eFlexo screens via SiteCap (ADR-108): the "New Jobcard" entry form, "Flex Media Master" (item catalog), "Customer / Group Wise Rates", Settings, Reports list, Job Card/Quotation lists. Live account (360 Degree Logical Marketing / Parmod Narang, trial tier), never automated by Claude — hkl performed every login/click in the SiteCap PWA himself; screenshots/HTML/text are the site's own content, no credentials involved.

**Confidence update (2026-07-03, follow-up capture):** Digital, Offset, and General Item Masters were subsequently captured directly. **The findings below do NOT generalize across categories — eFlexo uses three structurally different pricing models, not one:**
- **Flex** (§9.1/9.2/9.5's basis): area-based — `Sale Rate/sq.ft` + `Cost Rate/sq.ft` per catalog item, continuous custom width/height.
- **Digital & Offset**: fixed-size + quantity-tiered flat pricing — columns are `Item Name`, `Media Name`, `Size` (e.g. A4, 12x18), `Quantity` (print-run count, e.g. 1000/10000), `Amount` (one flat total for that exact size+quantity combination) — no separate sale/cost rate at all, no area calculation, effectively a price list of pre-defined SKUs (e.g. "Leaflet, Gloss Art Paper, A4, qty 10000 → ₹11,920").
- **General**: stocked/inventoried goods — `Sale Rate` + `Purchase Rate` + `Available Stock`, i.e. treated as warehouse consumables/accessories with quantity-deducted stock, not custom print jobs at all.

**What this means for §9.1/9.2/9.5:** their "no plate/ink/wastage/repeat/UPS breakdown" conclusion is now solidly confirmed for Flex (a genuine like-for-like comparison to our design), but the question barely applies to Digital/Offset the same way — those categories don't parametrize by area or ink at all in eFlexo, so there's no cost-breakdown-vs-flat-rate comparison to make there; it's a fundamentally different UX (pick a pre-priced SKU) vs. our design's parametric estimate-from-specs approach for every category. **If 360DLM's v1 scope includes digital/offset job types, this is a real design decision, not just a missing-data gap**: build one parametric engine for all categories (more powerful, more work), or mirror eFlexo's simpler pattern for non-flex categories (pre-priced SKU catalog, quick to build, matches an established market pattern) and reserve the parametric/ink-cost engine for flex specifically. Machine Master (also captured, empty) confirms machine cost/capacity/counter is tracked as a separate asset-maintenance record, not wired into per-job estimation anywhere observed.

### 9.1 Plate Cost Calculation: Auto vs. Manual Entry? — **RESOLVED: neither — no plate cost concept exists**

**Evidence:** Flex Media Master (the catalog admins use to define pricing) has exactly two cost-relevant columns per item: `Sale Rate/sq.ft` and `Cost Rate/sq.ft` — both flat, single numbers, no per-color or per-press variant. The New Jobcard line-item form has no plate, cylinder, or color-count field either — only `Rate/Sq.Ft` (auto-populated from the master's Sale Rate when a Media Type is selected, editable per line).

**Conclusion:** eFlexo does not model plate cost at all — not auto-calculated, not manually entered as a distinct line. Whatever plate/press cost a shop incurs is presumably folded into the one blended `Cost Rate/sq.ft` the admin sets per catalog item once, reused across every job using that item.

**Implication for our design:** our `plate_cost_config` (fixed | per_color | per_cm2) has no eFlexo precedent — it is a genuine differentiator, not a gap to close. Keep it, but expect it to be a new concept for operators trained on eFlexo/similar tools (factor into onboarding).

---

### 9.2 Ink Volume Pre-population: From Estimate or Manual? — **RESOLVED: not tracked at all**

**Evidence:** No ink-related field (ml, coverage %, color count) anywhere in Flex Media Master, Customer/Group Wise Rates, or the New Jobcard Flex Items row. Cost Rate/sq.ft is the only cost signal at item level, same as 9.1.

**Conclusion:** eFlexo has no ink-volume concept to pre-populate from — there's nothing to link an estimate's ink math to on the job-card side, because the job card doesn't have ink fields either.

**Implication for our design:** `ink_ml_per_color` JSONB and the flexo ink-volume formula are a genuine differentiator with no eFlexo precedent. Keep as designed.

---

### 9.3 Wastage Config UX: Global vs. Per-Job Override? — **PARTIALLY RESOLVED: no explicit wastage field found; likely folded into Cost Rate**

**Evidence:** No wastage/scrap field observed in Flex Media Master, Customer/Group Wise Rates, or New Jobcard — nor in Digital/Offset/General Item Masters, now also inspected (see confidence update above). Digital/Offset don't parametrize by area at all (fixed-size SKU + flat amount), so there's no wastage-percent-of-area concept to observe there in the first place; General is stocked goods, same reasoning.

**Conclusion:** Same pattern as plate cost and ink volume — eFlexo's flat per-item Cost Rate/sq.ft most likely has wastage baked in by the admin when they set the rate (padding it up-front), rather than a separate config surfaced anywhere in the UI.

**Implication for our design:** `wastage_config` (global + per-material-type) remains a genuine differentiator. Not fully ruled out that a hidden wastage field exists in an item-master edit form we didn't open (list view only was captured) — low-priority to verify further given the consistent pattern across every other cost concept checked.

---

### 9.4 Actual-vs-Estimated Tracking: Is It a Feature? — **RESOLVED: no — but adjacent margin tracking exists**

**Evidence:** eFlexo's Reports module lists 24 report types (Income Statement, Party Ledger, GST Invoice, Sales Invoice, Customer/Supplier Balance, Expense, Attendance, Payment, Purchase Invoice, Employee Performance, Jobcard Report, Detailed Jobcard, Outsource, Bank, Job PO, DC, Media, Items Category-wise, **Invoice Margin Report**, Receipt, Customer Group-wise, HSN, Digital Machine Counter) — none is a per-job actual-material-consumption-vs-estimate variance report. However, Flex Media Master's Sale Rate vs. Cost Rate columns feed a real, reportable **margin** (confirmed by "Invoice Margin Report" existing) — this is a *static catalog margin* (price − cost at time of use), not an *actual materials consumed* variance.

**Conclusion:** eFlexo tracks margin, not consumption variance. Our Phase 2 `actual_consumption` table + variance report remains a genuine, unclaimed differentiator — worth keeping, not deprioritizing.

**Refinement (2026-07-03, opened the actual Invoice Margin Report):** confirmed exactly how eFlexo computes and displays margin — an "Overall Summary" (Total Invoices Created, Total Bill Amount, Total Cost Amount, Total Margin Amount) plus a per-invoice table: `Invoice No | Invoice Date | Customer Name | Bill Amount | Cost Amount | Margin Amount | Profit Margin%`. This is margin computed at **invoice level** (Bill − Cost), not per-line, and matches our own `fn_estimate_rollup_costs`'s `margin_percent` output almost exactly in spirit — just surfaced as a standalone admin report with PDF export rather than inline in the estimate builder. Validates our margin-computation approach as aligned with real-world practice, not over-engineered.

---

### 9.5 Multi-Color Repeat / UPS-per-Shift Calculation — **RESOLVED: not supported**

**Evidence:** No repeat-distance, UPS, or cylinder-sharing field anywhere in Flex Media Master or New Jobcard. Multi-color/finish variants appear to be handled by defining separate catalog rows (e.g. "Vinyl With Matt Lam" vs. "Vinyl With Gloss Lam" vs. plain "Vinyl" as distinct `Media Name` entries with their own flat rate), not by a parametric repeat/color calculation.

**Conclusion:** eFlexo's costing model is fundamentally simpler than ours in this dimension — it substitutes catalog breadth (many named SKU-like variants) for parametric calculation.

**Implication for our design:** our repeat/UPS/cylinder-sharing fields are a genuine differentiator with no eFlexo precedent. Keep as designed, but note this is more complex than what the market (at least this competitor) currently offers — validate with an actual print-shop estimator that this complexity is wanted, not just theoretically correct.

---

### 9.6 GST Calculation Method — **RESOLVED: per-item, but no variation observed**

**Evidence:** Flex Media Master shows a `Tax` column per catalog item (not a single global setting) — every one of the ~10+ rows inspected shows `GST@18%`, no variation. The New Jobcard's line-item table also has a per-line `Tax` field, separate `Tax Amount` and `Total` columns.

**Conclusion:** eFlexo's data model supports per-item/per-line GST rates (the field exists per row), even though in this account every item happens to use the same 18% rate — so this doesn't confirm substrate-based rate variation is actually used anywhere, only that the schema *could* support it.

**Implication for our design:** our `estimate.gst_percent` global default + `estimate_line` override is compatible with and slightly more explicit than what's observed. No change needed.

**Confirmation (2026-07-03, opened Tax Master directly):** this real trial account has exactly ONE tax rate configured — `GST@18%` = 18.00, full stop. Settles the remaining ambiguity: eFlexo's schema *could* support multiple/substrate-based GST rates, but this business doesn't use that capability in practice. Our global-default-with-override design remains the right level of flexibility — neither over- nor under-built relative to observed real-world usage.

---

### 9.7 Custom Estimation Fields — **RESOLVED: real demand confirmed**

**Evidence:** eFlexo's Settings → Jobcard section has an explicit **"Custom Field"** configuration option, listed alongside Jobcard/Invoice/Quotation numbering and print-template settings.

**Confirmation (2026-07-03, opened the Custom Field settings screen directly):** it's not one generic form — there are 6 separate custom-field groups, each with its own "Add Custom Field" button: Custom Jobcard Field, Custom Flex Item Fields, Custom Digital Item Fields, Custom Offset Item Fields, Custom Other Item Fields, Custom General Item Fields. So custom fields are scoped per entity type (one job-level group + one per item category), not a single flat bag of extra attributes.

**Conclusion:** Custom fields are a real, named feature in this market segment — not a hypothetical nice-to-have — and the real implementation is scoped per entity type, which matters if we adopt the pattern (a single shared `custom_fields JSONB` column per relevant table, not one shared custom-fields table).

**Implication for our design:** reconsider promoting the Dolibarr-style JSONB custom-fields pattern from a deferred v2 idea to an early v1.1 addition, since a direct competitor already ships it. Does not need to block v1 launch, but shouldn't be pushed far down the roadmap either.

---

### 9.8 Additional finding (not one of the original 7): Customer/Group-wise rate overrides

**Evidence:** A dedicated "Customer / Group Wise Rates" master exists, separate from Flex Media Master — shown as a table of every catalog item with a base `Rates` column and an editable `Group Rate` column, filterable by Customer or Group and by All/Changed/Same.

**Relevance:** confirms per-customer/per-group pricing override is a real, expected feature in this market. Not currently in our schema — worth a deliberate scope decision (in v1 or explicitly deferred) rather than an oversight.

---

### 9.9 Confirmation: Quotation and Job Card share the same costing model

**Evidence:** Captured eFlexo's actual "New Quotation" form (`#v3_quotation/new`, reached via the page's "New" dropdown menu — no direct nav link existed for it). It has the identical line-item table to New Jobcard: same Flex/Digital/Offset/Other/General Items tabs, same columns (Description, Media Type, HSN/SAC, Width/Height value+unit+feet, Qty, Area, Rate/Sq.Ft, Unit, Charges Design/Other, Discount, Amount, Tax, Tax Amount, Total). Differences are only in downstream lifecycle fields: Quotation has Terms & Conditions and no payment/receipt section; Job Card has the reverse.

**Relevance:** this rules out the possibility that quotations use a different (perhaps more detailed) costing model than job cards — they don't. It reinforces 9.1/9.2/9.5's conclusions: eFlexo's single-flat-rate-per-item model applies uniformly across both quotation and job-card creation, not just one of them. No changes to prior answers; this is confirmatory evidence, gathered in a follow-up SiteCap capture round (2026-07-03).

---

## 10. Acceptance Criteria for v1 Shippable Version

A print estimation PWA is shippable when:

- [ ] **Estimate Builder Form**
  - [ ] Create a new estimate (assign estimate_no via locked counter, no gaps).
  - [ ] Add multiple lines (substrate, width, length, qty, inks, wastage override).
  - [ ] Real-time line-total computation (JS + RPC call for precision).
  - [ ] Total cost rollup (material + plate + labor + GST).
  - [ ] Mark estimate as "Sent to Customer" (state change, immutable thereafter).
  - [ ] Display customer-safe PDF (no cost fields, customer price only).

- [ ] **Material Masters**
  - [ ] Add/edit substrates (cost, unit, GSM).
  - [ ] Add/edit inks (color, cost_per_ml, coverage default).
  - [ ] Add/edit plate cost config (per-press, per-color, per-cm2 modes).
  - [ ] Add/edit wastage defaults (global + per-material-type).

- [ ] **Financial Integrity**
  - [ ] All monetary fields use NUMERIC, no FLOAT.
  - [ ] Rounding rule is implemented and tested (no silent drift in totals).
  - [ ] Invoice-number sequencing is gap-free (locked counter, not SEQUENCE).
  - [ ] Immutability enforced (state=sent → no cost field edits, only amendments).

- [ ] **Access Control**
  - [ ] Customer-facing estimate summary (no cost columns) readable by all logged-in users.
  - [ ] Cost/margin columns readable only by admin-tier roles (frontend + RPC role argument).
  - [ ] Variance data (actual consumption, Phase 2 placeholder) admin-only.
  - [ ] Open-risk documented (§7): design is not cryptographically sound without ADR-105 JWT (post-v1 work).

- [ ] **Integration**
  - [ ] Hub PWA shows estimate tile, links to `/print-estimation/`.
  - [ ] `?next=` parameter respected (user logs out → redirected to Hub, then back to estimate they were editing).
  - [ ] safe-bottom.css linked (content not hidden behind bottom chrome).
  - [ ] IST timestamps displayed to user (server UTC, converted in JS).
  - [ ] Indian amount formatting (₹, lakh/crore grouping, en-IN locale).

- [ ] **Testing**
  - [ ] 50+ spec tests: estimate creation, line additions, rounding, state transitions, role-based reads.
  - [ ] Hub integration tests (tile display, access verification).
  - [ ] Manual UAT with actual 360DLM job (e.g., quote a real flexo 4-color print job, compare estimate against hand-calculated figure).

- [ ] **Documentation**
  - [ ] MDD finalized (this document, post-eFlexo-trial updates).
  - [ ] RPC signatures and behavior documented.
  - [ ] Rounding rule and immutability rule clearly stated in code comments.
  - [ ] §7 open-risk (access control without JWT) flagged in commit message and memory.

---

## 11. Red-Team: 3 Concrete Failure Modes & Mitigations

### Red-Team 1: Cost/Margin Data Leaked to Unauthorized Role

**Scenario:** An estimator (role='estimator') who should only see customer-facing prices calls `fn_estimate_rollup_costs(p_estimate_id, caller_role)` directly (bypassing the frontend) and passes `caller_role='owner'`. The RPC's `IF caller_role IN ('owner','manager')` check (§5.1, fixed 2026-07-03) accepts it at face value — no signature validation — and returns the true margin. They now know the gross profit on all jobs.

**How the design mitigates this:**
- Design decision (§7.5): RPC checks `caller_role` argument.
- **Mitigation is incomplete:** An attacker forges the argument. **No cryptographic validation.**
- **Full mitigation (post-v1):** ADR-105 signed JWT. Hub PWA signs JWT on PIN verification; each API call sends JWT in Authorization header. RPC verifies signature before returning cost fields. Caller cannot forge a valid JWT.
- **For v1, accept this risk:** Document it as open, flag for Phase 3, and implement audit logging (Option C, §7.4) to detect repeated cost-field reads by non-admin users.

**Residual risk:** Estimator or shop-floor worker with SQL/curl access can still query the database directly. **This is a standing risk of the platform's broad `web_anon` grants** (ADR-106 footnote). Addressed by revoking grants (§7.4 Option A) or moving to JWT-gated RPCs.

---

### Red-Team 2: Rounding/Precision Gap Causes Silent Wrong Invoice

**Scenario:** An estimate for 100 rolls of substrate (₹45.1234 per roll, cost = ₹4,512.34) + ink (₹1,234.56) + plate (₹500.00) = ₹6,246.90. GST @ 18% = ₹1,124.44. Total = ₹7,371.34. But due to mixed rounding at different precision levels in the RPC, the invoice shows ₹7,371.35 or ₹7,371.33. GST audit fails; customer disputes the ₹0.01 (or ₹0.02).

**How the design mitigates this:**
- **Rounding rule (§8.2):** All intermediate sums at NUMERIC(14,4), line items rounded to NUMERIC(12,2) before storage, no un-rounding. GST computed once on the rounded subtotal.
- **Audit trail:** Rounding rule stated explicitly in migration file and code comments.
- **Testing:** Spec test with 10+ line items, verify total matches hand-calculated figure to the paise.
- **Acceptance criterion:** Manual UAT of a real estimate; engineer computes total independently, compares to PWA output.

**Residual risk:** If a future developer "optimizes" the RPC and adds an intermediate ROUND() call they shouldn't, silent drift returns. **Mitigation:** Code review gate (§10 acceptance criteria), and a per-estimate checksum test (optional, but recommended for audit safety — store SHA256(line_items_JSON) in the estimate record, re-verify on RPC calls).

---

### Red-Team 3: GST Invoice Sequencing Violation (Audit Non-Compliance)

**Scenario:** An invoice is created (EST-2026-0042, sent to customer). Later, estimator regrets the estimate and deletes it from the database (or the DB backup is restored, rolling back the counter). A new estimate is created and assigned EST-2026-0042 again. Two invoices with the same number exist. GST audit flags the duplicate.

**How the design mitigates this:**
- **Immutability (§8.4):** Estimates in state='sent' cannot be deleted. Deletion is only during draft state.
- **Locked counter (§8.1):** estimate_no is assigned from a locked config row, incremented atomically. Once assigned, the counter never goes backward.
- **Cancellation, not deletion:** If an estimate must be cancelled, its state='cancelled', but its estimate_no remains in the sequence as a gap-marker.
- **DB backup strategy:** Backups include the config counter row. If a backup is restored, the counter is restored to its value at backup time; any estimates created after the backup time are re-created (not re-numbered). This requires operatinal discipline (no blind point-in-time restores; coordinate with GST records).

**Residual risk:** A DBA performs a reckless `DELETE FROM print_estimation.config WHERE id=1; INSERT … VALUES (1);` to "reset" the counter. **Mitigation:** Remove DBA direct-write permission on the config table; counter updates only via locked RPC.

---

## Summary & Next Steps

**v1 Ready-to-Build Checklist:**
- Data model fully specified (§4, column-level detail).
- RPCs defined and pseudo-coded (§5).
- UX screens described (§6).
- Access control strategy chosen (§7, Option B for v1, Option A post-v1).
- Rounding and immutability rules locked down (§8).
- Open questions on eFlexo trial listed explicitly (§9, not assumed).
- Red-team risks mitigated (§11).

**Critical Pre-Build Actions:**
1. **eFlexo Trial Capture** (user action, time-boxed): During trial window, systematically walk through job card creation, estimate form, plate-cost calculation, and post-job reporting. Answer questions §9.1–9.7. Update this MDD accordingly.
2. **ADR Compliance Check:** Once eFlexo trial is done, review this MDD against the applicable ADRs (ADR-001 `?next=` parameter, ADR-081 safe-bottom.css, ADR-105 JWT — which this design explicitly notes as post-v1). File an ADR-108 if needed ("Print Estimation Finance Integrity & Access Control"), or cite ADR-106 (cross-schema access is grant-gated) for the v1 design decision to accept the broad `web_anon` grant with post-v1 JWT migration.
3. **Security Review:** Before production, a 2nd engineer must review §7 and §11 to ensure the access-control mitigations are actually implemented in the code (not just promised in the design).

---

**Document Author:** Claude Code (forward design)  
**Status:** Ready for eFlexo trial capture + implementation planning  
**Last Updated:** 2026-07-03