Clients System

The clients system provides comprehensive client management capabilities including creation, querying, updates, and deletion with built-in user attribution and row-level security.

Overview

The clients system supports:

• Personal Information: Name, gender, date of birth, nickname tracking
• Contact Details: Optional phone and email with validation
• Address Information: Street address, city, state, zip code
• Additional Information: Veteran status, ethnicity (optional demographic data)
• Internal Notes: Free-form text field (max 1000 characters)
• User Attribution: Track who created each client
• Security: Row-level security with ownership-based access control
• Performance: Optimized indexes for name searches and lookups

👤 Client Records

Manage client personal information, contacts, and address details. Optional fields provide maximum flexibility.

🔒 Secure by Default

Row-level security ensures users can only modify their own clients. Admins can manage any client.

⚡ Fast Queries

Optimized indexes provide fast queries for name searches, email lookups, and alphabetical sorting.

📋 Flexible Fields

Minimal required field (gender only) with optional name, contact, and address information based on your needs.

Quick Start

Creating Clients

import { getDatabase } from '#libs/database'
import type { ClientData } from '#libs/database'

const db = getDatabase()

// Minimal client (only required field)
const minimalClient: ClientData = {
  gender: 'prefer_not_to_say',
  nickname: 'Client A', // Recommended for display in client lists
  created_by: userId,
}

const clientId = await db.insertClient(minimalClient)
console.log(`Client created: ${clientId}`)

// Simple client with name
const clientData: ClientData = {
  first_name: 'John',
  last_name: 'Doe',
  gender: 'male',
  nickname: 'Johnny', // Will be shown in client lists
  created_by: userId, // User UUID from Clerk
}

const namedClientId = await db.insertClient(clientData)

// Client with full contact information
const fullClientData: ClientData = {
  first_name: 'Jane',
  last_name: 'Smith',
  gender: 'female',
  date_of_birth: '1990-03-22',
  nickname: 'Janie',
  phone: '5551234567',
  email: 'jane.smith@example.com',
  street_address: '456 Oak Ave',
  city: 'Los Angeles',
  state: 'CA',
  zip_code: '90001',
  veteran_status: false,
  ethnicity: 'Asian',
  notes: 'Referred by John Doe, prefers morning appointments',
  created_by: userId,
}

const fullClientId = await db.insertClient(fullClientData)

Querying Clients

// Get all clients (alphabetical by last name)
const allClients = await db.getClients({
  limit: 50,
  order_by: 'last_name',
})

// Get user's clients
const myClients = await db.getClients({
  created_by: userId,
  limit: 20,
})

// Get recent clients
const recentClients = await db.getClients({
  order_by: 'created_at',
  order_direction: 'desc',
  limit: 10,
})

// Optimized list view (selective fields)
const clientList = await db.getClients({
  fields: ['id', 'first_name', 'last_name', 'phone', 'email'],
  limit: 100,
})

Client Structure

Required Fields

Field	Type	Description	Example	Validation
gender	string	Gender identity	”male”	Enum (see below)

Gender Values:

• male - Male gender identity
• female - Female gender identity
• other - Other gender identity
• prefer_not_to_say - User prefers not to disclose

Optional Fields

Field	Type	Description	Example	Validation
first_name	string	Client first name	”John”	Max 100 chars
last_name	string	Client last name	”Doe”	Max 100 chars
date_of_birth	string	Date of birth	”1985-06-15”	YYYY-MM-DD, not future
nickname	string	Preferred name (primary UI identifier)	“Johnny”	Max 100 chars
phone	string	Phone number	”5551234567”	10 digits
email	string	Email address	”john@example.com”	Valid email format
street_address	string	Street address	”123 Main St”	Max 200 chars
city	string	City name	”San Francisco”	Max 100 chars
state	string	State code	”CA”	2 letters
zip_code	string	Zip code	”94102”	5 digits
veteran_status	boolean	U.S. Military veteran	true, false, null	Boolean or null
ethnicity	string	Self-identified ethnicity	”Hispanic or Latino”	Max 100 chars
notes	string	Internal notes	”VIP client”	Max 1000 chars
created_by	string	User UUID (from Clerk)	“uuid-from-clerk”	Valid UUID

