`db/migrations/009_business_views.sql`

16 lines
-- Per-business profile view counter. One row per (business, day, count).
-- The 30d sum gives the dashboard "views this month" stat; total is rarely
-- needed but cheap to compute on demand.
--
-- Bot/dedup: route handler checks user-agent + session before incrementing.

CREATE TABLE IF NOT EXISTS business_views (
  business_id BIGINT NOT NULL REFERENCES businesses(id) ON DELETE CASCADE,
  day         DATE NOT NULL,
  n           INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (business_id, day)
);

CREATE INDEX IF NOT EXISTS business_views_recent_idx
  ON business_views (business_id, day DESC);