anvil

Overview

Anvil is a self-hosted electronics inventory & manufacturing system — the InvenTree / PartsBox / GitPLM feature set rebuilt on a Rust / axum / Supabase stack. It manages the full lifecycle of an electronic part: the catalog, sourcing and pricing, physical stock, purchase-order intake, distributor data enrichment, and a native KiCad component library — all deployed as multi-arch containers on my k3s homelab cluster.

Why build it

Off-the-shelf inventory tools each missed something I wanted: proper internal part numbers with inheritance, an idempotent stock ledger safe for hardware to write to over MQTT, multi-source enrichment that keeps provenance instead of silently overwriting, and a KiCad library that serves my parts. Anvil is the excuse to build all of that on a stack I enjoy — and to push on the parts of Postgres (RLS, trigram search, recursive CTEs) that most CRUD apps never touch.

Architecture

A Cargo workspace with a deliberately thin split:

• crates/api — the axum server: every route, auth, database access, and business logic.
• crates/common — dependency-light shared types (the enum vocabulary). It's WASM-compatible: a db feature (enabled only by the API) conditionally derives sqlx::Type, so a future worker or Dioxus client can reuse the same types without pulling in a database driver.
• web/ — a Next.js / React frontend with a fully typed API client generated from the server's OpenAPI spec.
• migrations/ — 18 ordered, domain-grouped SQL migrations.

anvil/
├── crates/
│   ├── api/         # axum server — routes, auth, db, logic
│   └── common/      # shared enums (WASM-safe, db feature-gated)
├── web/             # Next.js UI + generated typed client
└── migrations/      # 18 domain-grouped sqlx migrations

The stack: axum 0.8, sqlx 0.8 (Postgres, compile-checked queries), Supabase for auth + storage, jsonwebtoken for local JWKS verification, moka for caching, reqwest for distributor APIs, and qrcode + barcoders for label rendering. Routing is pure axum; OpenAPI is layered on docs-only, so the two concerns stay independent.

Parts catalog

The core domain: every part that exists as a concept, with CRUD, typed parameters, family inheritance, and interchangeable-part groups.

Typo-tolerant search

The parts list runs a hybrid query: a substring ILIKE or a pg_trgm word-similarity match (<%) across mpn, description, and local_name — all six predicates backed by GIN trigram indexes. So both partial matches and typos land (atmega238 → ATMEGA328). While searching, it loosens the trigram cutoff and ranks by best similarity:

SET LOCAL pg_trgm.word_similarity_threshold = 0.3;

SELECT ...
FROM part p
WHERE p.mpn ILIKE $1 OR p.description ILIKE $1 OR p.local_name ILIKE $1
   OR $2 <% p.mpn OR $2 <% p.description OR $2 <% p.local_name
ORDER BY GREATEST(
  word_similarity($2, p.mpn),
  word_similarity($2, coalesce(p.description,'')),
  word_similarity($2, coalesce(p.local_name,''))
) DESC, p.id;

Filtering by a category matches its entire subtree via an inline WITH RECURSIVE walk, so "Mechanical" includes everything nested beneath it.

Parameters (EAV, JSONB)

Parts carry arbitrary attributes through a classic entity-attribute-value model — part_parameter (part_id, parameter_template_id, value jsonb) — where an ad-hoc parameter template auto-registers on first use. The KiCad and IPN layers flatten these JSONB values to plain strings when they need them.

Family inheritance — computed at read time

A part can point at a family parent (template_part_id) — often a generic template like RP235xN — and inherit its spec: description, datasheet, category, manufacturer, image, and parameters. The merge is fill-empty and applied on the server at read time: the part's own value wins where set, empty fields fall back to the parent, and identity fields (MPN, local name, IPN) never inherit. Nothing is ever copied onto the child, so editing the parent instantly changes what every variant resolves to. A recursive check rejects parent links that would form a cycle, and ?merge=false returns the un-inherited values the edit form needs so re-saving keeps inheritance intact.

Alternates are a deliberately separate model — symmetric, membership-only groups of interchangeable parts (a TI vs. ON vs. ST LM358) with no shared spec and no inheritance.

IPN engine

Every entity gets a human-facing Internal Part Number. The engine behind them is more involved than it looks.

Append-only history

IPNs live in a polymorphic entity_ipn table as an append-only history — reassigning or regenerating a number supersedes the old row rather than mutating it. Two partial unique indexes enforce the invariants: one current IPN per entity, and globally-unique current IPNs (superseded ones may repeat).

