Database Schema

Complete database schema documentation for PROVESA Web

Overview

PROVESA Web uses PostgreSQL as its database with Drizzle ORM for type-safe queries. The schema is organized into modular files by domain.

Location: /src/lib/server/db/schemas/

Authentication Tables

user

User accounts and profile information.

id · text · required

Primary key, auto-generated UUID

name · text · required

User’s display name

email · text · required

Unique email address

emailVerified · boolean · default: "false"

Whether email has been verified

image · text

Profile image URL

createdAt · timestamp · default: "now()"

Account creation timestamp

updatedAt · timestamp · default: "now()"

Last update timestamp (auto-updated)

Relations:

• One-to-many with session
• One-to-many with account

Indexes:

• email (unique)

---

session

Active user sessions for authentication.

id · text · required

Primary key, session ID

expiresAt · timestamp · required

Session expiration time

token · text · required

Unique session token (indexed)

createdAt · timestamp · default: "now()"

Session creation time

updatedAt · timestamp

Last session activity

ipAddress · text

Client IP address

userAgent · text

Client user agent string

userId · text · required

Foreign key to user.id (CASCADE delete)

Indexes:

• userId (for faster lookups)
• token (unique)

---

account

OAuth providers and credential storage.

id · text · required

Primary key

accountId · text · required

Provider-specific account ID

providerId · text · required

Provider name (e.g., ‘google’, ‘credentials’)

userId · text · required

Foreign key to user.id (CASCADE delete)

accessToken · text

OAuth access token

refreshToken · text

OAuth refresh token

idToken · text

OAuth ID token

accessTokenExpiresAt · timestamp

Access token expiration

refreshTokenExpiresAt · timestamp

Refresh token expiration

scope · text

OAuth scopes granted

password · text

Hashed password (for credential provider)

createdAt · timestamp · default: "now()"

Account link creation time

updatedAt · timestamp

Last update time

Indexes:

• userId

---

verification

Email verification and password reset tokens.

id · text · required

Primary key

identifier · text · required

Email or user identifier (indexed)

value · text · required

Verification token

expiresAt · timestamp · required

Token expiration time

createdAt · timestamp · default: "now()"

Token creation time

updatedAt · timestamp · default: "now()"

Last update time

Indexes:

• identifier

---

Content Management Tables

hero_slides

Homepage hero banner slides.

id · serial · required

Primary key, auto-increment

imageUrl · text · required

Cloudinary image URL

title · text · default: "''"

Main slide title

highlight · text

Highlighted text portion

description · text

Slide description/subtitle

badge · text

Small badge text (e.g., “Nuevo”)

align · text · default: "'left'"

Text alignment: ‘left’, ‘center’, or ‘right’

highlightColor · text · default: "'text-accent-yellow'"

CSS class for highlight color

sortOrder · integer · default: "0"

Display order (lower = first)

createdAt · timestamp · default: "now()"

Creation timestamp

Example:

{
  imageUrl: 'https://res.cloudinary.com/...jpg',
  title: 'Distribuidor Oficial',
  highlight: 'Coca-Cola',
  badge: 'Nuevo Producto',
  align: 'left',
  highlightColor: 'text-accent-yellow'
}

---

products

Product catalog for homepage sections.

id · serial · required

Primary key, auto-increment

name · text · required

Product name

description · text

Product description

images · jsonb · default: "[]"

Array of image URLs: string[]

features · jsonb · default: "[]"

Array of features: { title: string, desc: string, icon: string }[]

categories · jsonb · default: "[]"

Array of categories: { num: string, name: string, desc: string }[]

align · text · default: "'left'"

Layout alignment: ‘left’ or ‘right’

accentColor · text · default: "'primary'"

Accent color: ‘primary’ or ‘accent’

displayType · text · default: "'features'"

Display mode: ‘features’ or ‘categories’

sortOrder · integer · default: "0"

Display order

createdAt · timestamp · default: "now()"

