Context
Issue #617 adds the visitor_analytics module foundation. This issue adds the tenant-scoped PostgreSQL schema required to store visitor presence, visit events, and daily rollups for human visitor statistics.
AWCMS-Mini already enforces tenant safety using tenant_id, withTenant, ABAC, and PostgreSQL RLS. Visitor analytics must follow the same model and must not reuse audit tables as high-volume traffic tables.
Objective
Add visitor analytics database tables with RLS, FORCE RLS, indexes, and least-privilege grants.
Scope
Create a new migration using the next available migration number at implementation time. Do not hardcode an outdated migration number in the issue implementation.
Add these tables:
awcms_mini_visitor_sessionsawcms_mini_visit_eventsawcms_mini_visitor_daily_rollups
awcms_mini_visitor_sessions
Suggested columns:
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id uuid NOT NULL REFERENCES awcms_mini_tenants (id),
visitor_key_hash text NOT NULL,
identity_id uuid NULL REFERENCES awcms_mini_identities (id),
login_identifier_snapshot text NULL,
is_authenticated boolean NOT NULL DEFAULT false,
area text NOT NULL,
current_path text NULL,
first_seen_at timestamptz NOT NULL DEFAULT now(),
last_seen_at timestamptz NOT NULL DEFAULT now(),
ip_hash text NULL,
ip_address inet NULL,
user_agent_hash text NULL,
browser_name text NULL,
browser_version_major text NULL,
os_name text NULL,
device_type text NULL,
is_human boolean NOT NULL DEFAULT true,
bot_reason text NULL,
country_code text NULL,
region text NULL,
city text NULL,
timezone text NULL,
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now()
Recommended checks:
area IN ('admin', 'public', 'api', 'auth', 'setup', 'unknown')device_type IN ('desktop', 'mobile', 'tablet', 'bot', 'unknown')
awcms_mini_visit_events
Suggested columns:
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id uuid NOT NULL REFERENCES awcms_mini_tenants (id),
visitor_session_id uuid NULL REFERENCES awcms_mini_visitor_sessions (id),
identity_id uuid NULL REFERENCES awcms_mini_identities (id),
occurred_at timestamptz NOT NULL DEFAULT now(),
method text NOT NULL,
status_code integer NULL,
area text NOT NULL,
route_pattern text NULL,
path_sanitized text NOT NULL,
referrer_domain text NULL,
duration_ms integer NULL,
ip_hash text NULL,
user_agent_hash text NULL,
user_agent_parsed jsonb NOT NULL DEFAULT '{}'::jsonb,
geo jsonb NOT NULL DEFAULT '{}'::jsonb,
human_status text NOT NULL,
correlation_id text NULL,
created_at timestamptz NOT NULL DEFAULT now()Recommended checks:
area IN ('admin', 'public', 'api', 'auth', 'setup', 'unknown')human_status IN ('human', 'bot', 'unknown')status_code IS NULL OR (status_code >= 100 AND status_code <= 599)
awcms_mini_visitor_daily_rollups
Suggested columns:
tenant_id uuid NOT NULL REFERENCES awcms_mini_tenants (id),
date date NOT NULL,
area text NOT NULL,
human_unique_visitors integer NOT NULL DEFAULT 0,
human_pageviews integer NOT NULL DEFAULT 0,
bot_pageviews integer NOT NULL DEFAULT 0,
authenticated_unique_users integer NOT NULL DEFAULT 0,
public_unique_visitors integer NOT NULL DEFAULT 0,
admin_unique_users integer NOT NULL DEFAULT 0,
top_paths jsonb NOT NULL DEFAULT '[]'::jsonb,
top_browsers jsonb NOT NULL DEFAULT '[]'::jsonb,
top_devices jsonb NOT NULL DEFAULT '[]'::jsonb,
top_countries jsonb NOT NULL DEFAULT '[]'::jsonb,
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now(),
PRIMARY KEY (tenant_id, date, area)
Indexes:
CREATE INDEX ... ON awcms_mini_visitor_sessions (tenant_id, last_seen_at DESC);
CREATE INDEX ... ON awcms_mini_visitor_sessions (tenant_id, area, last_seen_at DESC);
CREATE INDEX ... ON awcms_mini_visit_events (tenant_id, occurred_at DESC);
CREATE INDEX ... ON awcms_mini_visit_events (tenant_id, area, occurred_at DESC);
CREATE INDEX ... ON awcms_mini_visit_events (tenant_id, human_status, occurred_at DESC);
CREATE INDEX ... ON awcms_mini_visit_events (visitor_session_id, occurred_at DESC);
CREATE INDEX ... ON awcms_mini_visit_events (identity_id, occurred_at DESC);
CREATE INDEX ... ON awcms_mini_visitor_daily_rollups (tenant_id, date, area);
RLS requirements:
- Enable RLS on all three tables.
- FORCE RLS on all three tables.
- Policy:
tenant_id = current_setting('app.current_tenant_id')::uuid.
- Ensure least-privilege app role grants follow the existing AWCMS-Mini pattern.
Out of scope
- Middleware collector.
- User-agent parser.
- Dashboard/API endpoints.
- Rollup job implementation.
- Online geolocation enrichment.
Acceptance criteria
Security and privacy notes
- Raw
ip_address must only be populated when raw IP collection is explicitly enabled.
- The default analytics mode should rely on
ip_hash, visitor_key_hash, and parsed device/browser fields.
- Do not store request body, cookies, authorization headers, password reset tokens, OAuth codes, or query-string secrets.
- This schema is for analytics, not audit. High-risk actions remain in the existing audit log system.
Dependencies
Depends on #617.
Context
Issue #617 adds the
visitor_analyticsmodule foundation. This issue adds the tenant-scoped PostgreSQL schema required to store visitor presence, visit events, and daily rollups for human visitor statistics.AWCMS-Mini already enforces tenant safety using
tenant_id,withTenant, ABAC, and PostgreSQL RLS. Visitor analytics must follow the same model and must not reuse audit tables as high-volume traffic tables.Objective
Add visitor analytics database tables with RLS, FORCE RLS, indexes, and least-privilege grants.
Scope
Create a new migration using the next available migration number at implementation time. Do not hardcode an outdated migration number in the issue implementation.
Add these tables:
awcms_mini_visitor_sessionsawcms_mini_visit_eventsawcms_mini_visitor_daily_rollupsawcms_mini_visitor_sessionsSuggested columns:
Recommended checks:
area IN ('admin', 'public', 'api', 'auth', 'setup', 'unknown')device_type IN ('desktop', 'mobile', 'tablet', 'bot', 'unknown')awcms_mini_visit_eventsSuggested columns:
Recommended checks:
area IN ('admin', 'public', 'api', 'auth', 'setup', 'unknown')human_status IN ('human', 'bot', 'unknown')status_code IS NULL OR (status_code >= 100 AND status_code <= 599)awcms_mini_visitor_daily_rollupsSuggested columns:
Indexes:
RLS requirements:
tenant_id = current_setting('app.current_tenant_id')::uuid.Out of scope
Acceptance criteria
tenant_id.withTenantdoes not leak tenant data.login_identifier_snapshotis nullable and must not be used for anonymous public visitors.bun run db:migratepasses.bun testpasses.bun run checkpasses.Security and privacy notes
ip_addressmust only be populated when raw IP collection is explicitly enabled.ip_hash,visitor_key_hash, and parsed device/browser fields.Dependencies
Depends on #617.