# OSS Schema & Workflow Pattern Reference — Detailed Appendix
_Rescued from ephemeral session scratchpad 2026-07-03 — this is the full-detail companion to
§7 ("Lightweight open-source ERP frameworks") and the "Top borrow list" in
`research_erp_lite_landscape_2026-07-03.md`. That report has the condensed, adversarially-spot-
checked summary; this file has the concrete file paths, license notes, fit scores, and SQL
sketches that didn't fit there. Use this when actually implementing a borrowed pattern._

> Confidence note: this appendix is single-agent research (git-hub/repo browsing), not
> adversarially verified the way the main report's 10 findings were. Treat specifics (star
> counts, exact file paths) as approximately correct, current as of the research date — verify
> against the live repo before citing a specific line/path in an ADR or MDD.

---

## AREA 1: Print Shop Management / Print MIS

| Project | License | Activity | Data Model Location | Key Entities | Fit |
|---|---|---|---|---|---|
| OpenPSS | Apache-2.0 | 7★, active 2024 | `openpss/` (MongoDB) | Job, Invoice, Employee, Receipt, Inventory | 3/5 |
| Printing Press Mgmt | license not found | low activity | `PrintingPressManagement/` (Java) | Order, Receipt, Inventory, Press | 2/5 |
| ERPNext Manufacturing | AGPL-3.0 | 7K+★, mature | `apps/manufacturing/` DocType | Work Order, Job Ticket, BOM, Routing, Imposition | 5/5 |
| Dolibarr | GPL-3.0 | 7.4K★, very active | `htdocs/*/class/` ORM | Invoice, Order, Product, Production | 4/5 |

**ERPNext Manufacturing (best fit for print):** `manufacturing/doctype/job_card/` (work order → job ticket → completion), `manufacturing/doctype/production_plan/` (batch planning/imposition), `stock/doctype/stock_entry/` (material consumption). DocType metadata enables workflow rules (estimated vs. actual time, approval states).

**Dolibarr:** `htdocs/commande/class/commande.class.php` (orders + line items + custom fields), `htdocs/product/class/product.class.php` (product variants — paper type/finish/color), `htdocs/expedition/class/shipment.class.php` (proof-of-completion photo tracking). Simple ORM, no forced framework — easy to adapt to PostgREST.

Observation: print-specific fields rarely exist in these OSS projects; most adapt generic manufacturing. Imposition/UPS calculation typically lives in PDF generators, not the DB — material costing should be normalized (paper weight, finish premiums) rather than copied from any one project.

---

## AREA 2: Field Service / Installation Management

| Project | License | Activity | Data Model Location | Key Entities | Fit |
|---|---|---|---|---|---|
| Beveren FSM (ERPNext app) | AGPL-3.0 | 7★, active dev | `beveren_fsm/doctype/` | Service Request, Order, Appointment, Work, Invoice | 5/5 |
| open-fieldservice | MIT | 50+★, recent | `clawnify/` (React/Node) | Job, Technician, Visit, Checklist, Photo | 4/5 |
| Mobile FSM App | open | early | React Native schema | Task, Status, Location, Photo, Timestamp | 3/5 |
| ERPNext Field Service | AGPL-3.0 | built-in module | `crm/doctype/field_service/` | Visit, Technician, SLA | 4/5 |

**Beveren FSM (most complete)** — flow: Service Request → Service Quotation (optional) → Service Order → Appointment → Execution. Immutable `Document` snapshots for audit trail. Multi-status: `request_logged → quoted → approved → scheduled → in_progress → completed`. Quotation is optional (flex for retainers vs. per-job); parts consumed from inventory.

Entities worth copying:
```
service_request:  id, customer_id, location, issue_description, priority,
                   created_at, service_date_from/to,
                   status enum(logged,quoted,approved,scheduled,in_progress,completed,cancelled),
                   assigned_technician_id, supervisor_id

service_appointment: id, service_order_id, scheduled_start/end, actual_start/end,
                      technician_id, address_id,
                      status enum(pending,accepted,in_progress,completed,no_show,rescheduled),
                      notes, signature_capture, photo_ids[]

work_execution:    id, appointment_id, labor_hours,
                    parts_consumed[] {part_id, qty, rate}, completion_notes, sign_off_by

proof_of_completion: id, appointment_id, photo_id, timestamp, technician_id, customer_signature
```

Workflow: Request Created → Logged → Quotation Sent → Quoted → Customer Approval → Approved → Technician Assigned → Scheduled → Work Started → In Progress → Photos+Sign-off → Completed → Invoice Generated → Billed.

