37 lines
1.3 KiB
PL/PgSQL
37 lines
1.3 KiB
PL/PgSQL
-- Migration: Create stream_jobs table
|
|
-- Description: Table pour suivre les jobs d'encodage audio
|
|
-- Date: 2025-01-27
|
|
|
|
CREATE TABLE IF NOT EXISTS stream_jobs (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
track_id UUID NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'encoding', 'done', 'error')),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
error_message TEXT,
|
|
|
|
-- Index pour performance
|
|
CONSTRAINT stream_jobs_track_id_fkey FOREIGN KEY (track_id) REFERENCES tracks(id) ON DELETE CASCADE
|
|
);
|
|
|
|
-- Index pour requêtes fréquentes
|
|
CREATE INDEX IF NOT EXISTS idx_stream_jobs_track_id ON stream_jobs(track_id);
|
|
CREATE INDEX IF NOT EXISTS idx_stream_jobs_status ON stream_jobs(status);
|
|
CREATE INDEX IF NOT EXISTS idx_stream_jobs_created_at ON stream_jobs(created_at);
|
|
|
|
-- Trigger pour mettre à jour updated_at automatiquement
|
|
CREATE OR REPLACE FUNCTION update_stream_jobs_updated_at()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.updated_at = NOW();
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
DROP TRIGGER IF EXISTS trigger_update_stream_jobs_updated_at ON stream_jobs;
|
|
|
|
CREATE TRIGGER trigger_update_stream_jobs_updated_at
|
|
BEFORE UPDATE ON stream_jobs
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION update_stream_jobs_updated_at();
|
|
|