website/db/reorg/04_meta.sql

-- 04_meta.sql: GUI registry (meta.tables / meta.columns), runtime-DDL functions, schema `lists`.
SET ROLE personal;

CREATE TABLE meta.tables (
  id            integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  schema_name   text NOT NULL,
  table_name    text NOT NULL,
  slug          text NOT NULL UNIQUE CHECK (slug ~ '^[a-z0-9][a-z0-9-]{0,62}$'),
  display_name  text NOT NULL,
  description   text,
  icon          text,
  picture_url   text,
  kind          text NOT NULL CHECK (kind IN ('list','core','ref')),
  editable      boolean NOT NULL DEFAULT true,
  has_done      boolean NOT NULL DEFAULT false,
  default_sort  text,
  sort_order    integer NOT NULL DEFAULT 0,
  archived_at   timestamptz,
  created_at    timestamptz NOT NULL DEFAULT now(),
  updated_at    timestamptz NOT NULL DEFAULT now(),
  UNIQUE (schema_name, table_name)
);
COMMENT ON TABLE meta.tables IS 'One row per GUI-visible table (runtime lists + core + ref). Structure truth stays in pg_catalog; this is presentation.';

CREATE TABLE meta.columns (
  id          integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  table_id    integer NOT NULL REFERENCES meta.tables(id) ON DELETE CASCADE,
  column_name text NOT NULL,
  label       text,
  ui_type     text NOT NULL CHECK (ui_type IN ('text','longtext','url','image','number','cents','date','datetime','bool','select','multiselect','fk','json')),
  options     jsonb NOT NULL DEFAULT '{}'::jsonb,
  position    integer NOT NULL DEFAULT 0,
  hidden      boolean NOT NULL DEFAULT false,
  help_text   text,
  created_at  timestamptz NOT NULL DEFAULT now(),
  updated_at  timestamptz NOT NULL DEFAULT now(),
  UNIQUE (table_id, column_name)
);
CREATE INDEX columns_table_id_index ON meta.columns (table_id);

CREATE TRIGGER touch_updated_at BEFORE UPDATE ON meta.tables  FOR EACH ROW EXECUTE FUNCTION meta.touch_updated_at();
CREATE TRIGGER touch_updated_at BEFORE UPDATE ON meta.columns FOR EACH ROW EXECUTE FUNCTION meta.touch_updated_at();

RESET ROLE;
-- schema for runtime lists, owned by personal_ddl (not by the migration owner)
CREATE SCHEMA lists AUTHORIZATION personal_ddl;
COMMENT ON SCHEMA lists IS 'Runtime-created user lists. Never touched by Drizzle migrations (excluded via schemaFilter). Create/alter only via meta.* functions.';
GRANT USAGE, CREATE ON SCHEMA meta TO personal_ddl;
GRANT SELECT, INSERT, UPDATE, DELETE ON meta.tables, meta.columns TO personal_ddl;
SET ROLE personal_ddl;

-- guess a ui_type from a column's catalog type (format_type output) and name
CREATE FUNCTION meta.guess_ui_type(p_type text, p_col text, p_is_enum boolean DEFAULT false) RETURNS text
LANGUAGE sql IMMUTABLE SET search_path = pg_catalog AS $$
  SELECT CASE
    WHEN p_is_enum THEN 'select'
    WHEN p_type LIKE '%[]' THEN 'multiselect'
    WHEN p_col IN ('picture_url','image_url','icon_url','image') THEN 'image'
    WHEN p_col LIKE '%\_url' OR p_col = 'url' THEN 'url'
    WHEN p_col LIKE '%\_cents' THEN 'cents'
    WHEN p_col IN ('notes','description','content','note') THEN 'longtext'
    WHEN p_type IN ('smallint','integer','bigint','real','double precision') OR p_type LIKE 'numeric%' THEN 'number'
    WHEN p_type = 'boolean' THEN 'bool'
    WHEN p_type = 'date' THEN 'date'
    WHEN p_type LIKE 'timestamp%' THEN 'datetime'
    WHEN p_type IN ('json','jsonb') THEN 'json'
    ELSE 'text' END
$$;

