P1: the generator models per-column marginals but not relationships — four semantic contracts do not hold (user_level, constant detail columns, status vs age, order/session containment)

## Summary

Four separate findings share one root cause: **the generator models each column's marginal distribution correctly, but does not model the relationships between columns and between tables.** Every column passes uniqueness, enum, null and formula checks; every relationship that gives those columns their meaning is absent.

The 2026-08-06 datafix already closed the *numeric* closures (order header vs detail, redundant counters, payment coverage — `scripts/gen/tables.py`'s "compute facts first, then back-fill declared columns"). What remains is the *semantic* and *temporal* relationships.

---

## B1. `user_level` is not monotonic in either of its defining measures

```
by cumulative spend: L1 91 → L2 262 → L3 193 ✗ → L4 2,503 → L5 1,367 ✗
by activity (events): ... → L3 111 → L4 50 ✗ → L5 44 ✗
```

Root cause — `is_vip` is drawn independently of spend, and the documented rule is "level 5 = spend >= 10000 **OR** is_vip", so the VIP flag promotes low-spending users into the top tier:

```
level is_vip users avg_spend n_spend>=10k
4 false 34,490 2,502.9 0
5 false 1,079 16,980.5 1,079 <- the real whales, 4% of tier 5
5 true 25,577 708.2 132 <- 96% of tier 5, spending 3.5x LESS than tier 4
```

**Any segmentation by `user_level` concludes that tier 4 is more valuable than tier 5**, which is the opposite of what the field means.

Note on the checker: `L4_semantics.sql` L4.5 does query this field, but its guidance only warns "if the avg_spend values are similar, the level is independent of spend". They are *not* similar, so a reader following the guidance passes it — while the inversion sits in the output. Suggest adding a monotonicity assertion: `avg(measure)` must increase with `user_level`.

**Fix**: derive the level purely from spend bands; keep `is_vip` as an independent flag that does not participate in tiering.

---

## B2. Eleven detail columns are constant-filled; one of them contradicts its own dimension

The most severe instance is a **contradiction**, not merely a constant. `user_attributions.tracking_params` is `{"utm_source": "douyin"}` for all 149,464 rows, while the same table joins to 14 distinct channels:

```
快手信息流 (Kuaishou feed) 10,812 rows utm_source=douyin
小红书种草 (RED) 10,805 rows utm_source=douyin
App Store 10,801 rows utm_source=douyin
直接访问 (direct) 10,732 rows utm_source=douyin <- direct traffic has no utm_source at all
```

Full list of constant columns:

| Table | Columns | Rows |
|---|---|---|
| `orders` | `shipping_address`, `remark`, `cancel_reason`, `refund_reason` | 854,078 |
| `posts` | `media_urls`, `tags`, `product_ids` | 427,039 |
| `push_notifications` | `deep_link`, `failure_reason` | 4,270,390 |
| `user_attributions` | `tracking_params` | 149,464 |
| `user_profiles` | `country` | 213,520 |
| `subscriptions` | `cancel_reason` | 21,352 |

`cancel_reason` has exactly one non-null value across 75,464 cancelled orders; `refund_reason` one value across 41,990 refunded orders.

**Analyses this removes**: UTM/parameter-level attribution, landing-page attribution, cancellation reason breakdown, refund reason classification, churn reason attribution, content tagging, push failure diagnosis, any geographic dimension.

**Fix**: sample these columns conditioned on the relevant dimension (channel for `tracking_params`, status for reason codes, a province/city table for addresses, a tag vocabulary for `posts.tags`).

---

## B3. Lifecycle status is independent of record age (5 tables)

Splitting each table's time range into its oldest and newest 20%, the status mix is nearly identical:

| Table | Example |
|---|---|
| `orders.status` | `pending` ~10% in every month; **oldest pending order is 2025-10-26**, i.e. unpaid for 90 days |
| `posts.status` | `published` 84.8% (oldest) vs 85.0% (newest) |
| `post_comments.status` | same pattern |
| `subscriptions.status` | `active` 61.1% vs 61.7%, `expired` 26.8% vs 26.7% |
| `users.status` | `active` 84.9% vs 85.1% |

Real systems auto-cancel unpaid orders within 30 minutes to 24 hours, so old records are almost always terminal.

Note the existing coverage: `L2.5` checks intra-row timestamp ordering and `L4.6` checks that lifecycle timestamps are non-null — neither asks whether the status *distribution* shifts with age.

**Analyses this removes**: fulfilment funnel, unpaid-order aging, operational backlog, cohort payment success rate, subscription churn timing.

**Fix**: condition the status draw on record age — records older than the terminal window must be in a terminal state.

---

## B4. Orders do not fall inside the user's own sessions

```sql
SELECT count(*) total, sum(CASE WHEN ok THEN 1 ELSE 0 END) inside FROM (
SELECT EXISTS (SELECT 1 FROM sessions s
WHERE s.user_id = o.user_id
AND o.placed_at BETWEEN s.start_time AND s.end_time) ok
FROM orders o WHERE o.user_id % 9973 = 42) t;
-- 104 | 1
```

**1 of 104 orders (1.0%)** — the rate you would expect from two independent random time series coinciding by chance. Sessions and orders are generated independently, so the causal relationship "a purchase happens during a session" does not exist in the data. (Verified across a modulo-sampled set of users, not a hand-picked one.)

**Analyses this removes**: session-level conversion attribution, pre-purchase browsing path, "which session converted", checkout abandonment within a session.

**Fix**: place each order's `placed_at` inside one of that user's session windows (and ideally emit the funnel events for that order inside the same session).

---

## Suggested priority

1. B1 — one change to the level rule, restores user segmentation and LTV tiering
2. B2 — sampling change on ~11 columns, restores five analysis families
3. B3 — condition status on age, restores fulfilment/lifecycle analysis
4. B4 — the most invasive change, but restores session-level attribution

All four are the same class of fix: **model the relationship, not just the marginal.**

Measured 2026-08-13 against `analytics-agent-wg` / `app_analytics` through a read-only role.

Start with scripts/gen/tables.py and the existing L4_semantics.sql guidance, including the L2.5 and L4.6 checks. Review the four findings in priority order, then verify that generated levels, dimension-dependent details, lifecycle status by age, and order/session containment preserve the relationships described in the issue.

Written by the indexing model from the issue text.