Database Initialization Fails During Docker Setup

Description

When running NextLog locally using Docker Compose, the database initialization process fails with multiple PostgreSQL errors during the installation phase.

Steps to Reproduce:

1. Clone the repository and set up Docker environment ( cp .env .env.docker)
2. Run docker-compose up -d to start the containers
3. Access the web interface and begin the installation process
4. The database installation API fails with various PostgreSQL errors

Error Logs:

nextlog-app | ERROR: trigger "update_users_updated_at" for relation "users" already exists (42710)
nextlog-app | ERROR: column "role" does not exist in table "users" (42703)
nextlog-app | ERROR: column "qrz_qsl_sent" does not exist in table "contacts" (42703)
nextlog-app | ERROR: syntax error at or near "NOT" (42601)
nextlog-app | ERROR: syntax error at or near "//" (42601)

Environment:

• Local Docker setup (Docker Compose)
• PostgreSQL container with automatic initialization via postgres-init.sql
• NextLog application container attempting to run installation API

Expected Behavior:
The database should initialize successfully and the application should complete installation without errors when running in Docker.

Actual Behavior:
Database initialization fails due to conflicts between the automatic PostgreSQL initialization and the application's installation API, preventing the application from starting properly.

The issue appears to be related to the interaction between the Docker-based PostgreSQL initialization and the application's database setup logic.

Our Solution Summary

Problem Identified:
Database initialization conflicts between Docker's automatic PostgreSQL setup (postgres-init.sql) and the application's installation API.

Key Issues Fixed:

1. Missing Table Fields

   • Added missing role, status, last_login fields to users table
   • Added missing QRZ QSL fields (qrz_qsl_sent, qrz_qsl_rcvd, etc.) to contacts table

2. Trigger Creation Conflicts

   • Replaced CREATE TRIGGER with conditional DO blocks using pg_trigger checks
   • Added existence checks before creating triggers to prevent "already exists" errors

Files Modified:

• /src/app/api/install/database/route.ts - Fixed trigger creation logic and SQL syntax
• postgres-init.sql - Added missing table fields (optional, for future prevention)

Result

Database installation now completes successfully without conflicts between Docker's automatic initialization and the application's installation process.

Start with src/app/api/install/database/route.ts and postgres-init.sql, then reproduce the installation with docker-compose up -d and inspect the PostgreSQL errors. Done means the database installation completes successfully in the Docker setup without trigger, missing-column, or SQL syntax errors.