-- reconcile meta.columns with pg_catalog for one table (or all registered tables).
-- Uses pg_attribute (not information_schema, which hides tables the caller has no grants on).
CREATE FUNCTION meta.sync(p_table_id integer DEFAULT NULL) RETURNS integer
LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, meta AS $$
DECLARE k integer;
BEGIN
  IF to_regclass('pg_temp._meta_cols') IS NULL THEN CREATE TEMP TABLE _meta_cols ON COMMIT DROP AS SELECT 1::integer AS table_id, ''::text AS col, ''::text AS typ, false AS is_enum, 0::integer AS pos, NULL::jsonb AS vals LIMIT 0; END IF;
  TRUNCATE _meta_cols;
  INSERT INTO _meta_cols
  SELECT t.id, a.attname, format_type(a.atttypid, a.atttypmod), ty.typtype = 'e', a.attnum,
         CASE WHEN ty.typtype = 'e' THEN (SELECT jsonb_agg(e.enumlabel ORDER BY e.enumsortorder) FROM pg_enum e WHERE e.enumtypid = ty.oid) END
    FROM meta.tables t
    JOIN pg_class c ON c.relname = t.table_name
    JOIN pg_namespace n ON n.oid = c.relnamespace AND n.nspname = t.schema_name
    JOIN pg_attribute a ON a.attrelid = c.oid AND a.attnum > 0 AND NOT a.attisdropped
    JOIN pg_type ty ON ty.oid = a.atttypid
   WHERE p_table_id IS NULL OR t.id = p_table_id;
  DELETE FROM meta.columns mc
   WHERE (p_table_id IS NULL OR mc.table_id = p_table_id)
     AND NOT EXISTS (SELECT 1 FROM _meta_cols x WHERE x.table_id = mc.table_id AND x.col = mc.column_name);
  INSERT INTO meta.columns (table_id, column_name, label, ui_type, options, position, hidden)
  SELECT x.table_id, x.col, initcap(replace(x.col, '_', ' ')), meta.guess_ui_type(x.typ, x.col, x.is_enum),
         CASE WHEN x.vals IS NOT NULL THEN jsonb_build_object('values', x.vals) ELSE '{}'::jsonb END,
         x.pos, x.col IN ('id','created_at','updated_at','user_id','sort_order')
    FROM _meta_cols x
  ON CONFLICT (table_id, column_name) DO NOTHING;
  GET DIAGNOSTICS k = ROW_COUNT;
  RETURN k;
END $$;

-- allowed column types for runtime lists -> SQL type
CREATE FUNCTION meta.sql_type(p_ui_type text) RETURNS text
LANGUAGE sql IMMUTABLE SET search_path = pg_catalog AS $$
  SELECT CASE p_ui_type
    WHEN 'text' THEN 'text' WHEN 'longtext' THEN 'text' WHEN 'url' THEN 'text' WHEN 'image' THEN 'text'
    WHEN 'select' THEN 'text' WHEN 'number' THEN 'numeric' WHEN 'cents' THEN 'integer'
    WHEN 'date' THEN 'date' WHEN 'datetime' THEN 'timestamptz' WHEN 'bool' THEN 'boolean'
    WHEN 'multiselect' THEN 'text[]' WHEN 'json' THEN 'jsonb' END
$$;

CREATE FUNCTION meta.assert_ident(p text) RETURNS void
LANGUAGE plpgsql IMMUTABLE SET search_path = pg_catalog AS $$
BEGIN
  IF p IS NULL OR p !~ '^[a-z][a-z0-9_]{0,62}$' THEN
    RAISE EXCEPTION 'invalid identifier: %', p USING ERRCODE = '22023';
  END IF;
END $$;

-- add one column to a runtime list. p_col: {"name","ui_type","label"?,"options"?,"default"?}
CREATE FUNCTION meta.add_column(p_slug text, p_col jsonb) RETURNS void
LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, meta AS $$
DECLARE
  t meta.tables; c text := p_col->>'name'; u text := coalesce(p_col->>'ui_type', 'text');
  st text := meta.sql_type(u); vals jsonb := p_col->'options'->'values'; ddl text;
BEGIN
  SELECT * INTO t FROM meta.tables WHERE slug = p_slug AND kind = 'list';
  IF NOT FOUND THEN RAISE EXCEPTION 'no runtime list %', p_slug; END IF;
  PERFORM meta.assert_ident(c);
  IF c IN ('id','name','notes','picture_url','done_at','sort_order','created_at','updated_at') THEN
    RAISE EXCEPTION 'column % is a standard column', c; END IF;
  IF st IS NULL THEN RAISE EXCEPTION 'unsupported ui_type %', u; END IF;
  ddl := format('ALTER TABLE lists.%I ADD COLUMN %I %s', t.table_name, c, st);
  IF p_col ? 'default' THEN ddl := ddl || format(' DEFAULT %L', p_col->>'default'); END IF;
  IF u = 'select' AND jsonb_typeof(vals) = 'array' THEN
    ddl := ddl || format(' CONSTRAINT %I CHECK (%I IN (%s))', t.table_name || '_' || c || '_check', c,
                         (SELECT string_agg(quote_literal(v), ',') FROM jsonb_array_elements_text(vals) v));
  END IF;
  EXECUTE ddl;
  INSERT INTO meta.columns (table_id, column_name, label, ui_type, options, position)
  VALUES (t.id, c, coalesce(p_col->>'label', initcap(replace(c, '_', ' '))), u,
          coalesce(p_col->'options', '{}'::jsonb),
          (SELECT coalesce(max(position), 0) + 1 FROM meta.columns WHERE table_id = t.id));
END $$;