CREATE UNIQUE INDEX entity_ipn_current_entity_uq
  ON entity_ipn (entity_kind, entity_id) WHERE is_current;
CREATE UNIQUE INDEX entity_ipn_current_ipn_uq
  ON entity_ipn (ipn) WHERE is_current;

Sequence values are claimed atomically per prefix with a row-locking bump — no global lock, no gaps from rolled-back inserts:

UPDATE ipn_counter SET next_value = next_value + 1
WHERE prefix = $1
RETURNING next_value - 1;

A tiny template grammar

Formats are templates rendered by a small parser: literals, {category:0Ns}, {seq:0Nd}, {mpn}, {manufacturer}, and {param.<Name>}. Prefix and format are each independently inherited from the nearest ancestor category via a recursive CTE. There's even an engineering-code spec, :eng, that converts component values to EIA codes — 10k → 103, 4k7 → 472 — parsing SI suffixes including the 4k7-style embedded decimal.

Deferred assignment

Draft parts — including everything created by a purchase-order import — don't spend an IPN. Numbering is deferred until a draft is promoted out of draft status, so a part that's later discarded or merged never burns a sequence number.

Stock — an event-sourced, idempotent ledger

Physical stock is a read model projected from an append-only event log. Every movement — receive, consume, transfer, adjust, quarantine — is a stock_event row; the stock_item.quantity is just the running total.

The important property is idempotency. Events are keyed on (device_id, sequence_number) and inserted with ON CONFLICT DO NOTHING — and the quantity delta is applied to the read model only when the event is genuinely new. A retried MQTT message from a scale or a scanner, or a double-submitted API call, can't double-count:

WITH ins AS (
  INSERT INTO stock_event (stock_item_id, device_id, sequence_number, event_type, quantity_delta)
  VALUES ($1, $2, $3, $4, $5)
  ON CONFLICT (device_id, sequence_number) DO NOTHING
  RETURNING quantity_delta
)
UPDATE stock_item si
SET quantity = si.quantity + (SELECT quantity_delta FROM ins)
WHERE si.id = $1 AND EXISTS (SELECT 1 FROM ins);

API-originated events auto-assign the next monotonic sequence per device (api:<user>) with retry-on-contention, and a CHECK (quantity >= 0) constraint turns an over-consume into a clean 422 rather than a negative balance.

Storage locations

Locations are a self-referential tree — warehouse → shelf → bin → reel → magazine-slot, 19 location types in all — with a layout type (none, row, grid_2d, grid_3d) describing physical geometry.

Bulk grid provisioning

Rack provisioning lays out every slot of a magazine rack or shelf grid in a single transaction. Given {rows, cols, depth} and axis-labeling options (alphabetic A/B/C… or numeric, column-first, separators, a prefix), it generates the full set of child sublocations and bulk-inserts them with UNNEST of parallel arrays rather than a row per INSERT:

INSERT INTO stock_location (parent_id, name, location_type, ...)
SELECT $1, name, 'magazine_slot', ...
FROM UNNEST($2::text[], $3::int[], $4::int[]) AS t(name, row, col);

It handles 1-D rows, 2-D grids, and 3-D grids from the same code path, and the UI renders the result as a cols × rows × depth board with per-slot stock counts and a slide-in panel per slot.

Purchase-order import

Receiving stock from a distributor order is a four-phase pipeline: parse → match → review → commit.

• Parse — CSV and Excel order/cart exports (via calamine) are normalized into structured lines through a per-vendor column mapping. Numeric fields stay as strings to preserve NUMERIC precision.
• Match — each line is resolved in priority order: exact supplier SKU, then exact MPN (part or linked manufacturer part), then a fuzzy MPN match using pg_trgm similarity() that returns ranked candidates; anything scoring ≥ 0.6 auto-promotes to the default. This stage is pure and DB-light, so it's thoroughly unit-tested.
• Review — nothing is written yet; the operator confirms matches, creates draft parts for the unmatched, and picks locations.
• Commit — runs asynchronously in a background tokio::spawn task as one all-or-nothing transaction: drafts new parts, upserts supplier parts, receives stock via the event log, and writes the purchase order + lines.

Commit returns 202 Accepted immediately and records progress in an import_job table (processed_lines, created_parts, …) that the frontend polls — so a large order can't be killed by a proxy timeout, and progress ticks on a separate connection from the work transaction. Imported parts are drafts, so they inherit the deferred-IPN behavior: no numbers are spent until they're promoted.

