[Server] Database Migration: Advanced Features Schema

No labels

No milestone

No project

No assignees

1 participant

Notifications

Due date

The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set.

Reference: WildDragonLLC/dragonflight#33

zgaetano commented

2026-05-24 11:56:05 -04:00

Owner

Overview

Create database migration for conform and trim job types, temp segments table, and asset tracking.

Migration: `006-advanced-features.sql`

Tasks

Add 'conform' to job_type enum
Add 'trim' to job_type enum
Create temp_segments table
Add conform_source_sequence_id to assets table
Create indexes for temp_segments
Create index for conform_source tracking

Schema Changes

-- Add job types
ALTER TYPE job_type ADD VALUE IF NOT EXISTS 'conform';
ALTER TYPE job_type ADD VALUE IF NOT EXISTS 'trim';

-- Temp segments table
CREATE TABLE IF NOT EXISTS temp_segments (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  job_id UUID NOT NULL REFERENCES jobs(id) ON DELETE CASCADE,
  clip_instance_id UUID NOT NULL,
  asset_id UUID NOT NULL REFERENCES assets(id) ON DELETE CASCADE,
  s3_key TEXT NOT NULL,
  expires_at TIMESTAMPTZ NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_temp_segments_expires_at ON temp_segments(expires_at);
CREATE INDEX IF NOT EXISTS idx_temp_segments_job_id ON temp_segments(job_id);

-- Asset conform tracking
ALTER TABLE assets ADD COLUMN IF NOT EXISTS conform_source_sequence_id UUID REFERENCES sequences(id);
CREATE INDEX IF NOT EXISTS idx_assets_conform_source ON assets(conform_source_sequence_id);

Files

services/mam-api/src/db/migrations/006-advanced-features.sql

Testing

Test migration on dev database
Verify indexes are created
Test rollback (if needed)
Document migration in changelog

Estimated Time

2 hours

## Overview Create database migration for conform and trim job types, temp segments table, and asset tracking. ## Migration: `006-advanced-features.sql` ### Tasks - [ ] Add 'conform' to job_type enum - [ ] Add 'trim' to job_type enum - [ ] Create temp_segments table - [ ] Add conform_source_sequence_id to assets table - [ ] Create indexes for temp_segments - [ ] Create index for conform_source tracking ### Schema Changes ```sql -- Add job types ALTER TYPE job_type ADD VALUE IF NOT EXISTS 'conform'; ALTER TYPE job_type ADD VALUE IF NOT EXISTS 'trim'; -- Temp segments table CREATE TABLE IF NOT EXISTS temp_segments ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), job_id UUID NOT NULL REFERENCES jobs(id) ON DELETE CASCADE, clip_instance_id UUID NOT NULL, asset_id UUID NOT NULL REFERENCES assets(id) ON DELETE CASCADE, s3_key TEXT NOT NULL, expires_at TIMESTAMPTZ NOT NULL, created_at TIMESTAMPTZ DEFAULT NOW() ); CREATE INDEX IF NOT EXISTS idx_temp_segments_expires_at ON temp_segments(expires_at); CREATE INDEX IF NOT EXISTS idx_temp_segments_job_id ON temp_segments(job_id); -- Asset conform tracking ALTER TABLE assets ADD COLUMN IF NOT EXISTS conform_source_sequence_id UUID REFERENCES sequences(id); CREATE INDEX IF NOT EXISTS idx_assets_conform_source ON assets(conform_source_sequence_id); ``` ## Files - `services/mam-api/src/db/migrations/006-advanced-features.sql` ## Testing - [ ] Test migration on dev database - [ ] Verify indexes are created - [ ] Test rollback (if needed) - [ ] Document migration in changelog ## Estimated Time 2 hours

zgaetano referenced this issue from a commit

2026-05-24 12:42:28 -04:00

feat(db): migration 012 — advanced features schema

zgaetano closed this issue

2026-05-24 12:42:29 -04:00

[Server] Database Migration: Advanced Features Schema #33