Observations: photos are critical (Nextcloud WebDAV or S3-via-PostgREST); geolocation for dispatch optimization; real-time GPS increasingly expected; retainer contracts = flat fee + hours tracked per contract.

---

## AREA 3: Equipment Rental / Production Asset Management

| Project | License | Activity | Data Model Location | Key Entities | Fit |
|---|---|---|---|---|---|
| RentalCore | custom | 65★, very active | `database/schema.sql` + Go models | Job, Customer, Device, Booking, Status | 5/5 |
| Signals Rental Framework | OSS | 13★ | `framework/app/` (Laravel) | Booking, Quotation, Job, Equipment | 4/5 |
| leihs | proprietary | active (academic) | Ruby/Postgres | Item, Borrow, Pool, Availability | 3/5 |
| Shelf.nu | Apache-2.0 | popular | Next.js + Supabase | Asset, Checkin/Checkout, Location | 3/5 |
| LibreBooking | GPL-3.0 | 752★ | `database_schema/create-schema.sql` | Resource, Schedule, Reservation, Availability | 4/5 |

**RentalCore (best fit for event/equipment rental)** — `database/RentalCore.sql` full schema dump:
```
device (asset):  id, name, serial_number, category_id, status, location,
                  condition_rating, next_maintenance_date, created_at, last_assigned_at

job (rental order): id, customer_id, start_date, end_date, status, created_at
   status: quote_pending → approved → confirmed → ready_for_pickup →
           in_rental → ready_for_return → returned → invoiced

job_device (line items): id, job_id, device_id, qty, rate_per_day, discount_pct, total,
                          condition_on_checkout, condition_on_return, damages_noted

invoice: id, job_id, customer_id, amount, tax, due_date, status
```

**LibreBooking (simpler generic booking engine)** — `database_schema/create-schema.sql`:
```
resource:     id, name, resource_type, status_id, administrator_group_id
reservation:  id, resource_id, user_id, start_datetime, end_datetime, title, description,
              reference_number, status
reservation_series: one-off vs. recurring (daily/weekly)
```

**Conflict detection (PostgreSQL-native, directly portable to a PostgREST view):**
```sql
CREATE VIEW available_devices_for_period AS
SELECT d.id, d.name FROM devices d
WHERE NOT EXISTS (
  SELECT 1 FROM job_devices jd JOIN jobs j ON jd.job_id = j.id
  WHERE jd.device_id = d.id
    AND j.status IN ('approved','confirmed','in_rental')
    AND j.end_date > $1 AND j.start_date < $2
);
```

Workflow: Quotation → Approval → Booking Confirmed → Equipment Assigned → Pickup → In Rental → Return Scheduled → Condition Inspection → Invoice → Payment.

Observations: multi-location tracking (branch A → branch B); damage assessment + liability (photos+notes); dynamic pricing (peak season, multi-day discounts); auto-invoice from job line items + time tracking.

---

## AREA 4: Agency / Client Project Management with Retainers

| Project | License | Activity | Data Model Location | Key Entities | Fit |
|---|---|---|---|---|---|
| Twenty CRM | AGPL-3.0 | 10K+★ | `packages/twenty-server/src/graphql/` | Company, Contact, Opportunity, Task, Deal | 5/5 |
| EspoCRM | AGPL-3.0 | 3K+★ | `application/Espo/Resources/` | Account, Contact, Lead, Opportunity | 4/5 |
| Krayin | BSD-3 | 2K+★ | `packages/laravel-crm` | Organization, Contact, Lead, Deal, Pipeline | 4/5 |
| SuiteCRM | AGPL-3.0 | large | `modules/Accounts/`, `modules/Contacts/` | Account, Contact, Opportunity, Activity | 4/5 |
| NextCRM | MIT | recent | `app/models/` (Prisma) | Company, Contact, Task, Project | 3/5 |

**Twenty CRM (best fit for a future digital-marketing arm)** — metadata-driven entity system (GraphQL-native equivalent of Frappe DocTypes):
```
company:     id, name, domainName, createdAt, linkedContacts[], opportunities[]
contact:     id, firstName, lastName, email, phone, position, company_id, owner_id
opportunity: id, name, company_id, contact_id, amount_cents, currency,
             stage(interested→qualified→proposal→won/lost), expected_close_date, owner_id
activity:    id, type enum(call,email,meeting,task), contact_id, opportunity_id,
             subject, notes, scheduled_at, completed_at, completed_by_id
```