Enrichment — multi-source with provenance

Anvil pulls part data from multiple distributors and keeps every source's answer, with a conflict inbox for disagreements — rather than scraping one source and overwriting.

Sources

• LCSC — no API key needed: it scrapes the product page's embedded __NEXT_DATA__ JSON and walks the tree for the fields and CDN image URLs.
• Mouser (Search API), DigiKey (Product Info v4 with a cached OAuth client-credentials token), and Nexar / Octopart (keyed by MPN).

Every source normalizes to one EnrichmentData shape, and all of them are best-effort — any error becomes None and never fails the request.

Provenance + merge policy

Instead of collapsing to a single value, a part_enrichment_value (part_id, field_key, source, value, pinned) table stores every source's value per field. A small, pure, well-tested merge policy decides what the part actually shows:

• a pinned (user-chosen) value always wins;
• otherwise fill-empty — never overwrite an existing value;
• when a field is empty and unpinned, one distinct value (or unanimous sources) auto-applies;
• two or more distinct values become an open conflict.

The conflict inbox

Conflicts surface in a global inbox — a single SQL grouping finds every field where sources disagree and the part is still empty:

SELECT part_id, field_key
FROM part_enrichment_value
GROUP BY part_id, field_key
HAVING COUNT(DISTINCT value) >= 2 AND bool_or(pinned) = false;

Resolving a conflict pins the chosen value and applies it — with the losing candidates still on record.

Sourcing, categories & labels

Sourcing

Manufacturers and suppliers are one unified vendor table distinguished by flags. manufacturer_part (unique per manufacturer_id + mpn) and supplier_part (unique per supplier_id + sku, with JSONB price breaks) hang off it, plus a vendor_rule engine with a buy_from_mode of only / prefer / exclude for sourcing preferences.

Categories

A self-referential category tree with cycle prevention on update (a recursive ancestor walk). Each category carries an ipn_prefix, ipn_format, and kicad_eligible flag — every one of which is independently inherited down the tree.

Labels & QR

Label templates render server-side to SVG: QR codes via the qrcode crate and Code128 barcodes via barcoders. A visual designer places QR / barcode / text / box / line / image elements with position, rotation, font, ECC, and template variables like {ipn} and {url}; QR endpoints for parts, locations, and stock items encode an IPN deep-link, and a qr_config JSONB column allows per-entity overrides.

KiCad HTTP library

Anvil implements KiCad's HTTP Library provider spec end to end, so the inventory is a live component library inside KiCad — search a part in the schematic editor and it comes straight from the database, symbol, footprint, and fields included.

The endpoints

Mounted outside /v1 (at /kicad/v1), it serves the four documents KiCad expects: root discovery, categories.json, parts-in-category, and part detail. Part parameters map to KiCad fields through a kicad_field_mapping table where a category-specific tier overrides the default (first-occurrence-wins), emitting the symbolIdStr, the value-source field, and per-field visibility flags.

Security model

The KiCad endpoints don't use Supabase auth — KiCad sends a static bearer token — so they get a purpose-built model:

• tokens are stored and looked up by SHA-256 hash (never in plaintext), with last_used_at stamped on use;
• every request runs in a read-only transaction pinned to the app_readonly role;
• a token can be scoped to a category subtree (expanded with a recursive CTE), and scoping is enforced on every endpoint — an out-of-scope part returns 404, not a leak.

Database & security

The database is where most of the interesting engineering lives.

Two-layer authorization

Authorization is defense-in-depth, enforced by Postgres itself — not just the app:

• Layer 1 — roles & GRANTs. Three inheriting roles, app_readonly ⊂ app_readwrite ⊂ app_admin, with table-level grants (operational tables writable by read-write, config tables admin-only).
• Layer 2 — Row-Level Security, GUC-gated. Every table has an RLS policy keyed on a request-scoped setting. The API opens each request in a transaction and sets the role + user + tier with SET LOCAL (which auto-resets at commit, so an elevated role never leaks across a pooled connection):

-- per request, inside the transaction:
SELECT set_config('role', $1, true);              -- role from a static allowlist
SELECT set_config('app.permission_tier', $2, true);
SELECT set_config('app.current_user_id', $3, true);

