Tracking User Activity

Track user activity by storing server-side events for important actions: login, signup, password reset, billing changes, resource creation, admin actions, and destructive operations. For MVPs and small SaaS products, start with application-level event logging tied to user ID, tenant ID, request metadata, and event type. Keep it privacy-conscious, queryable, and easy to debug.

Quick Fix / Quick Setup

Create a dedicated activity table and start writing server-side events for high-value actions.

CREATE TABLE user_activity_events (
  id BIGSERIAL PRIMARY KEY,
  tenant_id BIGINT NULL,
  user_id BIGINT NULL,
  event_type VARCHAR(100) NOT NULL,
  object_type VARCHAR(100) NULL,
  object_id VARCHAR(100) NULL,
  ip_address INET NULL,
  user_agent TEXT NULL,
  request_id VARCHAR(100) NULL,
  metadata JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_user_activity_user_id ON user_activity_events(user_id);
CREATE INDEX idx_user_activity_tenant_id ON user_activity_events(tenant_id);
CREATE INDEX idx_user_activity_event_type ON user_activity_events(event_type);
CREATE INDEX idx_user_activity_created_at ON user_activity_events(created_at DESC);

-- Example event insert
INSERT INTO user_activity_events (
  tenant_id, user_id, event_type, object_type, object_id, ip_address, user_agent, request_id, metadata
) VALUES (
  1, 42, 'project.created', 'project', 'proj_123', '203.0.113.10', 'Mozilla/5.0', 'req_abc123', '{"source":"web"}'::jsonb
);

Use server-side event writes first. Do not rely only on frontend analytics for account, billing, or audit events. Track high-value events, not every click.

Minimal event naming convention:

auth.login_succeeded
auth.login_failed
auth.password_reset_requested
billing.subscription_updated
project.created
project.deleted
api_key.created
api_key.revoked
admin.user_impersonation_started

What’s happening

User activity tracking records meaningful actions performed by authenticated or anonymous users.

This is useful for:

• audit history
• support debugging
• internal analytics
• abuse detection
• customer-facing activity feeds

For small SaaS products, the simplest durable setup is a database-backed event table written by your application code.

Your event schema should support:

• user identity
• tenant context
• event type
• target object
• request metadata
• flexible JSON metadata

Keep audit/activity tracking separate from product analytics:

• audit events should be reliable and server-side
• analytics events can be lower priority and asynchronous
• support and security investigations should not depend on browser delivery

Step-by-step implementation

1) Define which events matter

Start with:

• auth events
• billing events
• resource creation/deletion
• admin actions
• security-sensitive changes

Good starter list:

user.registered
auth.login_succeeded
auth.login_failed
auth.password_reset_requested
auth.password_changed
auth.email_verified
billing.checkout_started
billing.subscription_created
billing.subscription_updated
billing.payment_failed
billing.subscription_canceled
project.created
project.deleted
file.uploaded
api_key.created
api_key.deleted
role.changed
team.member_removed
webhook.processed
webhook.failed
job.failed

2) Design the schema

Recommended PostgreSQL table:

CREATE TABLE user_activity_events (
  id BIGSERIAL PRIMARY KEY,
  tenant_id BIGINT NULL,
  user_id BIGINT NULL,
  event_type VARCHAR(100) NOT NULL,
  object_type VARCHAR(100) NULL,
  object_id VARCHAR(100) NULL,
  ip_address INET NULL,
  user_agent TEXT NULL,
  request_id VARCHAR(100) NULL,
  metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Optional constraints:

ALTER TABLE user_activity_events
  ADD CONSTRAINT chk_event_type_not_empty CHECK (char_length(event_type) > 0);

ALTER TABLE user_activity_events
  ADD CONSTRAINT chk_metadata_is_object CHECK (jsonb_typeof(metadata) = 'object');

Optional metadata size guard:

ALTER TABLE user_activity_events
  ADD CONSTRAINT chk_metadata_reasonable_size
  CHECK (pg_column_size(metadata) <= 8192);

3) Standardize event names

Use stable dot-separated names:

domain.action
auth.login_succeeded
billing.subscription_updated
project.created
api_key.revoked

Rules:

• keep names short
• do not rename casually
• avoid route-specific naming
• use the same names across routes, jobs, and webhooks

4) Add request context

Generate or propagate a request ID for each request.

Headers to support:

X-Request-ID
X-Forwarded-For
User-Agent

Nginx example:

proxy_set_header X-Request-ID $request_id;
proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
proxy_set_header User-Agent $http_user_agent;

Store and reuse:

• request_id
• user_id
• tenant_id
• client IP
• user agent

5) Create a reusable tracking helper

Python example

