22 lines
659 B
SQL
22 lines
659 B
SQL
-- Add created_at column to plugins table
|
|
ALTER TABLE plugins
|
|
ADD COLUMN added_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP;
|
|
|
|
-- Backfill added_at from the earliest build for each plugin
|
|
WITH earliest_builds AS (
|
|
SELECT plugin_slug, MIN(created_at) AS earliest_created_at
|
|
FROM builds
|
|
GROUP BY plugin_slug
|
|
)
|
|
UPDATE plugins p
|
|
SET added_at = eb.earliest_created_at
|
|
FROM earliest_builds eb
|
|
WHERE p.slug = eb.plugin_slug;
|
|
|
|
-- Legacy plugins with no builds get backfilled to 5 months ago so they expire sooner
|
|
UPDATE plugins p
|
|
SET added_at = NOW() - INTERVAL '5 months'
|
|
WHERE NOT EXISTS (
|
|
SELECT 1 FROM builds b WHERE b.plugin_slug = p.slug
|
|
);
|