backend

Backend Architecture Guidelines

This document outlines our backend architecture and coding standards. Our backend stack includes:

• Database: PostgreSQL via Supabase
• ORM: Drizzle for type-safe database access
• API Layer: Next.js Server Actions
• Infrastructure: Serverless via Vercel

Core Principles

1. Type Safety: Maintain full type safety between database and application code
2. Scalability: Design for scale from the beginning
3. Maintainability: Follow consistent patterns across the codebase
4. Security: Validate all inputs and handle user permissions properly
5. Performance: Optimize database queries and API responses

Database Schema Design

Directory Structure

db/
├── db.ts                # Main database configuration
├── index.ts             # Public exports
├── migrations/          # Database migrations
└── schema/              # Database schema definitions
    ├── [entity].ts  # Individual entity schemas

Schema Definition Standards

Naming Conventions

• Use kebab-case for files: contacts.ts
• Use camelCase for table and column names in code
• Use snake_case for the actual database column names
• Export types with standardized prefixes: Select[Entity] and Insert[Entity]

Required Fields

All tables should include these standard fields:

{
  // Always use UUID for primary keys
  id: uuid("id").defaultRandom().primaryKey(),
  
  // User association (when applicable)
  userId: text("user_id").notNull(),
  
  // Timestamps
  createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
  updatedAt: timestamp("updated_at", { withTimezone: true })
    .defaultNow()
    .notNull()
    .$onUpdate(() => new Date())
}

Using Enums

For fields with a fixed set of values, use PostgreSQL enums:

import { pgEnum } from "drizzle-orm/pg-core"

// Define the enum
export const statusEnum = pgEnum("status", ["active", "pending", "archived"])

// Use it in your schema
status: statusEnum("status").notNull().default("pending")