Ribbit.deals — Database Design v1 PWA screens Website wireframes
FINAL v1.0 — for buy-in · Runnable DDL in ribbit-schema-v1.sql · Laravel conventions (snake_case, id PK, created_at/updated_at on all tables) · One database serves website + consumer app + business tools

One database, every surface

The website, the consumer app, and the business tools are three windows onto the same data. Key column names below; full definitions live in the companion markdown doc. Green = foreign key, orange = design note.

Identity & access
users
  • id, name, email, password
  • mobile_number, sms_opt_in_at
  • role customer | business | admin
  • referral_code, referred_by_user_id
  • the 4-field signup writes exactly this
businesses
  • id, name, category, status
  • contact_name, contact_email, contact_phone
  • billing_* nullable — defaults to contact
  • referral_code_used
  • status: lead → pending → active
business_users
  • id, business_id, user_id
  • role owner | manager | staff
  • pivot: one login works on web portal and app; staff role covers counter redemption verification
Locations & enrollment
locations
  • id, business_id, nickname
  • address, city, state, zip, phone
  • lat, lng geocoded on save
  • qr_token rotatable — the placard QR
  • short_code POND-XXXX manual fallback
  • qr_rotated_at, is_active
  • COUNT(locations) drives pricing
enrollment_leads
  • id, business_id nullable until created
  • contact fields progressive capture
  • step_reached 1–5, abandoned_at
  • followed_up_at, referral_code
  • follow-up rule: step_reached ≥ 2
subscriptions
  • id, business_id
  • terms monthly | annual
  • location_count, price_per_location
  • activation_fee, performance_fee_rate
  • agreed_at, signature_name contract acceptance
Offers & redemptions — the core loop
offers
  • id, business_id, title, description
  • image_path, offer_type, value
  • geofence_radius_m
  • max_redemptions, budget_cap
  • starts_at, ends_at, status draft | live | paused | ended — auto-pause at caps
offer_location
  • id, offer_id, location_id
  • pivot: one offer can run at any subset of a business's locations; discovery joins through this to geosearch
redemptions
  • id, offer_id, user_id, location_id
  • method placard_scan | manual_code
  • device_lat/lng, geo_validated_at GPS factor
  • scanned_qr_token placard factor — must match location
  • verified_at, performance_fee_amount fee snapshot
  • voided_at, void_reason 48-hr business void window
  • THE billable event — feeds invoices, dashboard stats, and points
Money
invoices
  • id, business_id, period_start/end
  • operations_total, performance_total
  • status draft | issued | paid | failed
  • issued_at, paid_at
invoice_lines
  • id, invoice_id, type
  • redemption_id nullable — itemizes performance fees per verified redemption
  • description, amount
charity_contributions
  • id, period, amount
  • charity_name, notes
  • quiet trust signal — powers the "given to local charities" stat on About
Engagement
points_ledger
  • id, user_id, delta
  • source redemption | referral | badge | admin
  • redemption_id nullable
  • append-only ledger; balance = SUM(delta) — auditable, no drift
badges / user_badges
  • badges: id, key, name, points_reward
  • user_badges: user_id, badge_id, earned_at
  • v1 keeps a handful of earnable moments
notifications
  • id, user_id, channel push | sms
  • type, payload_json, offer_id nullable
  • sent_at, opened_at
  • sms only where sms_opt_in_at is set

Follow one redemption through the system (good demo narrative)

  1. Customer taps Redeem in range → row created in redemptions with a one-time code.
  2. Staff verifies at the counter → verified_at stamped, performance_fee_amount snapshotted from the business's subscriptions.performance_fee_rate.
  3. The RIBBIT moment fires → points_ledger gets a +delta row tied to the redemption; badges checked.
  4. Business dashboard tiles update — views, redemptions, fees accrued are live queries on the same rows.
  5. For 48 hours the business can void it from the dashboard (voided_at, void_reason) — the trust valve for scan-and-walk-away cases.
  6. On the 1st, the billing job rolls unbilled, unvoided verified redemptions into invoice_lines under a new invoices row — every fee traceable to a real, verified purchase.

Design decisions worth saying out loud

  • Fee snapshots: redemptions.performance_fee_amount is copied at verification time, so later price changes never rewrite history — invoices stay defensible.
  • Append-only points: no points_balance column to drift out of sync; the ledger is the truth, and it gives the activity feed for free.
  • Leads are not businesses: enrollment_leads keeps tire-kickers out of the real businesses table; the step_reached ≥ 2 rule decides who gets a follow-up call.
  • Caps enforced at write time: the redemption insert checks max_redemptions and budget_cap in the same transaction — the auto-pause promise is a database guarantee, not a cron job's best effort.
  • The placard handshake (decision): verification = geofence GPS + scan of the location's placard qr_token. Merchant-presented QR pattern — no staff hardware, no staff app. Tokens rotate per location on demand; the redemption stores the scanned token for audit.
  • Void window over counter friction: scan counts as billable by default; businesses get 48 hours to void. Admin monitors void rates for abuse in both directions.
  • Same auth everywhere: business_users means the web Business Login and the app dashboard are one account system; the pricing gate rides the same session (the Street Cuisines role-middleware pattern).