Database schema — pageviews, visitors, campaigns, reports #2

New Issue

2026-06-10T09:34:01Z

jmiller commented

2026-06-10 09:34:01 +00:00

Summary

Design the database schema for privacy-first analytics tracking.

Tables

`#__mokosuiteanalytics_pageviews`

id, page_url, page_title, referrer_url, referrer_domain
visitor_hash (anonymized — hashed IP + user agent, rotated daily)
device_type (desktop, mobile, tablet), browser, os
country_code (GeoIP lookup, no IP stored)
session_id (short-lived, no cookies)
created_at

`#__mokosuiteanalytics_visitors`

id, visitor_hash, first_seen, last_seen, pageview_count
Note: visitor_hash rotates daily for privacy — same person gets new hash next day

`#__mokosuiteanalytics_campaigns`

id, name, source, medium, campaign_code (UTM params)
click_count, conversion_count
published, created_at

`#__mokosuiteanalytics_ad_accounts`

id, platform (google_ads, meta_ads, linkedin_ads)
account_id, api_credentials (encrypted JSON)
sync_enabled, last_sync, published

`#__mokosuiteanalytics_ad_metrics`

id, ad_account_id, date, impressions, clicks, conversions, spend, revenue
campaign_name, ad_group_name

`#__mokosuiteanalytics_reports`

id, title, config (JSON — date range, metrics, dimensions, filters)
schedule (none, daily, weekly, monthly), email_recipients
created_by, created_at

Privacy Design

No cookies — use session fingerprinting with daily rotation
IP addresses never stored — only anonymized country-level GeoIP
Visitor hashes rotated daily — cannot track individuals across days
All tracking opt-out via Do Not Track header respect
Data retention: configurable, default 90 days auto-purge

Tasks

Write sql/install.mysql.sql
Write sql/uninstall.mysql.sql
Table and Model classes
Daily visitor hash rotation (scheduled task)

## Summary Design the database schema for privacy-first analytics tracking. ## Tables ### `#__mokosuiteanalytics_pageviews` - id, page_url, page_title, referrer_url, referrer_domain - visitor_hash (anonymized — hashed IP + user agent, rotated daily) - device_type (desktop, mobile, tablet), browser, os - country_code (GeoIP lookup, no IP stored) - session_id (short-lived, no cookies) - created_at ### `#__mokosuiteanalytics_visitors` - id, visitor_hash, first_seen, last_seen, pageview_count - Note: visitor_hash rotates daily for privacy — same person gets new hash next day ### `#__mokosuiteanalytics_campaigns` - id, name, source, medium, campaign_code (UTM params) - click_count, conversion_count - published, created_at ### `#__mokosuiteanalytics_ad_accounts` - id, platform (google_ads, meta_ads, linkedin_ads) - account_id, api_credentials (encrypted JSON) - sync_enabled, last_sync, published ### `#__mokosuiteanalytics_ad_metrics` - id, ad_account_id, date, impressions, clicks, conversions, spend, revenue - campaign_name, ad_group_name ### `#__mokosuiteanalytics_reports` - id, title, config (JSON — date range, metrics, dimensions, filters) - schedule (none, daily, weekly, monthly), email_recipients - created_by, created_at ## Privacy Design - No cookies — use session fingerprinting with daily rotation - IP addresses never stored — only anonymized country-level GeoIP - Visitor hashes rotated daily — cannot track individuals across days - All tracking opt-out via `Do Not Track` header respect - Data retention: configurable, default 90 days auto-purge ## Tasks - [ ] Write `sql/install.mysql.sql` - [ ] Write `sql/uninstall.mysql.sql` - [ ] Table and Model classes - [ ] Daily visitor hash rotation (scheduled task)

jmiller referenced this issue

2026-06-10 09:47:43 +00:00

Tracking system plugin — privacy-first pageview collection #3

jmiller referenced this issue

2026-06-10 10:32:44 +00:00

Admin dashboard — charts, date ranges, real-time overview #4

jmiller referenced this issue

2026-06-10 16:00:54 +00:00

Ad platform integration — Google Ads, Meta Ads, LinkedIn Ads #5

jmiller referenced this issue

2026-06-11 01:11:09 +00:00

Report generation — scheduled emails, CSV/PDF export #6