Sites & Infrastructure
Nonprofit Member Directory
The chapter kept its membership in a shared spreadsheet that nobody could realistically search. We turned that workbook into a live directory where members find each other by typing what they actually mean, while a deliberate two-table design keeps emails and phone numbers out of any public response. A repeatable importer lets the team refresh the whole directory from an updated sheet with one command.
Stack
Concepts
How it works
The whole flow, traced from your first tap.
- 01
Node importer
Refresh from the spreadsheet
Running the importer reads the canonical worksheet from the membership workbook, cleans each row, and parses phone numbers and social handles out of a free-text contact field with regex before anything touches the database.
xlsxNode.jsSupabase service role - 02
Supabase Postgres
Split public profile from private contact
Profile fields land in md_members and contact PII in md_member_contacts, each upserted on a stable import key (the email, or a hash of name plus company when no email exists), so a re-import updates existing members rather than creating duplicates.
md_membersmd_member_contactsIdempotent upsert - 03
Postgres full-text search
Index for natural-language search
A database trigger rebuilds a weighted search vector on every insert or update, with name and title ranked above company and industry and bio text lowest, and trigram indexes back fuzzy and prefix matching.
tsvectorGIN + pg_trgmunaccent - 04
Browser
Browse and search the directory
A member types a name, role, or phrase like accountant in la; the client debounces the request, ignores stale responses, and keeps the previous results on screen while the new ones load, with admin-editable chips offering one-tap searches.
React 19Debounced fetchQuick-search chips - 05
Next.js API
Resolve intent on the server
The members API expands shorthand to canonical terms, resolves location aliases to real member cities, detects profession intent, and scores rows so the most relevant members rank first, with proxy-term fallbacks rather than an empty result.
Synonym tableMetro gazetteerRelevance scoring - 06
Next.js API
View a member, contact stays private
Opening a profile fetches only public columns from md_members plus experiences and images; email and phone are never in that response, and members sign in by magic link with a Supabase bearer token to edit their own record.
Public profile endpointRow-level securityMagic-link auth
The problem
Member data lived in an xlsx workbook with one canonical worksheet, mixing public profile fields and private contact details in the same rows. There was no way to search it, no way to protect the contact information, and every refresh meant manually reconciling edits against the spreadsheet.
What we built
A Next.js directory backed by Supabase, fed by a Node importer that reads the workbook and writes it into two tables. Visitors browse and search live data from the API; members can sign in by magic link to maintain their own profile, work history, and photos.
PII separation by design
Public profile fields go into md_members; email, phone, and social handles go into a separate md_member_contacts table keyed one-to-one by member id. The migration grants the anonymous role SELECT on md_members only and never on md_member_contacts, and the public profile endpoint deliberately selects only profile columns, so contact PII is structurally unreachable from the public app rather than just hidden in the UI.
Search that understands intent
Beyond Postgres full-text search over a weighted tsvector (name and title weighted above company and industry, with bio text lowest) backed by GIN and trigram indexes, the members API runs an intent layer: a synonym table that maps shorthand like cpa or biz dev to canonical terms, a metro gazetteer that resolves nyc or socal to member cities, name-prefix typeahead, profession intent detection with relevance scoring, and proxy-term fallbacks so a sparse query still returns the nearest useful matches instead of an empty page.
Outcome
The directory runs on real chapter data in production. Refreshing it is a single command that upserts by a stable import key, so re-running the importer updates existing members instead of duplicating them, and member contact details stay private by construction.
Interested in something similar?
Tell us what you need and we'll figure out how to ship it.