Missing migration for Attachment.headers TEXT to JSONB conversion

[jacobmaster]

Missing migration for Attachment.headers TEXT to JSONB conversion

Summary

After upgrading to django-post-office 3.7.0+, the Attachment.headers field causes a PostgreSQL error because the database column remains as TEXT type while the model now uses Django's native JSONField (which expects JSONB).

Environment

• django-post-office version: 3.10.1 (also tested 3.11.0)
• Django version: 4.2.24
• Python version: 3.11.12
• Database: PostgreSQL 17.4
• Upgrade path: Likely upgraded from pre-3.7.0 version where headers was TEXT

Error Description

When accessing the Django admin page for an email with attachments (/admin/post_office/email/<id>/change/), the following error occurs:

Exception Type: ProgrammingError at /admin/post_office/email/732308/change/
Exception Value: operator does not exist: text -> unknown
LINE 1: ...AND NOT (("post_office_attachment"."headers" -> 'Content...
                                                         ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

Root Cause Analysis

1. Migration 0008_attachment_headers (from 2018, Django 1.11 era) added the field as:

   migrations.AddField(
       model_name='attachment',
       name='headers',
       field=models.JSONField(blank=True, null=True, verbose_name='Headers'),
   )

2. Back in Django 1.11, there was no native JSONField. The migration likely used a third-party package (like django-jsonfield) that created TEXT columns in PostgreSQL.

3. Version 3.7.0 (May 2023) changed to Django's native JSONField which uses JSONB type in PostgreSQL:

   "Changed JSON columns to use Django's JSONField and drop jsonfield dependency."

4. No migration was provided to convert existing TEXT columns to JSONB for users upgrading from earlier versions.

Database State Verification

-- Current state (broken)
SELECT data_type FROM information_schema.columns
WHERE table_name = 'post_office_attachment' AND column_name = 'headers';
-- Returns: text

-- Expected state
-- Should return: jsonb

Reproduction Steps

1. Install django-post-office < 3.7.0 (when headers used third-party JSONField)
2. Run migrations to create tables
3. Upgrade to django-post-office >= 3.7.0
4. Create an email with attachments that have headers
5. Try to view the email in Django admin: /admin/post_office/email/<id>/change/
6. Error occurs when Django ORM tries to use JSONB -> operator on TEXT column

Solution?

Maybe a migration should be provided that:

1. Checks if the column is TEXT type
2. Safely converts existing data
3. Alters column to JSONB
4. Is idempotent (safe to run multiple times)
5. Only runs on PostgreSQL (other databases may not need this)

migrations.RunSQL(
    sql=[
        """
        UPDATE post_office_attachment
        SET headers = CASE
            WHEN headers IS NULL OR headers = '' THEN NULL
            WHEN headers::text ~ '^[\\s]*\\{.*\\}[\\s]*$' THEN headers
            ELSE NULL
        END;
        """,
        """
        ALTER TABLE post_office_attachment
        ALTER COLUMN headers TYPE jsonb USING headers::jsonb;
        """
    ],
    reverse_sql=[
        "ALTER TABLE post_office_attachment ALTER COLUMN headers TYPE text;"
    ],
)

Current Workaround

I can manually run SQL or create a custom migration in my own app to fix the column type. This is not ideal because it requires manual intervention for each installation.