Storage Backends

Orphnet Logging uses three Cloudflare storage backends, each optimized for different access patterns. Logs are written to all three during ingestion.

D1 -- Primary Query Store

Binding: D1_LOGGING

Role: Primary structured query store for all log data, user records, workspace records, project records, and API keys.

Log Storage

Logs are stored in the logs table with indexes on project_id, timestamp, and (project_id, session_id).

Columns:

Column	Type	Description
id	INTEGER	Auto-increment primary key
project_id	TEXT	Foreign key to projects table
session_id	TEXT	Optional session grouping
type	TEXT	Log type (e.g., llm, request, error)
category	TEXT	Resolved category (ai, http, system, custom)
level	TEXT	Log level (debug, info, error)
message	TEXT	Log message
timestamp	INTEGER	Unix milliseconds
data	TEXT	JSON-serialized metadata

Query Capabilities

• Full SQL filtering: any combination of project_id, session_id, type, category, level, timestamp range
• Pagination via LIMIT / OFFSET
• COUNT queries for totals
• Prepared statements with parameterized queries (no string interpolation)
• Strongly consistent reads

When D1 Is Used

The LogQueryService routes to D1 when:

• The query time window extends beyond the last 24 hours
• Complex filtering is requested (category, level, type)
• The caller explicitly sets "source": "d1"

KV -- Hot Cache

Binding: KV_LOGGING

Role: 24-hour hot cache for recent logs, API key edge cache, and temporary state (OAuth, magic links).

Log Cache

• Key format: logs:{projectId}:{sessionId}:{type}:{timestamp}
• Value: JSON-serialized log record
• TTL: 86,400 seconds (24 hours) -- entries expire automatically

API Key Cache

• Key format: api_key:{prefix}
• Value: { projectId, keyHash, isActive, scopes, workspaceId }
• TTL: 86,400 seconds

Temporary State

• OAuth state: oauth_state:{state} -- PKCE verifier and provider info (5-minute TTL)
• Magic link state: magic_link:{tokenHash} -- email and token hash (15-minute TTL)
• Rate limit counters: rate:{key} -- request counts per window

When KV Is Used

The LogQueryService routes to KV when:

• Both fromTimestamp and toTimestamp fall within the last 24 hours
• The caller explicitly sets "source": "kv"

Strengths and Limitations

Strengths:

• Sub-millisecond reads at the edge
• No query cost for recent log access
• Automatic TTL-based expiry

Limitations:

• Prefix-only filtering (no range queries, no ORDER BY)
• Eventually consistent (writes may take seconds to propagate globally)
• 25 MB value size limit per entry

R2 -- Archive

Binding: R2_LOGGING

Role: Long-term NDJSON archive for export and offline processing. No real-time query path.

Storage Format

• Key format: logs/{projectId}/{YYYY-MM-DD}/{HH}.ndjson
• Content: Newline-delimited JSON (one log entry per line)
• Hour granularity: ISO prefix enables efficient date-range listing

{"id":1,"project_id":"proj_...","message":"Hello","level":"info","type":"log","timestamp":1709812800000}
{"id":2,"project_id":"proj_...","message":"World","level":"info","type":"log","timestamp":1709812801000}

Export

The export endpoint returns a ReadableStream of NDJSON for memory-efficient bulk data retrieval. The R2 key format uses hour-granularity ISO prefix for efficient date-range listing.

Strengths and Limitations

Strengths:

• Cheapest storage for large volumes
• Efficient streaming export via ReadableStream
• Hierarchical path structure enables time-range exports

Limitations:

• Not queryable in real-time -- for data access, use D1 or KV
• Read-modify-write on append (fetches existing file, appends, re-puts)
• No indexing

Write Pipeline

During log ingestion, the queue consumer writes to all three backends via Promise.allSettled():

1. KV: Write with 24h TTL for immediate edge reads
2. D1: Write to logs table for queryable storage
3. R2: Append to hourly NDJSON file for archival

Individual backend failures are logged (console.error) but do not fail the overall write. The 202 Accepted response reflects queue acceptance, not storage success.

Smart Query Routing

The LogQueryService automatically selects the best backend for queries:

Condition	Backend	Reason
Time window entirely within last 24h	KV	Fastest edge reads
Time window extends beyond 24h	D1	Full SQL capabilities
Complex filters (category, level)	D1	SQL filtering
Explicit "source": "d1"	D1	Caller override
Explicit "source": "kv"	KV	Caller override
Archive export	R2	Bulk NDJSON streaming

See Also

• Architecture -- System architecture overview
• Configuration -- Binding configuration
• Rate Limits -- Rate limiting across backends