Creation timestamp

Example:

{
  name: 'Bebidas',
  description: 'Amplia variedad de bebidas',
  images: ['url1.jpg', 'url2.jpg'],
  features: [
    { title: 'Variedad', desc: '50+ marcas', icon: 'mdi:bottle' }
  ],
  displayType: 'features',
  align: 'left'
}

---

nosotros_config

Homepage “About Us” teaser section configuration (singleton).

id · serial · required

Primary key (only 1 row should exist)

badge · text · default: "'Nuestra Esencia'"

Section badge text

title · text

Main title

titleHighlight · text

Highlighted portion of title

description · text

Section description

ctaText · text · default: "'Conocer Más'"

Call-to-action button text

ctaLink · text · default: "'/nosotros'"

CTA button link

colors · jsonb

Color scheme: { bg, accent, textMain, textMuted, iconColor, statBg }

stats · jsonb

Statistics array: { number: string, label: string }[]

updatedAt · timestamp · default: "now()"

Last update time

Example:

{
  colors: {
    bg: '#455dd9',
    accent: '#ffd100',
    textMain: '#ffffff'
  },
  stats: [
    { number: '18+', label: 'Años' },
    { number: '500+', label: 'Clientes' }
  ]
}

---

nosotros_page

Full “About Us” page content (singleton).

id · serial · required

Primary key

heroBadge · text

Hero section badge

heroTitle · text

Hero section title

heroDescription · text

Hero section description

historyTitle · text

History section title

historyParagraphs · jsonb · default: "[]"

Array of history paragraphs: string[]

historyImageUrl · text

History section image URL

missionTitle · text

Mission section title

missionText · text

Mission statement

visionTitle · text

Vision section title

visionText · text

Vision statement

galleryImages · jsonb · default: "[]"

Photo gallery URLs: string[]

updatedAt · timestamp · default: "now()"

Last update time

---

User-Generated Content Tables

sugerencias

Customer suggestions and feedback.

id · uuid · required

Primary key, auto-generated UUID

tipo · text · required

Suggestion type (e.g., ‘Reclamo’, ‘Sugerencia’)

nombre · text

Customer name (optional, can be anonymous)

mensaje · text · required

Suggestion message

leido · boolean · default: "false"

Whether admin has marked as read

createdAt · timestamp · default: "now()"

Submission timestamp

---

sugerencias_config

Configuration for suggestion form options (singleton).

id · serial · required

Primary key

opciones · jsonb

Array of suggestion types: string[]

updatedAt · timestamp · default: "now()"

Last update time

Example:

{
  opciones: [
    'Sugerencia de Servicio',
    'Nuevo Producto Requerido',
    'Reclamo',
    'Felicitación'
  ]
}

---

postulaciones

Job applications from career page.

id · uuid · required

Primary key, auto-generated UUID

nombre · text · required

Applicant name

telefono · text · required

Phone number

email · text · required

Email address

sucursal · text · required

Branch/location applied to

cvUrl · text

Cloudinary URL of uploaded CV/resume

mensaje · text

Optional cover letter message

createdAt · timestamp · default: "now()"

Application submission time

---

empleo_sucursales

Available job locations/branches.

id · serial · required

Primary key

nombre · text · required

Branch name

activa · boolean · default: "true"

Whether branch is currently hiring

createdAt · timestamp · default: "now()"

Creation timestamp

---

Contest Tables

concursos

Active and past contests/giveaways.

id · serial · required

Primary key

title · text · default: "''"

Contest title

titleHighlight · text

Highlighted portion of title

description · text

Contest description

imageUrl · text

Cloudinary image URL

badgeText · text · default: "'Sorteo Activo'"

Badge text (e.g., ‘Activo’, ‘Finalizado’)

closeDate · text

Human-readable close date (e.g., ‘31 de Diciembre’)

prizeName · text

Prize description

ctaText · text · default: "'Ver Marcas Auspiciantes'"

Call-to-action text

