Edit Per-Tenant Tag & Description Views (Admin)

The admin editor at /admin/orgs/[id]/tag-views lets platform admins tweak two families of per-tenant SQL views, switched by the Tags | Descriptions toggle at the top:

• Tags — the price-tag content for each item lives in TagContent (JSONB on inventory / stones / watches rows), populated by tag_view_jewelry, tag_view_watch, tag_view_diamond, tag_view_colored_stone, and tag_view_pearl. When a tenant clicks Update Tag on an item, tag_refresh() runs the appropriate view filtered to that StockID and writes the result.
• Descriptions — desc_view_* (same five families) each return a single Description string per StockID. They feed the Generate → From template option on item descriptions, the legacy-app parity sibling of the ✦ AI generator. The option only appears in-app for tenants with the Description Templates feature flag enabled (Settings → Features); AI stays the universal default. Shipped empty (Description = NULL) — configure the SQL here before the option produces anything.

Both families share the same editor mechanics (Test / Apply / history / rollback) and the same public.tenant_view_history table.

Layout

• Group toggle — Tags | Descriptions
• Tab strip — one tab per inventory family (Jewelry, Watch, Diamond, Colored Stone, Pearl) within the active group
• SQL textarea showing the current view body (fetched via pg_get_viewdef)
• Sample StockID + Test button — runs the body as a CTE against the sample row and shows what each TagContent field would render to
• Apply button — snapshots the current body to tenant_view_history, then CREATE OR REPLACE VIEW to install the new body
• History list at the bottom — last 10 edits, each expandable to see the SQL, each with a Rollback button

Test-before-Apply

The Test button runs your body as a CTE against the active tenant schema:

WITH _v AS ({your body}) SELECT * FROM _v WHERE "StockID" = {sample}

Wraps in a transaction with SET LOCAL search_path so unqualified table refs in the body resolve to the tenant schema. The transaction commits (read-only, so no harm), and you see the resulting row — Line1 / Line2 / … / Line8 / Barcode / QR — with NULLs rendered as ∅ so you can tell NULL from empty string.

If the body fails to parse or execute, you get the Postgres error message in a red error block.

Apply (replace the view)

Confirms via dialog. On Apply:

1. Current body snapshots to public.tenant_view_history (so this Apply is itself reversible).
2. History prunes to the last 10 entries per tenant + view.
3. CREATE OR REPLACE VIEW tenant_xyz.tag_view_x AS {your body} runs.

After Apply, the textarea reloads from pg_get_viewdef — you’ll see the canonicalized SQL Postgres actually stored. Often Postgres adds ::text casts and re-formats whitespace.

Important: existing items don’t auto-refresh

Changing a view doesn’t update the TagContent for existing items. It only affects:

• New items created after the Apply
• Existing items when someone clicks Update Tag on the detail page
• Bulk SQL if you run SELECT tag_refresh("StockID", 'J') FROM tenant_xyz.inventory WHERE ...

This is intentional. View changes are non-destructive — existing tag content stays exactly as printed.

SQL safety

The Apply / Test paths validate the body via validateTenantViewBody() in $lib/server/tagViewSqlSafety (it guards both tag and description views):

• Body must start with SELECT or WITH (whitespace-tolerant, case-insensitive)
• Forbidden keywords (DROP / DELETE / INSERT / UPDATE / TRUNCATE / GRANT / REVOKE / CREATE / ALTER / etc.) rejected
• Multi-statement attempts (any ; outside string literals) rejected

So even though the textarea accepts arbitrary SQL, you can’t drop tables or insert junk through this editor.

Rollback

Each history row has a Rollback button. Confirm → snapshots the current body to history (the rollback is itself reversible), then CREATE OR REPLACE with the historical body. Same safety guards apply.

Cardinal note: hand-editing the views

If you’d rather edit the SQL directly in Neon (skipping the admin UI), nothing breaks — tag_refresh() reads whatever the view returns. The admin UI just gives you Test / history / rollback without leaving JT.

Tip on ::text casts

Postgres canonicalizes a view body when storing it — that’s why you often see ::text casts that you didn’t write. They’re plumbing for type unification across CASE branches and || concat. You can usually leave them alone; if you remove them and Postgres needs them, it’ll just re-insert.

For numeric columns that you want in a tag line, you MUST cast: '$' || RetailPrice::text works, '$' || RetailPrice errors (text || numeric not allowed in PG 10+).

See also

• Tag Templates Killed — the kill plan / how we got here
• Print Tags — physical printing flow