Empty String Handling

For optional fields, empty strings are automatically converted to null. You can safely pass "" for fields you want to clear.

Nickname as Primary Identifier

While nickname is optional, it serves as the primary display identifier in client lists when provided. The client list view shows nickname instead of full name to prioritize how clients prefer to be addressed. When nickname is not set, the display falls back to “first_name last_name” format.

UI Behavior:

• Nickname present: Display shows “Johnny”
• Nickname absent: Display shows “John Doe”

This supports flexible workflows where legal names may not be known initially.

Auto-Generated Fields

Field	Type	Description
id	string	Unique UUID
created_at	string	ISO 8601 timestamp
updated_at	string	ISO 8601 timestamp

Common Use Cases

List All Clients Alphabetically

Display all clients sorted by name:

import { getDatabase } from '#libs/database'
import { formatClientName, formatPhoneNumber } from '#utils/clients'

async function listClientsAlphabetically() {
  const db = getDatabase()

  const clients = await db.getClients({
    order_by: 'last_name',
    order_direction: 'asc',
    limit: 100,
  })

  clients.forEach(client => {
    console.log(formatClientName(client)) // "John Doe"
    if (client.phone) {
      console.log(`  Phone: ${formatPhoneNumber(client.phone)}`) // "(555) 123-4567"
    }
    if (client.email) {
      console.log(`  Email: ${client.email}`)
    }
  })
}

User’s Client Dashboard

Show clients created by specific user:

async function getUserClients(userId: string) {
  const db = getDatabase()

  return await db.getClients({
    created_by: userId,
    order_by: 'last_name',
    limit: 50,
  })
}

Client Details Page

Fetch single client with full details:

async function getClientDetails(clientId: string) {
  const db = getDatabase()
  const client = await db.getClientById(clientId)

  if (!client) {
    throw new Error('Client not found')
  }

  return client
}

Update Client Contact Info

Modify existing client (only owner or admin):

async function updateClientContact(clientId: string, phone: string, email: string) {
  const db = getDatabase()

  const success = await db.updateClient(clientId, {
    phone,
    email,
  })

  if (!success) {
    throw new Error('Client not found or no permission')
  }
}

Delete Client

Remove client (only owner or admin):

async function deleteClient(clientId: string) {
  const db = getDatabase()

  const success = await db.deleteClient(clientId)

  if (!success) {
    throw new Error('Client not found or no permission')
  }
}

Query Options

Filtering

Filter clients by various criteria:

By Creator

const myClients = await db.getClients({
  created_by: userId,
})

With Pagination

const page2Clients = await db.getClients({
  limit: 10,
  offset: 10, // Skip first 10 results
})

Recent First

const recentClients = await db.getClients({
  order_by: 'created_at',
  order_direction: 'desc',
  limit: 20,
})

Sorting

Control result order:

// Alphabetical by last name (default)
const byLastName = await db.getClients({
  order_by: 'last_name',
  order_direction: 'asc',
})

// By first name
const byFirstName = await db.getClients({
  order_by: 'first_name',
  order_direction: 'asc',
})

// Newest first
const newest = await db.getClients({
  order_by: 'created_at',
  order_direction: 'desc',
})

Field Selection

Select only needed fields for better performance:

// Minimal data for list views
const clientList = await db.getClients({
  fields: ['id', 'first_name', 'last_name'],
  limit: 100,
})

// With contact info
const withContacts = await db.getClients({
  fields: ['id', 'first_name', 'last_name', 'phone', 'email'],
  limit: 100,
})

Performance Tip

Selecting specific fields reduces data transfer and improves query performance. Use fields option for large result sets.

Utility Functions

The clients system provides helper functions for common formatting tasks:

formatClientName

Format client full name:

import { formatClientName } from '#utils/clients'

const fullName = formatClientName(client)
// Returns: "John Doe"

formatPhoneNumber

Format phone number for display:

import { formatPhoneNumber } from '#utils/clients'

const formatted = formatPhoneNumber('5551234567')
// Returns: "(555) 123-4567"

