Database Setup - Tambo360

Overview

Tambo360 uses PostgreSQL 15 as its primary database and Prisma as the ORM (Object-Relational Mapping) tool. This guide covers database installation, configuration, schema management, and migrations.

Prisma provides type-safe database access with automatic migration generation and a powerful query builder.

Database Technology Stack

PostgreSQL

Version 15

Prisma ORM

Version 5.22.0

Prisma Client

Version 5.22.0

Install PostgreSQL

Docker (Recommended)
Ubuntu/Debian
macOS
Windows

The easiest way to run PostgreSQL is using Docker:

docker run -d \
  --name tambo-postgres \
  -e POSTGRES_USER=postgres \
  -e POSTGRES_PASSWORD=tambo360 \
  -e POSTGRES_DB=tambo \
  -p 5433:5432 \
  -v tambo_postgres_data:/var/lib/postgresql/data \
  postgres:15

This command uses port 5433 on the host to avoid conflicts with existing PostgreSQL installations.

# Add PostgreSQL repository
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

# Install PostgreSQL 15
sudo apt update
sudo apt install postgresql-15 postgresql-contrib-15

# Start PostgreSQL service
sudo systemctl start postgresql
sudo systemctl enable postgresql

# Using Homebrew
brew install postgresql@15

# Start PostgreSQL service
brew services start postgresql@15

Download and install PostgreSQL from the official website.Use the installer wizard and note the password you set for the postgres user.

Verify Installation

# Check PostgreSQL version
psql --version
# Should output: psql (PostgreSQL) 15.x

# Connect to PostgreSQL
psql -U postgres -h localhost -p 5433

Database Configuration

Create Database

Create the Tambo360 database:

-- Connect as postgres user
psql -U postgres -h localhost -p 5433

-- Create database
CREATE DATABASE tambo;

-- Create user (optional)
CREATE USER tambo_user WITH PASSWORD 'your_secure_password';

-- Grant privileges
GRANT ALL PRIVILEGES ON DATABASE tambo TO tambo_user;

-- Exit
\q

Database Connection String

Set the DATABASE_URL environment variable:

# For Docker PostgreSQL (port 5433)
export DATABASE_URL="postgresql://postgres:tambo360@localhost:5433/tambo"

# For local PostgreSQL (port 5432)
export DATABASE_URL="postgresql://postgres:yourpassword@localhost:5432/tambo"

# For production with connection pooling
export DATABASE_URL="postgresql://user:password@host:5432/tambo?schema=public&connection_limit=10"

Never commit database credentials to version control. Always use environment variables.

Prisma Schema

The Prisma schema defines the database structure. Located at apps/backend/prisma/schema.prisma:

schema.prisma

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

// Enums
enum Unidad {
  kg
  litros
}

enum Moneda {
  USD
  EUR
  ARS
}

enum Categoria {
  quesos
  leches
}

enum TipoToken {
  verificacion
  recuperacion
}

enum TipoMerma {
  Natural
  Tecnica
  Administrativa
  Danio
}

enum ConceptoCosto {
  insumos_basicos
  leche_cruda
  cuajo_y_fermentos
  refrigeracion
}

// Models
model Usuario {
  idUsuario        String            @id @default(uuid())
  correo           String            @unique
  contrasena       String
  nombre           String            @db.VarChar(50)
  verificado       Boolean           @default(false)
  fechaCreacion    DateTime          @default(now())
  establecimientos Establecimiento[]
  tokens           VerificarToken[]
}

model Establecimiento {
  idEstablecimiento String           @id @default(uuid())
  nombre            String
  localidad         String
  provincia         String
  fechaCreacion     DateTime         @default(now())
  idUsuario         String
  usuario           Usuario          @relation(fields: [idUsuario], references: [idUsuario])
  loteProducciones  LoteProduccion[]
}

model Producto {
  idProducto       String           @id @default(uuid())
  nombre           String
  categoria        Categoria
  loteProducciones LoteProduccion[]
}

model LoteProduccion {
  idLote            String          @id @default(uuid())
  numeroLote        Int             @default(autoincrement())
  fechaProduccion   DateTime        @default(now())
  idProducto        String
  producto          Producto        @relation(fields: [idProducto], references: [idProducto])
  cantidad          Decimal
  unidad            Unidad
  idEstablecimiento String
  estado            Boolean         @default(false)
  establecimiento   Establecimiento @relation(fields: [idEstablecimiento], references: [idEstablecimiento])
  mermas            Merma[]
  costosDirectos    CostosDirecto[]
}

