Database Setup and Migrations for Inventory System

Inventory System uses PostgreSQL as its sole data store and has been tested extensively against Neon Serverless Postgres. Neon is the strongly recommended provider because the application’s Prisma adapter is built on the @neondatabase/serverless WebSocket driver, which eliminates the TCP handshake overhead that makes standard Postgres connections expensive in serverless environments. Local and Docker-based deployments can use any standard Postgres 15+ instance with no driver changes.

Connection strings

Neon — and Prisma’s migration toolchain — require two distinct connection strings with separate purposes.

DATABASE_URL (pooled)

Used by the running application. Routes through Neon’s connection pooler (PgBouncer in transaction mode), which caps each request to a single pooled connection. Suitable for the high-concurrency serverless workload but cannot execute multi-statement transactions or advisory locks — both of which Prisma’s migrate engine requires.

DATABASE_URL_UNPOOLED (direct)

Used exclusively by the Prisma CLI and migration commands. Connects directly to the Postgres instance, bypassing the pooler. Required for prisma migrate deploy, prisma db push, and prisma studio to operate correctly.

In prisma/schema.prisma this separation is declared explicitly:

datasource db {
  provider  = "postgresql"
  url       = env("DATABASE_URL")
  // Only use directUrl for Prisma migrate - uses unpooled for full SQL support
  directUrl = env("DATABASE_URL_UNPOOLED")
}

Prisma automatically uses url for all query-engine traffic and directUrl whenever the migrate engine needs a direct connection. You do not need to swap variables manually between commands.

Always use DATABASE_URL_UNPOOLED for prisma migrate deploy in CI/CD pipelines. Running migration commands through the pooled connection causes failures because PgBouncer in transaction mode does not support the advisory locks and multi-statement DDL that Prisma’s migration engine relies on. Set both variables in every deployment environment.

Prisma adapter and WebSocket driver

In production the application instantiates PrismaClient with the @prisma/adapter-neon driver adapter rather than the standard TCP-based client:

import { neon } from "@neondatabase/serverless";
import { PrismaNeon } from "@prisma/adapter-neon";
import { PrismaClient } from "@prisma/client";

const sql = neon(process.env.DATABASE_URL!);
const adapter = new PrismaNeon(sql);
const prisma = new PrismaClient({ adapter });

The @neondatabase/serverless driver opens a WebSocket connection to Neon’s edge proxy, which allows the query to complete inside a single HTTP request lifecycle — critical for Vercel serverless functions, which cannot maintain persistent TCP connections between invocations.

In local development the standard PrismaClient (without the adapter) works fine against a local Postgres instance or Neon direct connection. The driverAdapters preview feature is enabled in the schema to support both paths at compile time.

The serverExternalPackages setting in next.config.ts is required to prevent Next.js from bundling these packages at build time:

serverExternalPackages: [
  "@prisma/client",
  "@prisma/adapter-neon",
  "@neondatabase/serverless",
  "ws",
],

Migration commands

All Prisma CLI commands are wrapped as npm scripts in package.json and target prisma/schema.prisma explicitly.

Script	Command	When to use
`npm run prisma:generate`	`prisma generate --schema=prisma/schema.prisma`	After any change to `schema.prisma` to regenerate the TypeScript client. Also runs automatically via the `postinstall` hook.
`npm run prisma:migrate`	`prisma migrate deploy --schema=prisma/schema.prisma`	Apply all pending migration files to the database. Use in CI/CD and production deployments. Requires `DATABASE_URL_UNPOOLED`.
`npm run prisma:dbpush`	`prisma db push --schema=prisma/schema.prisma`	Push schema changes directly to the database without creating a migration file. Development only — do not use against production data.

The build script (prisma generate && next build) automatically regenerates the Prisma Client on every Vercel deployment, so you rarely need to run prisma:generate manually in CI. Reserve prisma:migrate for pipeline steps that apply migrations after the database has been provisioned.

Seeding

The seed script creates the minimum data required for the application to be usable after a fresh migration:

npm run seed