calculateAge

Calculate age from date of birth:

import { calculateAge } from '#utils/clients'

const age = calculateAge('1985-06-15')
// Returns: 39 (as of 2025-01-15)

formatAddress

Format full address string:

import { formatAddress } from '#utils/clients'

const address = formatAddress(client)
// Returns: "123 Main St, San Francisco, CA 94102"
// Or partial: "San Francisco, CA" (if street missing)
// Or empty string if no address fields

Pagination

Implement paginated client lists:

async function getClientsPage(userId: string, page: number, pageSize: number = 10) {
  const db = getDatabase()
  const offset = (page - 1) * pageSize

  const [clients, totalCount] = await Promise.all([
    db.getClients({
      created_by: userId,
      limit: pageSize,
      offset,
      order_by: 'last_name',
    }),
    db.getClientsCount({ created_by: userId }),
  ])

  const totalPages = Math.ceil(totalCount / pageSize)

  return {
    clients,
    pagination: {
      currentPage: page,
      pageSize,
      totalCount,
      totalPages,
      hasNextPage: page < totalPages,
      hasPrevPage: page > 1,
    },
  }
}

Example Usage:

const result = await getClientsPage(userId, 1, 10)
console.log(`Page ${result.pagination.currentPage} of ${result.pagination.totalPages}`)
console.log(`Total clients: ${result.pagination.totalCount}`)
result.clients.forEach(client => {
  console.log(formatClientName(client))
})

React Components

ClientFormRHF

Form component for creating/editing clients using React Hook Form + Zod validation:

File: src/components/react/ClientFormRHF.tsx

Props:

type Props = {
  csrfToken: string // CSRF token for form submission
  client?: Client | null // For edit mode (optional)
  apiEndpoint?: string // Custom API endpoint (optional)
}

Usage in Astro:

Create Mode

---
import ClientFormRHF from '#components/react/ClientFormRHF.tsx'
import { generateCSRFToken } from '#utils/csrf'

const csrfToken = await generateCSRFToken(Astro.locals.userId)
---

<ClientFormRHF client:load csrfToken={csrfToken} />

Edit Mode

---
import ClientFormRHF from '#components/react/ClientFormRHF.tsx'
import { generateCSRFToken } from '#utils/csrf'
import { getDatabase } from '#libs/database'

const clientId = Astro.params.id
const db = getDatabase()
const client = await db.getClientById(clientId)
const csrfToken = await generateCSRFToken(Astro.locals.userId)
---

<ClientFormRHF
  client:load
  client={client}
  csrfToken={csrfToken}
  apiEndpoint={`/api/clients/${clientId}`}
/>

Features:

• Organized fieldsets (Personal, Contact, Address, Notes)
• Real-time validation with error messages
• Error summary with anchor links to fields
• Submission error display
• CSRF token integration
• @fpkit/acss components for accessibility
• Controller pattern for checkboxes (veteran_status field)

Implementation Highlights:

The form demonstrates best practices for React Hook Form + @fpkit/acss integration:

// Checkbox using Controller pattern
<Controller
  name="veteran_status"
  control={control}
  render={({ field }) => (
    <Checkbox
      id="veteran_status"
      label="Veteran Status"
      checked={field.value ?? false}
      onChange={field.onChange}
      size="lg"
    />
  )}
/>

This pattern ensures proper state management and accessibility for controlled components.

ClientsGrid

The clients dashboard roster. One component renders both layouts — a card grid and an aligned list — from the same markup, switched by a data-view attribute.

Files: src/components/react/clients/ — ClientsGrid, ClientCard, ClientsToolbar, ClientsGridPagination, plus utils.ts for the display helpers.

Props:

type Props = {
  clients: Client[]
  lastVisits?: Record<string, string>
  view?: 'grid' | 'list'
  canEdit?: boolean
  emptyMessage?: string
  now?: Date | undefined
}

Usage:

---
import { ClientsGrid, ClientsGridPagination, ClientsToolbar } from '#components/react/clients'
import { getDatabase } from '#libs/database'

const params = Astro.url.searchParams
const query = (params.get('q') ?? '').trim()
const view = params.get('view') === 'list' ? 'list' : 'grid'

