silo/docs/SPECIFICATION.md

Silo: Item Database and Part Management System

Version: 0.2
Date: February 2026
Author: Kindred Systems LLC

---

1. Overview

Silo is an item database with configurable part number generation, designed for R&D-oriented workflows. It provides revision tracking, BOM management, file versioning, and physical inventory location management through a REST API and web UI. CAD and workflow integration (FreeCAD workbench, LibreOffice Calc extension) is maintained in separate repositories (silo-mod, silo-calc).

1.1 Core Philosophy

Silo treats part numbering schemas as configuration, not code. Multiple numbering schemes can coexist, each defined in YAML. The system is schema-agnostic—it doesn't impose a particular part numbering philosophy (intelligent vs. non-intelligent numbers) but instead provides the machinery to implement whatever scheme the organization requires.

1.2 Key Principles

• Items are the atomic unit: Everything is an item (parts, assemblies, drawings, documents)
• Schemas are mutable: Part numbering schemas can evolve, though migration tooling is out of scope for MVP
• Append-only history: All parameter changes are recorded; item state is reconstructable at any point in time
• Configuration over convention: Hierarchies, relationships, and behaviors are YAML-defined

---

2. Architecture

2.1 Components

┌─────────────────────────────────────────────────────────────┐
│              CAD Clients (silo-mod, silo-calc)               │
│  FreeCAD Workbench · LibreOffice Calc Extension             │
│  (maintained in separate repositories)                         │
└─────────────────────────────────────────────────────────────┘
                              │ REST API
                              ▼
┌─────────────────────────────────────────────────────────────┐
│                   Silo Server (silod)                        │
│  - REST API (86 endpoints)                                  │
│  - Authentication (local, LDAP, OIDC)                       │
│  - Schema parsing and validation                            │
│  - Part number generation engine                            │
│  - Revision management                                      │
│  - Relationship graph / BOM                                 │
│  - Web UI (React SPA)                                       │
└─────────────────────────────────────────────────────────────┘
                              │
              ┌───────────────┴───────────────┐
              ▼                               ▼
┌─────────────────────────┐     ┌─────────────────────────────┐
│   PostgreSQL            │     │   Local Filesystem          │
│   (psql.example.internal)│     │   - File storage            │
│   - Item metadata       │     │   - Revision files          │
│   - Relationships       │     │   - Thumbnails              │
│   - Revision history    │     │                             │
│   - Auth / Sessions     │     │                             │
│   - Audit log           │     │                             │
└─────────────────────────┘     └─────────────────────────────┘

2.2 Technology Stack

Component	Technology	Notes
Database	PostgreSQL 16	Existing instance at psql.example.internal
File Storage	Local filesystem	Files stored under configurable root directory
CLI & API Server	Go (1.24)	chi/v5 router, pgx/v5 driver, zerolog
Authentication	Multi-backend	Local (bcrypt), LDAP/FreeIPA, OIDC/Keycloak
Sessions	PostgreSQL pgxstore	alexedwards/scs, 24h lifetime
Web UI	React 19, Vite 6, TypeScript 5.7	Catppuccin Mocha theme, inline styles

---

3. Data Model

3.1 Items

An item is the fundamental entity. Items have:

• A part number (generated according to a schema)
• A type (part, assembly, drawing, document, etc.)
• Properties (key-value pairs, schema-defined and custom)
• Relationships to other items
• Revisions (append-only history)
• Files (optional, stored on the local filesystem)
• Location (optional physical inventory location)

3.2 Database Schema (Conceptual)

-- Part numbering schemas (YAML stored as text, parsed at runtime)
CREATE TABLE schemas (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT UNIQUE NOT NULL,
    version INTEGER NOT NULL DEFAULT 1,
    definition JSONB NOT NULL,  -- parsed YAML
    created_at TIMESTAMPTZ DEFAULT now(),
    updated_at TIMESTAMPTZ DEFAULT now()
);

-- Items (core entity)
CREATE TABLE items (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    part_number TEXT UNIQUE NOT NULL,
    schema_id UUID REFERENCES schemas(id),
    item_type TEXT NOT NULL,  -- 'part', 'assembly', 'drawing', etc.
    created_at TIMESTAMPTZ DEFAULT now(),
    current_revision_id UUID  -- points to latest revision
);