This executes prisma/seed.ts using ts-node with the project’s seed-specific tsconfig (tsconfig.seed.json). The seed creates:

An initial ADMIN Usuario record linked to the email address in SUPER_ADMIN_EMAIL
A default set of Categoria records with their product-code prefixes

The seed script is idempotent for categories (it upserts by name), but it will create a duplicate admin user if run more than once against a database that already has user data. Run it only on a fresh database or in a controlled migration environment.

Schema overview

The Prisma schema defines the following models. Every model that stores business data includes an empresaId foreign key, making the schema fully multi-tenant — each Empresa (tenant) owns its own isolated slice of every table.

Model	Purpose
`Empresa`	Tenant root. All other business models cascade-delete when an `Empresa` is deleted.
`Usuario`	Application user, linked to a Neon Auth identity via `neonAuthId`. Carries role (`SUPER_ADMIN`, `ADMIN`, `USUARIO`) and granular `Permiso` flags.
`Categoria`	Product category with a short `prefijo` string used to generate product codes (e.g. `BEB-0001`).
`Producto`	Inventory item with name, SKU code, price, and current stock quantity. Belongs to a `Categoria`.
`Movimiento`	Stock entry or exit event. Linked to a `Venta` or `OrdenCompra` when generated by those flows, or standalone for manual adjustments.
`Venta`	Confirmed sale. Holds the total, optional customer reference, and seller. Triggers stock-deducting `Movimiento` records.
`ItemVenta`	Individual line item on a `Venta`, storing the unit price at the time of sale and the quantity sold.
`Cotizacion`	Quote with reserved stock. Can transition to `CONVERTIDA` (becomes a `Venta`) or `CANCELADA` (releases reservation). Expires at `validaHasta`.
`ItemCotizacion`	Individual line item on a `Cotizacion`, mirroring the structure of `ItemVenta`.
`OrdenCompra`	Purchase order sent to a `Proveedor`. States: `BORRADOR` → `RECIBIDA` (generates stock-entry `Movimiento` records) or `CANCELADA`.
`OrdenCompraItem`	Individual line item on an `OrdenCompra`, recording quantity and unit cost.
`Proveedor`	Supplier with contact information. Scoped to an `Empresa`.
`Cliente`	Customer with optional document number, email, and address. Scoped to an `Empresa`.
`Auditoria`	Audit log entry. Records the action (`CREAR`, `ACTUALIZAR`, `ELIMINAR`, `LOGIN`), the affected entity type and ID, a JSON snapshot of before/after states, and the requester’s IP address.
`HistorialMovimientos`	Product movement history log. Records create, modify, and delete actions on products, capturing before/after JSON snapshots, the affected product ID, the user who made the change, and the requester’s IP.
`Notificacion`	In-app notification. Types include `STOCK_BAJO`, `STOCK_CRITICO`, `COTIZACION_POR_VENCER`, `INVITACION_ACEPTADA`, and `USUARIO_REGISTRADO`.
`HistorialPrecio`	Product price change record. Stores the previous and new price, the timestamp, and the user who made the change.
`ConfiguracionEmpresa`	Per-company settings: currency symbol, tax rate, logo URL, and customised company name. One-to-one with `Empresa`.
`Invitacion`	User invitation token. Tracks invited email, assigned role, expiry date, and acceptance state (`PENDIENTE`, `ACEPTADA`, `EXPIRADA`, `CANCELADA`).

Get Started

Core Features

Access Control

Deployment

Database Setup and Migrations for Inventory System

Connection strings

DATABASE_URL (pooled)

DATABASE_URL_UNPOOLED (direct)

Prisma adapter and WebSocket driver

Migration commands

Seeding

Schema overview

Build docs developers (and LLMs) love

Get Started

Core Features

Access Control

Deployment

Documentation Index

​Connection strings

DATABASE_URL (pooled)

DATABASE_URL_UNPOOLED (direct)

​Prisma adapter and WebSocket driver

​Migration commands

​Seeding

​Schema overview

Build docs developers (and LLMs) love

Connection strings

Prisma adapter and WebSocket driver

Migration commands

Seeding

Schema overview