81 lines
2.5 KiB
SQL
81 lines
2.5 KiB
SQL
CREATE TABLE IF NOT EXISTS template_asset (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
title VARCHAR(255) NOT NULL,
|
|
description TEXT,
|
|
file_name VARCHAR(255) NOT NULL,
|
|
storage_path TEXT NOT NULL,
|
|
storage_provider VARCHAR(30) NOT NULL DEFAULT 'local',
|
|
mime_type VARCHAR(120) NOT NULL,
|
|
size_bytes BIGINT NOT NULL DEFAULT 0,
|
|
sha256_hash VARCHAR(64) NOT NULL,
|
|
uploaded_by BIGINT REFERENCES app_user(id),
|
|
is_active BOOLEAN NOT NULL DEFAULT TRUE,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
ALTER TABLE template_item
|
|
ADD COLUMN IF NOT EXISTS content_format VARCHAR(20) NOT NULL DEFAULT 'markdown';
|
|
|
|
ALTER TABLE template_item
|
|
ADD COLUMN IF NOT EXISTS logo_asset_id BIGINT;
|
|
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (
|
|
SELECT 1
|
|
FROM pg_constraint
|
|
WHERE conname = 'template_item_logo_asset_id_fkey'
|
|
) THEN
|
|
ALTER TABLE template_item
|
|
ADD CONSTRAINT template_item_logo_asset_id_fkey
|
|
FOREIGN KEY (logo_asset_id) REFERENCES template_asset(id) ON DELETE SET NULL;
|
|
END IF;
|
|
END$$;
|
|
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (
|
|
SELECT 1
|
|
FROM pg_constraint
|
|
WHERE conname = 'template_item_content_format_check'
|
|
) THEN
|
|
ALTER TABLE template_item
|
|
ADD CONSTRAINT template_item_content_format_check
|
|
CHECK (content_format IN ('markdown', 'html'));
|
|
END IF;
|
|
END$$;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_template_item_logo_asset_id ON template_item(logo_asset_id);
|
|
CREATE INDEX IF NOT EXISTS idx_template_asset_active ON template_asset(is_active, created_at DESC);
|
|
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM pg_trigger WHERE tgname = 'trg_template_asset_updated_at'
|
|
) THEN
|
|
CREATE TRIGGER trg_template_asset_updated_at
|
|
BEFORE UPDATE ON template_asset
|
|
FOR EACH ROW EXECUTE FUNCTION set_updated_at();
|
|
END IF;
|
|
END$$;
|
|
|
|
ALTER TABLE template_asset ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE template_asset FORCE ROW LEVEL SECURITY;
|
|
|
|
DROP POLICY IF EXISTS template_asset_select_policy ON template_asset;
|
|
DROP POLICY IF EXISTS template_asset_modify_policy ON template_asset;
|
|
CREATE POLICY template_asset_select_policy ON template_asset
|
|
FOR SELECT USING (app_can_view_page('templates'));
|
|
CREATE POLICY template_asset_modify_policy ON template_asset
|
|
FOR ALL USING (app_has_permission('templates.write'))
|
|
WITH CHECK (app_has_permission('templates.write'));
|
|
|
|
UPDATE template_item
|
|
SET content = '{{template_logo}}
|
|
|
|
' || content,
|
|
updated_at = NOW()
|
|
WHERE category = 'meeting_protocol'
|
|
AND content NOT LIKE '%{{template_logo}}%';
|