**Retainer-specific pattern (the concrete piece to reuse when the digital-marketing arm launches):**
```
contract:  id, client_id, service_type, start_date, end_date, monthly_fee,
           billing_day_of_month, auto_renew, status(draft,active,paused,ended),
           contact_person_id, supervisor_id

contract_billing_cycle: id, contract_id, cycle_start, cycle_end, amount_invoiced,
                         invoice_id, status(pending,sent,paid,overdue)

campaign_task: id, contract_id, campaign_id, title, description, assigned_to,
               deadline, status, completed_date, linked_activity_ids[]
```

Observations: metadata extensibility (custom fields per client type) is crucial; Kanban pipeline needs denormalized status+owner; activity feed prevents tribal-knowledge loss; renewal dates should trigger 30/60/90-day notifications.

---

## AREA 5: Lightweight ERP Framework Architecture

| Project | License | Activity | Architecture | PostgREST Fit | Key Insight |
|---|---|---|---|---|---|
| ERPNext/Frappe | AGPL-3.0 | 7K+★ | DocType + hooks | Partial (Python-heavy) | Metadata-driven, dynamic forms, workflow rules |
| Dolibarr | GPL-3.0 | 7.4K★ | Modular ORM + extrafields | Yes (simple PHP ORM) | Minimal framework, easy custom fields |
| Twenty | AGPL-3.0 | 10K+★ | GraphQL + metadata | Yes (full API leverage) | Modern, schema versioning, workspace isolation |
| Odoo | AGPL-3.0 | large | ORM + manifest-driven | Partial (Python) | Powerful but opinionated |

### Concrete SQL sketches worth reusing

**Dolibarr-style simplicity + custom fields:**
```sql
CREATE TABLE companies (
  id BIGSERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL,
  status SMALLINT DEFAULT 1, created_at TIMESTAMP, updated_at TIMESTAMP
);
CREATE TABLE company_metadata (       -- extrafields pattern
  id BIGSERIAL PRIMARY KEY, company_id BIGINT REFERENCES companies(id),
  field_name VARCHAR(100), field_value TEXT, UNIQUE(company_id, field_name)
);
CREATE TABLE audit_log (
  id BIGSERIAL PRIMARY KEY, table_name VARCHAR(100), record_id BIGINT,
  action VARCHAR(20), changed_fields JSONB, changed_by_id BIGINT,
  changed_at TIMESTAMP DEFAULT NOW()
);
```

**ERPNext-style immutable workflow states + hooks:**
```sql
CREATE TABLE document_versions (
  id BIGSERIAL PRIMARY KEY, doctype VARCHAR(100), doc_id BIGINT, version_num INT,
  status VARCHAR(50),  -- Draft, Submitted, Amended, Cancelled
  version_data JSONB, created_by_id BIGINT, created_at TIMESTAMP,
  UNIQUE(doctype, doc_id, version_num)
);
CREATE TABLE workflow_rules (
  id BIGSERIAL PRIMARY KEY, doctype VARCHAR(100),
  trigger_on VARCHAR(50),  -- on_submit, on_save, on_change_field
  trigger_field VARCHAR(100), action VARCHAR(100), action_params JSONB,
  enabled BOOLEAN DEFAULT TRUE
);
```

**Twenty-style workspace isolation (multi-tenant, for a future SaaS/consultancy offering):**
```sql
CREATE TABLE workspaces (
  id BIGSERIAL PRIMARY KEY, name VARCHAR(255), slug VARCHAR(100) UNIQUE,
  owner_id BIGINT, created_at TIMESTAMP
);
ALTER TABLE companies ADD COLUMN workspace_id BIGINT REFERENCES workspaces(id);
CREATE POLICY workspace_isolation ON companies
  USING (workspace_id = current_setting('app.current_workspace_id')::BIGINT);
```

**Metadata-driven permissions (RLS-ready, requires the JWT/role-claim work deferred in ADR-105/106):**
```sql
CREATE TABLE role_permissions (
  id BIGSERIAL PRIMARY KEY, role_id BIGINT, doctype VARCHAR(100),
  permission_type VARCHAR(50), can_submit BOOLEAN,
  field_level_restriction JSONB, UNIQUE(role_id, doctype, permission_type)
);
CREATE POLICY role_based_access ON companies USING (
  EXISTS (SELECT 1 FROM role_permissions rp JOIN user_roles ur ON rp.role_id = ur.role_id
          WHERE ur.user_id = current_setting('app.user_id')::BIGINT
            AND rp.doctype = 'Company' AND rp.permission_type = 'read')
);
```

