Library Style Inventory (Tracked Assets)

Library-Style Inventory (Tracked Assets)

Overview

Library-style inventory extends the Inventory Tracker with per-unit asset tracking. Where the consumable model just decrements a quantity, the asset model tracks each individual unit ("Camera #3", "Drill #1") with its own QR label and check-in / check-out loan history.

A single inventory_items row can be either a consumable (quantity bucket) or a tracked asset (parent of N inventory_assets). The choice is made at item creation via a tracking_type column and is locked after the item exists.

Components:

• src/features/tools/components/InventoryTracker.tsx — main list, gets tracking_type filter + "Checked Out" stat tile + "Manage Assets" row action.
• src/features/tools/components/inventory/AssetsPanel.tsx — staff-facing Sheet for an item's assets (CRUD, check-out / check-in, regenerate token, print label).
• src/features/tools/components/inventory/AssetQrLabelDialog.tsx — preview + Avery 5160 PDF export (30 labels / sheet, no MintBucks branding).
• src/components/shared/PublicAssetQrLanding.tsx — public landing page reached by scanning an asset QR.
• src/lib/db/inventory-assets.ts — DB module (playbook §17.2 / §17.5 compliant).
• src/lib/assetCheckInQr.ts — buildAssetQrUrl, extractAssetQrPayload, and extractAssetIdFromQrValue. Reuses the shared extractCheckInQrPayload from src/lib/checkInQr.ts.
• supabase/functions/asset-qr-action/index.ts — verify_jwt: false Edge Function for public lookup / check-in and JWT-gated check-out.

Database

inventory_items.tracking_type

| Value | Behavior |

|-------|----------|

| `consumable` (default) | Existing quantity-based behavior. `quantity`, `has_sizes`, `sizes`, `low_stock_threshold` apply. |

| `asset` | Quantity / size / threshold are forced to `0` / `false` on insert. Counts in the table come from `inventory_assets` and `inventory_loans`. |

inventory_assets

| Column | Type | Notes |

|--------|------|-------|

| `id` | UUID | PK, default `gen_random_uuid()`. |

| `inventory_item_id` | UUID | FK → `inventory_items` (parent), `ON DELETE CASCADE`. |

| `organization_id` | UUID | RLS scope. |

| `asset_tag` | TEXT | Required. Unique per `(organization_id, asset_tag)`, e.g. `CAM-003`. |

| `serial_number` | TEXT | Optional manufacturer serial. |

| `status` | TEXT | `available` / `checked_out` / `maintenance` / `lost` / `retired` (CHECK constraint). |

| `condition` | TEXT | `new` / `good` / `fair` / `poor` / `damaged` (CHECK constraint). |

| `notes` | TEXT | Optional. |

| `image_url` | TEXT | Optional per-asset photo override. UI falls back to the parent item's `image_url` when null. |

| `qr_token` | TEXT | Opaque random capability token printed on the QR label. **UNIQUE.** Auto-minted by the `prepare_inventory_asset` BEFORE INSERT trigger if the caller leaves it blank. |

| `created_by` | UUID | Optional. |

| `created_at` / `updated_at` | TIMESTAMPTZ | Standard. |

inventory_loans

One row per check-out, closed when returned.

| Column | Type | Notes |

|--------|------|-------|

| `id` | UUID | PK, default `gen_random_uuid()`. |

| `asset_id` | UUID | FK → `inventory_assets`, `ON DELETE CASCADE`. |

| `organization_id` | UUID | RLS scope. |

| `borrower_user_id` | UUID | Optional FK → `auth.users` (for in-org borrowers). |

| `borrower_name` | TEXT | Required for `check_out_inventory_asset`. Free text — borrower may be external. |

| `borrower_contact` | TEXT | Optional email/phone. |

| `checked_out_at` | TIMESTAMPTZ | Set by RPC. |

| `checked_out_by` | UUID | Optional FK → `auth.users` (the staff member who created the loan). |

| `due_at` | TIMESTAMPTZ | Optional. |

| `returned_at` | TIMESTAMPTZ | NULL while loan is open; set by `check_in_inventory_asset`. |

| `returned_to` | UUID | Optional FK → `auth.users` (who received the return). |

| `return_condition` | TEXT | Mirrors the `condition` enum; defaulted into the asset's `condition` on check-in. |

| `notes` | TEXT | Single notes column — used for both check-out notes and check-in notes (later replaces earlier when both are non-empty). |

Invariant — at most one *open* loan per asset is enforced by a partial unique index idx_inventory_loans_open_per_asset on (asset_id) WHERE returned_at IS NULL. A second supporting index idx_inventory_loans_overdue on (organization_id, due_at) WHERE returned_at IS NULL powers the overdue stat. idx_inventory_loans_asset_history orders the per-asset history view.

RLS

Both tables follow the standard org-scoped pattern: any organization_users member can read; only read_write members can directly INSERT/UPDATE/DELETE. Public flows (anonymous check-in via scan) write via the SECURITY DEFINER RPCs through the Edge Function — never directly.

RPCs

