Data & storage

Facts verified 2026-09-15 by claude-code/2026.09. Generated from the authored source at docs/llm-wiki/data-and-storage.md — the wiki owns these facts (ADR: D1 of docs/plans/20260811_documentation_system_execution.md). Edit that file, then regenerate: python docs/portfolio/_build/generate_wiki_pages.py.

Supabase (auth + database)

  • Postgres with row-level security (RLS); auth via session cookies refreshed in web/middleware.ts.
  • Three clients in web/lib/supabase/: the browser client (client.ts — holds the auth session, talks to Supabase directly), the server client (server.ts — createServerClient, used by route handlers to verify the caller with supabase.auth.getUser()), and the admin/service-role client (bypasses RLS — privileged operations only).
  • Generated database types: web/lib/database.generated.ts (regenerate with web/scripts/generate-supabase-types.mjs).
  • Migrations and config: supabase/ at the repo root.

Cloud storage (dataset media)

  • Dataset imagery/video lives in Azure Blob / GCS; access goes through the API layer (web/lib/cloud-storage.ts and the /api/storage/* handlers) — browsers get short-lived signed URLs (e.g. /api/datasets/{slug}/image-url, /sign-video), never raw credentials.
  • Dataset onboarding embeds Azure credentials per dataset (/api/datasets/onboarding); validation via /api/datasets/verify-storage and /api/storage/validate-azure.
  • Chat note PDFs live in the Supabase location-notes storage bucket (1-hour signed URLs).

Assets, CAD objects and the three identifier spaces

Rediscovered more than once. Read this before writing any code that goes from an asset to geometry, or from a picked pixel back to an asset.

  • An asset IS a tag. pds_assets.equipment_tag and equipment_positions.tag (109 rows each on Polycarbon) are the register; everything else hangs off that string.
  • cad_objects (135,277 rows; 10,676 carry a tag; 95,287 have a mesh) is the extracted Navisworks model, one row per node, with a flattened property bag.
  • Tag → CAD objects is an EXACT property filter, not a search and not a heuristic: cad_objects.properties->>'Equip no' = '<tag>'. 79 rows for T100-AB106. v_cad_objects and searchCadObjects sit on the same property.
  • The APS viewer numbers the same model differently. Its objectid / dbId is not cad_objects.id, and nothing in either name warns you.
  • But the viewer’s Item/GUID property IS cad_objects.id — the same value, verified 2026-09-15 for both tag (SMSLD) and geometry (Solid) nodes. So dbId → GUID → database row is an exact, one-step join: a picked pixel identifies an object, it does not merely suggest one.
  • The viewer’s geometry nodes carry their own PDS Component Data.Equip no. A tag needs no inference either.
  • Trap, and it fails silently: viewer.search(value, ok, err, attrs) takes the bare property name. 'Equip no' returns 79 objects for T100-AB106, matching the database exactly; 'PDS Component Data/Equip no' returns nothing, with no error.
  • Existing route with a heuristic: GET /api/aps/model/locate?dataset=&tag= (what the assets page calls) attributes each geometry node to the nearest tag node by object id, because it requests only objectid for Solid nodes and so never sees their tag. It predates the GUID finding. Safe in the direction it is used; do not build a reverse lookup on it — the identity join above exists.
  • Only the Autodesk render can say what a pixel hit. The Ion CAD tileset (asset 4619103) carries no batch table. That asymmetry is why the render endpoints are two routes rather than one.

Full chain, counts, the local GLB export and the worked examples: docs/data/where-an-asset-is-defined.md.

Authoritative sources

docs/handbooks/content/database-cloud-storage/index.md · web/lib/supabase/ · web/lib/cloud-storage.ts — deep documentation in the Database & Cloud Storage Handbook. For assets ↔︎ CAD ↔︎ viewer: docs/data/where-an-asset-is-defined.md and web/app/api/aps/model/locate/route.ts.