Lens Versioning Schema

Tables

lenses.versions

Immutable snapshots of a lens's template body. Published versions cannot be modified.

Column	Type	Notes
id	uuid	PK
lens_id	uuid	FK -> lenses.lenses
version_number	int	Auto-incremented per lens
template_body	text	Lens template with placeholders. Min 50 chars.
status	content_status	draft -> published -> archived
changelog	text	Optional release note
parent_version_id	uuid	FK -> self (for forked versions)
published_at	timestamptz	Set automatically on publish
created_at	timestamptz

Constraints:

• lens_versions_template_body_min_length: length(trim(template_body)) >= 50 for ALL statuses.
• published_at is non-null if and only if status = 'published' (CHECK constraint).

Note: template_body was removed from lenses.lenses in migration 20260324000000. The canonical content now lives exclusively in lenses.versions.

lenses.version_parameters

Typed parameter definitions per version. Parameters belong to versions, not to the lens asset.

Column	Type	Notes
id	uuid	PK
version_id	uuid	FK -> lenses.versions
key	text	Template placeholder name
type	text	text, number, boolean, select, textarea, json
required	boolean
default_value	text
validation_schema	jsonb	Optional JSON Schema for validation
options	jsonb	For select type: [{label, value}]

lenses.parameters (DEPRECATED)

Legacy parameter definitions at the lens level. Use lenses.version_parameters instead.

lenses.version_resources

Junction table binding resources to named version slots.

Column	Type	Notes
version_id	uuid	FK -> lenses.versions
resource_id	uuid	FK -> ai.resources
binding_key	text	Named slot: context_doc, reference_image, etc.

Views

lenses.vw_lens_version_history

All versions with parameter count. Use .eq('status', 'published') to filter published-only.

lenses.vw_published_versions

Published versions only, with parameter count.

lenses.vw_lenses

Lenses with latest non-archived version metadata (via LATERAL join), original translation, and author profile. template_body is sourced from the version, not from the lens row.

lenses.vw_fork_history

Recursive fork ancestry chain. Query by lens_id to get all ancestors. Depth 1 = immediate parent. Capped at 20 levels.

RPCs

Function	Security	Description
lenses.fn_lens_create(...)	DEFINER	Atomic lens creation: lens + version 1 + translation + tags.
lenses.fn_lens_update(...)	DEFINER	Atomic lens update: visibility, template body (via version upsert), translation, tags.
lenses.fn_upsert_draft_version(p_lens_id, p_template_body, ...)	DEFINER	Creates/updates draft version. Validates 50-char minimum.
lenses.fn_publish_version(p_version_id)	DEFINER	Ownership check + atomic draft -> published transition.
lenses.fn_clone_lens(p_source_lens_id, p_version_id)	DEFINER	Clones from published version of public+published lens only.
lenses.fn_list_versions(p_lens_id)	INVOKER	Lists versions descending. Caller RLS applies.

Architecture

Version lifecycle

draft -> published -> archived

• Draft: Editable. Only one draft per lens at a time. fn_upsert_draft_version reuses existing draft or creates new version.
• Published: Immutable. published_at is set. Cannot be modified.
• Archived: Soft-deleted. Excluded from "latest version" resolution in views.

Content ownership

template_body lives exclusively in lenses.versions. The lenses.lenses table stores only metadata (visibility, status, fork lineage). This eliminates the previous dual-write sync via triggers.

Cloning rules

• Only public + published lenses can be cloned.
• Only published versions can be selected as clone source.
• Cloned lens is created as private with parent_lens_id set to source.

Rollback

-- See bottom of supabase/migrations/20260324000000_lens_schema_refactor.sql