Database Schema & ORM

Relevant source files

The following files were used as context for generating this wiki page:

• packages/db/src/schema/app.ts
• packages/db/src/schema/auth.ts
• packages/db/drizzle.config.ts
• packages/db/src/check-ltd.ts
• AGENTS.md
• README.md
• apps/web/src/app/wall/[slug]/page.tsx

KudosWall utilizes a strictly type-safe database architecture built on Drizzle ORM and PostgreSQL (via Neon). The system is designed to handle multi-tenant data structures, including users, organizations, workspaces, projects, and testimonials. The architecture emphasizes high-performance data fetching, unidirectional package flow, and runtime validation using Zod.

The database layer is centralized in the packages/db workspace, which contains schema definitions, migration configurations, and a shared client used by the api, auth, and web layers.

Sources: README.md:28-40, AGENTS.md:32-40, AGENTS.md:91-96

Architecture Overview

The database architecture follows a monorepo structure where the packages/db package serves as the single source of truth for the data layer. It leverages Neon serverless PostgreSQL for connection pooling and scalability.

Data Layer Relationships

The entity relationship model centers around the hierarchy of User -> Organization -> Workspace -> Project -> Testimonial.

Schema Definition & Standards

KudosWall enforces specific naming and formatting conventions for its Drizzle schemas to ensure consistency across the TypeScript and SQL layers.

• Snake Case vs. Camel Case: Database columns are defined using snake_case, while TypeScript fields use camelCase.
• Validation: Zod is used for runtime validation of database schemas and API inputs.
• Indices: All indexes and constraints must be explicitly named in the schema definitions.
• Relationships: Associations are defined using Drizzle's relations API for clean, nested reads.

Sources: AGENTS.md:32-40, README.md:120-125

Core Tables Summary

Entity	Primary Table Path	Key Identifiers
User	packages/db/src/schema/auth.ts	id, email, plan
Organization	packages/db/src/schema/app.ts	id, ownerId, plan
Workspace	packages/db/src/schema/app.ts	id, organizationId, ownerId
Project	packages/db/src/schema/app.ts	id, workspaceId, collectionSlug
Testimonial	packages/db/src/schema/app.ts	id, projectId, status

Sources: packages/db/src/check-ltd.ts:2-5, packages/db/src/check-ltd.ts:22-45, apps/web/src/app/wall/[slug]/page.tsx:64-75

ORM Operations & Data Fetching

The application employs different strategies for data fetching based on the performance requirements of the specific path.

Fetching Strategies

1. db.query: Used for clean, nested reads where developer productivity and readability are prioritized.
2. Explicit .select() & .innerJoin(): Reserved for performance-critical paths to minimize overhead and optimize SQL execution.
3. Caching: Dynamic routes like the "Wall of Love" or collection pages use Next.js unstable_cache with specific tags (e.g., wall, collection) to minimize database load.

Sources: AGENTS.md:32-40, apps/web/src/app/wall/[slug]/page.tsx:61-118

Sequence: Public Testimonial Fetching

Configuration & Tooling

The ORM is configured via drizzle.config.ts, which points to the schema files and output directories for migrations.

Migration Workflow

• db:push: Direct synchronization of schema changes to the database instance during development.
• db:generate: Creation of SQL migration scripts from schema changes.
• db:migrate: Execution of pending SQL scripts on the database.
• db:studio: A visual tool for exploring and editing table states.

Sources: README.md:135-150, packages/db/drizzle.config.ts

Example: Diagnostic Querying

Diagnostic scripts, such as check-ltd.ts, use the createDb utility and Drizzle operators like eq and or to verify data across the multi-tenant hierarchy.

// packages/db/src/check-ltd.ts:32-35
const orgs = await db.query.organization.findMany({
  where: or(eq(organization.id, targetId), eq(organization.ownerId, targetId)),
});

Sources: packages/db/src/check-ltd.ts:32-35

Environment Management

Database connections are strictly managed via environment variables validated by Zod. Direct access to process.env is prohibited in application logic to maintain type safety.

Variable	Scope	Purpose
DATABASE_URL	Server	Primary Neon PostgreSQL connection string
DATABASE_READ_URL	Server	Optional replica endpoint for high-scale widget reads

Sources: README.md:105-115, AGENTS.md:73-77

Summary

The Database Schema & ORM layer in KudosWall provides a robust foundation for testimonial management. By utilizing Drizzle ORM and a centralized schema in a monorepo structure, the project ensures that data operations are type-safe, performant, and maintainable. The use of Neon's serverless architecture combined with Next.js caching strategies allows the system to scale efficiently from individual developers to agency-level workloads.