-- Append-only revision history
CREATE TABLE revisions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    item_id UUID REFERENCES items(id) NOT NULL,
    revision_number INTEGER NOT NULL,
    properties JSONB NOT NULL,  -- all properties at this revision
    file_version TEXT,  -- storage version ID if applicable
    created_at TIMESTAMPTZ DEFAULT now(),
    created_by TEXT,  -- user identifier (future: LDAP DN)
    comment TEXT,
    UNIQUE(item_id, revision_number)
);

-- Item relationships (BOM structure)
CREATE TABLE relationships (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    parent_item_id UUID REFERENCES items(id) NOT NULL,
    child_item_id UUID REFERENCES items(id) NOT NULL,
    relationship_type TEXT NOT NULL,  -- 'component', 'alternate', 'reference'
    quantity DECIMAL,
    reference_designator TEXT,  -- e.g., "R1", "C3" for electronics
    metadata JSONB,  -- assembly-specific relationship config
    revision_id UUID REFERENCES revisions(id),  -- which revision this applies to
    created_at TIMESTAMPTZ DEFAULT now()
);

-- Location hierarchy (configurable via YAML)
CREATE TABLE locations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    path TEXT UNIQUE NOT NULL,  -- e.g., "lab/shelf-a/bin-3"
    name TEXT NOT NULL,
    parent_id UUID REFERENCES locations(id),
    location_type TEXT NOT NULL,  -- defined in location schema
    metadata JSONB,
    created_at TIMESTAMPTZ DEFAULT now()
);

-- Item inventory (quantity at location)
CREATE TABLE inventory (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    item_id UUID REFERENCES items(id) NOT NULL,
    location_id UUID REFERENCES locations(id) NOT NULL,
    quantity DECIMAL NOT NULL DEFAULT 0,
    updated_at TIMESTAMPTZ DEFAULT now(),
    UNIQUE(item_id, location_id)
);

-- Sequence counters for part number generation
CREATE TABLE sequences (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    schema_id UUID REFERENCES schemas(id),
    scope TEXT NOT NULL,  -- scope key (e.g., project code, type code)
    current_value INTEGER NOT NULL DEFAULT 0,
    UNIQUE(schema_id, scope)
);

---

4. YAML Configuration System

4.1 Part Numbering Schema

Schemas define how part numbers are generated. Each schema consists of segments that are concatenated with a separator.

# /etc/silo/schemas/kindred-rd.yaml
schema:
  name: kindred-rd
  version: 3
  description: "Kindred Systems R&D part numbering"
  
  # Separator between segments (default: "-")
  separator: "-"
  
  # Uniqueness enforcement
  uniqueness:
    scope: global
    case_sensitive: false
  
  segments:
    - name: category
      type: enum
      description: "Category code (2-3 characters)"
      required: true
      values:
        F01: "Hex Cap Screw"
        F02: "Socket Head Cap Screw"
        # ... 70+ categories across:
        # F01-F18: Fasteners
        # C01-C17: Fluid Fittings
        # R01-R44: Motion Components
        # S01-S17: Structural Materials
        # E01-E27: Electrical Components
        # M01-M18: Mechanical Components
        # T01-T08: Tooling and Fixtures
        # A01-A07: Assemblies
        # P01-P05: Purchased/Off-the-Shelf
        # X01-X08: Custom Fabricated Parts
      
    - name: sequence
      type: serial
      length: 4
      padding: "0"
      start: 1
      description: "Sequential number within category"
      scope: "{category}"
      
  format: "{category}-{sequence}"
  
  # Example outputs:
  # F01-0001 (first hex cap screw)
  # R27-0001 (first linear rail)
  # A01-0001 (first assembly)

Note: The schema was migrated from a {project}-{type}-{sequence} format (v1) to {category}-{sequence} (v3). Projects are now managed as many-to-many tags on items rather than embedded in the part number. See migrations/006_project_tags.sql.

4.2 Segment Types

Type	Description	Options
string	Fixed or variable length string	length, min_length, max_length, pattern, case
enum	Predefined set of values	values (map of code → description)
serial	Auto-incrementing integer	length, padding, start, scope
date	Date-based segment	format (strftime-style)
constant	Fixed value	value

4.3 Serial Scope Templates

The scope field in serial segments supports template variables referencing other segments:

# Sequence per category (current kindred-rd schema)
scope: "{category}"

# Global sequence (no scope)
scope: null

4.4 Alternative Schema Example (Simple Sequential)