model Merma {
  idMerma       String         @id @default(uuid())
  tipo          TipoMerma
  observacion   String?
  cantidad      Decimal
  fechaCreacion DateTime       @default(now())
  idLote        String
  lote          LoteProduccion @relation(fields: [idLote], references: [idLote])
}

model CostosDirecto {
  idCostoDirecto String         @id @default(uuid())
  concepto       ConceptoCosto
  monto          Decimal        @db.Decimal(13, 2)
  observaciones  String?        @default("")
  fechaCreacion  DateTime       @default(now())
  idLote         String
  lote           LoteProduccion @relation(fields: [idLote], references: [idLote])
}

model VerificarToken {
  tokenid   String    @id @default(uuid())
  idUsuario String
  usuario   Usuario   @relation(fields: [idUsuario], references: [idUsuario])
  tipo      TipoToken
  tokenHash String
  expiraEn  DateTime
  usadoEn   DateTime?
  creadoEn  DateTime  @default(now())

  @@index([idUsuario, tipo])
}

Database Schema Overview

Core Models

Usuario (User)

Stores user authentication and account information.Fields:

idUsuario: Unique UUID identifier
correo: Email address (unique)
contrasena: Hashed password
nombre: User’s name (max 50 characters)
verificado: Email verification status
fechaCreacion: Account creation timestamp

Relations:

Has many Establecimiento (establishments)
Has many VerificarToken (verification tokens)

Establecimiento (Establishment)

Represents dairy farm establishments owned by users.Fields:

idEstablecimiento: Unique UUID identifier
nombre: Establishment name
localidad: City/locality
provincia: Province/state
fechaCreacion: Creation timestamp
idUsuario: Owner’s user ID

Relations:

Belongs to one Usuario
Has many LoteProduccion (production batches)

Producto (Product)

Catalog of dairy products that can be produced.Fields:

idProducto: Unique UUID identifier
nombre: Product name
categoria: Category (quesos/leches)

Relations:

Has many LoteProduccion

LoteProduccion (Production Batch)

Tracks individual production batches with costs and waste.Fields:

idLote: Unique UUID identifier
numeroLote: Auto-incrementing batch number
fechaProduccion: Production date
cantidad: Quantity produced
unidad: Unit of measurement (kg/litros)
estado: Status flag

Relations:

Belongs to Producto and Establecimiento
Has many Merma (waste records)
Has many CostosDirecto (direct costs)

Merma (Waste/Loss)

Records production waste and losses.Fields:

idMerma: Unique UUID identifier
tipo: Type of waste (Natural/Tecnica/Administrativa/Danio)
cantidad: Quantity lost
observacion: Optional notes
fechaCreacion: Timestamp

Relations:

Belongs to one LoteProduccion

CostosDirecto (Direct Costs)

Tracks direct costs associated with production batches.Fields:

idCostoDirecto: Unique UUID identifier
concepto: Cost concept/category
monto: Amount (decimal with 13 digits, 2 decimal places)
observaciones: Optional notes
fechaCreacion: Timestamp

Relations:

Belongs to one LoteProduccion

VerificarToken (Verification Token)

Stores tokens for email verification and password recovery.Fields:

tokenid: Unique UUID identifier
idUsuario: Associated user ID
tipo: Token type (verificacion/recuperacion)
tokenHash: Hashed token value
expiraEn: Expiration timestamp
usadoEn: Usage timestamp (nullable)
creadoEn: Creation timestamp

Indexes:

Composite index on (idUsuario, tipo) for fast lookups

Running Migrations

Generate Prisma Client

cd apps/backend
npx prisma generate

This generates the type-safe Prisma Client based on your schema.

Create initial migration

npx prisma migrate dev --name init

This command:

Creates a new migration file in prisma/migrations/
Applies the migration to your development database
Generates Prisma Client

Apply migrations to production

npx prisma migrate deploy

Always test migrations in a staging environment before applying to production.

Seed the database (optional)

npm run seed
# or
npx prisma db seed

This runs the seed script at src/lib/seedProductos.ts.

Prisma Commands Reference

Schema Management

# Format schema file
pnpm prisma format

# Validate schema
pnpm prisma validate

# Generate Prisma Client
pnpm prisma generate

Migration Management

# Create and apply migration (dev)
pnpm prisma migrate dev --name migration_name

# Apply migrations (production)
pnpm prisma migrate deploy

# Reset database (WARNING: deletes all data)
pnpm prisma migrate reset

# View migration status
pnpm prisma migrate status

# Resolve migration issues
pnpm prisma migrate resolve --applied "migration_name"

Database Introspection

# Pull schema from existing database
pnpm prisma db pull

# Push schema changes without migration
pnpm prisma db push

Prisma Studio

Open a visual database editor:

pnpm prisma studio

