Inventory API Suite — Integration Instructions
Updated: October 10, 2026
Audience: Cody and authorized Iron Horse integration developers
Application: https://inventory.ihrailsoftware.com/
Credential policy: This sheet deliberately contains no API tokens, passwords, private keys, or real transaction payloads. Obtain access through the approved administrator/secret store; do not email credentials with this document.
1. Choose the right interface
Inventory is not a single REST server with a write route for every screen. Its complete application-facing suite has three layers plus BIRCH lookups:[1][3][5]
- Internal reporting API: read-only
GET /health,GET /api/internal/inventory/parts, andGET /api/internal/inventory/usage. Use this for integrations that need counts or movement history without an end-user session. - Supabase application API: authenticated REST resources and named database RPCs for part lookup, reports, receipt/return transactions, cycle counts, car archiving, and account administration. Permissions remain enforced by database functions and row-level security.
- Checkout authority:
POST /functions/v1/inventory-checkoutfor Checkout and Check-in. It resolves the current BIRCH work order, validates material mappings and quantities, and atomically posts approved movements. It is not a reporting endpoint or a dry-run endpoint. - BIRCH read adapters: car/work-order/material lookups used by Inventory. These are dependencies, not new Inventory-owned write endpoints.
Do not use the internal reporting bearer token as a Supabase user JWT. Do not post Checkout or Check-in directly to database tables or a legacy movement RPC to bypass the authority.
Base URLs and access
| Interface | Base | Authentication |
|---|---|---|
| Internal reporting | http://100.87.6.31:4371 | Approved Tailscale network access; reporting bearer token except /health |
| Supabase REST/RPC | https://jjrblgugjmlqkbxrgneq.supabase.co/rest/v1 | Project publishable/anon key in apikey plus signed-in user access token in Authorization |
| Supabase Auth | https://jjrblgugjmlqkbxrgneq.supabase.co/auth/v1 | Supabase SDK manages authentication and refresh; credentials are supplied securely by the user/integration |
| Checkout Edge Function | https://jjrblgugjmlqkbxrgneq.supabase.co/functions/v1/inventory-checkout | Signed-in user bearer token; active permitted Inventory profile |
| BIRCH reads | https://birchbackend.ihrailsoftware.com | See the BIRCH adapter section; do not assume reporting tokens apply |
The reporting address is private to the approved Tailscale network; it is not a public Internet endpoint. 127.0.0.1 on Cody's machine would point to Cody's machine, not Hopper. Do not expose port 4371 publicly. Prefer server-to-server reporting; the Node service does not implement browser CORS/preflight handling.[1]
Verification boundary
This revision reconciles current application source 802a3d014677e6a0e1303eb3a0cc54042d36c394 (October 5) with the separately running reporting service source a2d608870d0d16ca0487d15526cd0eb7bd5d44c0. The reporting server and session module are unchanged between those source revisions. The live frontend JavaScript and CSS also hash-match the assets committed at the current application revision.[1][2][3]
Live checks on October 10 verified reporting health, valid/missing/invalid authentication, parts and usage reads, pagination, active filtering, an empty result, invalid-date rejection, and unknown-route rejection. Authenticated Supabase part/usage search calls returned successfully. The Edge Function answered OPTIONS and rejected an empty authenticated POST before any movement processing. No production movements, account changes, count changes, archive changes, or notification sends were made for this documentation. Write contracts below are source-verified, not a claim that every write was re-executed in production. Automatic OpenAPI discovery returned 401; this guide is based on inspected implementation and SQL rather than an assumed generated spec.
2. Reporting quick start
Supply INVENTORY_INTERNAL_API_TOKEN using your approved secret mechanism before running the commands. Never paste its value into a ticket or commit. These examples are read-only.[1]
export INVENTORY_API_BASE='http://100.87.6.31:4371'
: "${INVENTORY_INTERNAL_API_TOKEN:?Load the approved reporting token securely first}"
curl --fail-with-body --silent --show-error \
"$INVENTORY_API_BASE/health"
curl --fail-with-body --silent --show-error --get \
"$INVENTORY_API_BASE/api/internal/inventory/parts" \
-H "Authorization: Bearer $INVENTORY_INTERNAL_API_TOKEN" \
--data-urlencode 'search=brake' \
--data-urlencode 'active=true' \
--data-urlencode 'limit=25'
curl --fail-with-body --silent --show-error --get \
"$INVENTORY_API_BASE/api/internal/inventory/usage" \
-H "Authorization: Bearer $INVENTORY_INTERNAL_API_TOKEN" \
--data-urlencode 'start_date=2026-10-01T00:00:00-05:00' \
--data-urlencode 'end_date=2026-10-01T23:59:59.999-05:00' \
--data-urlencode 'limit=100'
Use a date library with America/Chicago for business-day boundaries; do not hard-code -05:00 year-round. A plain YYYY-MM-DD is interpreted as a UTC date by this API, not a Central date.[1]
GET /health
Unauthenticated response:[1]
{"ok":true,"service":"inventory-internal-api","version":"1.0.0"}
This is service liveness only. It does not test database login or prove a protected data request will succeed. Follow it with a small authenticated parts request.
3. Reporting request and response reference
Pagination shared by /parts and /usage
limit: positive integer; default100, capped at1000. Invalid/nonpositive values fall back to100.page: one-based positive integer; default1, capped at100000.offset: explicit zero-based offset. It overridespage; invalid/negative values become0.page_offset: supported compatibility alias foroffset. Preferoffset; do not send both.- Offset mode does not recalculate the echoed
pagination.page; usepagination.offsetas the authoritative position. pagination.totalis the filtered exact count for that request, not a frozen snapshot.has_next_pagemeans only that the server returned a fulllimitrows. It may be true on the final full page. Stop whenoffset + returned >= totalwhen a total is present, or whenreturned < limit.- Ordering: parts by
part_noascending; usage bycreated_atdescending. There is no cursor/snapshot guarantee or secondary tie-breaker in this wrapper. Concurrent changes can shift offset pages; deduplicate exports by stable IDs.[1]
Illustrative envelope only; counts below are not live inventory data:
{
"rows": [],
"pagination": {
"limit": 100,
"offset": 0,
"page": 1,
"returned": 0,
"total": 0,
"has_next_page": false
},
"filters": {"part_no": null, "search": null, "active": null}
}
GET /api/internal/inventory/parts
Optional filters:[1]
part_no: case-insensitive partial part-number match.search: case-insensitive match across part number, barcode, title, and description.%, parentheses, and commas are converted to spaces in this search parameter.active:true/1orfalse/0; case-insensitive. Omitted or unrecognized values do not apply an active-status filter.- Filters combine with AND, except the fields within
search, which combine with OR.
Returned row fields:[1]
| Field | Meaning |
|---|---|
part_id | Inventory part UUID; use this for application write references |
item_id | Legacy item ID; may be null; not interchangeable with part_id |
part_no, barcode, title, description | Part identification; description falls back to title, then part number |
active | Current active flag |
on_hand | Current recorded on-hand quantity; a legacy negative balance may remain negative while corrective Check-ins move it toward zero |
on_order, on_po | Aliases for quantity on purchase order; do not sum them |
cost, unit_of_measure | Stored inventory cost and unit; unit can be null |
last_updated_at | Inventory update time, falling back to part update time |
quickbooks_qty | Latest staged qty_qb, falling back to staged qty; null when unavailable |
quickbooks_qty_in_process, quickbooks_total_adjustment | Latest staged QuickBooks comparison fields; can be null |
quickbooks_staged_at | Timestamp of the selected staging record; null when unavailable |
QuickBooks fields are a staged comparison, not a real-time QuickBooks query. Do not replace on_hand with a QuickBooks field. Numeric quantity/cost fields in this wrapper default to zero when the corresponding inventory value is missing; zero does not independently prove a completed physical count.[1]
GET /api/internal/inventory/usage
Optional filters:[1]
start_date: inclusivecreated_atlower boundary; date-only values start at00:00:00.000Z.end_date: inclusive upper boundary; date-only values end at23:59:59.999Z.car: case-insensitive partial match against car/location.part_no: case-insensitive partial part-number match.- ISO timestamps are normalized to UTC. Validate real calendar dates in your client; the date-only branch is format-based. Always URL-encode parameters.
Returned row fields:[1]
| Field | Meaning |
|---|---|
movement_line_id | Stable movement-line UUID |
transaction_id, transaction_no | Parent transaction identity |
legacy_adjustment_id, legacy_created_by | Legacy provenance when available |
date | Movement timestamp |
car | Car or special location label |
part_id, part_no, barcode, description | Part identity and description |
movement_type | Underlying movement kind |
quantity | Absolute value of quantity_delta; not signed usage |
quantity_delta | Signed effect on inventory |
net_used_quantity | Negative of quantity_delta; reporting sign convention |
employee | Display attribution supplied by the usage-history view |
For net consumption, sum net_used_quantity, not quantity. Reporting history can include receipts, returns, and legacy rows; this read API does not expose a work_order_id filter. Do not use an all-history sum from this endpoint to decide current-work-order checkout allowance. The checkout authority separately computes current-order posted usage and excludes voids and other work orders.[1][4][5]
Reporting errors
| HTTP | Meaning and recovery |
|---|---|
200 with empty rows | Request succeeded; filters or effective database permissions yielded no rows |
400 | Invalid date or other explicit bad request; fix the request |
401 | Missing/incorrect reporting token; body currently uses error: "bad_request" and message: "Unauthorized." |
404 | Authenticated unknown path or unsupported method; body uses error: "not_found" |
500 | Upstream database/auth/query failure; capture status and redacted message, then contact the operator |
503 | Reporting token or server-side Supabase authentication configuration is missing |
The service checks reporting authentication before unknown protected routes. An unauthenticated request to an unknown route can return 401 rather than 404. There is no dedicated 405 handling on this wrapper. All responses are JSON with Cache-Control: no-store.[1]
The running service renews its Supabase session when absent, expired, or near expiry. Consumers still send only the reporting token. A 200 health check followed by failed protected reads is an operator issue to diagnose, not a reason to rotate unrelated credentials or send passwords to consumers.[2]
Complete paged export example (read-only)
This uses Node.js 22 or another runtime with built-in fetch. Supply the reporting token from your secret store and pipe standard output to a protected JSONL destination.[1]
const base = process.env.INVENTORY_API_BASE || 'http://100.87.6.31:4371';
const token = process.env.INVENTORY_INTERNAL_API_TOKEN;
if (!token) throw new Error('Reporting token is required');
const seen = new Set();
let offset = 0;
for (;;) {
const url = new URL('/api/internal/inventory/parts', base);
url.searchParams.set('limit', '500');
url.searchParams.set('offset', String(offset));
const response = await fetch(url, {
headers: { Authorization: `Bearer ${token}` },
signal: AbortSignal.timeout(30000),
});
const body = await response.json();
if (!response.ok) throw new Error(`Inventory request failed: ${response.status}`);
for (const row of body.rows) {
if (!seen.has(row.part_id)) {
seen.add(row.part_id);
process.stdout.write(JSON.stringify(row) + '\n');
}
}
const page = body.pagination;
offset = page.offset + page.returned;
if (page.returned === 0 || page.returned < page.limit ||
(typeof page.total === 'number' && offset >= page.total)) break;
}
For usage exports, change the route to /api/internal/inventory/usage, add a fixed date range, and deduplicate on movement_line_id. Offset paging is not an accounting snapshot; reconcile at a quiet interval or use an approved snapshot extraction if exact concurrent consistency is required.
4. Supabase database/API reference
Scope, transport, and evidence
Source: current client and final ordered SQL definitions.[3][4]
This section covers the current client implementation and ordered database migrations 0001–0030. It is a source-verified contract, not a full live dump of deployed SQL grants or proof that every write was executed. The live checks and source revisions are stated in section 1. Historical docs/api-plan.md names are not implemented-route authority. Migration references use NNNN:line within supabase/migrations; the source links at the end identify the exact revision.[3][4]
- Supabase base:
https://<PROJECT_REF>.supabase.co. Client configuration:VITE_SUPABASE_URL,VITE_SUPABASE_ANON_KEY(src/lib/supabase.ts). Use a public project key forapikey, and the signed-in user's access token forAuthorization: Bearer <USER_ACCESS_TOKEN>. A project key is not an Inventory login. - Database RPC:
POST /rest/v1/rpc/<function>withContent-Type: application/jsonand the exact namedp_*arguments below. No arguments means{}. SDK:supabase.rpc(name, args). - Resources:
/rest/v1/<table_or_view>, SDKsupabase.from(name). SQLRETURNS TABLEproduces an array of snake_case rows, even when exactly one row is returned. Scalarnumericandjsonbfunctions return scalar/object data, not row arrays;voidhas no business result. Always inspect SDKerror/ HTTP status before usingdata. - UUIDs are strings;
text[]is a JSON string array;jsonbparameters take JSON arrays/objects, not double-encoded strings. SQLnumericpreserves fractional/negative inventory values; handle client numeric conversion deliberately. Avoid assuming abigintis always within JavaScript's safe-integer range. - There are 12 distinct RPC names referenced by
src/lib, 6 directly referenced REST resources, 2 additional retained client-callable RPCs, and 7 service-role RPCs (6 invoked by the checkout function and 1 SQL helper). All are listed below. Presence insrc/libproves a wrapper reference, not that every wrapper has a currently reachable UI control.
Identity, roles, and session lifecycle
Source: current client and final ordered SQL definitions.[3][4]
Supabase's database role authenticated is not the application enum inventory_user_role. Application roles are viewer, clerk, manager, admin, cycle_count, super_admin. Role predicates look up user_profiles.id = auth.uid() and require active = true; do not infer these permissions from a username, legacy role label, or a locally cached session.
| Database-enforced operation/predicate | Application roles when active | Evidence |
|---|---|---|
is_inventory_member(); ordinary member-protected reads | All six roles | 0001:96 |
is_inventory_manager(); manager-policy table writes | manager, admin, super_admin | Final override 0016:1 |
can_inventory_cycle_count(); count RPC and count insert/update policies | manager, admin, cycle_count, super_admin | 0016:17, 0015 |
is_inventory_super_admin(); user administration, history archive | super_admin only | 0013:17, 0023, 0027 |
| Delete eligible non-archived, non-BIRCH usage line | admin, super_admin | 0022:113 plus 0027:239 trigger |
| Post movements through allowed legacy or validated routes | All except viewer | 0027:6, 0029:46, 0030:40 |
Important exceptions: search_inventory_parts_page checks SQL EXECUTE privileges, not active membership. lookup_part_by_identifier and retained search_inventory_parts have no membership check and no PUBLIC/anon EXECUTE revoke in this migration chain. An explicit GRANT ... TO authenticated alone does not make a function authenticated-only: PostgreSQL's default PUBLIC function EXECUTE can remain unless revoked. Do not describe these reads as protected by active-member RLS; all three are SECURITY DEFINER. Actual inherited/default ACLs require a separate read-only deployment inspection.
signIn(identifier,password)trims the identifier; an identifier containing@goes directly toauth.signInWithPassword({email,password}). Otherwise it callsresolve_inventory_loginfirst, then signs in with the returned email. Username resolution is anchored inlegacy_portal_users: a newly created profile-only username is not independently searched; use email if it has no matching eligible legacy row.getCurrentSession()callsauth.getSession();onSessionChange()wrapsauth.onAuthStateChange.loadCurrentInventoryUser()callsauth.getUser()and readsuser_profiles(id,email,display_name,role,active)for that user. A missing profile yieldsnull. Read the current profile before enabling role-gated workflows; session existence alone is not membership approval. The client usescreateClientwithout custom auth settings; persistence/refresh follows the installed Supabase SDK defaults.- New
auth.usersinserts triggerhandle_new_inventory_user: profile display name comes from metadatadisplay_name, thenname, then the email local part, then a fallback; username comes from metadata; default application access is active clerk (0008:11, trigger installed in0005). This is database bootstrap behavior, not evidence that public signup is enabled.src/libexposes no signup wrapper. auth.resetPasswordForEmail(email,{redirectTo:'https://inventory.ihrailsoftware.com/'})requests a recovery email; this is an external side effect, not a database RPC.auth.updateUser({password})changes the current/recovered user's password. Administrative direct password replacement is a separate super-admin RPC below. Never log passwords, access tokens, recovery links, or refresh tokens.signOut()callsauth.signOut()without custom options. Role/active changes update the profile, not the auth password. Neither access-change nor direct password-reset SQL explicitly revokes sessions/refresh tokens; do not promise immediate token invalidation. Re-check membership at protected operations. Inactive users can still read their own profile under the self-read policy, and the part-search exception above still matters.
Application-referenced RPCs — complete list
Source: current client and final ordered SQL definitions.[3][4]
Argument defaults are SQL defaults; parameters without defaults must be supplied (use explicit null only where supported). Return lists below describe database rows, not the client's camelCase display objects.
1. resolve_inventory_login — username-to-email lookup
- Args:
p_identifier text. - Returns: rows
{email citext}; empty for blank input/no eligible username; at most one match. An identifier containing@is returned unchanged after trimming, without account validation. - Auth: explicitly granted to
anonandauthenticated; intended pre-login call, no membership requirement. - Behavior: case-insensitive legacy username; requires legacy authorised=true and both deleted flags=false; linked profile, if present, must be active. Link matching uses auth ID, email, or username; profile email preferred. A staged legacy email can resolve without an activated auth account, so resolution does not prove login will succeed. Read-only.
- Source:
0008:40; clientinventoryApi.ts:200.
2. lookup_part_by_identifier — legacy single-result part lookup
- Args:
p_identifier text,p_exact boolean = false. - Returns: rows
{part_id uuid, legacy_item_id integer, part_no text, barcode text, title text, description text, qty_on_hand numeric, qty_on_po numeric, full_desc text}; zero or one row. - Auth: authenticated grant; no active-member predicate or PUBLIC/anon revoke is present. Read-only
SECURITY DEFINER. - Behavior: barcode normalization lowercases and removes
:; part normalization lowercases and removes-. Barcode is exact-normalized; part number is exact-normalized ifp_exact=true, otherwise substringLIKE. Blank input gives no rows. Ranking: barcode exact, part exact, other; then active descending and part number. It includes inactive parts and hides ambiguity withLIMIT 1. Missing balances become zero.full_descjoins legacy ID (or UUID), number, title, on-hand, on-PO with|. - Source:
0002:22; clientinventoryApi.ts:414. For authoritative quantity lookup, prefer the ambiguity-detecting direct-read sequence described below.
3. search_inventory_parts_page — parts list/search
- Args:
p_query text = '',p_active boolean = null,p_limit integer = 20,p_offset integer = 0. - Returns: rows
{part_id uuid, legacy_item_id integer, part_no text, barcode text, title text, description text, active boolean, qty_on_hand numeric, qty_on_po numeric, cost numeric, last_updated_at timestamptz, total_count bigint}.total_countis always null in the final definition. - Auth: authenticated EXECUTE; PUBLIC and anon revoked; no active-profile/membership check. Read-only
SECURITY DEFINER. - Behavior: blank query includes all matching active-status rows; otherwise
ILIKEsubstrings against number, barcode, title, description.p_active=nullincludes active and inactive. Order active descending, then part number; offset floored at zero.p_limit=nullremoves the SQL LIMIT; negative limits error. Do not infer unlimited HTTP delivery: deployment/API row caps may still apply.%and_are pattern characters unless escaped. - Client pagination: requests
pageSize+1and discards the lookahead;hasNextPagecomes from the extra row. “All” sends null limit/zero offset and counts returned rows, not an independent database total. Source0019:1supersedes0017; clientinventoryApi.ts:394,455.
4. search_inventory_usage_history — recent movement history
- Args:
p_query text = '',p_limit integer = 100,p_offset integer = 0. - Returns: rows
{movement_line_id uuid, created_at timestamptz, car_id text, part_no text, description text, kind text, quantity_delta numeric, created_by_display_name text}. - Auth: authenticated EXECUTE only; body requires active membership. Read-only
SECURITY DEFINER. - Behavior: non-archived rows from the last six months (
created_at >= now()-interval '6 months'), including when searching. Query trimmed;ILIKEmatches car, part number, description, employee display name. Limit null→100, clamped 1…1000; offset null/negative→0. Order newest first, car ascending, part ascending. No total count or unique-ID tiebreaker; use returnedmovement_line_id, not reconstructed row identity. Query does not separately filter transaction status. - Client: takes absolute delta for displayed quantity and retains signed delta as
inventoryEffect;hasNextPage = rows.length === pageSize, so a full final page can lead to an empty next page. Source0023:7supersedes0020…0022; clientinventoryApi.ts:735.
5. post_inventory_movement — legacy special-location movement
- Args:
p_kind text,p_car_id text,p_lines jsonb,p_notes text = null. Lines:[{part_id:<UUID>,qty:<positive number>,note:<text|null>}]. - Returns: one row
{transaction_id uuid, transaction_no bigint, line_count integer}. - Auth: body requires active membership; final transaction trigger requires active non-viewer for posted check-in/out at special locations and rejects matching read-only preview origins. Authenticated users cannot use manual adjustment to bypass work-order validation.
- Allowed practical use:
p_kind='Checkout'or'Check-in'andp_car_id='NOT PUT ON CAR'or'ITEM RECEIVED'. Kind normalization also recognizescheck_out,checkout,check_in,checkincase-insensitively, with-→_. Normal-car check-in/out fails because this legacy writer cannot populate required validated-work-order metadata. Although the older body acceptsadjustment/manual_adjustmentand a linequantity_delta, that is not an authenticated app write contract after0027. - Effects/safety: atomic header+line inserts, actor from
auth.uid(), ledger trigger adds signed deltas to on-hand (checkout negative; check-in positive). Each line requires a UUID and quantity>0; no stock-availability floor or idempotency key. Never automatically retry an ambiguous response: first reconcile the posted transaction/history and balance. Source0002:139plus0027:6; clientinventoryApi.ts:676.
6. add_inventory_cycle_count — set the open physical count
- Args:
p_identifier text,p_qty numeric(required, zero or positive). - Returns: one row
{cycle_count_line_id uuid, part_id uuid, part_no text, counted_qty numeric, variance_qty numeric}. - Auth: active
manager,admin,cycle_count, orsuper_adminvia finalcan_inventory_cycle_count(). - Effects: uses exact-normalized legacy lookup (therefore still picks one ambiguous match); inserts or overwrites the part's single open
cycle_count_linesrow. Snapshots current on-hand intoexpected_qty_snapshot; variance=count−snapshot; records actor/update time. On update it retains the original count creation time/actor. Does not change on-hand, post reconciliation, or create a count session. Retrying can replace the snapshot and update metadata, so this is not a request-key idempotent operation. Verify the returned part identity. Source0013:186+0016:17; clientinventoryApi.ts:693.
7. archive_inventory_cars — archive selected car/location history
- Args:
p_car_ids text[](non-null/non-empty),p_scope text = 'inactive_cars'. - Returns: one row
{archive_batch_id uuid, archived_line_count integer}. - Auth: active
super_adminonly; PUBLIC/anon EXECUTE revoked. - Effects: creates
archive_batcheswithcreated_by, marks currently unarchived matching lines with timestamp/actor/batch/reason, stores affected count. Car matching uppercases and trims outer spaces; it does not compact internal punctuation/spaces. Scopes:inactive_cars,not_put_on_car,manual; unknown/null scope falls back toinactive_cars. - Safety: “inactive” is a label, not a database check against BIRCH active cars. Preview eligibility is client-side and external-car lookup has a local fallback; validate real eligibility before archival. Does not reverse on-hand; validated posted usage remains in work-order allowance calculations even after archive. Repeating creates another batch, possibly with zero lines; no request-key idempotency. Final source
0027:127replaces the older membership-only/bad-audit-column implementation; clientinventoryApi.ts:704.
8. delete_inventory_usage_line — destructive reversal of legacy usage
- Args:
p_movement_line_id uuid(required). - Returns: one row
{movement_line_id uuid, part_id uuid, quantity_delta numeric, reversed_delta numeric}where reversed_delta=−quantity_delta. - Auth: active
adminorsuper_admin; authenticated EXECUTE only. - Effects: deletes one non-archived ledger line, reversing its balance effect through the movement trigger; missing/already archived raises an error. The transaction header is not deleted. Final
0027trigger rejects any authenticated deletion of a line withbirch_work_order_id, including super-admin calls; use an approved check-in/return route instead. - Safety: non-idempotent success semantics; response loss followed by retry can return “not found.” The client still attempts a raw REST DELETE if the RPC errors, but direct
inventory_movement_linesDML is revoked by0027; do not recommend that fallback. Source0022:113,0027:79,239; clientinventoryApi.ts:809,815.
9. get_inventory_user_admin_rows — activated and staged users
- Args: none (
{}). - Returns: rows
{profile_id uuid, legacy_user_id integer, username text, email citext, display_name text, role inventory_user_role, active boolean, legacy_authorised boolean, legacy_deleted boolean, has_login boolean, legacy_roles text[]}. Profile ID/role/active can be null for staged users; legacy fields can be null for profile-only users. - Auth: active
super_adminonly (body check); authenticated EXECUTE grant. Older PUBLIC/anon grants are not explicitly revoked, but the body still enforces super-admin identity. - Behavior: combines legacy users/roles with matching profiles by linked ID/email/username, then adds unmatched profiles; sorted display name.
has_loginis implemented as profile exists, not a fresh auth-password check. No mutation. Source0013:58; clientinventoryApi.ts:214.
10. set_inventory_user_access — role/active replacement
- Args:
p_profile_id uuid,p_role inventory_user_role,p_active boolean(all required). - Returns: one row
{profile_id uuid, role inventory_user_role, active boolean}. - Auth: active
super_adminonly (body check); authenticated EXECUTE grant. No PUBLIC/anon revoke appears; body enforces the boundary. - Effects: changes the target profile role, active flag, and update time; unknown profile raises an error. Does not create auth accounts, change legacy authorisation flags, or revoke sessions. The SQL does not prevent self-demotion/deactivation or protect the last super-admin; choose the target carefully. Source
0013:151; clientinventoryApi.ts:285(client discards the returned row).
11. reset_inventory_user_password — direct administrative password reset
- Args:
p_profile_id uuid,p_new_password text(non-null, length≥8; client trims first). - Returns:
void. - Auth: authenticated EXECUTE only and active
super_adminbody check. Target profile and auth user must exist; target need not currently be active. - Effects: replaces
auth.users.encrypted_passwordwith bcrypt hash, clearsrecovery_token/recovery_sent_at, updates timestamp. Does not send email or explicitly revoke existing sessions. Never expose this through an anonymous integration or log payloads. Source0024:3; clientinventoryApi.ts:361.
12. create_inventory_user — create or replace/reactivate login
- Args:
p_email text,p_display_name text,p_username text(required argument; may be null),p_password text,p_role inventory_user_role = 'clerk',p_active boolean = true. - Returns: one row
{profile_id uuid, email citext, display_name text, username citext, role inventory_user_role, active boolean}. - Auth: authenticated EXECUTE only and active
super_adminbody check. - Validation/effects: email trimmed/lowercased with a basic
@check; display name nonblank; username trimmed/lowercased or null; password length≥8 (client trims password first). Creates email-confirmed auth user/identity if absent. If email already exists, replaces its password and confirms email if needed; then upserts profile name/email/username/role/active and links matching legacy rows by email or username. Explicit nullp_activebecomes true; nullp_roledoes not mean default and violates profile requirements. No email is sent by this SQL. - Safety: not a harmless “create if absent,” and not request-key idempotent. Inspect the existing target before calling; client wrapper ignores returned row. Source
0025:3; clientinventoryApi.ts:320.
Additional retained RPCs (not referenced by current src/lib)
Source: current client and final ordered SQL definitions.[3][4]
| RPC / exact arguments | Return | Authority and behavior |
|---|---|---|
search_inventory_parts(p_query text='', p_active boolean=null, p_limit integer=null) | Same fields as part-page rows except total_count | 0011:1; authenticated grant retained from 0002; no membership predicate or PUBLIC/anon revoke. Read-only definer search with same four-field ILIKE filter and active/part-number ordering. p_limit is ignored, not clamped. Use the paged RPC for new callers. HTTP row caps can still apply. |
archive_old_inventory_movement_lines(p_before timestamptz=now()-interval '6 months') | One row {archived_line_count integer, archived_before timestamptz} | 0023:119; authenticated EXECUTE only; active super-admin. Sets archive time/actor/reason=age_retention_6_months on unarchived lines strictly older than cutoff; no archive batch, balance reversal, or deletion. Explicit null cutoff matches no rows. No bound prevents choosing a newer cutoff; confirm scope. Search hiding old rows does not invoke this function or prove scheduled archival exists. |
Direct REST resources used by the client
Source: current client and final ordered SQL definitions.[3][4]
These 6 names exhaust literal .from(...) calls in src/lib. Member-protected table/view reads normally require an authenticated active profile; the own-profile exception is noted. The two report views are security_invoker=true, so underlying RLS applies. RLS policies and table privileges are separate requirements; the migration chain is not a full dump of deployment-default table ACLs.
| Resource / method | Selected columns and query shape | Return / important semantics |
|---|---|---|
user_profiles GET | id,email,display_name,role,active; id=eq.<AUTH_USER_ID>; SDK maybeSingle() | Current profile object or null. RLS allows self-read even if inactive, or all profiles to any active Inventory member. Source inventoryApi.ts:254; policies 0001:598, 0013:49. |
parts GET | id,part_no,title,description; separate exact part_no=eq.<IDENTIFIER> and barcode=eq.<IDENTIFIER>; limit 2 each | UUID/text metadata rows, case-insensitive citext equality; no active filter. De-duplicate IDs across both queries and reject more than one unique part. Source inventoryApi.ts:386. |
part_inventory GET | qty_on_hand; part_id=eq.<PART_UUID>; maybeSingle() | Current numeric balance, including negative/fractional values. Missing/invalid balance is unavailable, not zero in the quantity-on-hand path. Source inventoryApi.ts:401. |
inventory_usage_history GET | Fallback ID lookup: movement_line_id,created_at,car_id,part_no,quantity_delta with created_at=in.(...); archive preview: car_id,created_at, car not null, newest first, inclusive ranges of 1000 rows | Detailed all-age non-archived view, unlike the six-month search RPC. Full view also has legacy adjustment/creator IDs, transaction ID/no/kind, part UUID/barcode/description, employee display name, archive time/reason. Employee names resolve current then linked legacy profile/user, then Legacy User <id> / Legacy Import. Sources inventoryApi.ts:761,827, final view 0010:1. |
cycle_count_report GET | Report: part_no,qty_on_hand,qty_cycle_count,variance_qty; Counts: part_id,part_no,title,qty_on_hand,quickbooks_qty,counted_at; order part_no ascending; inclusive ranges of 1000 | All parts, left-joined open count, active movement rollup, latest staged QuickBooks quantity. Full view also has qty_on_car,quickbooks_qty_in_process,quickbooks_total_adjustment,reconciled_at. Count/variance/QuickBooks values may be null. Client displays onPo=0 in Counts as a constant, not a queried on-PO balance. Sources inventoryApi.ts:873,890, view 0001:538. |
inventory_movement_lines DELETE | id=eq.<LINE_UUID>&archived_at=is.null | Legacy fallback after failed delete RPC only. Not an available authenticated write route after 0027 (INSERT/UPDATE/DELETE revoked). Use the authorized RPC and preserve its original error. Source inventoryApi.ts:815. |
The quantity-on-hand client uses exact part-number and barcode queries first; if no exact match, it uses search_inventory_parts_page with limit=2, no active filter, and escaped SQL wildcard characters. It rejects ambiguous matches and fetches the chosen UUID's balance separately (inventoryBalance.ts:18). It deliberately does not use the single-result legacy lookup.
Direct-write permission boundaries, not extra recommended workflows:
partsmetadata—including title, description, barcode, BIRCH mapping, active flag—andpart_inventoryhave manager-policyFOR ALLRLS (manager/admin/super_admin,0001+ final0016). No dedicated metadata-update RPC or direct metadata writer appears in thissrc/libsnapshot.partspart numbers are not unique after0002; negative prices are not prohibited after0003. Do not claim active=false archives a part's movement history. Raw balance edits bypass normal movement workflow even where policy/grants permit them; do not use them to post usage or corrections.cycle_count_sessions/cycle_count_lines: active manager/admin/cycle_count/super_admin can INSERT/UPDATE under0015/0016; insertstarted_by/counted_bymust equal auth UID or be null. DELETE remains manager/admin/super_admin, not cycle_count. The nonnegative table CHECK was removed for legacy compatibility in0004, but the count RPC still rejects negative input. Direct-table policy therefore is not equivalent to the RPC's validation.user_profileswrites are super-admin-policy only (0013); ordinary users do not receive self-update of role/active.archive_batchesretains manager-policy table writes, but inserting/updating a batch does not archive movement lines; actual authenticated line archival requires super-admin through the final RPC/trigger.inventory_transactionsandinventory_movement_lines: explicit INSERT/UPDATE/DELETE revokes for anon and authenticated (0027), regardless of older permissive RLS policies. Do not attempt direct ledger DML, manual-adjustment type shifting, or raw REST delete as a bypass.- Checkout override audit and notification outbox tables: all PUBLIC/anon/authenticated privileges revoked (
0026); service administration only. Override audits are append-only with a rejecting UPDATE/DELETE trigger and restrictive parent FKs (0027). The outbox is mutable delivery state, not an append-only audit table.
Service-only movement and notification RPCs — not browser APIs
Source: current client and final ordered SQL definitions.[3][4]
All 7 below explicitly revoke PUBLIC/anon/authenticated EXECUTE and grant service_role. Keep the service key exclusively in the trusted backend; do not add it to browser code, documents, or example payloads. Service routines accepting p_actor_id trust the server to verify and derive that actor; a browser must not call them with a self-selected actor or work-order assignment. SQL does not contact BIRCH: the checkout backend resolves the current order and enriches quantities first.
Both commit routines have the same exact signature: p_actor_id uuid, p_kind text, p_car_id text, p_birch_work_order_id bigint, p_birch_work_order_no text, p_lines jsonb, p_idempotency_key uuid, p_approved_warnings jsonb='[]', p_notes text=null.
commit_validated_inventory_movement(0029:7, replacing0026): active non-viewer actor; Checkout/Check-in; nonblank car/order number, non-null order ID and request UUID. Lines usepart_id,qty,note,assigned_quantity(assignment may be null); backend validates individual positive quantities and derives assignments from BIRCH. SQL requires non-null unique BIRCH mapping across all Inventory rows; inactive mapped parts remain allowed. It aggregates by part, verifies consistent assignments, serializes per request and car/order/part, and recomputes net usage. Checkout unassigned/overage warnings require exact current approval data; check-ins do not generate these warnings. Returns a JSON object, either{status:'confirmation_required',warnings:[...]}without movement writes, or{status:'committed',idempotent_replay:boolean,transaction_id:uuid,transaction_no:bigint,line_count:integer,notification_outbox_ids:[uuid],warnings:[...]}. Commit atomically writes header, signed lines/balances, warning-approval audits, and one outbox row per unassigned-part audit. SQL only queues notifications; the backend may then send email.commit_corrective_negative_inventory_checkin(0030:7): same actor/order requirements and signature, Check-in only. Backend selects it if any selected check-in part has missing/ambiguous BIRCH mapping; the entire submitted batch uses this path, so every selected part must have a current negative balance and aggregate requested quantity must be positive and ≤ absolute negative balance. Locks each balance; cannot cross zero. SQL itself does not require mapping to be blocked, so route selection is a trusted-server responsibility. Writes validated-source check-in lines markedcorrective_negative_checkin; no override audits or notification rows. Returns the committed object above with empty warnings/outbox IDs; on initial commit alsocorrections:[{part_id,part_no,previous_on_hand,checkin_quantity,projected_on_hand}]; replay does not include corrections.inventory_work_order_net_usage(p_car_id text,p_birch_work_order_id bigint,p_part_id uuid)→ scalarnumeric(0027:92). Internal read helper: max(0, sum of −delta) for posted check-out/check-in, matching compact uppercase alphanumeric car, order and part. Includes archived lines, excludes voided transactions. Called by the validated commit SQL, not directly by currentsrc/libor the Edge Function.claim_inventory_checkout_notification(p_outbox_id uuid=null)→ JSON outbox row or SQL null (0026:415). Claims the oldest due pending/failed row (optionally exact ID), or a sending row claimed >15 minutes ago; usesFOR UPDATE SKIP LOCKED. Sets sending/claim time, increments attempts, clears error. This is a write, not a poll-only read. Returned row fields:id,override_audit_id,transaction_id,part_id,car_id,birch_work_order_id,birch_work_order_no,part_no,part_description,requested_quantity,approving_employee,approved_at,recipient,status,attempt_count,next_attempt_at,provider_message_id,last_error,claimed_at,sent_at,created_at,updated_at,provider_draft_id(last field added in0028).record_inventory_checkout_notification_draft(p_outbox_id uuid,p_provider_draft_id text)→void(0028:11). Trimmed nonblank draft ID required; only sending row, initial assignment or same-value repeat; globally unique non-null draft IDs. Different value/wrong state/missing row errors. Persists draft identity before sending, so retries retain the same provider resource.complete_inventory_checkout_notification(p_outbox_id uuid,p_provider_message_id text)→void(0026:449). For sending/sent rows, sets sent/provider ID/time, clears claim/error; blank provider ID becomes null. No affected-row assertion; unknown ID or other status silently does nothing. Does not send email itself.fail_inventory_checkout_notification(p_outbox_id uuid,p_error text)→void(0026:466). Sending rows only: marks failed, clears claim, truncates error to 1000 chars, schedules retry usingmin(1 hour,1 minute × 2^min(attempt_count,6)); provider draft retained. Unknown ID/other status silently does nothing. Does not undo a committed movement.
Warning/approval JSON (database form): part_id,part_no,part_description,car_id,warning_kind ('overage'|'unassigned'),birch_work_order_id,birch_work_order_no,assigned_quantity (numeric|null),net_used_quantity,requested_quantity,projected_quantity,remaining_quantity (numeric|null),overage_quantity; returned warnings additionally contain approved (which can be null before matching approval). The browser-facing checkout request uses camelCase approval keys; the backend maps them. Never manufacture an approval from stale/local arithmetic.
Idempotency and entry point: the commit routines fingerprint kind, normalized car, order ID/no, notes and sorted line JSON excluding assigned_quantity; actor must also match an existing request key. Reusing a committed key with a different actor/fingerprint errors; matching replay returns original transaction identity without another movement. Reuse the same UUID/payload for a genuine retry, including warning confirmation; use a new UUID only for a deliberately new movement. Browser postWorkOrderMovement calls configured VITE_INVENTORY_CHECKOUT_API_URL with a user bearer token and cookies, or functions.invoke('inventory-checkout',{body}) (POST /functions/v1/inventory-checkout). The latter verifies the token with auth.getUser(token), checks the profile and request origin, enriches lines, chooses one commit RPC, then drains notification outbox IDs. Queued/failed notification delivery is not a reason to repost a committed movement. A transport timeout or lost response alone never proves no commit occurred.
Internal SQL helpers/triggers — not business RPC entry points
Source: current client and final ordered SQL definitions.[3][4]
The remaining 13 final SQL functions are included in coverage so function count is not confused with application API count:
- Role helpers:
is_inventory_member(),is_inventory_manager(),is_inventory_super_admin(),can_inventory_cycle_count()→ scalar boolean; no side effects; exact active-role rules are above. They are definer predicates used by RLS/RPCs, notsrc/libcalls. Do not assume all have explicit anon/PUBLIC revokes. - Normalizers:
normalize_inventory_barcode(value text)andnormalize_inventory_part_no(value text)→ text; immutable SQL, null→empty string, lowercase, strip respectively:and-; no side effects. - Trigger-only, no caller arguments,
RETURNS trigger:set_updated_at,apply_inventory_movement_delta,handle_new_inventory_user,enforce_validated_work_order_movement,enforce_inventory_movement_line_archive_authority,enforce_validated_movement_line_delete_authority,reject_inventory_override_audit_mutation. They respectively timestamp changes; maintain balances on line INSERT/UPDATE/DELETE; bootstrap auth profiles; enforce final movement authority; gate archive-state changes; reject authenticated BIRCH-line deletion; reject audit UPDATE/DELETE. They are not ordinary PostgREST business calls. Some triggers deliberately preserve JWT-less service/import maintenance; that is not browser permission.
SDK and HTTP templates (placeholders; do not run writes as connectivity tests)
Source: current client and final ordered SQL definitions.[3][4]
Read-only SDK example after normal Supabase login; the SDK attaches its current session token:
import { createClient } from '@supabase/supabase-js'
const supabase = createClient('https://<PROJECT_REF>.supabase.co', '<PUBLIC_ANON_KEY>')
const { data: sessionData, error: sessionError } = await supabase.auth.getSession()
if (sessionError || !sessionData.session) throw new Error('Sign in first')
const { data, error } = await supabase.rpc('search_inventory_parts_page', {
p_query: '<PART_SEARCH>', p_active: null, p_limit: 21, p_offset: 0,
})
if (error) throw error
const rows = (data ?? []).slice(0, 20)
const hasNextPage = (data ?? []).length > 20
Equivalent read-only HTTP RPC and current-balance resource requests:
curl --fail-with-body 'https://<PROJECT_REF>.supabase.co/rest/v1/rpc/search_inventory_parts_page' \
-H 'apikey: <PUBLIC_ANON_KEY>' -H 'Authorization: Bearer <USER_ACCESS_TOKEN>' \
-H 'Content-Type: application/json' \
--data '{"p_query":"<PART_SEARCH>","p_active":null,"p_limit":21,"p_offset":0}'
curl --fail-with-body --get 'https://<PROJECT_REF>.supabase.co/rest/v1/part_inventory' \
-H 'apikey: <PUBLIC_ANON_KEY>' -H 'Authorization: Bearer <USER_ACCESS_TOKEN>' \
--data-urlencode 'select=qty_on_hand' --data-urlencode 'part_id=eq.<PART_UUID>'
Write template only — mutates the open cycle count, requires an authorized role and explicit operator intent:
const { data: countRows, error: countError } = await supabase.rpc('add_inventory_cycle_count', {
p_identifier: '<EXACT_PART_NUMBER_OR_BARCODE>',
p_qty: approvedPhysicalCount, // operator-approved numeric value, >= 0
})
if (countError) throw countError
const recordedCount = countRows?.[0] // verify part_id/count; not a stock reconciliation
Before any non-idempotent write: validate target UUID/identity, role and intended effect; record returned IDs; read back the exact affected resource via an authorized path. Do not automatically retry legacy movements, archive batches, user creation/password replacement, or counts after uncertain responses. Do not treat a client error message, zero-row raw DELETE, or successful void notification update as proof that the desired state changed. None of these templates contains a real key, password, token, user UUID, or fabricated execution result.
5. Inventory checkout Edge Function and BIRCH read adapters
Verification scope and critical safety boundary
This contract was checked against the current application revision listed in section 1, including the final SQL overrides. It describes implemented behavior rather than the historical API plan.
There is no HTTP check, submit, preview, or dry-run mode in this implementation. The handler ignores unknown body fields and always proceeds toward the commit RPC after validation. A valid first POST with approvedWarnings: [] can immediately commit if no warning needs approval. Sending mode: "check", mode: "preview", or dryRun: true does not make it read-only.[5][7]
The confirmation_required response branch does not create a movement, override audit, or notification outbox row; that is a conditional result, not a caller-selectable safe-check API. Every valid movement POST must be treated as a mutation and possible email trigger. Check-in is a stock-increasing movement, not a “check” operation.[5][7]
Offline verification passed 353 existing tests and five additional isolated contract checks. These check request dispatch, approvals, notes, and validation with mocked dependencies; they do not post production movements or send mail. SQL behavior is source-reviewed, not database-executed.
The narrow live checks listed in section 1 do not prove a valid production movement, all deployed SQL grants, or notification delivery. No production write/notification test was performed.
Exact services, routes, and source identities
| Surface | Identity / default route | Authority and configuration |
|---|---|---|
| Inventory movement Edge Function | POST https://jjrblgugjmlqkbxrgneq.supabase.co/functions/v1/inventory-checkout | Client default Supabase project + functions.invoke('inventory-checkout'); VITE_SUPABASE_URL can change project.[3][14] |
| Optional client transport override | Exact URL in VITE_INVENTORY_CHECKOUT_API_URL | Client fetches that URL verbatim with JSON, user bearer token when present, and credentials: 'include'; no fixed alternate route is defined.[3] |
| BIRCH work-order list | GET https://birchbackend.ihrailsoftware.com/get_active_workorder_simplified | Hard-coded in Movement selector; Edge resolves relative to optional BIRCH_API_URL base.[5][6] |
| BIRCH work-order detail | GET https://birchbackend.ihrailsoftware.com/get_workorder_by_id?work_id=<id> | Edge-only upstream read after resolving exactly one eligible order.[5] |
| BIRCH full active-car list | POST https://birchbackend.ihrailsoftware.com/get_cars with {"active_only":1} | Read-adapter POST used by fetchActiveCars() / Archive, not the current Movement selector; optional VITE_ACTIVE_CARS_API_URL and VITE_ACTIVE_CARS_API_KEY (X-API-Key).[3][6] |
| Stored transaction provenance | source = 'inventory_app_birch_validated', external_reference = 'BIRCH-WO-' + resolved id | Both regular and corrective service routines write these values.[7][9] |
These are code-derived defaults, not a claim about every deployed environment override. The current list route is not get_active_workorder_simplified_template; older docs/api-plan.md text naming that route is stale relative to activeCars.ts.[6]
Authentication, CORS, and role limits
- Use a signed-in Inventory user's Supabase access token in
Authorization: Bearer <USER_ACCESS_TOKEN>andContent-Type: application/json. For direct Supabase requests also supply the project's public client key inapikey: <PUBLIC_CLIENT_KEY>; the SDK supplies its transport headers. Never put a service-role or Google private key in a client.[5][3] - Function configuration declares
verify_jwt = true; additionally the handler callsauth.getUser(token)and reads that user'suser_profiles.active, role. This is user authentication, not an Inventory internal-API service-key contract; gateway rejections can differ from handler JSON errors.[5][15] - Active
clerk,manager,admin,cycle_count, andsuper_admincan post and acknowledge warnings.viewer, inactive users, and absent profiles cannot. There is no separate manager-only warning-approval step. The Edge predicate is exactlyactive === true && lower(role) !== 'viewer', not an explicit role allowlist; SQL rechecks the database profile. Do not use that implementation detail to invent custom roles.[7][12] - Default browser origins:
https://inventory.ihrailsoftware.comandhttps://inventory-app-40u.pages.dev; configurable through comma-separatedINVENTORY_ALLOWED_ORIGINS. Unique HTTPS*.inventory-app-40u.pages.devpreview origins are rejected before the allowlist, even if configured. Other origins are rejected; absent/blank Origin is permitted but still requires authentication.[5][12] - Allowed preflight:
OPTIONS→ 204. Advertised methods:POST, OPTIONS; allowed headers:authorization, apikey, content-type, x-client-info; max age 600;Vary: Origin. JSON responses useCache-Control: no-store. NoAccess-Control-Allow-Credentialsis set by this handler; a cross-origin custom-URL deployment needs compatible CORS despite the client'scredentials: 'include'.[5][3]
POST request contract
Use canonical camelCase fields; snake_case aliases listed below are also parsed. Actor, work-order identity, assignment quantities, part number/description, and notification destination are server-derived, not client authority.[5][7]
| Field | Contract |
|---|---|
kind | Required. Canonical "Checkout" or "Check-in". Handler lowercases and replaces the first hyphen with underscore; accepted normalized forms are checkout, check_out, checkin, check_in. Surrounding whitespace is not trimmed by its initial validation. |
carId / car_id | Required nonempty trimmed string; supply the actual car number, not a selector option ID or work-order number. Matching normalizes away non-ASCII-alphanumeric characters and uppercases. |
idempotencyKey / idempotency_key | Required UUID matching versions 1–5 and standard variant; use a fresh v4 UUID for each new intended movement. Preserve for confirmation and uncertain-outcome retries. |
lines | Required nonempty array of objects. No explicit line-count cap is imposed here. Each contains partId / part_id (existing Inventory part UUID), qty (positive finite number; fractional allowed), optional note. |
notes | Optional transaction note. Truthy values stringify; falsey values become SQL null. Per-line note behaves similarly. Preserve notes across retries. |
approvedWarnings / approved_warnings | Optional array, default empty. Each item must contain the complete exact warning snapshot below, in camelCase or snake_case. It is not an array of part IDs or booleans. |
The parser numerically coerces quantities, but integrations should send JSON numbers, not rely on coercion. partId is only nonblank-checked at the Edge; database lookup/casting enforces actual identity. Display-only line fields partNo/partDescription are ignored by the Edge. Unknown fields, including mode, dryRun, a client work-order ID, and assigned_quantity on a line, do not change the authority path.[5][3]
Example new checkout — mutating, documentation only
All IDs and values below are illustrative, not a real posting instruction. Replace with authorized real data only when an actual movement is intended.
{
"kind": "Checkout",
"carId": "IHRX4219",
"idempotencyKey": "00000000-0000-4000-8000-000000000001",
"notes": "Authorized repair usage",
"lines": [{
"partId": "00000000-0000-4000-8000-000000000101",
"qty": 2,
"note": "Repair task"
}],
"approvedWarnings": []
}
Example check-in — also mutating
{
"kind": "Check-in",
"carId": "IHRX4219",
"idempotencyKey": "00000000-0000-4000-8000-000000000002",
"notes": "Return unused material",
"lines": [{"partId":"00000000-0000-4000-8000-000000000101","qty":1}],
"approvedWarnings": []
}
Check-in adds positive inventory quantity; Checkout subtracts it. Both resolve the car's current BIRCH work order on each request; this endpoint does not accept a caller-selected historical work order for a return.[5][7]
Current-work-order and part-mapping rules
- Read
get_active_workorder_simplified; compare normalizedrailcar_idto the requested car. A matching order is eligible only iflocked_by === nulland currentworkupdates[0].status_id !== 99. Status/lock metadata must be explicit nonnegative safe-integer numbers or digit-only strings. Missing, boolean, blank, fractional, or otherwise malformed matching-order metadata fails closed.[5][6] - “Current” means the first update supplied by BIRCH, not highest status or a locally sorted date. Historical status 99 does not disqualify an order; current 98 or 900 remains eligible. An explicit lock ID of zero is still locked. There is no additional car blacklist, main-screen status range, or
shipped_datetest in this eligibility code.[5][6] - Require exactly one eligible matching order. No match and multiple matches both block; an eligible real order plus an excluded template is allowed. Two eligible orders for one car stay ambiguous even if the UI shows both options; the submitted value remains the car number.[5][6]
- Read
get_workorder_by_id?work_id=<resolved id>; require detail id, work-order number, and normalized car to match the list identity, then recheck eligibility. List/detail errors, timeouts, changed identity, new lock/template status, and malformed metadata block before the commit RPC. Edge upstream reads each have a 20-second timeout.[5] - Resolve Inventory
parts.id → parts.birch_id; require a unique mapping across all Inventory rows, not just active ones. Inactive mapped parts are allowed. Checkout rejects unmapped/ambiguous Inventory parts; an unassigned warning instead means a uniquely mapped BIRCH part is absent from that order's job parts.[5][7][17] - Assignment equals the sum of matching
joblist[].jobparts[].quantityusing stable numericpart_id, not a text part-code match. No match returns null (unassigned), while explicit zero remains assigned zero. Invalid non-finite matching quantities throw. The helper also treats a non-array/missing job list as no match and skips missing/non-array jobparts; do not claim comprehensive BIRCH-schema validation.[16] - For Check-in, any missing/ambiguous part mapping routes the entire request through
commit_corrective_negative_inventory_checkin. Every selected part must have a locked negative on-hand balance, and its aggregated requested quantity must not exceed the amount needed to reach zero. Mixed requests therefore apply that restriction to mapped lines too. This path still requires the current work order and active non-viewer; it creates no checkout warning/email.[5][9]
Warning calculation and explicit employee approval
The latest inventory_work_order_net_usage definition is in migration 0027, superseding 0026: net used is max(0, sum(-quantity_delta)) for posted Checkout/Check-in lines with the same normalized car, BIRCH work-order ID, and Inventory part. Archived lines still count. Voided/non-posted transactions, other orders, other parts, and legacy lines without that work-order ID do not count. Archiving must not restore allowance.[8]
The service routine aggregates duplicate requested part lines, serializes by idempotency key and car/work-order/part, and computes warnings only for Checkout. projected = net_used + requested; absent assignment → unassigned; projected above assigned → overage; otherwise no warning. remaining = assigned - net_used may be negative; overage is floored at zero. Standard mapped Check-in is not capped to prior net usage by this routine; net usage itself is floored at zero.[7][8]
A warning result is HTTP 200, for example (synthetic values):
{
"status": "confirmation_required",
"warnings": [{
"warning_kind": "overage",
"part_id": "00000000-0000-4000-8000-000000000101",
"part_no": "QA-PART",
"part_description": "Synthetic example part",
"car_id": "IHRX4219",
"birch_work_order_id": 4250,
"birch_work_order_no": "WO-EXAMPLE",
"assigned_quantity": 5,
"net_used_quantity": 4,
"requested_quantity": 2,
"projected_quantity": 6,
"remaining_quantity": 1,
"overage_quantity": 1,
"approved": null
}]
}
For unassigned, assigned_quantity and remaining_quantity are null and overage_quantity is zero. Raw SQL approved can be null when no approval exists; the frontend normalizes that to false.[3][7]
Approval flow:
- Display every returned warning and all its identity/quantity fields to the employee. Cancel means do not retry; no movement was created by the confirmation-required branch.[7]
- After explicit employee approval, resend the same original request, including the same idempotency key, kind, car, notes, and lines, adding the exact complete snapshot for each approved warning. The parser accepts the returned snake_case objects directly, or equivalent camelCase objects; response
approvedis ignored as input and is not itself authorization.[5][7] - Example retry-body construction only:
const retryBody = { ...originalRequest, approvedWarnings: explicitlyApprovedWarningSnapshots };. Do not auto-approve all warnings or recompute their numbers. The accepted camelCase names arepartId,partNo,partDescription,carId,warningKind,birchWorkOrderId,birchWorkOrderNo,assignedQuantity,netUsedQuantity,requestedQuantity,projectedQuantity,remainingQuantity, andoverageQuantity.[5] - All strings above must be nonblank, kind must be
overage/unassigned, work-order ID a safe integer, all quantities finite except the two nullable fields. SQL recomputes the snapshot and compares every field. Any unapproved/stale warning yields a freshconfirmation_requiredfor the whole request: re-display and obtain fresh approval. Nothing is partially posted.[5][7] approvedWarningsis deliberately excluded from the movement fingerprint, as are server-computed assignment quantities. Kind, normalized car, resolved work-order id/number, transaction note, and the sorted line payload (including per-line notes/quantities) define the request. An already-committed key with a changed fingerprint or actor errors; same actor/fingerprint returns the original committed transaction withidempotent_replay: trueandwarnings: [].[7]- A confirmation-only attempt has not reserved a transaction/key. Nevertheless keep the same key for that intended movement; generate a new key only for a new intended movement, not to evade conflict/uncertain outcomes. Edge BIRCH/mapping validation occurs before SQL replay lookup, so an upstream outage or changed current work order can prevent replay. A committed replay can still retry notification delivery; it is not read-only.[5][7]
Result and error contracts
Successful commit JSON includes status: "committed", transaction_id (UUID), transaction_no (number), line_count (submitted line count), idempotent_replay (boolean), warnings (array), notification_outbox_ids (UUID strings), and Edge-added notifications (array). The corrective first commit additionally returns corrections: [{part_id, part_no, previous_on_hand, checkin_quantity, projected_on_hand}]; corrective replays omit corrections.[5][7][9]
| HTTP | Handler meaning / representative error |
|---|---|
| 200 | confirmation_required or committed; inspect status, not HTTP alone. An unexpected non-object RPC result becomes {"status":"error"} at 200; frontend rejects unknown statuses.[5][3] |
| 204 | Accepted-origin OPTIONS; handler performs no movement/auth/database work for it.[5] |
| 400 | Default caught validation/SQL/upstream/follow-up error: malformed JSON/object/lines/approvals, unsupported kind, blank car, invalid idempotency key, missing/duplicate work order, identity/eligibility/mapping failure, BIRCH timeout or HTTP error, missing parts, corrective balance limits, or idempotency conflict. No dedicated 409 conflict or 502/504 upstream status is assigned.[5][7][9] |
| 401 | Authentication is required. or Inventory session is invalid or expired.[5] |
| 403 | Active non-viewer Inventory access is required to post movements., preview read-only message, or unknown-origin message. Origin validation runs before method handling.[5] |
| 405 | Method not allowed. for methods other than POST/OPTIONS after origin acceptance.[5] |
| 503 | Inventory movement authority is not configured. (missing server Supabase URL/key), or Inventory membership could not be verified: ... for profile-query error.[5] |
Handler errors are {"error":"..."}. Once kind is validated, pre-commit errors normally add “This Checkout/Check-in was not posted, and Inventory quantities were not changed.” A notification-claim failure after a successful commit can instead return HTTP 400 saying the movement WAS posted and quantities updated: do not post again; contact support. Status alone does not prove no mutation. A transport failure can also leave outcome unknown: reconcile by transaction/idempotency key rather than create a new request.[5][17]
Notifications and delivery authority
- Approved unassigned Checkout creates one outbox row per warned part and an append-only override audit atomically with the movement. Overage approval creates an audit but no email outbox; mapped Check-in and corrective Check-in do not send these emails.[7][8][9]
- Default outbox recipient is
morgan@ihrail.com. The current handler uses delegated Gmail, default userscrandell@ihrail.com, default senderIron Horse Inventory <scrandell@ihrail.com>. Server variables areGOOGLE_SERVICE_ACCOUNT_JSON_BASE64,GMAIL_DELEGATED_USER, andINVENTORY_NOTIFICATION_FROM. Do not expose their secret values.[5][10] notifications[]item states:{"status":"sent","outboxId":"..."}after delivery/completion;{"status":"queued","outboxId":"..."}after missing Gmail authority or a caught send/completion failure;{"status":"empty"}if nothing could be claimed.emptyis not proof that email was sent; a row can be ineligible/in progress/not due. No outboxes →notifications: [].[5]- Database statuses are separately
pending,sending,sent,failed. Claim increments attempts, leasessending, and can reclaim after 15 minutes; failed rows have exponential retry delay capped at one hour. The shown handler drains only outbox IDs returned by a committed response; it has nodrainmode or scheduler guarantee.[5][10] - Gmail uses
gmail.compose+gmail.readonly, creates a draft, persists its ID before sending, reuses that draft on retry, and reconciles deterministic Message-ID against Sent mail after ambiguous send failure. This is duplicate-delivery protection in code, not proof of actual delivery; ordinary caught delivery errors leave the inventory commit intact and return queued.[5][11][18]
BIRCH read-adapter contracts and practical read examples
These calls are read operations as used by the current code; their contracts were source-reviewed, not re-executed for this document. In particular, POST /get_cars is a lookup POST, unlike the mutating Inventory movement POST. The work-order list envelope is {active_workorder: [...]}; detail is a root object with id, work_order, railcar_id, locked_by, workupdates, and joblist rather than a data wrapper. List/detail requests add Accept but no BIRCH authorization/API-key header in this code; that does not independently verify the upstream service's access policy.[5][6]
## 5. Movement eligibility list; no request body or BIRCH auth header added by the adapter.
curl --fail-with-body --max-time 20 \
-H 'Accept: application/json' \
'https://birchbackend.ihrailsoftware.com/get_active_workorder_simplified'
## 5. Detail: substitute the ID resolved from the preceding list, not a guessed work order.
curl --fail-with-body --max-time 20 \
-H 'Accept: application/json' \
'https://birchbackend.ihrailsoftware.com/get_workorder_by_id?work_id=<RESOLVED_ID>'
## 5. Full active-car read used for Archive/shared lookup, NOT Movement eligibility.
curl --fail-with-body --max-time 20 -X POST \
-H 'Accept: application/json' -H 'Content-Type: application/json; charset=utf-8' \
--data '{"active_only":1}' \
'https://birchbackend.ihrailsoftware.com/get_cars'
fetchMovementCars() requires {active_workorder: [...]}, validates every row's nonblank normalized railcar_id, first status and explicit lock metadata, filters eligibility per order, then sorts/normalizes cars. It preserves multiple eligible rows per car with unique selector IDs; displayId remains the car number. On an upstream HTTP/network/JSON/schema/15-second timeout failure it returns source: 'local', an error, and only the two special locations—never fabricated sample cars. An empty/all-filtered valid list returns source: 'api' and those two locations without error.[6]
fetchActiveCars() accepts a bare array or arrays under data, cars, activeCars, active_cars, active_workorder, railcarIds, results, or items (including nested envelopes). Rows can be strings/numbers or objects with recognized car-ID fields such as rfid, car_id, carId, car, car_number, carNumber, railcar, railcar_id, railcarId, or nested car/railcar IDs. Normalized rows have id, displayId, searchText and optional track/spot/customer/status/carType/source. Non-2xx, invalid JSON, or zero recognizable cars returns source: 'local', an error, and three hard-coded sample cars plus special locations. Treat those samples as fallback UI data, not live operational authority. This loader has no explicit fetch timeout.[6]
Both append NOT PUT ON CAR and ITEM RECEIVED, with source: 'legacy-option'. Those are not BIRCH car orders. The legacy post_inventory_movement RPC is retained for these special locations; normal-car Checkout/Check-in must use validated authority. Direct transaction/line REST writes are revoked, authenticated manual-adjustment bypass is blocked, and authenticated deletion of BIRCH-validated lines is blocked. Correct usage with a legitimate check-in/return, not by deleting or archiving allowance history.[6][8]
Preview and server-only/SQL-only operations
Synthetic preview is frontend interception, requiring VITE_WORK_ORDER_PREVIEW_MODE=synthetic-read-only plus a unique *.inventory-app-40u.pages.dev hostname. postWorkOrderMovement() calls simulateWorkOrderPreview() instead of transport; scenarios are PREVIEW-OVERAGE and PREVIEW-UNASSIGNED. Its synthetic committed/notification.status: simulated results are not database or email evidence. Canonical Pages/custom production hosts remain live; the build flag alone does not make them read-only. Independently, the Edge denies unique preview origins.[3][12][13]
Do not publish server-internal routines as employee HTTP APIs: commit_validated_inventory_movement, commit_corrective_negative_inventory_checkin, inventory_work_order_net_usage, claim_inventory_checkout_notification, record_inventory_checkout_notification_draft, complete_inventory_checkout_notification, and fail_inventory_checkout_notification are granted only to service_role, with public/anon/authenticated execution revoked. They are SQL-defined service RPCs, not safe client-check endpoints; claim/fail/complete/draft-record operations mutate state. Trigger functions such as enforce_validated_work_order_movement are SQL/trigger-only internals, not callable integration routes. Do not bypass the Edge by manually supplying trusted work-order/assignment values or expose service-role credentials.[8][9][10]
Code references: Edge parser/auth/dispatch index.ts:116–185,426–531; resolver/mapping/notifications index.ts:205–423; browser contract inventoryApi.ts:514–665; adapters activeCars.ts:21–166,206–251; latest commit/usage/correction SQL 0029:7–310, 0027:92–121, 0030:7–287. Live mutation behavior and notification-provider permissions were not tested for this document.
6. Integration checklist and support handoff
- Choose reporting access versus a named Inventory user session; keep their credentials separate. Start with a read-only health/data call, never a movement or password-reset test.
- Use Inventory UUIDs for writes, not legacy item numbers, display labels, or BIRCH part IDs. Preserve returned transaction/line IDs.
- Implement both
confirmation_requiredandcommittedbranches. Obtain explicit employee approval for the exact warning snapshot; never add a fake dry-run flag. - Retain the same movement idempotency key and exact request on genuine retries. An HTTP error or notification failure can happen after a successful commit. Reconcile uncertain outcomes before sending anything new.
- Keep date-zone conversion, numeric signs, filtering, and pagination explicit. Archived usage still counts toward validated current-work-order allowance.
- Before any direct database operation not used by the current client, confirm the actual deployed grants and approved business workflow; a table name or historical plan is not permission.
- For support, provide the UTC request time, method/path, redacted parameters, HTTP status, returned transaction ID and idempotency key (when applicable), and whether the result said committed. Never include bearer tokens, passwords, recovery URLs, or private keys.
Not implemented as standalone application endpoints
The older API plan's rpc_* names and proposed export, reconciliation, and BIRCH-sync Edge Functions are not an implemented HTTP suite in this source. Client-side export utilities and operator scripts are not public write endpoints. Use the exact routes/RPC names in this guide, and do not invent /api/internal/inventory/checkout, a /check mode, or a bulk-write API.[3][4][5]
Revision notes
This revision replaces the June reporting-only sheet. It adds all client RPCs and direct resources, the retained/service-only distinctions, Checkout/Check-in authority, October work-order eligibility behavior, corrective negative-balance Check-in constraints, current-work-order allowance rules, explicit warning approval, error/retry semantics, and source/verification boundaries. No credential values or live inventory row examples are included.
Sources
[1] https://github.com/dgentry-ihr/inventory-app/blob/a2d608870d0d16ca0487d15526cd0eb7bd5d44c0/internal-api/server.mjs — Running internal API implementation [2] https://github.com/dgentry-ihr/inventory-app/blob/a2d608870d0d16ca0487d15526cd0eb7bd5d44c0/internal-api/supabase-session.mjs — Internal API authentication session recovery [3] https://github.com/dgentry-ihr/inventory-app/blob/802a3d014677e6a0e1303eb3a0cc54042d36c394/src/lib/inventoryApi.ts — Current Inventory client API [4] https://github.com/dgentry-ihr/inventory-app/tree/802a3d014677e6a0e1303eb3a0cc54042d36c394/supabase/migrations — Database migrations 0001–0030 [5] https://github.com/dgentry-ihr/inventory-app/blob/802a3d014677e6a0e1303eb3a0cc54042d36c394/supabase/functions/inventory-checkout/index.ts — Checkout Edge Function [6] https://github.com/dgentry-ihr/inventory-app/blob/802a3d014677e6a0e1303eb3a0cc54042d36c394/src/lib/activeCars.ts — Current BIRCH car and work-order selectors [7] https://github.com/dgentry-ihr/inventory-app/blob/802a3d014677e6a0e1303eb3a0cc54042d36c394/supabase/migrations/0029_allow_inactive_mapped_part_checkout.sql — supabase/migrations/0029_allow_inactive_mapped_part_checkout.sql [8] https://github.com/dgentry-ihr/inventory-app/blob/802a3d014677e6a0e1303eb3a0cc54042d36c394/supabase/migrations/0027_enforce_birch_movement_authority.sql — supabase/migrations/0027_enforce_birch_movement_authority.sql [9] https://github.com/dgentry-ihr/inventory-app/blob/802a3d014677e6a0e1303eb3a0cc54042d36c394/supabase/migrations/0030_allow_negative_corrective_checkin.sql — supabase/migrations/0030_allow_negative_corrective_checkin.sql [10] https://github.com/dgentry-ihr/inventory-app/blob/802a3d014677e6a0e1303eb3a0cc54042d36c394/supabase/migrations/0026_birch_work_order_checkout_validation.sql — supabase/migrations/0026_birch_work_order_checkout_validation.sql [11] https://github.com/dgentry-ihr/inventory-app/blob/802a3d014677e6a0e1303eb3a0cc54042d36c394/supabase/functions/_shared/googleDelegatedGmail.ts — supabase/functions/_shared/googleDelegatedGmail.ts [12] https://github.com/dgentry-ihr/inventory-app/blob/802a3d014677e6a0e1303eb3a0cc54042d36c394/supabase/functions/_shared/checkoutRequestPolicy.ts — supabase/functions/_shared/checkoutRequestPolicy.ts [13] https://github.com/dgentry-ihr/inventory-app/blob/802a3d014677e6a0e1303eb3a0cc54042d36c394/src/lib/workOrderPreview.ts — src/lib/workOrderPreview.ts [14] https://github.com/dgentry-ihr/inventory-app/blob/802a3d014677e6a0e1303eb3a0cc54042d36c394/src/lib/supabase.ts — src/lib/supabase.ts [15] https://github.com/dgentry-ihr/inventory-app/blob/802a3d014677e6a0e1303eb3a0cc54042d36c394/supabase/functions/inventory-checkout/config.toml — supabase/functions/inventory-checkout/config.toml [16] https://github.com/dgentry-ihr/inventory-app/blob/802a3d014677e6a0e1303eb3a0cc54042d36c394/supabase/functions/_shared/workOrderRules.ts — supabase/functions/_shared/workOrderRules.ts [17] https://github.com/dgentry-ihr/inventory-app/blob/802a3d014677e6a0e1303eb3a0cc54042d36c394/supabase/functions/_shared/inventoryPartMappingPolicy.ts — supabase/functions/_shared/inventoryPartMappingPolicy.ts [18] https://github.com/dgentry-ihr/inventory-app/blob/802a3d014677e6a0e1303eb3a0cc54042d36c394/supabase/migrations/0028_persist_gmail_draft_delivery.sql — supabase/migrations/0028_persist_gmail_draft_delivery.sql