def track_event(
    db,
    event_type: str,
    user_id: int | None = None,
    tenant_id: int | None = None,
    object_type: str | None = None,
    object_id: str | None = None,
    ip_address: str | None = None,
    user_agent: str | None = None,
    request_id: str | None = None,
    metadata: dict | None = None,
):
    db.execute(
        """
        INSERT INTO user_activity_events (
          tenant_id, user_id, event_type, object_type, object_id,
          ip_address, user_agent, request_id, metadata
        )
        VALUES (
          %(tenant_id)s, %(user_id)s, %(event_type)s, %(object_type)s, %(object_id)s,
          %(ip_address)s, %(user_agent)s, %(request_id)s, %(metadata)s::jsonb
        )
        """,
        {
            "tenant_id": tenant_id,
            "user_id": user_id,
            "event_type": event_type,
            "object_type": object_type,
            "object_id": object_id,
            "ip_address": ip_address,
            "user_agent": user_agent,
            "request_id": request_id,
            "metadata": json.dumps(metadata or {}),
        },
    )

6) Write events after successful actions

Do not write success events before the business action commits.

Wrong order:

track_event(db, "project.created", user_id=user.id, tenant_id=tenant.id)
create_project(...)
db.commit()

Correct order:

project = create_project(...)
db.commit()

track_event(
    db,
    "project.created",
    user_id=user.id,
    tenant_id=tenant.id,
    object_type="project",
    object_id=project.id,
    request_id=request_id,
    metadata={"source": "web"},
)
db.commit()

If you use one transaction scope, ensure the event only persists when the action succeeds.

7) Track workers and webhooks too

Add source context:

{"source":"worker"}
{"source":"webhook"}
{"source":"admin"}
{"source":"system"}
{"source":"api"}

Webhook example fields:

• provider event ID
• retry count
• processing status

Example:

track_event(
    db,
    "billing.subscription_updated",
    user_id=user_id,
    tenant_id=tenant_id,
    object_type="subscription",
    object_id=str(subscription_id),
    request_id=request_id,
    metadata={
        "source": "webhook",
        "provider": "stripe",
        "provider_event_id": stripe_event_id,
    },
)

8) Redact sensitive data

Do not store:

• passwords
• raw auth headers
• session tokens
• API secrets
• full request bodies
• full card details
• unnecessary personal data

Good metadata example:

{
  "source": "web",
  "plan": "pro",
  "status": "canceled"
}

Bad metadata example:

{
  "password": "plaintext",
  "authorization": "Bearer ...",
  "card_number": "4242424242424242"
}

9) Add indexes for support queries

CREATE INDEX idx_user_activity_user_id ON user_activity_events(user_id);
CREATE INDEX idx_user_activity_tenant_id ON user_activity_events(tenant_id);
CREATE INDEX idx_user_activity_event_type ON user_activity_events(event_type);
CREATE INDEX idx_user_activity_created_at ON user_activity_events(created_at DESC);

For common tenant+time filters:

CREATE INDEX idx_user_activity_tenant_created_at
ON user_activity_events(tenant_id, created_at DESC);

For user+time filters:

CREATE INDEX idx_user_activity_user_created_at
ON user_activity_events(user_id, created_at DESC);

10) Build simple query views

Recent activity by user:

SELECT event_type, object_type, object_id, request_id, metadata, created_at
FROM user_activity_events
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 50;

Billing events for a tenant:

SELECT event_type, object_id, metadata, created_at
FROM user_activity_events
WHERE tenant_id = 1
  AND event_type LIKE 'billing.%'
ORDER BY created_at DESC
LIMIT 100;

Failed auth attempts by IP:

SELECT ip_address, count(*) AS attempts
FROM user_activity_events
WHERE event_type = 'auth.login_failed'
  AND created_at > NOW() - INTERVAL '24 hours'
GROUP BY ip_address
ORDER BY attempts DESC;

11) Add retention and archiving

For MVPs, keep recent events in the main database and archive older rows later.

Delete old rows example:

DELETE FROM user_activity_events
WHERE created_at < NOW() - INTERVAL '180 days';

Archive-first workflow:

1. export old rows to object storage or warehouse
2. verify archive integrity
3. delete old rows
4. reindex if needed

For very large tables, use monthly partitions.

12) Validate in production

Perform these actions in a test account:

• login
• create a resource
• update billing
• trigger one webhook-driven change

Then confirm:

• events exist in user_activity_events
• the same request_id appears in app logs
• event ordering is correct
• tenant and user IDs are populated
• metadata is minimal and safe

Common causes

• Tracking only on the frontend instead of the backend
• Missing tenant_id or user_id in event writes
• No request correlation ID across logs and events
• Events inserted before a transaction commits
• Duplicate writes caused by retries or double form submissions
• Unbounded metadata payloads causing table bloat
• No retention policy for old events
• Incorrect proxy/IP configuration leading to bad client IP data

