PRAGMA foreign_keys = ON;
-- V2 tournament evidence is persisted only after migration 0045 has accepted
-- the exact selected manifest. The elite snapshot is inserted last and acts as
-- the aggregate-completeness marker for the atomic D1 batch.
CREATE TABLE beauty_design_v2_selections (
id TEXT PRIMARY KEY,
manifest_id TEXT NOT NULL,
identity_id TEXT NOT NULL,
site_spec_id TEXT NOT NULL,
project_id TEXT NOT NULL,
tournament_version TEXT NOT NULL CHECK (
tournament_version = 'beauty-design-tournament-v2'
),
compiler_version TEXT NOT NULL CHECK (
compiler_version = 'beauty-design-generator-v2'
),
capsule_id TEXT NOT NULL,
capsule_version TEXT NOT NULL,
selected_bundle_id TEXT NOT NULL,
selected_ordinal INTEGER NOT NULL CHECK (
selected_ordinal BETWEEN 0 AND 23
),
selected_candidate_nonce INTEGER NOT NULL CHECK (
selected_candidate_nonce BETWEEN 0 AND 255
),
candidate_count INTEGER NOT NULL CHECK (
candidate_count BETWEEN 16 AND 24
),
semantic_fingerprint TEXT NOT NULL CHECK (
length(semantic_fingerprint) = 64
AND semantic_fingerprint = lower(semantic_fingerprint)
AND lower(semantic_fingerprint) NOT GLOB '*[^0-9a-f]*'
),
semantic_first_viewport_fingerprint TEXT NOT NULL CHECK (
length(semantic_first_viewport_fingerprint) = 64
AND semantic_first_viewport_fingerprint =
lower(semantic_first_viewport_fingerprint)
AND lower(semantic_first_viewport_fingerprint) NOT GLOB '*[^0-9a-f]*'
),
quality_floor REAL NOT NULL CHECK (quality_floor BETWEEN 0 AND 100),
selected_quality_score REAL NOT NULL CHECK (
selected_quality_score BETWEEN 0 AND 100
),
selected_capsule_coherence_score REAL NOT NULL CHECK (
selected_capsule_coherence_score BETWEEN 0 AND 100
),
selected_content_fit_score REAL NOT NULL CHECK (
selected_content_fit_score BETWEEN 0 AND 100
),
used_safe_fallback INTEGER NOT NULL DEFAULT 0 CHECK (
used_safe_fallback = 0
),
requires_review INTEGER NOT NULL DEFAULT 0 CHECK (
requires_review = 0
),
acceptance_state TEXT NOT NULL DEFAULT 'AUTO_ACCEPTED' CHECK (
acceptance_state = 'AUTO_ACCEPTED'
),
selection_reason TEXT NOT NULL CHECK (
length(trim(selection_reason)) BETWEEN 8 AND 1000
),
tournament_json TEXT NOT NULL CHECK (
json_valid(tournament_json)
AND json_extract(tournament_json, '$.version') = tournament_version
AND json_extract(tournament_json, '$.candidateCount') = candidate_count
AND json_array_length(tournament_json, '$.candidates') = candidate_count
AND json_extract(tournament_json, '$.selectedOrdinal') = selected_ordinal
AND json_extract(tournament_json, '$.minimumQualityScore') = quality_floor
AND json_extract(tournament_json, '$.usedSafeFallback') = 0
AND json_extract(tournament_json, '$.requiresReview') = 0
AND json_extract(tournament_json, '$.selectionDisposition') = 'accepted'
AND json_extract(tournament_json, '$.selectionReason') = selection_reason
),
selected_lineage_json TEXT NOT NULL CHECK (
json_valid(selected_lineage_json)
AND json_extract(selected_lineage_json, '$.hardRejected') = 0
AND json_extract(selected_lineage_json, '$.capsuleId') = capsule_id
AND json_extract(selected_lineage_json, '$.capsuleVersion') =
capsule_version
AND json_extract(selected_lineage_json, '$.bundleId') =
selected_bundle_id
),
selected_semantic_fingerprint_json TEXT NOT NULL CHECK (
json_valid(selected_semantic_fingerprint_json)
AND json_extract(selected_semantic_fingerprint_json, '$.version') =
'beauty-semantic-fingerprint-v2'
AND json_extract(
selected_semantic_fingerprint_json,
'$.fingerprint'
) = semantic_fingerprint
AND json_extract(
selected_semantic_fingerprint_json,
'$.firstViewportFingerprint'
) = semantic_first_viewport_fingerprint
),
created_at TEXT NOT NULL,
FOREIGN KEY (manifest_id, project_id, site_spec_id)
REFERENCES beauty_design_manifests(id, project_id, site_spec_id)
ON DELETE RESTRICT,
FOREIGN KEY (identity_id, project_id, site_spec_id)
REFERENCES beauty_design_identities(id, project_id, site_spec_id)
ON DELETE RESTRICT,
UNIQUE (id, project_id, site_spec_id, manifest_id),
UNIQUE (manifest_id),
UNIQUE (identity_id),
UNIQUE (site_spec_id, semantic_fingerprint),
CHECK (selected_ordinal < candidate_count),
CHECK (selected_quality_score >= quality_floor)
);
CREATE INDEX beauty_design_v2_selections_capsule_idx
ON beauty_design_v2_selections(capsule_id, created_at);
CREATE INDEX beauty_design_v2_selections_project_idx
ON beauty_design_v2_selections(project_id, site_spec_id, manifest_id);
CREATE TRIGGER beauty_design_v2_selection_manifest_guard
BEFORE INSERT ON beauty_design_v2_selections
WHEN NOT EXISTS (
SELECT 1
FROM beauty_design_manifests AS manifest
JOIN beauty_design_identities AS identity
ON identity.id = manifest.identity_id
AND identity.project_id = manifest.project_id
AND identity.site_spec_id = manifest.site_spec_id
WHERE manifest.id = NEW.manifest_id
AND manifest.identity_id = NEW.identity_id
AND manifest.site_spec_id = NEW.site_spec_id
AND manifest.project_id = NEW.project_id
AND manifest.generator_version = NEW.compiler_version
AND manifest.candidate_nonce = NEW.selected_candidate_nonce
AND json_extract(manifest.manifest_json, '$.generatorVersion') =
NEW.compiler_version
AND json_extract(manifest.manifest_json, '$.candidateNonce') =
NEW.selected_candidate_nonce
AND identity.state = 'ACCEPTED'
AND identity.accepted_at IS NOT NULL
)
BEGIN
SELECT RAISE(
ABORT,
'beauty v2 selection lacks an isolated accepted manifest'
);
END;
CREATE TRIGGER beauty_design_v2_selections_no_update
BEFORE UPDATE ON beauty_design_v2_selections
BEGIN SELECT RAISE(ABORT, 'beauty v2 selections are immutable'); END;
CREATE TRIGGER beauty_design_v2_selections_no_delete
BEFORE DELETE ON beauty_design_v2_selections
BEGIN SELECT RAISE(ABORT, 'beauty v2 selections are immutable'); END;
CREATE TRIGGER beauty_design_v2_selections_no_replace
BEFORE INSERT ON beauty_design_v2_selections
WHEN EXISTS (
SELECT 1
FROM beauty_design_v2_selections AS existing
WHERE existing.id = NEW.id
OR existing.manifest_id = NEW.manifest_id
OR existing.identity_id = NEW.identity_id
)
BEGIN SELECT RAISE(ABORT, 'beauty v2 selections reject replacement'); END;
CREATE TABLE beauty_design_v2_candidates (
selection_id TEXT NOT NULL,
project_id TEXT NOT NULL,
site_spec_id TEXT NOT NULL,
manifest_id TEXT NOT NULL,
ordinal INTEGER NOT NULL CHECK (ordinal BETWEEN 0 AND 23),
candidate_nonce INTEGER NOT NULL CHECK (candidate_nonce BETWEEN 0 AND 255),
capsule_id TEXT NOT NULL,
bundle_id TEXT NOT NULL,
semantic_fingerprint TEXT NOT NULL CHECK (
length(semantic_fingerprint) = 64
AND semantic_fingerprint = lower(semantic_fingerprint)
AND lower(semantic_fingerprint) NOT GLOB '*[^0-9a-f]*'
),
first_viewport_fingerprint TEXT NOT NULL CHECK (
length(first_viewport_fingerprint) = 64
AND first_viewport_fingerprint = lower(first_viewport_fingerprint)
AND lower(first_viewport_fingerprint) NOT GLOB '*[^0-9a-f]*'
),
eligible INTEGER NOT NULL CHECK (eligible IN (0, 1)),
selected INTEGER NOT NULL CHECK (selected IN (0, 1)),
quality_json TEXT NOT NULL CHECK (json_valid(quality_json)),
gates_json TEXT NOT NULL CHECK (
json_valid(gates_json)
AND json_array_length(gates_json) = 7
),
lineage_json TEXT NOT NULL CHECK (
json_valid(lineage_json)
AND json_extract(lineage_json, '$.capsuleId') = capsule_id
AND json_extract(lineage_json, '$.bundleId') = bundle_id
),
semantic_fingerprint_json TEXT NOT NULL CHECK (
json_valid(semantic_fingerprint_json)
AND json_extract(semantic_fingerprint_json, '$.version') =
'beauty-semantic-fingerprint-v2'
AND json_extract(semantic_fingerprint_json, '$.fingerprint') =
semantic_fingerprint
AND json_extract(
semantic_fingerprint_json,
'$.firstViewportFingerprint'
) = first_viewport_fingerprint
),
aesthetic_position_json TEXT NOT NULL CHECK (
json_valid(aesthetic_position_json)
),
candidate_json TEXT NOT NULL CHECK (
json_valid(candidate_json)
AND json_extract(candidate_json, '$.ordinal') = ordinal
AND json_extract(candidate_json, '$.candidateNonce') = candidate_nonce
AND json_extract(candidate_json, '$.capsuleId') = capsule_id
AND json_extract(candidate_json, '$.bundleId') = bundle_id
AND json_extract(candidate_json, '$.semanticFingerprint.fingerprint') =
semantic_fingerprint
AND json_extract(
candidate_json,
'$.semanticFingerprint.firstViewportFingerprint'
) = first_viewport_fingerprint
AND json_extract(candidate_json, '$.eligible') = eligible
AND json_extract(candidate_json, '$.selected') = selected
AND json_extract(candidate_json, '$.quality') = json(quality_json)
AND json_extract(candidate_json, '$.gates') = json(gates_json)
AND json_extract(candidate_json, '$.lineage') = json(lineage_json)
AND json_extract(candidate_json, '$.semanticFingerprint') =
json(semantic_fingerprint_json)
AND json_extract(candidate_json, '$.aestheticPosition') =
json(aesthetic_position_json)
),
created_at TEXT NOT NULL,
PRIMARY KEY (selection_id, ordinal),
FOREIGN KEY (selection_id, project_id, site_spec_id, manifest_id)
REFERENCES beauty_design_v2_selections(
id,
project_id,
site_spec_id,
manifest_id
)
ON DELETE RESTRICT,
UNIQUE (
selection_id,
ordinal,
project_id,
site_spec_id,
manifest_id
),
UNIQUE (selection_id, candidate_nonce),
CHECK (length(trim(capsule_id)) > 0 AND length(trim(bundle_id)) > 0)
);
CREATE TRIGGER beauty_design_v2_candidate_selection_guard
BEFORE INSERT ON beauty_design_v2_candidates
WHEN NOT EXISTS (
SELECT 1
FROM beauty_design_v2_selections AS selection
WHERE selection.id = NEW.selection_id
AND selection.project_id = NEW.project_id
AND selection.site_spec_id = NEW.site_spec_id
AND selection.manifest_id = NEW.manifest_id
AND NEW.ordinal < selection.candidate_count
AND (
(
NEW.selected = 1
AND NEW.eligible = 1
AND NEW.ordinal = selection.selected_ordinal
AND NEW.candidate_nonce = selection.selected_candidate_nonce
AND NEW.capsule_id = selection.capsule_id
AND NEW.bundle_id = selection.selected_bundle_id
AND NEW.semantic_fingerprint = selection.semantic_fingerprint
AND NEW.first_viewport_fingerprint =
selection.semantic_first_viewport_fingerprint
AND json_extract(NEW.lineage_json, '$.hardRejected') = 0
AND json_extract(NEW.quality_json, '$.total') =
selection.selected_quality_score
AND json_extract(NEW.quality_json, '$.capsuleCoherence') =
selection.selected_capsule_coherence_score
AND json_extract(NEW.quality_json, '$.contentFit') =
selection.selected_content_fit_score
)
OR (
NEW.selected = 0
AND NEW.ordinal != selection.selected_ordinal
)
)
)
BEGIN
SELECT RAISE(
ABORT,
'beauty v2 candidate conflicts with selected-candidate authority'
);
END;
CREATE TRIGGER beauty_design_v2_candidates_no_update
BEFORE UPDATE ON beauty_design_v2_candidates
BEGIN SELECT RAISE(ABORT, 'beauty v2 candidates are immutable'); END;
CREATE TRIGGER beauty_design_v2_candidates_no_delete
BEFORE DELETE ON beauty_design_v2_candidates
BEGIN SELECT RAISE(ABORT, 'beauty v2 candidates are immutable'); END;
CREATE TRIGGER beauty_design_v2_candidates_no_replace
BEFORE INSERT ON beauty_design_v2_candidates
WHEN EXISTS (
SELECT 1
FROM beauty_design_v2_candidates AS existing
WHERE existing.selection_id = NEW.selection_id
AND (
existing.ordinal = NEW.ordinal
OR existing.candidate_nonce = NEW.candidate_nonce
)
)
BEGIN SELECT RAISE(ABORT, 'beauty v2 candidates reject replacement'); END;
CREATE TABLE beauty_design_v2_elite_snapshots (
id TEXT PRIMARY KEY,
selection_id TEXT NOT NULL,
project_id TEXT NOT NULL,
site_spec_id TEXT NOT NULL,
manifest_id TEXT NOT NULL,
archive_version TEXT NOT NULL CHECK (
archive_version = 'beauty-quality-diversity-archive-v1'
),
capacity INTEGER NOT NULL CHECK (capacity BETWEEN 4 AND 64),
entry_count INTEGER NOT NULL CHECK (
entry_count BETWEEN 0 AND capacity
),
archive_json TEXT NOT NULL CHECK (
json_valid(archive_json)
AND json_extract(archive_json, '$.version') = archive_version
AND json_extract(archive_json, '$.capacity') = capacity
AND json_extract(archive_json, '$.cellCapacity') = 1
AND json_array_length(archive_json, '$.entries') = entry_count
),
created_at TEXT NOT NULL,
FOREIGN KEY (selection_id, project_id, site_spec_id, manifest_id)
REFERENCES beauty_design_v2_selections(
id,
project_id,
site_spec_id,
manifest_id
)
ON DELETE RESTRICT,
UNIQUE (selection_id)
);
-- The snapshot is the final statement in the repository batch. It cannot be
-- inserted until the complete, contiguous candidate field and its one selected
-- authority exactly match the canonical tournament report.
CREATE TRIGGER beauty_design_v2_elite_snapshot_completion_guard
BEFORE INSERT ON beauty_design_v2_elite_snapshots
WHEN NOT EXISTS (
SELECT 1
FROM beauty_design_v2_selections AS selection
WHERE selection.id = NEW.selection_id
AND selection.project_id = NEW.project_id
AND selection.site_spec_id = NEW.site_spec_id
AND selection.manifest_id = NEW.manifest_id
AND (
SELECT count(*)
FROM beauty_design_v2_candidates AS candidate
WHERE candidate.selection_id = selection.id
AND candidate.project_id = selection.project_id
AND candidate.site_spec_id = selection.site_spec_id
AND candidate.manifest_id = selection.manifest_id
) = selection.candidate_count
AND (
SELECT count(*)
FROM beauty_design_v2_candidates AS candidate
WHERE candidate.selection_id = selection.id
AND candidate.selected = 1
AND candidate.eligible = 1
AND candidate.ordinal = selection.selected_ordinal
) = 1
AND NOT EXISTS (
SELECT 1
FROM beauty_design_v2_candidates AS candidate
WHERE candidate.selection_id = selection.id
AND json(candidate.candidate_json) != json_extract(
selection.tournament_json,
'$.candidates[' || candidate.ordinal || ']'
)
)
)
BEGIN
SELECT RAISE(
ABORT,
'beauty v2 elite snapshot requires a complete authoritative tournament'
);
END;
CREATE TRIGGER beauty_design_v2_elite_snapshots_no_update
BEFORE UPDATE ON beauty_design_v2_elite_snapshots
BEGIN SELECT RAISE(ABORT, 'beauty v2 elite snapshots are immutable'); END;
CREATE TRIGGER beauty_design_v2_elite_snapshots_no_delete
BEFORE DELETE ON beauty_design_v2_elite_snapshots
BEGIN SELECT RAISE(ABORT, 'beauty v2 elite snapshots are immutable'); END;
CREATE TRIGGER beauty_design_v2_elite_snapshots_no_replace
BEFORE INSERT ON beauty_design_v2_elite_snapshots
WHEN EXISTS (
SELECT 1
FROM beauty_design_v2_elite_snapshots AS existing
WHERE existing.id = NEW.id OR existing.selection_id = NEW.selection_id
)
BEGIN SELECT RAISE(ABORT, 'beauty v2 elite snapshots reject replacement'); END;
-- Visual reviews are append-only evidence for the already accepted selected
-- candidate. They cannot establish acceptance and cannot target another
-- project, SiteSpec, manifest, or non-selected tournament candidate.
CREATE TABLE beauty_design_v2_visual_reviews (
id TEXT PRIMARY KEY,
selection_id TEXT NOT NULL,
project_id TEXT NOT NULL,
site_spec_id TEXT NOT NULL,
manifest_id TEXT NOT NULL,
candidate_ordinal INTEGER NOT NULL CHECK (
candidate_ordinal BETWEEN 0 AND 23
),
source TEXT NOT NULL CHECK (
source IN (
'STRUCTURAL_METRICS',
'AUTOMATED_VISUAL_CRITIC',
'HUMAN_REVIEW'
)
),
viewport TEXT NOT NULL CHECK (
viewport IN (
'MOBILE',
'TABLET',
'DESKTOP',
'DESKTOP_WIDE',
'FULL_PAGE'
)
),
screenshot_ref TEXT,
screenshot_sha256 TEXT CHECK (
screenshot_sha256 IS NULL OR (
length(screenshot_sha256) = 64
AND screenshot_sha256 = lower(screenshot_sha256)
AND lower(screenshot_sha256) NOT GLOB '*[^0-9a-f]*'
)
),
overall_score REAL NOT NULL CHECK (overall_score BETWEEN 0 AND 100),
coherence_score REAL NOT NULL CHECK (
coherence_score BETWEEN 0 AND 100
),
capsule_adherence_score REAL NOT NULL CHECK (
capsule_adherence_score BETWEEN 0 AND 100
),
recent_cohort_similarity REAL NOT NULL CHECK (
recent_cohort_similarity BETWEEN 0 AND 1
),
accepted INTEGER NOT NULL CHECK (accepted IN (0, 1)),
critique_json TEXT NOT NULL CHECK (json_valid(critique_json)),
reviewer_identity TEXT,
reviewed_at TEXT NOT NULL,
FOREIGN KEY (
selection_id,
candidate_ordinal,
project_id,
site_spec_id,
manifest_id
)
REFERENCES beauty_design_v2_candidates(
selection_id,
ordinal,
project_id,
site_spec_id,
manifest_id
)
ON DELETE RESTRICT,
CHECK (
(screenshot_ref IS NULL AND screenshot_sha256 IS NULL)
OR (
length(trim(screenshot_ref)) > 0
AND screenshot_sha256 IS NOT NULL
)
),
CHECK (
(
source = 'HUMAN_REVIEW'
AND length(trim(reviewer_identity)) > 0
)
OR (
source != 'HUMAN_REVIEW'
AND reviewer_identity IS NULL
)
)
);
CREATE INDEX beauty_design_v2_visual_reviews_selection_idx
ON beauty_design_v2_visual_reviews(
project_id,
site_spec_id,
selection_id,
candidate_ordinal,
viewport
);
CREATE TRIGGER beauty_design_v2_visual_review_authority_guard
BEFORE INSERT ON beauty_design_v2_visual_reviews
WHEN NOT EXISTS (
SELECT 1
FROM beauty_design_v2_selections AS selection
JOIN beauty_design_v2_elite_snapshots AS snapshot
ON snapshot.selection_id = selection.id
AND snapshot.project_id = selection.project_id
AND snapshot.site_spec_id = selection.site_spec_id
AND snapshot.manifest_id = selection.manifest_id
JOIN beauty_design_v2_candidates AS candidate
ON candidate.selection_id = selection.id
AND candidate.ordinal = selection.selected_ordinal
AND candidate.project_id = selection.project_id
AND candidate.site_spec_id = selection.site_spec_id
AND candidate.manifest_id = selection.manifest_id
WHERE selection.id = NEW.selection_id
AND selection.project_id = NEW.project_id
AND selection.site_spec_id = NEW.site_spec_id
AND selection.manifest_id = NEW.manifest_id
AND candidate.ordinal = NEW.candidate_ordinal
AND candidate.selected = 1
AND candidate.eligible = 1
)
BEGIN
SELECT RAISE(
ABORT,
'beauty v2 visual review lacks selected-manifest authority'
);
END;
CREATE TRIGGER beauty_design_v2_visual_reviews_no_update
BEFORE UPDATE ON beauty_design_v2_visual_reviews
BEGIN SELECT RAISE(ABORT, 'beauty v2 visual reviews are immutable'); END;
CREATE TRIGGER beauty_design_v2_visual_reviews_no_delete
BEFORE DELETE ON beauty_design_v2_visual_reviews
BEGIN SELECT RAISE(ABORT, 'beauty v2 visual reviews are immutable'); END;
CREATE TRIGGER beauty_design_v2_visual_reviews_no_replace
BEFORE INSERT ON beauty_design_v2_visual_reviews
WHEN EXISTS (
SELECT 1
FROM beauty_design_v2_visual_reviews AS existing
WHERE existing.id = NEW.id
)
BEGIN SELECT RAISE(ABORT, 'beauty v2 visual reviews reject replacement'); END;