All RPCs are LANGUAGE plpgsql with SET search_path TO 'public' (the token generator additionally includes 'extensions' so it can resolve extensions.gen_random_bytes). Check-out / check-in / regenerate are SECURITY DEFINER and validate qr_token themselves.

| RPC | Args | Returns | Grants |

|-----|------|---------|--------|

| `gen_inventory_asset_qr_token()` | — | `text` (24-char base62-ish) | implicit (called by the trigger / other SECURITY DEFINER functions). |

| `check_out_inventory_asset(p_asset_id uuid, p_qr_token text, p_borrower_name text, p_borrower_contact text DEFAULT NULL, p_borrower_user_id uuid DEFAULT NULL, p_due_at timestamptz DEFAULT NULL, p_checked_out_by uuid DEFAULT NULL, p_notes text DEFAULT NULL)` | `TABLE(loan_id uuid)` | `authenticated`, `service_role`. |

| `check_in_inventory_asset(p_asset_id uuid, p_qr_token text, p_return_condition text DEFAULT NULL, p_returned_to uuid DEFAULT NULL, p_notes text DEFAULT NULL)` | `TABLE(loan_id uuid)` (NULL when the asset was already available — idempotent) | `anon`, `authenticated`, `service_role` (anon path is the public scan return flow). |

| `regenerate_inventory_asset_qr_token(p_asset_id uuid)` | `text` (new token) | `authenticated`, `service_role`. Invalidates all previously printed labels for this asset. |

prepare_inventory_asset is a BEFORE INSERT/UPDATE OF qr_token trigger that mints a token via gen_inventory_asset_qr_token() whenever one is missing.

QR URL Scheme

Asset QR codes follow the existing /qr/{kind}/{uuid}?t={token} pattern; dispatch happens in src/main.tsx (QrLandingDispatcher) on the shared /qr/:qrType/:recordId route.

https://app.alignmint.app/qr/asset/{asset_id}?t={qr_token}

• qrType is asset (joining the existing event and volunteer kinds — see src/lib/checkInQr.ts).
• recordId is the inventory_assets.id UUID.
• t is the opaque qr_token (rotated by regenerate_inventory_asset_qr_token).
• Building the URL is centralized in src/lib/assetCheckInQr.ts → buildAssetQrUrl. The token is mandatory at build time — the function throws if you pass an empty token, which is a deliberate guardrail against minting unsigned labels.
• Parsing is centralized in extractAssetQrPayload (returns { id, token }). extractAssetIdFromQrValue is a thin wrapper for callers that only need the id.

> Asset QR labels do not use the qr_codes table. That table is reserved for marketing-style trackable links. Labels render via AlignmintBrandedQr for the on-screen preview and pdf-lib + the qrcode package for the Avery 5160 PDF.

Edge Function — asset-qr-action

supabase/functions/asset-qr-action/index.ts (verify_jwt: false, registered in supabase/config.toml under [functions.asset-qr-action]) is the single public entry point for QR scans.

Request body:

{
  action: 'lookup' | 'check_in' | 'check_out';
  asset_id: string;       // UUID, validated server-side
  qr_token: string;       // mandatory for every action
  // check_out only:
  borrower_name?: string;
  borrower_contact?: string;
  borrower_user_id?: string; // UUID
  due_at?: string;        // ISO 8601
  notes?: string;
  // check_in only:
  return_condition?: 'new' | 'good' | 'fair' | 'poor' | 'damaged';
}

| `action` | Auth | Behavior |

|----------|------|----------|

| `lookup` | Public + token | Returns the asset, parent item summary, current open loan (if any), org name + logo. Used by `PublicAssetQrLanding` to render status. |

| `check_in` | Public + token | Closes the open loan via `check_in_inventory_asset` RPC. Anyone with the printed QR can return an item — this is a feature, not a bug. Idempotent: returns OK with `loan_id: null` when the asset was already available. |

| `check_out` | JWT (read_write org member) **+ token** | Calls `check_out_inventory_asset` with `p_checked_out_by = staff.userId`. Returns 401 with status `unauthorized` if no JWT or caller is not a `read_write` member of the asset's org. Returns 409 with status `unavailable` when the asset is not currently `available`. |

All paths require the qr_token to match. If it doesn't match, the function returns invalid and the landing page shows the "link invalid or expired" state — this is the rotation-revocation mechanism for lost or replaced labels.