disclaimer · text

Legal disclaimer or terms

isActive · boolean · default: "false"

Whether contest is currently active (only one should be active)

sortOrder · integer · default: "0"

Display order

createdAt · timestamp · default: "now()"

Creation timestamp

---

concursos_ganadores

Contest winners and testimonials.

id · serial · required

Primary key

concursoId · integer

Foreign key to concursos.id (CASCADE delete)

winnerName · text · default: "''"

Winner’s name

prize · text

Prize won

testimonial · text

Winner’s testimonial/quote

imageUrl · text

Winner’s photo URL

dateLabel · text

Date label (e.g., ‘Diciembre 2024’)

sortOrder · integer · default: "0"

Display order

createdAt · timestamp · default: "now()"

Creation timestamp

Relations:

• Many-to-one with concursos (optional, can be null)

---

Configuration Tables

footer_info

Company information for footer (singleton).

id · serial · required

Primary key

description · text

Company description

address · text

Main office address

phone · text

Phone number

mobile · text

Mobile number

email · text

Contact email

facebookUrl · text

Facebook profile URL

instagramUrl · text

Instagram profile URL

tiktokUrl · text

TikTok profile URL

whatsappUrl · text

WhatsApp contact URL

updatedAt · timestamp · default: "now()"

Last update time

---

footer_branches

Branch locations displayed in footer.

id · serial · required

Primary key

name · text · required

Branch name

address · text

Branch address

sortOrder · integer · default: "0"

Display order

createdAt · timestamp · default: "now()"

Creation timestamp

---

legal_pages

Editable legal pages (privacy policy, terms, etc.).

id · serial · required

Primary key

slug · text · required

URL slug (unique, e.g., ‘privacidad’, ‘terminos’)

title · text · default: "''"

Page title

content · text · default: "''"

Page content (HTML or markdown)

updatedAt · timestamp · default: "now()"

Last update time

Indexes:

• slug (unique)

---

site_config

Key-value configuration storage.

id · serial · required

Primary key

key · text · required

Config key (unique, e.g., ‘logo_url’)

value · text · required

Config value

Indexes:

• key (unique)

Example:

{ key: 'logo_url', value: 'https://res.cloudinary.com/.../logo.png' }

---

theme_config

Theme color configuration (singleton).

id · serial · required

Primary key

primaryColor · text · default: "'#1565C0'"

Primary brand color (hex)

secondaryColor · text · default: "'#FFD100'"

Secondary brand color (hex)

accentColor · text · default: "'#E4002B'"

Accent color (hex)

backgroundColor · text · default: "'#FAFAF7'"

Background color (hex)

updatedAt · timestamp · default: "now()"

Last update time

Note: The theme service actually manages colors in a CSS file (src/routes/layout.css) rather than this database table in the current implementation.

---

Database Relationships

Entity Relationship Diagram

user ──┬─< session (userId)
       └─< account (userId)

concursos ──< concursos_ganadores (concursoId)

Singletons:
- footer_info (1 row)
- nosotros_config (1 row)
- nosotros_page (1 row)
- sugerencias_config (1 row)
- theme_config (1 row)

Schema Management

Adding New Tables

1. Create schema file in /src/lib/server/db/schemas/:

// nueva_tabla.schema.js
import { pgTable, serial, text } from 'drizzle-orm/pg-core';

export const nuevaTabla = pgTable('nueva_tabla', {
    id: serial('id').primaryKey(),
    nombre: text('nombre').notNull()
});

2. Export in /src/lib/server/db/schema.js:

export * from './schemas/nueva_tabla.schema.js';

3. Push to database:

npm run db:push

Migrations

Drizzle Kit generates migrations automatically:

# Generate migration
drizzle-kit generate

# Apply migration
drizzle-kit migrate

# View schema in browser
drizzle-kit studio

Next Steps

• Services & Repositories - How to query these tables
• Architecture Overview - Overall system design
• Project Structure - File organization