Migration V26: Rename checksum fields and add per-user deduplication.
This migration renames checksum fields for clarity and adds per-user file deduplication while preserving the ability to query for popular files across all users.
Changes
- Enables
pgcryptoextension for cryptographic functions - Renames
checksumcolumn tofile_checksum(for clarity) - Drops unique index on
file_checksum(allows same file from different users) - Adds
user_file_checksumcolumn (SHA256 of user_id + file_checksum) - Creates unique index on
user_file_checksumto enforce per-user uniqueness - Backfills existing records with calculated user_file_checksum values
Requirements
- PostgreSQL with
pgcryptoextension support (enabled automatically)
Purpose
- Same user cannot upload the same file twice (duplicate prevention via user_file_checksum)
- Different users CAN upload the same file (no unique constraint on file_checksum)
- Original
file_checksumfield preserved for finding most popular images across all users - Clearer naming: file_checksum vs user_file_checksum