[BUG]: Database schema loading fails: DROP TRIGGER on non-existent table `payment_receipts`

[mbstudio2004]

Description

When attempting to load the database schema using db:schema:load or db:migrate on a fresh database, the migration fails because structure.sql tries to DROP TRIGGER on the payment_receipts table before the table is created.

Error Message

psql:/app/db/structure.sql:275: ERROR:  relation "public.payment_receipts" does not exist
bin/rails aborted!
failed to execute:
psql --set ON_ERROR_STOP=1 --quiet --no-psqlrc --output /dev/null --file /app/db/structure.sql lago

Steps to Reproduce

1. Create a fresh PostgreSQL database
2. Run bundle exec rails db:schema:load or bundle exec rails db:migrate
3. Migration fails at line 275 of structure.sql

Root Cause

In /app/db/structure.sql:

• Line 275: DROP TRIGGER IF EXISTS before_payment_receipt_insert ON public.payment_receipts;
• Line 4055: CREATE TABLE public.payment_receipts (...);

The DROP TRIGGER statement executes before the table is created, causing the error. While DROP TRIGGER IF EXISTS should be safe, PostgreSQL still throws an error when the table doesn't exist.

Expected Behavior

Schema should load successfully without errors. The DROP TRIGGER statement should either:

1. Be moved after the CREATE TABLE statement, OR
2. Be wrapped in a conditional that checks table existence first

Actual Behavior

Schema loading fails with the error above, preventing fresh database setup.

Environment

• Lago Version: v1.38.0 (also tested on v1.33.4 - same issue)
• PostgreSQL: 15+ (tested on Supabase PostgreSQL)
• Deployment: Kubernetes via Helm chart
• Database: Fresh/empty database

Workaround

Manually create the payment_receipts table before running schema load:

CREATE TABLE IF NOT EXISTS public.payment_receipts (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    number character varying NOT NULL,
    payment_id uuid NOT NULL,
    organization_id uuid NOT NULL,
    created_at timestamp(6) without time zone NOT NULL,
    updated_at timestamp(6) without time zone NOT NULL,
    billing_entity_id uuid NOT NULL
);

Then run db:schema:load or db:migrate.

Impact

• Severity: High - Blocks fresh database setup
• Frequency: 100% - Happens on every fresh database initialization
• Workaround Available: Yes (manual table creation)

Suggested Fix

Move the DROP TRIGGER statement to after the CREATE TABLE statement, or wrap it in a DO block that checks for table existence:

DO $$
BEGIN
    IF EXISTS (SELECT FROM pg_tables WHERE schemaname = 'public' AND tablename = 'payment_receipts') THEN
        DROP TRIGGER IF EXISTS before_payment_receipt_insert ON public.payment_receipts;
    END IF;
END $$;

Or simply move line 275 to after line 4055 (after the CREATE TABLE statement).

Additional Context

This bug affects:

• Fresh installations via Helm chart
• Docker deployments with empty databases
• Any scenario where db:schema:load or db:migrate is run on a fresh database

The issue is present in the structure.sql file generated by Rails, suggesting the migration order in the codebase may need review.

[vincent-pochet]

Thank you for reporting @mbstudio2004, we will investigate 👍

[vincent-pochet]

@mbstudio2004 we were not able to reproduce the issue with the latest version.
Can you confirm this is still an issue for you?