20 lines
857 B
SQL
20 lines
857 B
SQL
-- Create tasks table for background job management
|
|
CREATE TABLE IF NOT EXISTS tasks (
|
|
id SERIAL PRIMARY KEY,
|
|
task_type VARCHAR(50) NOT NULL, -- e.g., 'nasa_download'
|
|
status VARCHAR(20) NOT NULL DEFAULT 'pending', -- pending, running, completed, failed, cancelled
|
|
description VARCHAR(255),
|
|
params JSONB, -- Store input parameters (body_ids, dates)
|
|
result JSONB, -- Store output results
|
|
progress INTEGER DEFAULT 0, -- 0 to 100
|
|
error_message TEXT,
|
|
created_by INTEGER, -- User ID who initiated
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
started_at TIMESTAMP WITH TIME ZONE,
|
|
completed_at TIMESTAMP WITH TIME ZONE
|
|
);
|
|
|
|
CREATE INDEX idx_tasks_status ON tasks(status);
|
|
CREATE INDEX idx_tasks_created_at ON tasks(created_at DESC);
|