The same RPCs are also called directly (with the user's JWT, via the regular supabase.rpc(...) client) from AssetsPanel.tsx for the in-app staff workflow — RLS already restricts reads to org members.

Response shape — PublicAssetReceipt:

{
  status: 'available' | 'checked_out' | 'maintenance' | 'lost' | 'retired'
        | 'just_checked_in' | 'just_checked_out'
        | 'invalid' | 'unauthorized' | 'unavailable';
  asset?: { id, asset_tag, serial_number, condition, image_url };
  item?: { id, name, description, image_url, location, category };
  open_loan?: { id, borrower_name, borrower_contact, checked_out_at, due_at } | null;
  organization?: { name, logo_url, parent_logo_url };
  message?: string;
}

UI Surfaces

InventoryTracker.tsx (modifications)

• New Tracking Type filter Select next to Category / Status filters. (Note: the existing inventory filter bar uses Selects rather than Popovers; the new asset-type filter is added in the same style. The STYLING-GUIDE Popover filter standard applies to *new* filter pages, not to surgical additions inside an existing inline filter bar — see plan §audit-finding 4.)
• New Checked Out stat tile, expanding the grid to 5 tiles (grid-cols-2 sm:grid-cols-5).
• "Qty" cell shows {open_loans} / {total_assets} for asset rows; consumable rows are unchanged. Counts are batched via fetchOpenLoanCountsByItem / fetchAssetCountsByItem to avoid N+1.
• Row action menu adds Manage Assets for asset items, opening the AssetsPanel Sheet.
• Add/Edit dialog adds the Tracking Type Select; quantity / sizes / threshold fields hide when asset is chosen and the Select is locked after the item exists (disabled={Boolean(editingItem)}) since flipping the type would orphan loans.

AssetsPanel.tsx (Sheet)

• Status filter Select (All / Available / Checked Out / Maintenance / Lost / Retired) and Print all labels action.
• Per-asset card with status badge, current borrower, due date, and a 3-dot menu (MoreHorizontal): Edit, Print Label, Regenerate QR Token, Delete.
• Inline Check Out button opens a Dialog with borrower name (required), contact, due date, and notes.
• Inline Check In button closes the open loan via checkInInventoryAsset (no extra dialog — the public scan flow is the place to capture return condition; the in-app shortcut is intentionally one-tap).
• The Edit dialog's Status Select hides checked_out (only the Check Out button can flip the asset to that state, since it must atomically create a loan row). When the asset is currently checked out the Select is disabled with a hint to check the asset back in first.

AssetQrLabelDialog.tsx

• Preview grid of up to 30 AlignmintBrandedQr labels for the selected assets (each label renders the public QR URL with token).
• "Download PDF Sheet" generates an Avery 5160 sheet (3 cols × 10 rows on US Letter, 1" × 2⅝" labels) — pure pdf-lib + qrcode PNGs at error-correction level H and dark = #0f1f3d. No MintBucks artwork.

PublicAssetQrLanding.tsx

• Routed via /qr/asset/:assetId?t=... through the QrLandingDispatcher switch in src/main.tsx (the dispatcher reads useParams().qrType and forwards asset to this component, all other kinds to PublicCheckInQrLanding).
• Mirrors PublicCheckInQrLanding (Card shells, status icons, friendly date formatters).
• On mount, calls lookup. Renders a Mark as Returned form when the asset is currently checked out (condition select + notes), or a Sign In to Check Out CTA (deep linked to /?inventoryAssetId=…) when available. There is no anonymous check-out path by design.

Permissions

• Read: any org member (RLS).
• Write (asset CRUD, in-app check-out / check-in, regenerate token): read_write org membership (RLS + edge-function authorization for the public surface).
• Public check-in via scan: anyone holding the current qr_token.
• Public check-out via scan: never — always requires JWT + read_write membership of the asset's organization.

Tier Access

• Free for all tiers (matches the parent Inventory Tracker).

i18n

• Component-facing keys live under tools.pages.inventoryAssets.* in src/i18n/locales/en/tools.json (synced to all locales via scripts/i18n-sync-locales.js).
• Public-facing landing keys live in src/i18n/locales/en/publicAsset.json (registered as the publicAsset namespace in src/i18n/index.ts; stub files exist for every supported locale and are populated by the same sync script).
• Action verbs (Save, Cancel, Close, Create) reuse actions.* per the i18n collision policy. Note: because nsSeparator: '.' is set in src/i18n/index.ts, t('actions.cancel') from a useTranslation('tools') hook resolves through i18next's namespace fallback rather than as a nested tools.actions.cancel key. This pattern matches existing usage elsewhere in the app (e.g. MileageTracker.tsx).

Operational Notes

• pgcrypto schema gotcha. Supabase installs pgcrypto into the extensions schema. Functions with a restrictive search_path (e.g. SET search_path TO 'public') cannot resolve bare gen_random_bytes(...). The gen_inventory_asset_qr_token function fully qualifies the call as extensions.gen_random_bytes(24) and includes 'extensions' in its search path. If you author a new RPC that needs cryptographic randomness, follow the same pattern (or add extensions to the search path explicitly).
• Idempotent check-in. Calling check_in_inventory_asset on an asset that is already available returns loan_id: null and silently sets status to available if it had drifted (e.g. manual maintenance toggle). The Edge Function surfaces this as just_checked_in with no error.
• Token rotation. regenerate_inventory_asset_qr_token mutates qr_token in place. Any printed labels with the previous token become permanently unusable — there is no undo. The UI surfaces this with a destructive warning.

Related Docs

• 13-INVENTORY-TRACKER.md — consumable-mode behavior and base CRUD.
• 10-QR-CODE-GENERATOR.md — generic marketing QR system. Asset QRs intentionally do not use that system or the qr_codes table.
• src/lib/checkInQr.ts / src/lib/assetCheckInQr.ts — shared QR payload extractor and asset-specific helpers.

Synced from IFMmvp-Frontend documentation: pages/tools/17-INVENTORY-ASSETS.md