Skip to main content

​
Overview

Tresa Contafy uses PostgreSQL 12+ as its database with Sequelize as the ORM. This guide covers database setup, configuration, and connection management.

​
Requirements

PostgreSQL

Version 12 or higher required

Sequelize

ORM for database migrations and models

​
Local Development Setup

1

Install PostgreSQL

Install PostgreSQL on your local machine:
brew install postgresql@16
brew services start postgresql@16
2

Create Database

Create a new database for Tresa Contafy:
# Connect to PostgreSQL
psql postgres

# Create database
CREATE DATABASE tresa_contafy;

# Create user (optional)
CREATE USER tresa_user WITH PASSWORD 'your_password';

# Grant privileges
GRANT ALL PRIVILEGES ON DATABASE tresa_contafy TO tresa_user;

# Exit
\q
3

Configure DATABASE_URL

Add the connection string to your .env file:
DATABASE_URL=postgresql://tresa_user:your_password@localhost:5432/tresa_contafy

​
Production Setup

1

Create PostgreSQL Service

In your Railway project:
  1. Click “New” → “Database” → “Add PostgreSQL”
  2. Railway will automatically provision the database
2

Copy Connection String

Railway provides the DATABASE_URL automatically:
  • Go to your PostgreSQL service
  • Click “Variables” tab
  • Copy the DATABASE_URL value
3

Add to API Service

Add DATABASE_URL to your API service environment variables:
DATABASE_URL=${{Postgres.DATABASE_URL}}
Railway automatically enables SSL for database connections

​
Other Hosting Providers

For AWS RDS, DigitalOcean, or other providers: 
DATABASE_URL=postgresql://username:[email protected]:5432/tresa_contafy

​
Database Configuration

​
Connection Settings

The database configuration is defined in src/database/config.ts: 
const sequelize = new Sequelize(databaseUrl, {
  dialect: 'postgres',
  logging: false,
  dialectOptions: {
    ssl: isRailway || process.env.NODE_ENV === 'production' 
      ? { 
          require: true, 
          rejectUnauthorized: false 
        } 
      : false,
    keepAlive: true,
    keepAliveInitialDelayMillis: 10000,
  },
  pool: {
    max: 5,
    min: 1,
    acquire: 60000,
    idle: 30000,
    evict: 10000,
  },
});

​
Connection Pool Settings

​
max
number
default:"5"
Maximum number of simultaneous connections
​
min
number
default:"1"
Minimum number of connections to keep alive (prevents frequent reconnections)
​
acquire
number
default:"60000"
Maximum time (ms) to acquire a connection before throwing error
​
idle
number
default:"30000"
Maximum time (ms) a connection can be idle before being released
​
evict
number
default:"10000"
Time interval (ms) to check for idle connections

​
SSL Configuration

SSL is automatically enabled when:
  • DATABASE_URL contains railway.app or rlwy.net
  • NODE_ENV is set to production
For manual SSL configuration: 
dialectOptions: {
  ssl: {
    require: true,
    rejectUnauthorized: false // For self-signed certificates
  }
}

​
Database Schema

​
Main Tables

Tresa Contafy uses the following database tables:
User accounts with authentication and profile information
  • Email verification tokens
  • Password reset tokens
  • Firebase UID (for Google auth)
  • Trial and branding settings
RFC profiles (Mexican tax IDs) for each user
  • Multiple profiles per user
  • Fiscal regime information
  • SAT download credentials (FIEL)
  • Freeze settings for period locking
Incoming invoices (facturas) from XML CFDI files
  • Tax calculations (IVA, ISR, IEPS)
  • Payment type (PUE/PPD)
  • SAT validation status
Outgoing expenses from XML CFDI files
  • Payment dates
  • Tax deductibility tracking
Payment complement documents (complementos de pago)
  • Links to related invoices
  • Payment matching for PPD invoices
Stripe subscription management
  • Trial periods
  • Subscription status
  • Plugin access
Stripe webhook events log
  • Payment history
  • Subscription changes

​
Testing Database Connection

Verify your database connection: 
# Using psql
psql $DATABASE_URL -c "SELECT version();"

​
Troubleshooting

Problem: Database connection times outSolutions:
  • Verify DATABASE_URL is correct
  • Check firewall rules allow connections
  • Ensure database is running and accessible
  • Verify SSL settings match provider requirements
Problem: SSL connection required or certificate errorsSolutions:
  • Add ?sslmode=require to connection string
  • Set rejectUnauthorized: false for self-signed certificates
  • Update dialectOptions.ssl in config
Problem: Too many connections errorSolutions:
  • Reduce pool.max in configuration
  • Check for connection leaks in application code
  • Upgrade database plan for more connections
Problem: Random connection resetsSolutions:
  • Enable keepAlive: true in dialectOptions
  • Increase pool.idle timeout
  • Ensure pool.min is at least 1

​
Next Steps

Run Migrations

Initialize database schema with Sequelize migrations

Environment Variables

Configure all required environment variables

Build docs developers (and LLMs) love

Get started for freeTalk to us