This was not a day of visible product-building. It was a day of fixing the foundation the product sits on — the kind of work that produces nothing to screenshot but everything to trust. We set out to run UAT and build role-specific kits. Before that could be safe, the schema underneath had to be made sound. It now is.
"Every dashboard number in the product was being computed over 56% synthetic demo data, and nobody could have known by looking."
What Shipped (Verified & Committed)
The 1:1 + MBO inspection subsystem
✅ New Table Structure
One unified one_on_ones table
Serves every persona pairing (SDR↔AE, AE↔SEAC, all leadership 1:1s). Keyed on the relationship rather than hardcoded participant columns. Adding CSM or SME 1:1s later requires no schema change — it becomes configuration.
✅ Field vs Leadership Differentiation
Smart meeting semantics
Field 1:1s (peers) carry status and asks, no coaching. Leadership 1:1s carry coaching, self-assessment, and MBO. This matches how these meetings actually work — peers don't coach peers. The schema enforces it.
✅ Private Self-Assessments
Honest feedback mechanism
The rep writes honestly because the leader cannot see it unless the rep chooses to share. Sharing is one-way (cannot be silently retracted). A self-assessment the leader can read is not a self-assessment at all.
✅ The MBO Inspection Trail
The competitive moat
Objectives get inspected every 1:1 with cited evidence. The trail is immutable. This turns quarter-end scoring (beauty contest judged on last two weeks) into defensible payout backed by continuous evidence. Disputes are settled by data.
🎯 End-to-End Verification Result
The first real MBO objective was written to the database today. That write had been silently blocked by an RLS policy since before July 6 — every leader paired through the new relationship model was unable to create an objective, and nothing surfaced the failure. Verified end-to-end with real authenticated sessions (Playwright, seeded accounts, torn down after). Repeatable test saved to the repo. The inspection trail is now live.
What Was Fixed (Foundation)
Two security holes + data contamination
🔴 Security #1: Privilege Escalation
Any user could become a leader
Any authenticated user could set their own role and subscription tier by updating their profile row. A permissive RLS policy with an empty check nullified protections. Closed with policy drop plus database-level guard that cannot be bypassed.
🔴 Security #2: Email-Click Privilege Grant
Email click = org-wide management access
An ordinary rep could send a "become a leader" invitation to an email they controlled and, on clicking through, be granted org-wide management privileges with zero admin approval. Now grants a neutral tier; elevation requires deliberate action.
🔴 Security #3: Paywall Bypass
Coupon redemption was directly callable
Coupon redemption (which writes subscription tier) was directly callable by any user session. Moved server-side only. No more tier elevation without proper flow.
⚠️ Data Trust: Demo Contamination
The biggest find of the day
Every dashboard metric was computed over mixed real and demo data. Analysis_history was 56% demo. MBO was 100% demo. Leader scorecards, funnels, HQ analytics — all mixed. Fixed by making demo exclusion the default at the data layer and applying it across ~20 aggregation sites.
⚠️ Data Trust: Cached Counter Drift
Counter was wrong on 26% of accounts
A cached counter was wrong on 26% of accounts and shown verbatim in the UI. Replaced with a live-computed value that cannot drift.
⚠️ Data Trust: Compensation on Demo Data
Reps could be paid on synthetic handoffs
Spiffs, QBRs, and career reviews were counting demo data as real. A rep could have been compensated on synthetic activity. Closed.
Architecture Consolidation
The pattern that caused the mess
The schema had accumulated 4–5 competing representations of the same concepts (roles, memberships, pairings, invites) because each new persona got a parallel copy of an existing feature instead of generalizing it. This session consolidated that sprawl:
🔧 Removed Dead Code
First pass cleanup
8 dead tables, 10 dead columns, 7 functions removed in the first pass alone. Schema now reflects what's actually in use.
🔧 Relationship Model
One generic model replaces duplicates
Replaced per-persona pairing/leader tables with one generic relationship model where cardinality rules live as data. Adding SEAC/CSM/SME is now configuration, not schema change.
Established one canonical owner for each concept and wrote it into AGENTS.md, which Lovable now reads on every request as a guardrail against the next duplicate.
// AGENTS.md now defines schema ownership:
- roles: one source of truth (app, not user-settable)
- memberships: org → person relationship via email
- pairings: generic relationship model (type field, not table)
- invites: single table, not split by role
What Stays in the Repo
Continuity across sessions
-
AGENTS.md — Schema OwnershipThe schema principles (one owner per concept, identifier rules, authorization rules). Lovable reads it on every request to avoid duplicating patterns. Lives in the repo, carries the reasoning independent of any chat.
-
docs/SCHEMA_MIGRATION_LOG.md — Full Phase RecordThe complete phase-by-phase record, including reversals and why they happened. Not in chat history — it's in the repo where the team can find it anytime.
Open Items Ranked
Carried forward, none blocking UAT
-
1. Entitlement scattered across four tables (Enterprise Launch Blocker)
organizations.subscription_tier,profiles.tier,testhive_leads.tier,subscriptions. Same duplication pattern as roles. An SSO user currently lands on the free tier regardless of their company's contract, with no reconciliation. Highest-value next target for the architecture team. -
2. Tier has no database-level enforcementEvery free/paid check is in application code; a direct API call bypasses all of them. Revenue leak + an isolation claim enterprise buyers ask about.
-
3. Aggregation functions leak across tenant boundaries
refresh_call_activity_dailyaggregates without an org boundary — cross-tenant leak. Harmless only because nothing invokes it. Must not be scheduled until fixed. -
4. Two snapshot tables permanently emptyUI shows zeros as truth. Audit needed.
-
5. Leader pages disagree on the same metricTwo pages reading different tables for the same handoff number.
-
6. ae_id is null on all 76 accountsProduct gap, not a bug. The AE-filter path has never been exercised.
-
7. Dead-feature audit pending130 tables with zero rows + zero code references. Worth running to identify what else can be removed.
The Method That Worked
Worth repeating
Every meaningful find came from reading the actual schema rather than trusting the summary of it. The platform-vs-org role distinction, the empty security check, the two-different-ID-columns bug, the demo contamination — none were visible from the outside. The repeatable discipline that surfaced everything:
- Read-only diagnostic first
- Guard clauses that abort on unexpected data
- Verification queries with expected values
- Never cascade — always explicit
- Dual-write before cutover
- Keep a rollback script
What We Need to Work On
Immediately after UAT validation
-
Consolidate entitlements (Priority #1)Replace the four-table scatter with one canonical subscription_tier. This is the launch blocker — no enterprise deal closes with SSO landing everyone on free tier.
-
Push tier to database layerMove all free/paid checks out of application code into column-level constraints. RLS policies enforce tier rules at query time.
-
Fix cross-tenant aggregationAdd org_id filter to all aggregation functions before any are scheduled. Non-negotiable for multi-tenant.
-
UAT feedback loopRun SDR team through the product with real data. Catch usability issues that schema fixes don't surface.
What's Coming Next
The kit build + broader rollout
The schema is now clean. Demo data is no longer invisible. Security holes are closed. The 1:1 and MBO system is verified to work. This clears the way for:
-
UAT with real SDR cohort (This Week)Team tests with clean data. Catch usability issues. Gather feedback on workflows.
-
Role-specific kit build (Next)SDR kit, AE kit, Leader kit. Each optimized for their workflow, no dead features.
-
Entitlement consolidation (Parallel Track)Four tables → one, pushed to database layer. Unblocks enterprise SSO flow.
-
DCN pilot onboarding (With Mike's Contact)Once assigned, onboard Mike's dedicated person with clean schema, verified product, and clear MBO inspection trails.