Danila KhodjaefandCursor
8e444fa461
Prod env files now win over root .env so harmonize:db can open gallery_prod after gallery_dev; tour_stops gains updated_at; halls no longer block the overlay on every painting texture. Docs cover the fixes and one-artist image pull from prod; Duccio painting assets synced from TrueNAS. Co-authored-by: Cursor <cursoragent@cursor.com>
50 lines
1.8 KiB
PL/PgSQL
50 lines
1.8 KiB
PL/PgSQL
-- Guided tours: curated ordered stops with per-painting text.
|
|
|
|
CREATE TABLE IF NOT EXISTS tours (
|
|
id SERIAL PRIMARY KEY,
|
|
title VARCHAR(200) NOT NULL,
|
|
description TEXT NOT NULL DEFAULT '',
|
|
status VARCHAR(20) NOT NULL DEFAULT 'draft'
|
|
CHECK (status IN ('draft', 'published')),
|
|
cover_painting_id INTEGER REFERENCES paintings(id) ON DELETE SET NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS tours_status_idx ON tours (status);
|
|
CREATE INDEX IF NOT EXISTS tours_updated_at_idx ON tours (updated_at DESC);
|
|
|
|
CREATE TABLE IF NOT EXISTS tour_stops (
|
|
id SERIAL PRIMARY KEY,
|
|
tour_id INTEGER NOT NULL REFERENCES tours(id) ON DELETE CASCADE,
|
|
painting_id INTEGER NOT NULL REFERENCES paintings(id) ON DELETE CASCADE,
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
|
body TEXT NOT NULL DEFAULT '',
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE (tour_id, painting_id)
|
|
);
|
|
|
|
-- Existing DBs created before updated_at: migrate-sync-timestamps.sql also adds it.
|
|
ALTER TABLE tour_stops ADD COLUMN IF NOT EXISTS updated_at TIMESTAMPTZ NOT NULL DEFAULT now();
|
|
|
|
CREATE INDEX IF NOT EXISTS tour_stops_tour_idx ON tour_stops (tour_id, sort_order);
|
|
CREATE INDEX IF NOT EXISTS tour_stops_painting_idx ON tour_stops (painting_id);
|
|
|
|
CREATE OR REPLACE FUNCTION tours_touch_updated_at()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.updated_at = now();
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
DROP TRIGGER IF EXISTS tours_updated_at ON tours;
|
|
CREATE TRIGGER tours_updated_at
|
|
BEFORE UPDATE ON tours
|
|
FOR EACH ROW EXECUTE PROCEDURE tours_touch_updated_at();
|
|
|
|
DROP TRIGGER IF EXISTS tour_stops_updated_at ON tour_stops;
|
|
CREATE TRIGGER tour_stops_updated_at
|
|
BEFORE UPDATE ON tour_stops
|
|
FOR EACH ROW EXECUTE PROCEDURE tours_touch_updated_at();
|