**Dolibarr-style audit/GDPR trail:**
```sql
CREATE TABLE audit_trail (
  id BIGSERIAL PRIMARY KEY, table_name VARCHAR(100), record_id BIGINT, action VARCHAR(20),
  old_value JSONB, new_value JSONB, user_id BIGINT, timestamp TIMESTAMP DEFAULT NOW(),
  ip_address INET
);
CREATE TABLE deletion_requests (
  id BIGSERIAL PRIMARY KEY, user_id BIGINT, requested_at TIMESTAMP, approved_at TIMESTAMP,
  approved_by_id BIGINT, status VARCHAR(50)  -- pending, approved, executed, rejected
);
```

**Document state-machine RPC (directly usable as a PostgREST `rpc/` endpoint):**
```sql
CREATE TYPE document_status AS ENUM ('draft','submitted','amended','cancelled','archived');

CREATE OR REPLACE FUNCTION transition_document_status(
  p_doctype VARCHAR, p_doc_id BIGINT, p_new_status VARCHAR
) RETURNS JSON AS $$
DECLARE v_current_status document_status; v_allowed BOOLEAN;
BEGIN
  SELECT status INTO v_current_status FROM documents WHERE doctype = p_doctype AND id = p_doc_id;
  v_allowed := (
    (v_current_status = 'draft' AND p_new_status IN ('submitted','cancelled')) OR
    (v_current_status = 'submitted' AND p_new_status IN ('amended','cancelled')) OR
    (v_current_status = 'amended' AND p_new_status IN ('submitted','cancelled'))
  );
  IF NOT v_allowed THEN RETURN jsonb_build_object('error','Invalid status transition'); END IF;
  UPDATE documents SET status = p_new_status WHERE id = p_doc_id;
  INSERT INTO document_versions (...) VALUES (...);
  RETURN jsonb_build_object('success', TRUE, 'new_status', p_new_status);
END; $$ LANGUAGE plpgsql;
```

---

## Borrow list — ranked (fit score /10 for 360DLM specifically)

1. **Immutable document versioning** (ERPNext/Beveren FSM) — 10/10. Audit trail + GST GSTR compliance. `document_versions` JSONB snapshots + state machine.
2. **Dynamic custom fields** (Dolibarr `extrafields`) — 9/10. SME field variance (GST codes, vehicle reg, crew IDs) without schema thrashing.
3. **Multi-status workflow state machine** (Beveren FSM, RentalCore) — 10/10. Core to all 5 domains; enum + RLS/trigger enforced transitions.
4. **Location-based scheduling** (Beveren FSM, open-fieldservice) — 8/10. PostGIS for proximity/dispatch; essential for events/field service, less for print.
5. **Workspace/tenant isolation** (Twenty) — 7/10. Future-proofs the multi-client consultancy offering.
6. **Asset condition ratings** (RentalCore, Shelf.nu) — 8/10. `condition_rating` + damages JSONB log; vital for rental/production assets.
7. **Consumption-based costing** (ERPNext Stock/Production) — 9/10. Material entry + line items, auto cost roll-up — directly feeds the print estimation engine's actual-vs-estimated variance tracking (see `MDD_print_estimation.md`).
8. **Proof-of-completion photo tracking** (Beveren FSM, RentalCore) — 8/10. Already largely implemented in 360LM's own recce/installation pattern — cross-reference rather than re-borrow.
9. **Retainer/subscription contracts** (Twenty, Krayin) — 6/10. Not immediate; future digital-marketing arm.
10. **Audit/GDPR trail** (Dolibarr, ERPNext, Twenty) — 8/10. Regulatory, often overlooked until an audit forces it.

## Repo quick reference

- ERPNext Manufacturing: https://github.com/frappe/erpnext/tree/develop/apps/manufacturing
- Dolibarr: https://github.com/Dolibarr/dolibarr
- Beveren FSM: https://github.com/Beveren-Software-Inc/Field_Service_Management
- open-fieldservice: https://github.com/clawnify/open-fieldservice
- RentalCore: https://github.com/nbt4/rentalcore
- LibreBooking: https://github.com/LibreBooking/librebooking
- Twenty: https://github.com/twentyhq/twenty
- Krayin: https://github.com/krayin/laravel-crm
- Frappe framework: https://github.com/frappe/frappe

## Bottom line (unchanged from the main report)

Borrow Frappe's immutable versioning (audit/compliance), Dolibarr's simplicity (JSON custom
fields, no heavy ORM), Beveren FSM's field-service flow (survey→quote→schedule→proof — closely
matches 360LM's own recce→installation flow already), RentalCore's asset/booking model (for the
events domain), Twenty's metadata extensibility (custom fields, roles, future RLS). Avoid
Odoo-level over-engineering — enforce business logic via PostgreSQL stored procedures + RLS, not
application code, keeping the vanilla-JS PWAs stateless and PostgREST-queryable.