Access at http://localhost:5555

Prisma Studio provides a user-friendly GUI for browsing and editing database records.

Database Seeding

The seed script populates initial product data:

src/lib/seedProductos.ts

import { PrismaClient, Categoria } from '@prisma/client';

const prisma = new PrismaClient();

async function main() {
  // Seed products
  await prisma.producto.createMany({
    data: [
      { nombre: 'Queso Cremoso', categoria: Categoria.quesos },
      { nombre: 'Queso Sardo', categoria: Categoria.quesos },
      { nombre: 'Leche Entera', categoria: Categoria.leches },
      { nombre: 'Leche Descremada', categoria: Categoria.leches },
    ],
    skipDuplicates: true,
  });
}

main()
  .catch((e) => console.error(e))
  .finally(() => prisma.$disconnect());

Run the seed:

npm run seed

Backup and Restore

Backup Database

# Using pg_dump
pg_dump -U postgres -h localhost -p 5433 -d tambo -F c -f tambo_backup_$(date +%Y%m%d).dump

# Docker database backup
docker exec -t tambo-db pg_dumpall -c -U postgres > tambo_backup_$(date +%Y%m%d).sql

Restore Database

# Using pg_restore
pg_restore -U postgres -h localhost -p 5433 -d tambo tambo_backup.dump

# Docker database restore
cat tambo_backup.sql | docker exec -i tambo-db psql -U postgres

Always test backups regularly by restoring to a test environment.

Performance Optimization

Database Indexes

The schema includes an index on VerificarToken(idUsuario, tipo) for fast token lookups:

model VerificarToken {
  // ... fields
  
  @@index([idUsuario, tipo])
}

Connection Pooling

For production, configure connection pooling in the DATABASE_URL:

DATABASE_URL="postgresql://user:password@host:5432/tambo?connection_limit=10&pool_timeout=10"

Query Optimization

Use Prisma’s select and include for efficient queries:

// Only fetch needed fields
const user = await prisma.usuario.findUnique({
  where: { idUsuario },
  select: {
    nombre: true,
    correo: true,
    establecimientos: {
      select: { nombre: true, localidad: true }
    }
  }
});

Troubleshooting

Cannot connect to database

Check if PostgreSQL is running:

# Check Docker container
docker ps | grep postgres

# Check local service
sudo systemctl status postgresql

# Test connection
psql -U postgres -h localhost -p 5433 -d tambo

Migration failed

Check migration status and resolve:

npx prisma migrate status

# Mark as applied if manually fixed
npx prisma migrate resolve --applied "migration_name"

# Rollback (dev only)
npx prisma migrate reset

Prisma Client out of sync

Regenerate Prisma Client:

npx prisma generate

# If issues persist, clean and regenerate
rm -rf node_modules/.prisma
npx prisma generate

Permission denied errors

Grant proper permissions to database user:

GRANT ALL PRIVILEGES ON DATABASE tambo TO tambo_user;
GRANT ALL ON SCHEMA public TO tambo_user;

Best Practices

Use Migrations

Always use Prisma migrations instead of manual schema changes.

Version Control

Commit migration files to version control.

Backup Regularly

Schedule automated backups of production databases.

Test Migrations

Test all migrations in staging before production deployment.

Next Steps

Environment Variables

Configure DATABASE_URL and other settings

API Reference

Learn how to query the database via API

Deployment

Deploy your database to production

Get Started

Core Features

Guides

​Overview

​Database Technology Stack

PostgreSQL

Prisma ORM

Prisma Client

​Install PostgreSQL

​Verify Installation

​Database Configuration

​Create Database

​Database Connection String

​Prisma Schema

​Database Schema Overview

​Core Models

​Running Migrations

​Prisma Commands Reference

​Schema Management

​Migration Management

​Database Introspection

​Prisma Studio

​Database Seeding

​Backup and Restore

​Backup Database

​Restore Database

​Performance Optimization

​Database Indexes

​Connection Pooling

​Query Optimization

​Troubleshooting

​Best Practices

Use Migrations

Version Control

Backup Regularly

Test Migrations

​Next Steps

Environment Variables

API Reference

Deployment

Build docs developers (and LLMs) love

Overview

Database Technology Stack

Install PostgreSQL

Verify Installation

Database Configuration

Create Database

Database Connection String

Prisma Schema

Database Schema Overview

Core Models

Running Migrations

Prisma Commands Reference

Schema Management

Migration Management

Database Introspection

Prisma Studio

Database Seeding

Backup and Restore

Backup Database

Restore Database

Performance Optimization

Database Indexes

Connection Pooling

Query Optimization

Troubleshooting

Best Practices

Next Steps