Expanded causes:

• event names differ between route handlers and workers
• timestamps use inconsistent timezones
• high-volume low-value events flood the table
• webhook retries create repeated entries without deduplication
• background workers never emit events
• support tooling has no filter by tenant or user

Debugging tips

Start with one known action and trace it end to end.

Database checks

Recent events:

psql "$DATABASE_URL" -c "SELECT event_type, user_id, tenant_id, created_at FROM user_activity_events ORDER BY created_at DESC LIMIT 20;"

Event counts by type:

psql "$DATABASE_URL" -c "SELECT event_type, count(*) FROM user_activity_events GROUP BY event_type ORDER BY count(*) DESC;"

Events for one user:

psql "$DATABASE_URL" -c "SELECT * FROM user_activity_events WHERE user_id = 42 ORDER BY created_at DESC LIMIT 50;"

Query plan for a common support filter:

psql "$DATABASE_URL" -c "EXPLAIN ANALYZE SELECT * FROM user_activity_events WHERE tenant_id = 1 AND created_at > NOW() - INTERVAL '7 days' ORDER BY created_at DESC LIMIT 100;"

Log correlation

Search Nginx logs by request ID:

grep "req_abc123" /var/log/nginx/access.log

Search app logs by request ID:

grep "req_abc123" /var/log/app/app.log

Health check:

curl -I https://yourapp.com/health

Gunicorn logs:

journalctl -u gunicorn -n 200 --no-pager

Docker logs:

docker logs <app_container> --tail 200

Sentry release list:

sentry-cli releases list

What to check when data is wrong

If events are missing:

• verify the tracker runs after authentication context is available
• verify the event write happens after transaction success
• verify worker and webhook code paths also call the tracker
• verify the DB session commits

If duplicate events appear:

• check worker retry behavior
• check webhook idempotency handling
• check double form submission or client retries
• add unique dedupe keys where needed

Example dedupe column:

ALTER TABLE user_activity_events ADD COLUMN dedupe_key VARCHAR(200) NULL;
CREATE UNIQUE INDEX idx_user_activity_dedupe_key
ON user_activity_events(dedupe_key)
WHERE dedupe_key IS NOT NULL;

If IP addresses are wrong:

• confirm proxy headers are forwarded correctly
• confirm your app trusts the proxy only where appropriate
• verify you are not logging internal load balancer IPs instead of client IPs

If queries are slow:

• add compound indexes
• reduce metadata size
• archive old rows
• avoid SELECT * in admin views
• use EXPLAIN ANALYZE

For broader launch checks, use the full production list at SaaS Production Checklist.

Related guides

• Pair activity events with application logs: Structuring a Flask/FastAPI SaaS Project
• Review your broader production architecture: SaaS Architecture Overview (From MVP to Production)
• Choose tools that support this setup cleanly: Choosing a Tech Stack for a Small SaaS
• Validate everything before launch: SaaS Production Checklist

FAQ

What should I track first in an MVP?

Start with login/signup events, password changes, subscription changes, resource creation/deletion, and admin actions. These provide the most support and security value.

Should I use Mixpanel, PostHog, or my database?

Use your database first for audit-style events that must be reliable. Add an analytics tool later for product behavior and funnel analysis if needed.

Can I track anonymous users?

Yes. Store a session or anonymous ID plus IP and user agent if appropriate, but avoid collecting unnecessary personal data.

How do I avoid performance issues?

Track only important events, index common filters, keep metadata small, and archive old records. For higher volume, move low-priority writes to a queue.

Do I need user consent for activity tracking?

It depends on your jurisdiction and what you collect. Keep tracking limited to operational and security needs, document it in your privacy policy, and avoid unnecessary personal data.

Should activity tracking live in the same database as the app?

For most MVPs, yes. Keep the schema simple and indexed. Split later only if event volume or retention needs grow.

Is this the same as analytics?

No. Audit/activity tracking records important actions reliably on the server. Product analytics often focuses on funnels and behavior and may use a separate pipeline.

Should I store raw request bodies?

Usually no. Store selected metadata only. Raw bodies often contain secrets, personal data, or unnecessary noise.

How long should I retain events?

Keep only what you need for support, security, and compliance. Common defaults are 90 to 365 days in the primary database.

Should failed login attempts be tracked?

Yes. They are useful for security monitoring, support, and abuse detection. Avoid storing raw passwords or secret material.

Final takeaway

For small SaaS products, start with a single server-side activity events table and a reusable tracking helper.

Track only high-value events first, attach request and tenant context, and keep sensitive data out of metadata.

Make activity data searchable by support and correlated with logs and monitoring tools so production debugging is faster.