# /etc/silo/schemas/simple.yaml
schema:
  name: simple
  version: 1
  description: "Simple non-intelligent numbering"
  
  segments:
    - name: prefix
      type: constant
      value: "P"
      
    - name: sequence
      type: serial
      length: 6
      padding: "0"
      scope: null  # global counter
      
  format: "{prefix}{sequence}"
  separator: ""
  
  # Output: P000001, P000002, ...

4.5 Location Hierarchy Schema

# /etc/silo/schemas/locations.yaml
location_schema:
  name: kindred-lab
  version: 1
  
  hierarchy:
    - level: 0
      type: facility
      name_pattern: "^[a-z-]+$"
      
    - level: 1
      type: area
      name_pattern: "^[a-z-]+$"
      
    - level: 2
      type: shelf
      name_pattern: "^shelf-[a-z]$"
      
    - level: 3
      type: bin
      name_pattern: "^bin-[0-9]+$"
      
  # Path format
  path_separator: "/"
  
  # Example paths:
  # lab/main-area/shelf-a/bin-1
  # lab/storage/shelf-b/bin-12

4.6 Assembly Metadata Schema

Each assembly can define its own relationship tracking behavior:

# Stored in item properties or as a linked document
assembly_config:
  # What relationship types this assembly uses
  relationship_types:
    - component      # standard BOM entry
    - alternate      # interchangeable substitute
    - reference      # related but not part of BOM
    
  # Whether to track reference designators
  use_reference_designators: true
  designator_format: "^[A-Z]+[0-9]+$"  # e.g., R1, C3, U12
  
  # Revision linking behavior
  child_revision_tracking: specific  # or "latest"
  
  # Custom properties for relationships
  relationship_properties:
    - name: mounting_orientation
      type: enum
      values: [top, bottom, left, right, front, back]
    - name: notes
      type: text

---

5. Client Integration

CAD workbench and spreadsheet extension implementations are maintained in separate repositories (silo-mod, silo-calc). The Silo server provides the REST API endpoints consumed by those clients.

5.1 File Storage Strategy

Files are stored on the local filesystem under a configurable root directory. Storage path convention: items/{partNumber}/rev{N}.ext. SHA-256 checksums are captured on upload for integrity verification.

Future option: exploded storage (unpack ZIP-based CAD archives for better diffing).

5.2 Checkout Locking (Future)

Future multi-user support will need a server-side locking strategy:

• Pessimistic locking: Checkout acquires exclusive lock
• Optimistic locking: Allow concurrent edits, handle conflicts on commit

Recommendation: Pessimistic locking for CAD files (merge is impractical).

---

6. Web Interface

6.1 Architecture