-- create a runtime list table with the standard columns + extras
CREATE FUNCTION meta.create_list(
  p_slug text, p_table text, p_display_name text,
  p_has_done boolean DEFAULT true, p_columns jsonb DEFAULT '[]'::jsonb,
  p_picture_url text DEFAULT NULL, p_description text DEFAULT NULL, p_icon text DEFAULT NULL,
  p_sort_order integer DEFAULT 0
) RETURNS integer
LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, meta AS $$
DECLARE tid integer; col jsonb;
BEGIN
  PERFORM meta.assert_ident(p_table);
  IF EXISTS (SELECT 1 FROM pg_tables WHERE schemaname = 'lists' AND tablename = p_table) THEN
    RAISE EXCEPTION 'lists.% already exists', p_table; END IF;
  EXECUTE format($f$CREATE TABLE lists.%1$I (
      id          integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
      name        text NOT NULL CHECK (btrim(name) <> ''),
      notes       text,
      picture_url text,
      %2$s
      sort_order  integer NOT NULL DEFAULT 0,
      created_at  timestamptz NOT NULL DEFAULT now(),
      updated_at  timestamptz NOT NULL DEFAULT now())$f$,
    p_table, CASE WHEN p_has_done THEN 'done_at timestamptz,' ELSE '' END);
  EXECUTE format('CREATE TRIGGER touch_updated_at BEFORE UPDATE ON lists.%I FOR EACH ROW EXECUTE FUNCTION meta.touch_updated_at()', p_table);
  INSERT INTO meta.tables (schema_name, table_name, slug, display_name, description, icon, picture_url, kind, editable, has_done, default_sort, sort_order)
  VALUES ('lists', p_table, p_slug, p_display_name, p_description, p_icon, p_picture_url, 'list', true, p_has_done,
          CASE WHEN p_has_done THEN 'done_at nulls first, sort_order, name' ELSE 'sort_order, name' END, p_sort_order)
  RETURNING id INTO tid;
  PERFORM meta.sync(tid);
  UPDATE meta.columns SET label = 'Done', ui_type = 'datetime' WHERE table_id = tid AND column_name = 'done_at';
  FOR col IN SELECT * FROM jsonb_array_elements(p_columns) LOOP
    PERFORM meta.add_column(p_slug, col);
  END LOOP;
  RETURN tid;
END $$;

CREATE FUNCTION meta.rename_column(p_slug text, p_old text, p_new text) RETURNS void
LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, meta AS $$
DECLARE t meta.tables;
BEGIN
  SELECT * INTO t FROM meta.tables WHERE slug = p_slug AND kind = 'list';
  IF NOT FOUND THEN RAISE EXCEPTION 'no runtime list %', p_slug; END IF;
  PERFORM meta.assert_ident(p_new);
  IF p_old IN ('id','name','done_at','created_at','updated_at') THEN RAISE EXCEPTION 'standard column'; END IF;
  EXECUTE format('ALTER TABLE lists.%I RENAME COLUMN %I TO %I', t.table_name, p_old, p_new);
  UPDATE meta.columns SET column_name = p_new WHERE table_id = t.id AND column_name = p_old;
END $$;

CREATE FUNCTION meta.drop_column(p_slug text, p_col text) RETURNS void
LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, meta AS $$
DECLARE t meta.tables;
BEGIN
  SELECT * INTO t FROM meta.tables WHERE slug = p_slug AND kind = 'list';
  IF NOT FOUND THEN RAISE EXCEPTION 'no runtime list %', p_slug; END IF;
  IF p_col IN ('id','name','notes','picture_url','done_at','sort_order','created_at','updated_at') THEN
    RAISE EXCEPTION 'standard column'; END IF;
  EXECUTE format('ALTER TABLE lists.%I DROP COLUMN %I', t.table_name, p_col);
  DELETE FROM meta.columns WHERE table_id = t.id AND column_name = p_col;
END $$;

CREATE FUNCTION meta.archive_list(p_slug text) RETURNS void
LANGUAGE sql SECURITY DEFINER SET search_path = pg_catalog, meta AS $$
  UPDATE meta.tables SET archived_at = now() WHERE slug = p_slug AND kind = 'list' AND archived_at IS NULL
$$;

-- real DROP only after 7 days in the archive (backups cover the rest)
CREATE FUNCTION meta.drop_list(p_slug text) RETURNS void
LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, meta AS $$
DECLARE t meta.tables;
BEGIN
  SELECT * INTO t FROM meta.tables WHERE slug = p_slug AND kind = 'list';
  IF NOT FOUND THEN RAISE EXCEPTION 'no runtime list %', p_slug; END IF;
  IF t.archived_at IS NULL OR t.archived_at > now() - interval '7 days' THEN
    RAISE EXCEPTION 'archive % first and wait 7 days', p_slug; END IF;
  EXECUTE format('DROP TABLE lists.%I', t.table_name);
  DELETE FROM meta.tables WHERE id = t.id;
END $$;

RESET ROLE;
REVOKE EXECUTE ON ALL FUNCTIONS IN SCHEMA meta FROM PUBLIC;
GRANT EXECUTE ON FUNCTION meta.touch_updated_at() TO PUBLIC;   -- trigger fn, must stay callable