-- every table carries a policy like:
CREATE POLICY part_select ON part FOR SELECT USING (
  current_setting('app.permission_tier', true) IS NOT NULL
  AND (status <> 'draft'
       OR current_setting('app.permission_tier', true) = ANY (ARRAY['read_write','admin']))
);

The payoff: a direct, anon PostgREST-style connection with no GUC set sees zero rows — proven by a dedicated RLS-gate test. Drafts are additionally hidden from read-only users by the same predicate.

Other schema tricks

• Polymorphic references through one shared entity_kind enum (part, location, stock item, build order, …), reused by entity_ipn and attachment, and kept 1:1 with the Rust enum by a string_enum! macro.
• Trigram search (pg_trgm + GIN indexes) used two ways — word_similarity for the parts search box, similarity for import matching.
• Recursive CTEs in five places: category IPN inheritance, category-subtree filtering, category-cycle prevention, KiCad token scoping, and family-parent cycle checks.
• Direct-to-object-store uploads — the API never proxies file bytes: the browser gets a short-lived signed upload URL, PUTs straight to Supabase Storage, then confirms; downloads are short-TTL signed GETs.

API & platform

Routing and OpenAPI

Routing is plain axum — resources are merged into a /v1 router, with the KiCad provider and an /i/{ipn} deep-link resolver mounted alongside. OpenAPI is layered on docs-only (utoipa, no utoipa-axum), so adding a route and documenting it stay independent and upstream axum examples port directly. The spec is served live at /api-docs and dumpable offline with cargo run -p api -- openapi — no database or network needed.

That offline dump is the linchpin of the typed frontend client: openapi-typescript regenerates web/lib/api/schema.d.ts from it, and CI fails on any drift between the Rust types and the TypeScript client.

Shared request machinery

• Pagination / sort / expand helpers, where sort and expand resolve against a per-route allowlist of public-key → SQL-column — injection-safe and unit-tested.
• Structured error mapping — one ApiError enum produces a uniform {error:{code,message,details}} body, and Postgres errors are translated meaningfully: a unique violation becomes a 409 with the offending constraint name surfaced, a check violation a 422.

Auth — local JWKS verification

Supabase access tokens are verified locally against the project's JWKS (fetched once, cached an hour, self-healing on key rotation), with a fallback to a GoTrue lookup only when local verification isn't possible. A single-flight cache (moka's try_get_with) coalesces concurrent requests bearing the same uncached token into one validation — no per-request network round-trip and no thundering herd. The tier extractors (AuthUser / WriteUser / AdminUser) make a handler's signature be its auth requirement.

Release

Tagged releases build native multi-arch images without QEMU — amd64 and arm64 each on their own runner, pushed by digest and stitched into one manifest list (arm64 under emulation took ~2 h; native is a fraction of that). The running version is baked in and reported by /health and the UI.

Testing & CI

The test suite mixes fast pure-function unit tests with real database integration tests.

• Integration tests run against a throwaway Postgres, gated on a TEST_DATABASE_URL so they skip cleanly when it's unset. A support module shims Supabase's auth schema, applies migrations, and seeds users and parts. Suites cover the two-layer authz model (read-only can't insert; drafts hidden from read-only but visible to read-write), the RLS gate (an anon connection with no GUC sees nothing), deferred-IPN-on-promotion, imports, enrichment, stock events, and more.
• Unit tests cover the pure logic directly: IPN rendering and engineering codes, the enrichment merge policy, sort/expand parsing, the LCSC scrape, and KiCad token scoping.

CI spins up a Postgres 16 service and runs the full gauntlet — cargo build --all-targets, fmt --check, clippy --all-targets, cargo test --workspace, then for the web client: regenerate the types and git diff --exit-code to fail on OpenAPI→TypeScript drift, followed by typecheck and build.

What's worth stealing

• Defense-in-depth authorization enforced by Postgres — role grants and GUC-gated RLS — so the database denies anon/unauthorized access, proven by tests.
• An append-only, idempotent event-sourced stock ledger safe for retried device messages.
• Multi-source enrichment with full per-field provenance and a global conflict inbox, including a keyless LCSC scraper.
• Read-time family inheritance — server-merged, never denormalized, cycle-checked.
• A composable IPN engine — append-only history, atomic counters, category-inherited templates, and an engineering-code grammar.
• A complete KiCad HTTP library with SHA-256-hashed, category-scoped, strictly read-only tokens.
• Docs-only OpenAPI with an offline dump and CI-drift-gated typed TypeScript client, shipped as native multi-arch images.