Nany Connect

Overview

Problem and context

My contributions

Technical architecture

The database is the security boundary

Every table has row-level security, so access rules hold even if a request bypasses the application. Helper functions keep cross-table checks fast, and public reads go through restricted views.

Layered server functions

Typed server functions pass through middleware in order: authenticate, load the profile, then check the role. Elevated database access is created only after the administrator check passes.

Requests before messages

A parent sends a hire request to a nanny, and only one can be pending per pair. Messaging between them unlocks when the request is accepted.

Private documents

Uploaded documents live in private storage, scoped to their owner’s folder and served through short-lived signed links. Once an administrator has reviewed a document, it is locked against edits.

Typed application layer

React 19 and TanStack Start, with Cloudflare Workers as the deployment target. Routes and server functions share types end to end.

Engineering challenges

1. 01

   Authorization that holds without the UI

   Challenge

   Three roles see different data, and Supabase exposes the database to the browser directly. Hiding controls in the interface protects nothing.

   Approach

   Row-level security is enabled on every table. Default privileges are revoked and granted back per column, so a user can update only the fields meant for them. Cross-table checks run through dedicated functions to avoid policies evaluating each other recursively.

   Technology

   • PostgreSQL row-level security
   • Column privileges
   • Supabase

   Outcome

   The rules are enforced by the database for every client, including direct API calls.

2. 02

   One query instead of eighty-two

   Challenge

   Loading the conversation list fetched each conversation’s latest message and unread count separately. For 40 conversations that was 82 database calls, growing with every new conversation.

   Approach

   A single SQL function returns each conversation with its newest message and unread count, using a lateral join and an existing index. It runs with the caller’s permissions, so row-level security still applies. The admin overview got the same treatment.

   Technology

   • SQL functions
   • Lateral joins
   • Indexes

   Outcome

   The conversation list loads in 2 calls regardless of how many conversations exist.

3. 03

   Handling identity documents

   Challenge

   Nannies upload sensitive documents that administrators must review, and nobody else should ever be able to open them or swap them after approval.

   Approach

   Files go to private buckets with type and size limits. Storage policies restrict each user to their own folder, and database constraints tie every file path to its owner. Review locks the document and the verified fields, with a controlled way to resubmit after a rejection.

   Technology

   • Supabase Storage
   • Signed URLs
   • Check constraints
   • Triggers

   Outcome

   Documents are readable only by their owner and reviewers, and verified data cannot be changed silently.

4. 04

   From prototype to something deployable

   Challenge

   A prototype has no answer for suspended accounts, abusive message volume, unsafe redirects, or leaking personal data into logs.

   Approach

   A hardening pass added redirect validation with tests, content security and transport headers, a per-user message rate limit enforced in the database, removal of an endpoint that allowed ID enumeration, and error reporting that strips personal data.

   Technology

   • Security headers
   • Rate limiting
   • Database policies

   Outcome

   The application was prepared for production deployment with these protections in place.

Results and impact

Database calls to load 40 conversations

82 → 2

Per-conversation queries replaced by one database function.

• Backend, data model, and authorization built independently
• Row-level security on every table
• Prepared for production deploymentA public launch has not been announced.

Visuals