Digital Archive 1.5

The problem

The Tzu Chi Foundation keeps a digital archive of about 2 million items: photographs, books, journals, a chronicle and web articles. Staff and members search it and request originals. It ran on CodeIgniter 3 (end of life), MySQL and Apache Solr through an unmaintained PHP extension. Access checks were spread across controllers, and the admin side was 14 modules, about 10 of them near-identical CRUD.

What I built

A full rewrite. Design and analysis began in June 2026. I first mapped the old code with code-graph indexes: 222 PHP files and roughly 600 methods, of which 25–30% were duplicated boilerplate, and only 5 parts that were truly unique (assets, import, request workflow, search, auth) plus an admin CMS that could be generic. The new system is a self-hosted Supabase stack on PostgreSQL 17, with a Next.js public site and a React admin. Search covers exact text, semantic meaning, search by image, and an AI assistant that answers from the archive with links to the records it used.

How it works

1. Legacy data lands in a read-only raw schema, then is curated into a core schema with one asset table: shared columns plus a JSONB attrs field checked against dictionary tables.
2. Text records are embedded with Qwen3-Embedding-4B, served by the foundation’s internal model gateway, into pgvector; photographs get CLIP vectors from the same gateway for image search; pgroonga handles Chinese full-text search.
3. Data calls from the browser go to Supabase (PostgREST), apart from a few Next.js routes such as the assistant and the PDF proxy. Tables are deny-all by default; search and the request workflow run as functions in the api schema, and tables that need plain CRUD are opened only through RLS policies, all under least-privilege roles.
4. Edge Functions cover what SQL cannot: turning a text or image query into a vector, and the ingest endpoint.
5. An ingest API with per-key daily quotas queues new records; a worker derives vectors and refreshes pages.
6. The AI assistant plans, retrieves through read-only functions, reranks, answers, and then checks its own citations.

Decisions

• Supabase RPC + RLS instead of a Rust backend. Supabase already ships login, storage and an API layer, so business logic moved into plpgsql functions; my earlier axum code became a reference.
• One asset table with dictionary-checked attributes. A new media type is a dictionary row, not a schema change. 2 million rows did not need partitioning.
• Deny-all baseline with forced RLS. Nothing is readable until a policy or a granted function opens it, so every path into the data, direct table access or RPC, can be audited; audit tables cannot be updated or deleted.
• Two-layer search time limits. Both the database and the API layer cap query time, so one search cannot stall the server.

Outcome

The public site and admin run as a staging copy on Sealos. Production cutover has not happened yet. AI tagging of the collection was still running as of mid-October. Built with Claude Code on a spec → plan → implement → review loop. Codex wrote the formal migrations; Claude planned each batch and checked every migration against the live database.