const db = getDatabase()
const totalCount = await db.getClientsCount(query ? { search: query } : {})
const clients = await db.getClients({
  limit: 10,
  order_by: 'last_name',
  ...(query ? { search: query } : {}),
})
const lastVisits = await db.getClientLastVisits(clients.map(c => c.id))
---

<ClientsToolbar query={query} view={view} matchCount={totalCount} />
<ClientsGrid clients={clients} lastVisits={lastVisits} view={view} canEdit />
<ClientsGridPagination currentPage={1} totalPages={Math.ceil(totalCount / 10)} totalClients={totalCount} />

Features:

• Three facts per row: the name, the date of birth with age, and the last visit
• Card grid on a phone; from lg up, list view adds a shared column ruler
• Absences are stated, not blanked — “Date of birth not recorded”, “No visits yet”
• A nickname that differs from the legal name shows as a “Goes by” line
• Every interactive target meets the 44px WCAG 2.5.5 floor

Server state: search (?q=), paging (?page=) and layout (?view=) are all resolved on the server, so a search narrows the whole roster rather than the page on screen. No framework hydration — the roster, cards and pager are static HTML.

Search-as-you-type: the toolbar is a plain GET form, so Enter, Clear, the pager links and the no-JS path all work on their own. A small inline script on src/pages/dashboard/clients/index.astro upgrades it: 300ms after the reader stops typing it fetches that same ?q= URL and swaps in everything after .cl-page__head — the roster or its empty state, plus the pager — leaving the input untouched.

Swapping rather than reloading is load-bearing. A reload destroys the input mid-word, and every keystroke typed while the new document is on the wire dies with the old one: pause just long enough to let the search fire, type on, and the box reads “alarez” for a reader who typed “alvarez”, with the roster filtered to the term they never typed. The window is one server round trip wide (260-357ms measured locally), which is why the debounce length does not fix it. e2e/clients-dashboard.authed.spec.ts pins both halves — that typing never navigates, and that keys typed mid-fetch survive.

Last visit comes from db.getClientLastVisits(ids) — the newest non-cancelled orders.order_date per client, returned as YYYY-MM-DD. A client with no visit is absent from the map and renders as “No visits yet”.

Note

ClientsListView (src/components/react/ClientsListView.tsx) previously served this page and is no longer used by any route. Prefer ClientsGrid for new work.

Validation Rules

Complete validation reference:

Field	Required	Min/Max	Format	Special Rules
first_name	No	0-100 chars	Text	Trimmed
last_name	No	0-100 chars	Text	Trimmed
gender	Yes	N/A	Enum	male, female, other, prefer_not_to_say
date_of_birth	No	N/A	YYYY-MM-DD	Valid date, not future
phone	No	N/A	Numeric	10 digits (any format) or empty
email	No	N/A	Email	Valid format if provided
street_address	No	0-200 chars	Text	Trimmed
city	No	0-100 chars	Text	Trimmed
state	No	2 chars	Text	2-letter code (e.g., CA)
zip_code	No	N/A	Numeric	5 digits or empty
notes	No	0-1000 chars	Text	Trimmed, database constraint
created_by	No (auto)	N/A	UUID	Set to current user

Date of Birth Validation

Date of birth uses UTC timezone handling to prevent off-by-one errors. The date cannot be in the future.

Phone Validation

Phone numbers can be entered in any format (e.g., “(555) 123-4567”, “555-123-4567”, “5551234567”). The validator extracts digits and requires exactly 10.

Security Model

Authentication

All client operations require:

• Valid Clerk JWT token
• User record in database (synced from Clerk)

Authorization

Station Floor (API and pages):

• POST /api/clients/create and GET /api/clients/edit/[id] require volunteer or higher (SERVICE_DAY_STATION_ROLES, the policy the Service Day stations guard on); a member gets 403
• Updates (PUT/POST /api/clients/edit/[id]) apply the same floor ahead of the ownership check, so a member who still owns clients created before the create gate gets 403, never a 404 that confirms an id exists
• /dashboard/clients and /dashboard/clients/create apply the same floor in their frontmatter, before any read; a member is redirected to /dashboard?error=insufficient_permissions
• The Clients nav link’s team_manager floor decides who is offered the link, not who can open the page
• All of these run through the service-role client, which bypasses RLS, so until they move to a user-scoped client this check is the only gate

