Understanding the Structure of the Paperclip Activity Log for Audit Trails and Action Attribution

The Paperclip activity log is a relational activity_log table that captures every system mutation with mandatory actor attribution, entity references, and extensible JSONB metadata, enforcing strict company-scoped isolation for tamper-evident audit trails.

Paperclip is an open-source operations platform that treats observability as a first-class concern. Every change—whether initiated by a human user, an autonomous agent, or a background process—is persisted in a centralized activity log that serves as the immutable source of truth for compliance, debugging, and forensic analysis. The implementation resides in the paperclipai/paperclip repository and combines a Drizzle ORM schema with shared TypeScript contracts and a plugin-facing SDK.

Database Schema and Table Structure

The audit trail is physically stored in the activity_log table defined in packages/db/src/schema/activity_log.ts. Each row represents a single discrete event and contains the following columns:

• id – Primary key (uuid), auto-generated.
• company_id – Foreign key enforcing tenant isolation; every query is scoped to a specific company.
• actor_type – Categorical discriminator indicating the initiator class (system, user, agent).
• actor_id – Concrete identifier of the initiator (e.g., user email, agent UUID).
• action – Canonical verb describing the operation (e.g., issue.created, run.started).
• entity_type – Classification of the affected resource (issue, run, routine).
• entity_id – UUID or unique string referencing the specific affected resource.
• agent_id (optional) – Links the event to the autonomous agent that executed the action.
• run_id (optional) – Associates the activity with a specific heartbeat run, enabling traceability for automated workflows.
• responsible_user_id (optional) – Captures the ultimate human accountable for delegated or automated operations.
• details (jsonb) – Free-form payload storing rich context such as field diffs, error stack traces, or custom tags.
• created_at (timestamp with time zone) – Immutable server timestamp set to now() by default.

Indexing Strategy for Audit Queries

To support high-volume forensic lookups, the schema defines targeted indexes in packages/db/src/schema/activity_log.ts:

• companyCreatedIdx – Composite index on (company_id, created_at) for chronological tail queries within a tenant.
• companyAgentCreatedIdx – Filters activity by agent within a company scope.
• companyResponsibleUserCreatedIdx – Enables accountability searches by the responsible human user.
• runIdIdx – Retrieves all events belonging to a specific heartbeat execution.
• entityIdx – Fetches complete audit histories for a given (entity_type, entity_id) pair.

Action Attribution and Actor Model

Attribution is hierarchical. The actor_type and actor_id fields identify the immediate executor, while responsible_user_id provides a bridge for delegated authority. When an agent performs work on behalf of a user, actor_type is agent, actor_id references the agent UUID, and responsible_user_id contains the delegating user’s identifier. This distinction is critical for compliance reporting, as it preserves both who triggered the code and who owns the outcome.

Shared Types and API Contract

Client applications interact with the log through a REST endpoint declared in packages/shared/src/api.ts:

GET ${API_PREFIX}/activity

The response payload conforms to the ActivityEvent type defined in packages/shared/src/types/activity.ts. This shared contract ensures that web dashboards, CLIs, and third-party integrations deserialize audit records consistently without direct database coupling.

Writing Audit Records

Server-Side Insertion

Internal services write directly to the table using the Drizzle ORM interface. Because the schema maps snake_case columns to camelCase properties, inserts use object keys matching the TypeScript interface:

import { db } from "@/db";
import { activityLog } from "@/db/schema/activity_log";

await db
  .insert(activityLog)
  .values({
    companyId: company.id,
    actorType: "user",
    actorId: user.id,
    action: "issue.created",
    entityType: "issue",
    entityId: newIssue.id,
    responsibleUserId: user.id,
    details: { title: newIssue.title, description: newIssue.description },
  });

Plugin SDK Integration

Plugins running inside the Paperclip sandbox consume the ctx.activity.log method, which requires the activity.log.write capability. This abstraction prevents direct database access while maintaining rich audit trails. The method signature is defined in packages/plugins/sdk/src/types.ts:

activity.log(entry: PluginActivityEntry): Promise<void>

Usage within a plugin handler:

export async function handler(ctx: PluginContext) {
  await ctx.activity.log({
    companyId: ctx.company.id,
    actorType: "agent",
    actorId: ctx.agent.id,
    action: "run.completed",
    entityType: "run",
    entityId: ctx.run.id,
    details: { status: "success", durationMs: 1245 },
  });
}

Querying Activity Data

Frontend components retrieve scoped audit trails via the shared API. The following React Query hook demonstrates filtering by entity, leveraging the indexes defined in the schema:

import { useQuery } from "@tanstack/react-query";
import { api } from "@/api";

export const useIssueActivity = (companyId: string, issueId: string) => {
  return useQuery(["issues", "activity", issueId], () =>
    api.get(`/activity?companyId=${companyId}&entityType=issue&entityId=${issueId}`)
  );
};

Because every request implicitly carries the company_id predicate, the query planner utilizes companyCreatedIdx or entityIdx for efficient execution, preventing cross-tenant data leakage.

Summary

• The activity_log table in packages/db/src/schema/activity_log.ts stores immutable audit records with strict company_id isolation.
• Attribution is handled via actor_type, actor_id, and responsible_user_id, supporting both direct user actions and delegated agent workflows.
• Entity linking through entity_type and entity_id enables per-resource audit histories via the entityIdx index.
• Extensibility is provided by the details JSONB column, which accepts arbitrary structured metadata without schema migrations.
• Integration layers include the shared ActivityEvent type, the REST endpoint in packages/shared/src/api.ts, and the plugin SDK’s ctx.activity.log method constrained by the activity.log.write capability.

Frequently Asked Questions

How does Paperclip distinguish between a user action and an automated agent action?

The schema uses the actor_type column as a discriminator. When an autonomous process generates an event, actor_type is set to agent and agent_id captures the specific agent UUID. If the agent is performing work on behalf of a human, responsible_user_id contains the delegating user’s identifier, preserving accountability while accurately recording the execution context.

What is the purpose of the details JSONB field in the activity log?

The details column stores unstructured, event-specific context such as field-level diffs, error messages, or diagnostic telemetry. Using jsonb allows the system to capture rich metadata without requiring schema migrations for every new event type, while PostgreSQL’s indexing capabilities still permit efficient querying of specific keys when necessary.

How does the activity log enforce data isolation between companies?

Every row in the activity_log table contains a mandatory company_id foreign key. All database indexes are composite keys that prefix company_id, and the application layer (both REST endpoints and plugin SDK methods) implicitly filters queries by the authenticated company context. This design makes cross-company leakage structurally impossible at the storage layer.

Where can I find the TypeScript definitions for activity log entries?

The canonical type definition for client-side activity representations is ActivityEvent, exported from packages/shared/src/types/activity.ts. For plugin authors, the write-specific contract is PluginActivityEntry defined in packages/plugins/sdk/src/types.ts, which mirrors the database schema but omits server-managed fields like created_at.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}