Domain Registration Fix - March 9, 2026

Issue

Domain registration for naturalelementsearlylearning.com.au failed with error:

Status Code: 500 Internal Server Error
Error: "Failed to submit domain registration request"
Details: "column \"domain_type\" of relation \"domain_registration_requests\" does not exist"

Root Cause

The backend code (routes/domainRequests.js) was attempting to INSERT a domain_type column that didn't exist in the production database table.

Code expectation:

domain_type = 'registration',  // Default value in destructuring

SQL INSERT statement:

INSERT INTO domain_registration_requests (
  tenant_id, customer_id, contract_id, domain_name, domain_type, years, ...

Actual database schema: Missing domain_type column

Why This Happened

1. The original schema file (2026_02_11_domain_registration_requests.sql) didn't include domain_type
2. Code was enhanced to support different domain request types (registration, transfer, renewal, dns-only)
3. Database schema was not updated when code was enhanced
4. Development environment may have had the column manually added
5. Production database was missing the column

Resolution

Immediate Fix (Production)

Added the missing column directly to production database:

ALTER TABLE domain_registration_requests 
ADD COLUMN domain_type VARCHAR(50) DEFAULT 'registration';

Applied: March 9, 2026 at approximately 14:30 UTC

Schema Updates

1. Updated base schema: rmm-psa-database/2026_02_11_domain_registration_requests.sql
   • Added domain_type column with:
     • Type: VARCHAR(50)
     • Default: 'registration'
     • Constraint: CHECK (domain_type IN ('registration', 'transfer', 'renewal', 'dns-only'))
2. Created migration: rmm-psa-database/migrations/2026_03_09_add_domain_type_column.sql
   • Safe migration with existence check
   • Can be run on any database (idempotent)
   • Adds column only if it doesn't exist

Domain Type Values

The domain_type column supports:

• registration (default) - Register a new domain for the first time
• transfer - Transfer an existing domain from another registrar
• renewal - Renew/extend an existing domain registration
• dns-only - DNS management without domain registration

Testing

After the fix, domain registration should work correctly. To test:

1. Visit the customer portal
2. Navigate to domain registration
3. Search for an available domain
4. Fill in contact information
5. Submit the registration request

The request should now:

• ✅ Create a record in domain_registration_requests table
• ✅ Set domain_type to 'registration' by default
• ✅ Set status to 'pending'
• ✅ Notify root tenant admins
• ✅ Return success response to customer

Verification Query

To verify the fix in production:

-- Check the column exists
SELECT column_name, data_type, column_default 
FROM information_schema.columns 
WHERE table_name = 'domain_registration_requests' 
AND column_name = 'domain_type';
 
-- Check if registration succeeded
SELECT request_id, domain_name, domain_type, status, created_at, requested_by
FROM domain_registration_requests
WHERE domain_name = 'naturalelementsearlylearning.com.au'
ORDER BY created_at DESC;

Related Files

• Backend route: rmm-psa-backend/routes/domainRequests.js
• Base schema: rmm-psa-database/2026_02_11_domain_registration_requests.sql
• Migration: rmm-psa-database/migrations/2026_03_09_add_domain_type_column.sql
• Analysis doc: rmm-psa-backend/DOMAIN_REGISTRATION_ANALYSIS.md

Commits

• Schema fix: Updated base schema and created migration
• Backend enhancement: Enhanced logging for domain registration debugging

Next Steps

1. ✅ Column added to production database
2. ✅ Schema files updated
3. ✅ Migration created for future deployments
4. ⏳ User should retry domain registration
5. ⏳ Verify request appears in database
6. ⏳ Confirm admin notification received

Prevention

To prevent similar issues:

1. Schema-first approach: Update schema files before or with code changes
2. Migration discipline: Create migrations for all schema changes
3. Development parity: Ensure dev/staging databases match production schema
4. Integration tests: Add tests that exercise full request flow
5. Schema validation: Consider adding automated schema validation in CI/CD

Contact

If issues persist after this fix, check:

• Backend logs: /api/domain-requests endpoint
  
• Database: domain_registration_requests table
• Error response from API call