Public Read (RLS):

• The clients_select_all policy lets any authenticated JWT read every client
• Enables organization-wide client search; the pages and API above read through the service role instead, so the station floor is what keeps a member out

Ownership Write:

• Users can only create clients attributed to themselves
• Users can only update/delete their own clients
• Prevents impersonation and unauthorized modifications

Admin Override:

• super_admin role can update/delete any client
• Ownership cannot be transferred (immutable created_by)
• Maintains audit trail

Service Role:

• Unrestricted access for webhooks and system operations
• Bypasses all RLS restrictions

Ownership Immutability

The created_by field cannot be changed after creation, even by super admins. This maintains the audit trail and prevents ownership transfer.

API Endpoints

POST /api/clients/create

Create a new client record.

Security: Auth + Role (volunteer+) + Rate Limit + CSRF + Validation

Request (JSON):

{
  "first_name": "John",
  "last_name": "Doe",
  "gender": "male",
  "phone": "5551234567",
  "email": "john@example.com",
  "csrfToken": "token-here"
}

Response (Success):

{
  "success": true,
  "clientId": "uuid-string"
}

GET /api/clients/edit/[id]

Retrieve a single client by ID.

Security: Auth + Role (volunteer+)

Response (Success):

{
  "success": true,
  "client": {
    "id": "uuid",
    "first_name": "John",
    "last_name": "Doe",
    "gender": "male",
    "phone": "5551234567",
    "email": "john@example.com",
    "created_at": "2025-01-14T10:30:00Z",
    "updated_at": "2025-01-14T10:30:00Z"
  }
}

PUT /api/clients/[id]

Update an existing client record.

Security: Auth + Role (volunteer+) + Rate Limit + CSRF + Validation + Ownership Check

Request (JSON):

{
  "phone": "5559876543",
  "email": "newemail@example.com",
  "csrfToken": "token-here"
}

Response (Success):

{
  "success": true,
  "clientId": "uuid-string"
}

Partial Updates

You only need to include fields you want to update. Omitted fields remain unchanged.

Performance Tips

Use Field Selection

For large result sets, select only needed fields:

// ✅ FAST - Minimal data transfer
const clients = await db.getClients({
  fields: ['id', 'first_name', 'last_name'],
  limit: 100,
})

// ⚠️ SLOWER - All fields returned
const clients = await db.getClients({
  limit: 100,
})

Limit Result Sets

Always use pagination for lists:

// ✅ GOOD - Limited result set
const clients = await db.getClients({
  limit: 10,
  offset: 0,
})

// ❌ BAD - Fetches all records
const allClients = await db.getClients({
  limit: 999999,
})

Leverage Indexes

Use indexed fields for filtering and sorting:

// ✅ FAST - Uses idx_clients_created_by
const myClients = await db.getClients({
  created_by: userId,
})

// ✅ FAST - Uses idx_clients_last_name
const sorted = await db.getClients({
  order_by: 'last_name',
})

Troubleshooting

Client Not Found After Creation

Symptom: Client created successfully but not visible in queries

Cause: RLS policy requires authentication

Solution: Ensure JWT token is present in request headers

Permission Denied on Update

Symptom: You do not have permission to update this client error

Cause: Attempting to update client owned by another user

Solution: Verify ownership or request super_admin privileges

Validation Errors

Symptom: Invalid input error on form submission

Cause: Field doesn’t meet validation requirements

Solution: Check validation rules table above and error details

Empty String vs Null

Symptom: Optional fields showing empty string instead of null

Cause: Form submitting "" for empty fields

Solution: Empty strings are automatically converted to null - no action needed

Related Documentation

Technical Reference:

• Clients Table Feature - Complete technical documentation

Database Documentation:

• Database Architecture - Understanding the abstraction layer
• User Management - User attribution and authorization

Migration History:

• Migration 014: Clients Table