The web UI is a React single-page application served at / by the Go server. The SPA communicates with the backend exclusively via the JSON REST API at /api/*.

• Stack: React 19, React Router 7, Vite 6, TypeScript 5.7
• Theme: Catppuccin Mocha (dark) via CSS custom properties
• Styling: Inline React.CSSProperties — no CSS modules, no Tailwind
• State: Local useState + custom hooks (no Redux/Zustand)
• SPA serving: web/dist/ served by Go's NotFound handler with index.html fallback for client-side routing

6.2 Pages

Page	Route	Description
Items	/	Master-detail layout with resizable split panel, sortable table, 5-tab detail view (Main, Properties, Revisions, BOM, Where Used), in-pane CRUD forms
Projects	/projects	Project CRUD with sortable table, in-pane forms
Schemas	/schemas	Schema browser with collapsible segments, enum value CRUD
Settings	/settings	Account info, API token management
Audit	/audit	Item completeness scoring
Login	/login	Username/password form, conditional OIDC button

6.3 Design Patterns

• In-pane forms: Create/Edit/Delete forms render in the detail pane area (Infor ERP-style), not as modal overlays
• Permission checks: Write actions conditionally rendered based on user role
• Fuzzy search: Debounced search with scope toggle (All/PN/Description)
• Persistence: useLocalStorage hook for user preferences (layout mode, column visibility)

6.4 URI Handler

Register silo:// protocol handler:

silo://open/PROTO-AS-0001         # Open latest revision
silo://open/PROTO-AS-0001?rev=3   # Open specific revision

See frontend-spec.md for full component specifications.

---

7. Revision Tracking

7.1 Append-Only Model

Every property change creates a new revision record. The current state is always the latest revision, but any historical state can be reconstructed.

Item: PROTO-AS-0001

Revision 1 (2026-01-15): Initial creation
  - description: "Main chassis assembly"
  - material: null
  - weight: null
  
Revision 2 (2026-01-20): Updated properties
  - description: "Main chassis assembly"
  - material: "6061-T6 Aluminum"
  - weight: 2.5
  
Revision 3 (2026-02-01): Design change
  - description: "Main chassis assembly v2"
  - material: "6061-T6 Aluminum"  
  - weight: 2.3

7.2 Revision Creation

Revisions are created explicitly by user action (not automatic):

• silo commit from FreeCAD
• "Save Revision" button in web UI
• API call with explicit revision flag

7.3 Revision vs. File Version

• Revision: Silo metadata revision (tracked in PostgreSQL)
• File Version: File on disk corresponding to a revision

A single Silo revision may span multiple file uploads during editing. Only committed revisions create formal revision records.

---

8. Relationships and BOM

8.1 Relationship Types

Type	Description	Use Case
component	Part is used in assembly	Standard BOM entry
alternate	Interchangeable substitute	Alternative sourcing
reference	Related item, not in BOM	Drawings, specs, tools

8.2 Reference Designators

For assemblies that require them (electronics, complex mechanisms):

# Relationship record
parent: PROTO-AS-0001
child: PROTO-PT-0042
type: component
quantity: 4
reference_designators: ["R1", "R2", "R3", "R4"]

8.3 Revision-Specific Relationships

Relationships can link to specific child revisions or track latest:

# Locked to specific revision
child: PROTO-PT-0042
child_revision: 3

# Always use latest (default for R&D)
child: PROTO-PT-0042
child_revision: null  # means "latest"

Assembly metadata YAML controls default behavior per assembly.

8.4 Flat BOM and Assembly Costing

Two endpoints provide procurement- and manufacturing-oriented views of the BOM:

Flat BOM (GET /api/items/{partNumber}/bom/flat) walks the full assembly tree and returns a consolidated list of leaf parts only (parts with no BOM children). Quantities are multiplied through each nesting level and duplicate parts are summed.

Assembly A (qty 1)
├── Sub-assembly B (qty 2)
│   ├── Part X (qty 3)   → total 6
│   └── Part Y (qty 1)   → total 2
└── Part Z (qty 4)       → total 4

Response:

{
  "part_number": "A",
  "flat_bom": [
    { "part_number": "X", "description": "...", "total_quantity": 6 },
    { "part_number": "Y", "description": "...", "total_quantity": 2 },
    { "part_number": "Z", "description": "...", "total_quantity": 4 }
  ]
}

Assembly Cost (GET /api/items/{partNumber}/bom/cost) builds on the flat BOM and multiplies each leaf's total_quantity by its standard_cost to produce per-line extended costs and a total assembly cost.

{
  "part_number": "A",
  "total_cost": 124.50,
  "cost_breakdown": [
    { "part_number": "X", "total_quantity": 6, "unit_cost": 10.00, "extended_cost": 60.00 },
    { "part_number": "Y", "total_quantity": 2, "unit_cost": 7.25, "extended_cost": 14.50 },
    { "part_number": "Z", "total_quantity": 4, "unit_cost": 12.50, "extended_cost": 50.00 }
  ]
}

Both endpoints detect BOM cycles and return HTTP 409 with the offending path:

{ "error": "cycle_detected", "detail": "BOM cycle detected: A → B → A" }

---

9. Physical Inventory

9.1 Location Management

Locations are hierarchical, defined by YAML schema. Each item can exist at multiple locations with quantities.

Location: lab/main-area/shelf-a/bin-3
  - PROTO-PT-0001: 15 units
  - PROTO-PT-0002: 8 units
  
Location: lab/storage/shelf-b/bin-1
  - PROTO-PT-0001: 50 units (spare stock)

9.2 Inventory Operations

• Add: Increase quantity at location
• Remove: Decrease quantity at location
• Move: Transfer between locations
• Adjust: Set absolute quantity (for cycle counts)

All operations logged for audit trail (future consideration).

---

10. Authentication

Silo supports three authentication backends that can be enabled independently or combined. When authentication is disabled (auth.enabled: false), all routes are open and a synthetic dev user with the admin role is injected into every request.

10.1 Backends

Backend	Use Case	Config Key
Local	Username/password stored in database (bcrypt cost 12)	auth.local
LDAP	FreeIPA / Active Directory via LDAP bind	auth.ldap
OIDC	Keycloak or any OpenID Connect provider (redirect flow)	auth.oidc

10.2 Role Model

Three roles with a strict hierarchy: admin > editor > viewer

Permission	viewer	editor	admin
Read items, projects, schemas, BOMs	Yes	Yes	Yes
Create/update items and revisions	No	Yes	Yes
Upload files, manage BOMs	No	Yes	Yes
Import CSV/ODS	No	Yes	Yes
Manage own API tokens	Yes	Yes	Yes
User management (future)	No	No	Yes

10.3 API Tokens

Raw token format: silo_ + 64 hex characters (32 random bytes from crypto/rand). Only the SHA-256 hash is stored in the database. Tokens inherit the owning user's role.

10.4 Sessions

PostgreSQL-backed sessions via alexedwards/scs pgxstore. Cookie: silo_session, HttpOnly, SameSite=Lax, 24h lifetime. Secure flag is set when auth.enabled is true.

See AUTH.md for full architecture details and AUTH_USER_GUIDE.md for setup instructions.

---

11. API Design

11.1 REST Endpoints (86 Implemented)

# Health (no auth)
GET    /health                                              # Basic health check
GET    /ready                                               # Readiness (DB)

# Auth (no auth required)
GET    /login                                               # Login page
POST   /login                                               # Login form handler
POST   /logout                                              # Logout
GET    /auth/oidc                                           # OIDC login redirect
GET    /auth/callback                                       # OIDC callback

# Public API (no auth required)
GET    /api/auth/config                                     # Auth backend configuration (for login UI)

# Server-Sent Events (require auth)
GET    /api/events                                          # SSE stream for real-time updates

# Auth API (require auth)
GET    /api/auth/me                                         # Current authenticated user
GET    /api/auth/tokens                                     # List user's API tokens
POST   /api/auth/tokens                                     # Create API token
DELETE /api/auth/tokens/{id}                                # Revoke API token

# Direct Uploads (editor)
POST   /api/uploads/presign                                 # Get upload URL [editor]

# Schemas (read: viewer, write: editor)
GET    /api/schemas                                         # List all schemas
GET    /api/schemas/{name}                                  # Get schema details
GET    /api/schemas/{name}/form                             # Get form descriptor (field groups, widgets, category picker)
POST   /api/schemas/{name}/segments/{segment}/values        # Add enum value [editor]
PUT    /api/schemas/{name}/segments/{segment}/values/{code} # Update enum value [editor]
DELETE /api/schemas/{name}/segments/{segment}/values/{code} # Delete enum value [editor]

# Projects (read: viewer, write: editor)
GET    /api/projects                                        # List projects
GET    /api/projects/{code}                                 # Get project
GET    /api/projects/{code}/items                           # Get project items
GET    /api/projects/{code}/sheet.ods                       # Export project sheet as ODS
POST   /api/projects                                        # Create project [editor]
PUT    /api/projects/{code}                                 # Update project [editor]
DELETE /api/projects/{code}                                 # Delete project [editor]

# Items (read: viewer, write: editor)
GET    /api/items                                           # List/filter items
GET    /api/items/search                                    # Fuzzy search
GET    /api/items/by-uuid/{uuid}                            # Get item by UUID
GET    /api/items/export.csv                                # Export items to CSV
GET    /api/items/template.csv                              # CSV import template
GET    /api/items/export.ods                                # Export items to ODS
GET    /api/items/template.ods                              # ODS import template
POST   /api/items                                           # Create item [editor]
POST   /api/items/import                                    # Import items from CSV [editor]
POST   /api/items/import.ods                                # Import items from ODS [editor]

# Item Detail
GET    /api/items/{partNumber}                              # Get item details
PUT    /api/items/{partNumber}                              # Update item [editor]
DELETE /api/items/{partNumber}                              # Archive item [editor]

# Item-Project Tags
GET    /api/items/{partNumber}/projects                     # Get item's projects
POST   /api/items/{partNumber}/projects                     # Add project tags [editor]
DELETE /api/items/{partNumber}/projects/{code}              # Remove project tag [editor]

# Revisions
GET    /api/items/{partNumber}/revisions                    # List revisions
GET    /api/items/{partNumber}/revisions/compare            # Compare two revisions
GET    /api/items/{partNumber}/revisions/{revision}         # Get specific revision
POST   /api/items/{partNumber}/revisions                    # Create revision [editor]
PATCH  /api/items/{partNumber}/revisions/{revision}         # Update status/labels [editor]
POST   /api/items/{partNumber}/revisions/{revision}/rollback # Rollback to revision [editor]

# Files
GET    /api/items/{partNumber}/files                        # List item file attachments
GET    /api/items/{partNumber}/file                         # Download latest file
GET    /api/items/{partNumber}/file/{revision}              # Download file at revision
POST   /api/items/{partNumber}/file                         # Upload file [editor]
POST   /api/items/{partNumber}/files                        # Associate uploaded file with item [editor]
DELETE /api/items/{partNumber}/files/{fileId}               # Remove file association [editor]
PUT    /api/items/{partNumber}/thumbnail                    # Set item thumbnail [editor]

# BOM
GET    /api/items/{partNumber}/bom                          # List direct children
GET    /api/items/{partNumber}/bom/expanded                 # Multi-level BOM (recursive)
GET    /api/items/{partNumber}/bom/flat                     # Flattened BOM (leaf parts, rolled-up quantities)
GET    /api/items/{partNumber}/bom/cost                     # Assembly cost roll-up
GET    /api/items/{partNumber}/bom/where-used               # Where-used (parent lookup)
GET    /api/items/{partNumber}/bom/export.csv               # Export BOM as CSV
GET    /api/items/{partNumber}/bom/export.ods               # Export BOM as ODS
POST   /api/items/{partNumber}/bom                          # Add BOM entry [editor]
POST   /api/items/{partNumber}/bom/import                   # Import BOM from CSV [editor]
POST   /api/items/{partNumber}/bom/merge                    # Merge BOM from ODS with conflict resolution [editor]
PUT    /api/items/{partNumber}/bom/{childPartNumber}        # Update BOM entry [editor]
DELETE /api/items/{partNumber}/bom/{childPartNumber}        # Remove BOM entry [editor]

# .kc Metadata (read: viewer, write: editor)
GET    /api/items/{partNumber}/metadata                     # Get indexed .kc metadata
PUT    /api/items/{partNumber}/metadata                     # Update metadata fields [editor]
PATCH  /api/items/{partNumber}/metadata/lifecycle           # Transition lifecycle state [editor]
PATCH  /api/items/{partNumber}/metadata/tags                # Add/remove tags [editor]

# .kc Dependencies (viewer)
GET    /api/items/{partNumber}/dependencies                 # List raw dependencies
GET    /api/items/{partNumber}/dependencies/resolve         # Resolve UUIDs to part numbers + file availability

# .kc Macros (viewer)
GET    /api/items/{partNumber}/macros                       # List registered macros
GET    /api/items/{partNumber}/macros/{filename}            # Get macro source content

# Audit (viewer)
GET    /api/audit/completeness                              # Item completeness scores
GET    /api/audit/completeness/{partNumber}                 # Item detail breakdown

# Integrations — Odoo (read: viewer, write: editor)
GET    /api/integrations/odoo/config                        # Get Odoo configuration
GET    /api/integrations/odoo/sync-log                      # Get sync history
PUT    /api/integrations/odoo/config                        # Update Odoo config [editor]
POST   /api/integrations/odoo/test-connection               # Test connection [editor] (stub)
POST   /api/integrations/odoo/sync/push/{partNumber}        # Push to Odoo [editor] (stub)
POST   /api/integrations/odoo/sync/pull/{odooId}            # Pull from Odoo [editor] (stub)

# Sheets (editor)
POST   /api/sheets/diff                                     # Diff ODS sheet against DB [editor]

# Part Number Generation (editor)
POST   /api/generate-part-number                            # Generate without creating item [editor]

11.2 Not Yet Implemented

The following endpoints from the original design are not yet implemented:

# Locations (tables exist, no API handlers)
GET    /api/locations
POST   /api/locations
GET    /api/locations/{path}
DELETE /api/locations/{path}

# Inventory (tables exist, no API handlers)
GET    /api/inventory/{partNumber}
POST   /api/inventory/{partNumber}/adjust
POST   /api/inventory/{partNumber}/move

---

11.3 .kc File Integration

Silo supports the .kc file format — a ZIP archive that is a superset of FreeCAD's .fcstd. A .kc file contains everything an .fcstd does, plus a silo/ directory with platform metadata.

Standard entries (preserved as-is)

Document.xml, GuiDocument.xml, BREP geometry files (.brp), thumbnails/

Silo entries (silo/ directory)

Path	Purpose
silo/manifest.json	Instance origin, part UUID, revision hash, .kc schema version
silo/metadata.json	Custom schema field values, tags, lifecycle state
silo/history.json	Local revision log (server-generated on checkout)
silo/dependencies.json	Assembly link references by Silo UUID
silo/macros/*.py	Embedded macro scripts bound to this part

Commit-time extraction

When a .kc file is uploaded via POST /api/items/{partNumber}/file, the server:

1. Opens the ZIP and scans for silo/ entries
2. Parses silo/manifest.json and validates the UUID matches the item
3. Upserts silo/metadata.json fields into the item_metadata table
4. Replaces silo/dependencies.json entries in the item_dependencies table
5. Replaces silo/macros/*.py entries in the item_macros table
6. Broadcasts SSE events: metadata.updated, dependencies.changed, macros.changed

Extraction is best-effort — failures are logged as warnings but do not block the upload.

Checkout-time packing

When a .kc file is downloaded via GET /api/items/{partNumber}/file/{revision}, the server repacks the silo/ directory with current database state:

• silo/manifest.json — current item UUID and metadata freshness
• silo/metadata.json — latest schema fields, tags, lifecycle state
• silo/history.json — last 20 revisions from the database
• silo/dependencies.json — current dependency list from item_dependencies

Non-silo ZIP entries are passed through unchanged. If the file is a plain .fcstd (no silo/ directory), it is served as-is.

ETag caching: the server computes an ETag from revision_number:metadata.updated_at and returns 304 Not Modified when the client's If-None-Match header matches.

Lifecycle state machine

The lifecycle_state field in item_metadata follows this state machine:

draft → review → released → obsolete
  ↑        ↓
  └────────┘

Valid transitions are enforced by PATCH /metadata/lifecycle. Invalid transitions return 422 Unprocessable Entity.

Metadata response shape

{
  "schema_name": "kindred-rd",
  "lifecycle_state": "draft",
  "tags": ["prototype", "v2"],
  "fields": {"material": "AL6061", "finish": "anodized"},
  "manifest": {
    "uuid": "550e8400-e29b-41d4-a716-446655440000",
    "silo_instance": "silo.example.com",
    "revision_hash": "abc123",
    "kc_version": "1.0"
  },
  "updated_at": "2026-02-18T12:00:00Z",
  "updated_by": "forbes"
}

Dependency response shape

[
  {
    "uuid": "550e8400-...",
    "part_number": "F01-0042",
    "revision": 3,
    "quantity": 4.0,
    "label": "M5 Bolt",
    "relationship": "component"
  }
]

Resolved dependency response shape

[
  {
    "uuid": "550e8400-...",
    "part_number": "F01-0042",
    "label": "M5 Bolt",
    "revision": 3,
    "quantity": 4.0,
    "resolved": true,
    "file_available": true
  }
]

Macro list response shape

[
  {"filename": "validate_dims.py", "trigger": "manual", "revision_number": 5}
]

Macro detail response shape

{
  "filename": "validate_dims.py",
  "trigger": "manual",
  "content": "import FreeCAD\n...",
  "revision_number": 5
}

Database tables (migration 018)

• item_metadata — schema fields, lifecycle state, tags, manifest info
• item_dependencies — parent/child UUID references with quantity and relationship type
• item_macros — filename, trigger type, source content, indexed per item

---

12. MVP Scope

12.1 Implemented

• PostgreSQL database schema (13 migrations)
• YAML schema parser for part numbering
• Part number generation engine
• CLI tool (cmd/silo)
• API server (cmd/silod) with 86 endpoints
• Filesystem-based file storage
• BOM relationships (component, alternate, reference)
• Multi-level BOM (recursive expansion with configurable depth)
• Where-used queries (reverse parent lookup)
• Flat BOM flattening with quantity roll-up and cycle detection
• Assembly cost roll-up using standard_cost
• BOM CSV and ODS export/import
• Reference designator tracking
• Revision history (append-only) with rollback and comparison
• Revision status and labels
• Project management with many-to-many item tagging
• CSV import/export with dry-run validation
• ODS spreadsheet import/export (items, BOMs, project sheets)
• Web UI for items, projects, schemas, audit, settings (React SPA)
• File attachments with presigned upload and thumbnail support
• Authentication (local, LDAP, OIDC) with role-based access control
• API token management (SHA-256 hashed)
• Session management (PostgreSQL-backed)
• Audit logging and completeness scoring
• CSRF protection (nosurf)
• Fuzzy search
• .kc file extraction pipeline (metadata, dependencies, macros indexed on commit)
• .kc file packing on checkout (manifest, metadata, history, dependencies)
• .kc metadata API (get, update fields, lifecycle transitions, tags)
• .kc dependency API (list, resolve with file availability)
• .kc macro API (list, get source content)
• ETag caching for .kc file downloads
• Property schema versioning framework
• Docker Compose deployment (dev and prod)
• systemd service and deployment scripts

12.2 Partially Implemented

• Location hierarchy (database tables exist, no API endpoints)
• Inventory tracking (database tables exist, no API endpoints)
• Date segment type (schema parser placeholder only)
• Part number format validation on creation
• Odoo ERP integration (config and sync-log functional; push/pull are stubs)

12.3 Not Started

• Unit tests (Go server — 9 test files exist, coverage is partial)
• Schema migration tooling
• Checkout locking
• Approval workflows
• Exploded file storage with diffing
• Notifications
• Reporting/analytics

---

13. Open Questions

1. Thumbnail generation: Generate thumbnails from CAD files on commit? Useful for web UI browsing.

2. Search indexing: PostgreSQL full-text search sufficient, or add dedicated search (Meilisearch, etc.)?

3. Checkout locking: Pessimistic vs optimistic locking strategy for multi-user CAD file editing.

---

14. References

14.1 Design Influences

• CycloneDX BOM specification: JSON/YAML schema patterns for component identification, relationships, and metadata (https://cyclonedx.org)
• OpenBOM data model: Reference-instance separation, flexible property schemas
• Ansible inventory YAML: Hierarchical configuration patterns with variable inheritance

14.2 Related Standards

• ISO 10303 (STEP): Product data representation
• IPC-2581: Electronics assembly BOM format
• Package URL (PURL): Standardized component identification

---

Appendix A: Example YAML Files

A.1 Complete Part Numbering Schema

See schemas/kindred-rd.yaml for the full schema (v3). Summary:

# kindred-rd-schema.yaml (abbreviated)
schema:
  name: kindred-rd
  version: 3
  description: "Kindred Systems R&D part numbering"
  
  separator: "-"
  
  uniqueness:
    scope: global
    case_sensitive: false
  
  segments:
    - name: category
      type: enum
      description: "Category code"
      required: true
      values:
        F01: "Hex Cap Screw"
        F02: "Socket Head Cap Screw"
        # ... 70+ categories (see full file)
      
    - name: sequence
      type: serial
      length: 4
      padding: "0"
      start: 1
      description: "Sequential number within category"
      scope: "{category}"
  
  format: "{category}-{sequence}"
    
  # Example outputs: F01-0001, R27-0001, A01-0001

A.2 Complete Location Schema

# kindred-locations.yaml
location_schema:
  name: kindred-lab
  version: 1
  description: "Kindred Systems lab and storage locations"
  
  path_separator: "/"
  
  hierarchy:
    - level: 0
      type: facility
      description: "Building or site"
      name_pattern: "^[a-z][a-z0-9-]*$"
      examples: ["lab", "warehouse", "office"]
      
    - level: 1
      type: area
      description: "Room or zone within facility"
      name_pattern: "^[a-z][a-z0-9-]*$"
      examples: ["main-lab", "storage", "assembly"]
      
    - level: 2
      type: shelf
      description: "Shelving unit"
      name_pattern: "^shelf-[a-z]$"
      examples: ["shelf-a", "shelf-b"]
      
    - level: 3
      type: bin
      description: "Individual container or bin"
      name_pattern: "^bin-[0-9]{1,3}$"
      examples: ["bin-1", "bin-42", "bin-100"]
  
  # Properties tracked per location type
  properties:
    facility:
      - name: address
        type: text
        required: false
    area:
      - name: climate_controlled
        type: boolean
        default: false
    shelf:
      - name: max_weight_kg
        type: number
        required: false
    bin:
      - name: bin_size
        type: enum
        values: [small, medium, large]
        default: medium

A.3 Assembly Configuration

# Stored as item property or linked document
# Example: assembly PROTO-AS-0001
assembly_config:
  name: "Main Chassis Assembly"
  
  relationship_types:
    - component
    - alternate
    - reference
  
  use_reference_designators: false
  
  child_revision_tracking: latest
  
  # Assembly-specific BOM properties
  relationship_properties:
    - name: installation_notes
      type: text
    - name: torque_spec
      type: text
    - name: adhesive_required
      type: boolean
      default: false
  
  # Validation rules
  validation:
    require_quantity: true
    min_components: 1