Database Operations

Database module structure, CRUD patterns, and Supabase query conventions

Database Operations

All database interactions in HostMetrics are encapsulated in modules under src/lib/db/. Components and pages never call Supabase directly — they use hooks (from src/hooks/) which in turn call these database modules.

Module Structure

src/lib/db/
├── _client.ts              # Supabase client + getCurrentUserId()
├── index.ts                # Main operations (trips, vehicles, earnings)
├── schema/                 # TypeScript types (7 files, 2750+ lines)
│   ├── trips.ts
│   ├── vehicles.ts
│   ├── expenses.ts
│   ├── partners.ts
│   ├── documents.ts
│   ├── billing.ts
│   └── index.ts            # Re-exports all types
├── partners.ts             # Partner operations
├── agreements.ts           # Agreement operations
├── documents.ts            # Document operations
├── import-service.ts       # CSV import logic
├── billing.ts              # Billing and subscription operations
├── storage.ts              # File storage (Supabase Storage)
└── toll-transactions.ts    # Toll data import and queries

The Client Pattern

Every database module imports from _client.ts, which provides two key functions:

import { getClient, getCurrentUserId } from "./_client";

• getClient() — Returns the Supabase client instance (browser or server depending on context)
• getCurrentUserId() — Returns the authenticated user’s ID. Throws if not authenticated.

Every query must filter by user_id to enforce data isolation between users.

Standard CRUD Pattern

Each database module follows a consistent structure. Here is a typical example:

import { getClient, getCurrentUserId } from "./_client";
import type { Trip, TripInsert, TripUpdate } from "./schema";

export const trips = {
  /** List all trips for the current user */
  async list(filters?: { status?: string; vehicleId?: string }) {
    const userId = await getCurrentUserId();
    let query = getClient()
      .from("trips")
      .select("*")
      .eq("user_id", userId)
      .order("trip_end", { ascending: false });

    if (filters?.status) {
      query = query.eq("status", filters.status);
    }
    if (filters?.vehicleId) {
      query = query.eq("vehicle_id", filters.vehicleId);
    }

    const { data, error } = await query;
    if (error) throw error;
    return data as Trip[];
  },

  /** Get a single trip by ID */
  async get(id: string) {
    const userId = await getCurrentUserId();
    const { data, error } = await getClient()
      .from("trips")
      .select("*")
      .eq("id", id)
      .eq("user_id", userId)
      .single();

    if (error) throw error;
    return data as Trip;
  },

  /** Create a new trip */
  async create(trip: TripInsert) {
    const userId = await getCurrentUserId();
    const { data, error } = await getClient()
      .from("trips")
      .insert({ ...trip, user_id: userId })
      .select()
      .single();

    if (error) throw error;
    return data as Trip;
  },

  /** Update an existing trip */
  async update(id: string, updates: TripUpdate) {
    const userId = await getCurrentUserId();
    const { data, error } = await getClient()
      .from("trips")
      .update(updates)
      .eq("id", id)
      .eq("user_id", userId)
      .select()
      .single();

    if (error) throw error;
    return data as Trip;
  },

  /** Delete a trip */
  async delete(id: string) {
    const userId = await getCurrentUserId();
    const { error } = await getClient()
      .from("trips")
      .delete()
      .eq("id", id)
      .eq("user_id", userId);

    if (error) throw error;
  },
};

Pagination for Large Tables

Supabase limits query results to 1,000 rows by default. For tables that may exceed this limit (like trips and toll_transactions), use range-based pagination:

async listAll() {
  const userId = await getCurrentUserId();
  const PAGE_SIZE = 1000;
  let allData: Trip[] = [];
  let from = 0;

  while (true) {
    const { data, error } = await getClient()
      .from("trips")
      .select("*")
      .eq("user_id", userId)
      .order("trip_end", { ascending: false })
      .range(from, from + PAGE_SIZE - 1);

    if (error) throw error;
    if (!data || data.length === 0) break;

    allData = allData.concat(data);
    if (data.length < PAGE_SIZE) break;
    from += PAGE_SIZE;
  }

  return allData;
}

Error Handling

All database operations check for errors and throw with context:

const { data, error } = await getClient()
  .from("vehicles")
  .insert(vehicle);

if (error) {
  throw new Error(`Failed to create vehicle: ${error.message}`);
}

Hooks catch these errors and expose them via an error state property for components to render.

Schema Types

TypeScript types for all database tables are defined in src/lib/db/schema/ across 7 files with over 2,750 lines of type definitions. Each table typically has three type variants:

// The full row type (what you get back from a SELECT)
export interface Trip {
  id: string;
  user_id: string;
  reservation_id: string;
  vehicle_id: string | null;
  trip_start: string;
  trip_end: string;
  // ... 30+ fields
}

// The insert type (required fields for INSERT)
export type TripInsert = Omit<Trip, "id" | "created_at" | "updated_at">;

// The update type (all fields optional for UPDATE)
export type TripUpdate = Partial<TripInsert>;

Joins and Related Data

For queries that need data from related tables, use Supabase’s select syntax:

const { data, error } = await getClient()
  .from("trips")
  .select(`
    *,
    vehicles (id, make, model, year, photo_url)
  `)
  .eq("user_id", userId);

Soft Deletes

Some entities (like vehicles) use soft deletes. Instead of removing the row, the status is updated:

async softDelete(id: string) {
  const userId = await getCurrentUserId();
  const { error } = await getClient()
    .from("vehicles")
    .update({ status: "deleted" })
    .eq("id", id)
    .eq("user_id", userId);

  if (error) throw error;
}

Queries for active records should filter out deleted rows:

.neq("status", "deleted")