`data/yolo-overnight/dig-integrity-20260525-1334.md`

70 lines
# DIG Number Integrity — Published wallco.ai Designs

- **Run:** 2026-05-25 13:34 PT
- **DB:** `dw_unified` @ 127.0.0.1
- **Table:** `public.all_designs` (filter `brand = 'wallco.ai' AND is_published = true`)
- **Column under audit:** `dig_number`

## Result

**0 violations.** Every published wallco.ai design has a non-null `dig_number`.

| Metric | Count |
|---|---:|
| Published wallco.ai designs | 273 |
| Published with `dig_number` NULL | **0** |
| Published with `dig_number` populated | 273 |
| All wallco.ai rows (any publish state) with DIG | 8,745 / 8,745 |
| Malformed (not matching `^DIG_AI_\d{6}$`) | 0 |
| Canonical match (`DIG_AI_<lpad(id,6,'0')>`) | 273 / 273 |

## Why coverage is total

`all_designs` has a BEFORE INSERT trigger that auto-assigns `dig_number` when null:

```sql
CREATE TRIGGER trg_spoon_all_designs_assign_dig
  BEFORE INSERT ON all_designs
  FOR EACH ROW EXECUTE FUNCTION fn_spoon_all_designs_assign_dig();

-- function body:
IF NEW.dig_number IS NULL OR NEW.dig_number = '' THEN
  NEW.dig_number := 'DIG_AI_' || lpad(NEW.id::text, 6, '0');
END IF;
```

Plus a partial UNIQUE index `spoon_all_designs_dig_number_idx ON (dig_number) WHERE dig_number IS NOT NULL` to keep numbers distinct. The trigger only fires on INSERT, so an UPDATE that flipped `dig_number` back to NULL could still create a violation — currently none exist.

## Sample (random 5 published)

| id | dig_number | kind | category | on_shopify | on_spoonflower | created_at |
|---:|---|---|---|:---:|:---:|---|
| 9965 | DIG_AI_009965 | seamless_tile | inspired | f | f | 2026-05-20 |
| 9967 | DIG_AI_009967 | seamless_tile | inspired | f | f | 2026-05-20 |
| 9888 | DIG_AI_009888 | seamless_tile | drunk-animals-designer | f | f | 2026-05-20 |
| 9922 | DIG_AI_009922 | seamless_tile | drunk-animals-designer | f | f | 2026-05-20 |
| 9870 | DIG_AI_009870 | seamless_tile | inspired | f | f | 2026-05-20 |

## Observations worth flagging (not violations)

- All 5 sampled published designs are `on_shopify=f` and `on_spoonflower=f`. The full set is 273 published but none have been pushed to Shopify or Spoonflower yet — `is_published` here appears to mean "approved/visible inside wallco.ai", not "live on retail surfaces". If "published" was intended to mean "on Shopify", that's a separate semantic gap to revisit, but it's outside the DIG-integrity question asked.
- `ai_title` is blank on the sampled rows. Not part of the DIG check, but a related polish gap if these are surfaced to users by DIG alone.

## Queries used

```sql
SELECT
  COUNT(*) FILTER (WHERE brand='wallco.ai' AND is_published) AS total_published,
  COUNT(*) FILTER (WHERE brand='wallco.ai' AND is_published AND dig_number IS NULL) AS published_no_dig
FROM all_designs;

SELECT
  COUNT(*) FILTER (WHERE dig_number !~ '^DIG_AI_[0-9]{6}$') AS malformed,
  COUNT(*) FILTER (WHERE dig_number = 'DIG_AI_'||lpad(id::text,6,'0')) AS canonical_match
FROM all_designs WHERE brand='wallco.ai' AND is_published;
```

## Verdict

PASS — no action required. Suggest adding the same check to a nightly sanity job alongside `dw-unified-sanity` reports, since the trigger does not protect against UPDATEs nulling the column.