The following is copy/pasted from backend repo Any changes made here should be copied back.

Dashboard API Edge Function

Purpose: REST API endpoints for the Customer Dashboard, providing transaction history, content performance metrics, activity timelines, and RoC (Return on Contribution) breakdowns.

Status: Active Last Updated: 2025-12-22 Version: 1.5.0

Overview

The Dashboard API is a Supabase Edge Function that provides authenticated REST endpoints for customer-facing dashboards. It enables users to:

View current account balance and balance history
View paginated transaction history with filtering and sorting
Drill down into individual transaction details
Monitor content performance and RoC earnings
Visualize activity trends over time
Analyze RoC earnings by source, day, or querier

Base URL

https://<project-ref>.supabase.co/functions/v1/dashboardApi

Authentication

All endpoints require JWT authentication via the Authorization header:

Authorization: Bearer <user-jwt>

The JWT must contain app claims with:

userId - User's UUID
orgId - Organization ID
accessLevel - User's access level (optional)

Endpoints

1. GET /transactions

Description: Paginated list of transactions with filtering and sorting.

Query Parameters:

Parameter	Type	Default	Description
`page`	integer	1	Page number (1-indexed)
`limit`	integer	20	Items per page (max 100)
`type`	string	-	Comma-separated transaction types filter
`role`	string	"all"	Filter by user's role: `user`, `contributor`, `ipr_owner`, `all`
`date_from`	ISO date	-	Start date (inclusive)
`date_to`	ISO date	-	End date (inclusive)
`sort_by`	string	"created_at"	Sort column: `created_at`, `platform_fee`, `credits_earned`
`sort_order`	string	"desc"	Sort order: `asc`, `desc`

Transaction Types:

document_add - Document upload
document_delete - Document deletion
document_update - Document update
query_usage - RAG query cost
credit_earned - RoC credit earned as contributor
credit_spent - Balance deduction
ipr_revenue - IPR royalty (future)
license_fee - License fee (future)

Response:

{
  data: [
    {
      id: string,
      transaction_type: string,
      created_at: string,
      platform_fee: number | null,
      credits_earned: number | null,
      usage_duration_seconds: number | null,
      source_url: string | null,
      source_title: string | null,
      contributor_id: string | null,   // Document owner's user ID (for messaging/profile link)
      contributor_name: string | null, // Document owner's full_name
      role: string,  // user's role in this transaction
      balance_before: number | null,
      balance_after: number | null,
      summary: {
        contributors_count: number,
        sources_count: number
      },
      dublin_core: {              // Dublin Core metadata for popover display
        dc_title?: string | null,
        dc_creator?: string | null,
        dc_publisher?: string | null,
        dc_date?: string | null,
        dc_rights?: string | null,
        dc_description?: string | null,
        dc_source?: string | null,
        dc_identifier?: string | null
      } | null
    }
  ],
  pagination: {
    page: number,
    limit: number,
    total: number,
    total_pages: number,
    has_next: boolean,
    has_prev: boolean
  },
  filters_applied: {
    types: string[],
    dateFrom: string | null,
    dateTo: string | null,
    sortBy: string,
    sortOrder: "asc" | "desc",
    role: string
  }
}

Example:

curl -X GET \
  'https://<project>.supabase.co/functions/v1/dashboardApi/transactions?page=1&limit=10&type=query_usage,credit_earned&role=all' \
  -H 'Authorization: Bearer <jwt>'

2. GET /transactions/:id

Description: Full transaction details including source attribution and RoC distribution.

Response:

{
  id: string,
  transaction_type: string,
  created_at: string,
  user_id: string | null,
  org_id: string,
  platform_fee: number | null,
  usage_duration_seconds: number | null,
  hourly_rate: number | null,
  roc_split_percent: number | null,

  // For query_usage/credit_earned transactions
  query_details?: {
    question: string | null,
    model: string | null,
    retrieval_method: string | null,
    relevance_score: number | null
  },

  // For document operations (document_add, document_delete, document_update)
  document_details?: {
    source_url: string | null,
    source_title: string | null,
    document_count: number | null,
    vector_count: number | null,
    source_type: string | null,
    dublin_core?: {              // Dublin Core metadata (from upload)
      dc_title?: string | null,
      dc_creator?: string | null,
      dc_publisher?: string | null,
      dc_date?: string | null,
      dc_rights?: string | null,
      dc_description?: string | null,
      dc_source?: string | null,
      dc_identifier?: string | null
    } | null
  },

  balance_impact: {
    balance_before: number | null,
    balance_after: number | null,
    deduction: number | null
  },

  // Source attribution for queries
  sources?: [
    {
      source_url: string,
      source_title: string | null,  // Document title from vector metadata
      contributor_id: string | null,
      contributor_name: string | null,
      content_type: "contribution" | "ipr",
      portion: number,
      roc_earned: number | null,
      ipr_cost: number | null,
      chunks_used: number | null,
      dublin_core?: {              // Dublin Core metadata for popover display
        dc_title?: string | null,
        dc_creator?: string | null,
        dc_publisher?: string | null,
        dc_date?: string | null,
        dc_rights?: string | null,
        dc_description?: string | null,
        dc_source?: string | null,
        dc_identifier?: string | null
      } | null
    }
  ],

  roc_distribution?: {
    total_distributed: number,
    contributors_paid: number
  }
}

Example:

curl -X GET \
  'https://<project>.supabase.co/functions/v1/dashboardApi/transactions/123e4567-e89b-12d3-a456-426614174000' \
  -H 'Authorization: Bearer <jwt>'

3. GET /content-performance

Description: Per-document RoC earnings and usage statistics for content you've contributed.

User Filtering: This endpoint automatically filters to show only the logged-in user's own documents. The contributor_id is extracted from the JWT claims and passed to the SQL function - there is no parameter to view other users' content. This is intentional for privacy: users can only see performance metrics for their own contributions.

Query Parameters:

Parameter	Type	Default	Description
`days`	integer	30	Lookback period (1-365)
`limit`	integer	20	Max documents to return (max 100)
`sort_by`	string	"roc_earned"	Sort by: `roc_earned`, `times_queried`, `avg_relevance`
`org_id`	string	-	Filter by organization (optional)

Response:

{
  data: [
    {
      source_url: string,
      source_title: string | null,
      uploaded_at: string | null,
      // Note: contributor_id/contributor_name are NOT included because this endpoint
      // already filters by the logged-in user - all documents shown are the user's own content
      performance: {
        times_queried: number,
        total_roc_earned: number,
        avg_relevance_score: number | null,
        avg_portion: number | null,
        unique_queriers: number,
        last_queried: string | null
      },
      dublin_core: {              // Dublin Core metadata for popover display
        dc_title?: string | null,
        dc_creator?: string | null,
        dc_publisher?: string | null,
        dc_date?: string | null,
        dc_rights?: string | null,
        dc_description?: string | null,
        dc_source?: string | null,
        dc_identifier?: string | null
      } | null
    }
  ],
  summary: {
    total_documents: number,
    total_roc_earned: number,
    total_times_queried: number,
    avg_relevance_overall: number | null,
    period_days: number
  }
}

Example:

curl -X GET \
  'https://<project>.supabase.co/functions/v1/dashboardApi/content-performance?days=30&limit=10&sort_by=roc_earned' \
  -H 'Authorization: Bearer <jwt>'

4. GET /activity-timeline

Description: Daily activity aggregates for timeline/chart visualization.

Query Parameters:

Parameter	Type	Default	Description
`days`	integer	30	Lookback period (1-365)
`granularity`	string	"day"	Aggregation: `day`, `week`, `month`
`org_id`	string	-	Filter by organization (optional)

Response:

{
  data: [
    {
      date: string,           // YYYY-MM-DD
      queries_made: number,   // Queries you initiated
      queries_received: number, // Queries that used your content
      documents_uploaded: number,
      roc_earned: number,
      platform_fees_paid: number,
      balance_delta: number   // roc_earned - platform_fees_paid
    }
  ],
  summary: {
    total_queries_made: number,
    total_queries_received: number,
    total_documents_uploaded: number,
    total_roc_earned: number,
    total_platform_fees: number,
    net_balance_change: number,
    period_days: number,
    transaction_counts: {     // Transaction counts by type for KPI cards
      query_usage: number,
      credit_earned: number,
      document_add: number,           // Count of upload transactions
      unique_documents_added: number, // Count of unique source_urls uploaded
      document_delete: number,
      document_update: number,
      credit_spent: number,
      ipr_revenue: number,
      license_fee: number,
      stripe_payment: number,
      stripe_refund: number
    }
  }
}

Example:

curl -X GET \
  'https://<project>.supabase.co/functions/v1/dashboardApi/activity-timeline?days=30&granularity=day' \
  -H 'Authorization: Bearer <jwt>'

5. GET /balance

Description: Returns the user's current RoC credit balance and optionally recent balance history.

Query Parameters:

Parameter	Type	Default	Description
`include_history`	boolean	false	Include recent balance-affecting transactions
`history_limit`	integer	10	Number of history items (max 50)

Response:

{
  balance: number,              // Current balance in minutes (RoC credits)
  balance_updated_at: string | null,  // ISO timestamp of last update
  currency: string,             // "minutes" (RoC credit unit)
  history?: [                   // Only if include_history=true
    {
      transaction_id: string,
      created_at: string,
      transaction_type: string,
      change: number,           // Absolute value of change
      direction: "credit" | "debit",
      balance_before: number | null,
      balance_after: number | null
    }
  ]
}

Example:

curl -X GET \
  'https://<project>.supabase.co/functions/v1/dashboardApi/balance?include_history=true&history_limit=5' \
  -H 'Authorization: Bearer <jwt>'

6. GET /roc-breakdown

Description: RoC earnings breakdown grouped by source, day, or querier.

Query Parameters:

Parameter	Type	Default	Description
`days`	integer	30	Lookback period (1-365)
`limit`	integer	20	Max items to return (max 100)
`group_by`	string	"source"	Group by: `source`, `day`, `querier`
`org_id`	string	-	Filter by organization (optional)

Response:

{
  data: [
    {
      source_url: string,       // For group_by=source: document URL
                                // For group_by=day: date string
                                // For group_by=querier: user ID
      source_title: string | null,
      total_roc_earned: number,
      times_used: number,
      unique_queriers: number,
      avg_portion_per_query: number | null,
      avg_relevance: number | null
    }
  ],
  totals: {
    total_roc_earned: number,
    total_times_used: number,
    unique_sources: number,
    period_days: number
  }
}

Example:

curl -X GET \
  'https://<project>.supabase.co/functions/v1/dashboardApi/roc-breakdown?days=30&group_by=source&limit=10' \
  -H 'Authorization: Bearer <jwt>'

7. GET /leaderboard

Description: Top contributors ranked by RoC credits earned. Enables discovery of subject matter experts.

Query Parameters:

Parameter	Type	Default	Description
`days`	integer	30	Lookback period (1-365)
`limit`	integer	20	Number of contributors to return (max 100)
`offset`	integer	0	Pagination offset for scrolling
`around_me`	boolean	false	Center results around current user's rank
`org_id`	string	-	Filter by organization (defaults to user's org)

Response:

{
  data: [
    {
      rank: number,                    // Position on leaderboard
      contributor_id: string,          // UUID for messaging/profile link
      contributor_name: string | null, // User's full_name (not email)
      documents_contributed: number,   // Unique documents contributed
      times_content_used: number,      // Number of times content was used
      total_roc_earned: number,        // Total RoC credits earned
      avg_relevance_score: number | null,
      last_activity_at: string | null,
      is_current_user: boolean         // Highlights user's own entry
    }
  ],
  summary: {
    total_contributors: number,        // Total contributors in org/period
    total_roc_distributed: number,     // Total RoC distributed
    period_days: number,               // Lookback period
    user_rank: number | null,          // Current user's rank (null if not on board)
    user_total_roc: number | null      // Current user's RoC earned
  },
  pagination: {
    offset: number,
    limit: number,
    total: number,
    has_more: boolean
  }
}

Example - Top 10:

curl -X GET \
  'https://<project>.supabase.co/functions/v1/dashboardApi/leaderboard?limit=10&days=30' \
  -H 'Authorization: Bearer <jwt>'

Example - "Where Am I?" (centered on user):

curl -X GET \
  'https://<project>.supabase.co/functions/v1/dashboardApi/leaderboard?around_me=true&limit=20&days=30' \
  -H 'Authorization: Bearer <jwt>'

Frontend Integration:

Use is_current_user to highlight the user's row
Use summary.user_rank for "Where Am I?" button
Use around_me=true to scroll leaderboard to user's position
Use offset for pagination arrows (up/down navigation)

Error Responses

All endpoints return consistent error responses:

{
  error: string,
  timestamp: string
}

HTTP Status Codes:

400 - Bad Request (invalid parameters)
401 - Unauthorized (missing/invalid JWT)
404 - Not Found (resource doesn't exist or access denied)
405 - Method Not Allowed (only GET supported)
500 - Internal Server Error

Database Requirements

This API requires the following database objects (created by migration 20251213000001_dashboard_api_functions.sql):

SQL Functions

Function	Purpose
`get_content_performance()`	Per-document RoC metrics aggregation
`get_activity_timeline()`	Daily activity aggregates
`get_roc_breakdown()`	RoC breakdown by source

Indexes

Index	Purpose
`idx_transactions_dashboard_list`	Transaction list queries
`idx_transactions_contributor_content`	Contributor content queries
`idx_transactions_by_roles`	Role-based transaction lookup

File Structure

dashboardApi/
├── README.md              # This file
├── index.ts               # Main entry point and routing
├── _deps.ts               # Dependency re-exports
├── deno.json              # Deno configuration
├── types.ts               # TypeScript interfaces
├── dashboardApi.test.ts   # Unit tests
├── handlers/
│   ├── transactions.ts    # /transactions endpoints
│   ├── content.ts         # /content-performance endpoint
│   ├── activity.ts        # /activity-timeline endpoint
│   ├── roc.ts             # /roc-breakdown endpoint
│   ├── balance.ts         # /balance endpoint
│   └── leaderboard.ts     # /leaderboard endpoint
└── utils/
    ├── pagination.ts      # Pagination helpers
    └── filters.ts         # Query filter parsing

Development

Run Locally

deno task run:dashboardApi

Run Tests

TEST_NAME=dashboardApi deno task test:fns

Deploy

supabase functions deploy dashboardApi

Front-End Integration Notes

For 11ty/Nunjucks Integration

Authentication: The front-end must obtain a Supabase JWT via supabase.auth.signIn() and include it in all API requests.
CORS: The API includes CORS headers for browser requests.
Pagination UI: Use pagination.has_next and pagination.has_prev for navigation controls.

Chart Data: The /activity-timeline endpoint returns data ready for Chart.js or similar:

const labels = data.map((d) => d.date);
const rocValues = data.map((d) => d.roc_earned);

Transaction Drill-Down: Click a transaction row to call /transactions/:id for full details modal.
Role Indicators: Use the role field to show badges (User/Contributor/IPR Owner).
Currency Formatting: All monetary values are in the platform's base currency (RoC credits).
Query Question (query_details.question): The user's original query text is available in transaction drill-down.

Availability:

Endpoint Query Available?

GET /transactions (list) No - summary data only

GET /transactions/:id (detail) Yes - in query_details.question

Transaction types with query details:
- query_usage - The query the user made
- credit_earned - The query that used the contributor's content
Frontend usage:
```
// Fetch transaction detail
const response = await fetch(`/dashboardApi/transactions/${transactionId}`, {
  headers: { Authorization: `Bearer ${jwt}` },
});
const tx = await response.json();

// Access the question (null-safe)
const question = tx.query_details?.question || "Question not available";
```
Notes:
- question may be null for older transactions or if not stored in metadata
- Only users involved in the transaction (user, contributor, ipr_owner, licensee) can access details
Source URLs (source_url): Document URLs are now properly returned for all transaction types.

Data Location Field Availability

Transaction list source_url ✅ Available (falls back to metadata.sources[0].source_url)

For credit_earned transactions:
- The source_url field identifies which document earned the RoC credits
- Frontend can display clickable links to the contributing document
- The "Your Contributing Document" section in transaction detail modals will render correctly
Frontend usage:
```
// In transaction list rendering
if (tx.transaction_type === "credit_earned" && tx.source_url) {
  // Render clickable link to the source document
  renderDocumentLink(tx.source_url, tx.source_title);
}
```
Source Titles (source_title): Document titles are populated from vector metadata. Important notes:

Endpoint	Query Available?
`GET /transactions` (list)	No - summary data only
`GET /transactions/:id` (detail)	Yes - in `query_details.question`

Data Location	Field	Availability
Transaction list	`source_url`	✅ Available (falls back to `metadata.sources[0].source_url`)

Data Location	Field	Availability
Transaction list	`source_title`	✅ Available (falls back to `metadata.sources[0].source_title`)
Transaction detail `sources[]`	`source_title`	✅ Available (from `metadata.sources[].source_title`)
Content Performance	`source_title`	✅ Available (SQL COALESCE fallback)
RoC Breakdown	`source_title`	✅ Available (SQL COALESCE fallback)

When titles may be NULL:

Transactions created before the rag function was updated to extract titles from vector metadata
Documents uploaded without a sourceTitle in the request (title comes from Netlify processing)
The SQL migration 20251215000001_source_title_metadata_fallback.sql backfills titles from vector stores

Frontend fallback pattern:

// Check both field names for compatibility
const title =
  source.source_title || source.title || extractTitleFromUrl(source.source_url);

Building a Reusable Transaction Detail Component

The /transactions/:id endpoint returns different data depending on the transaction type. Here's guidance for building a single, reusable component that handles all types.

Transaction Type Categories

Category	Transaction Types	Key Fields
Query	`query_usage`, `credit_earned`	`query_details`, `sources`, `roc_distribution`
Document	`document_add`, `document_delete`, `document_update`	`document_details`
Financial	`credit_spent`, `ipr_revenue`, `license_fee`, `stripe_payment`, `stripe_refund`	`balance_impact`

Response Field Availability by Type

┌─────────────────────┬───────────┬───────────┬───────────┬─────────────┐
│ Field               │ query_    │ credit_   │ document_ │ credit_     │
│                     │ usage     │ earned    │ add/del   │ spent       │
├─────────────────────┼───────────┼───────────┼───────────┼─────────────┤
│ platform_fee        │ ✅        │ ❌        │ ✅        │ ✅          │
│ credits_earned      │ ❌        │ ✅        │ ❌        │ ❌          │
│ query_details       │ ✅        │ ✅        │ ❌        │ ❌          │
│ document_details    │ ❌        │ ❌        │ ✅        │ ❌          │
│ sources[]           │ ✅        │ ✅        │ ❌        │ ❌          │
│ roc_distribution    │ ✅        │ ❌        │ ❌        │ ❌          │
│ balance_impact      │ ✅        │ ❌        │ ✅        │ ✅          │
└─────────────────────┴───────────┴───────────┴───────────┴─────────────┘

TypeScript Types (Copy to Frontend)

// Transaction types enum for switch statements
type TransactionType =
  | "document_add"
  | "document_delete"
  | "document_update"
  | "query_usage"
  | "credit_earned"
  | "credit_spent"
  | "ipr_revenue"
  | "license_fee"
  | "stripe_payment"
  | "stripe_refund";

interface QueryDetails {
  question: string | null;
  model: string | null;
  retrieval_method: string | null;
  relevance_score: number | null;
}

interface DocumentDetails {
  source_url: string | null;
  source_title: string | null;
  document_count: number | null;
  vector_count: number | null;
  source_type: string | null;
}

interface BalanceImpact {
  balance_before: number | null;
  balance_after: number | null;
  deduction: number | null;
}

interface DublinCoreMetadata {
  dc_title?: string | null;
  dc_creator?: string | null;
  dc_publisher?: string | null;
  dc_date?: string | null;
  dc_rights?: string | null;
  dc_description?: string | null;
  dc_source?: string | null;
  dc_identifier?: string | null;
}

interface SourceDetail {
  source_url: string;
  source_title: string | null;
  contributor_id: string | null;
  contributor_name: string | null;
  content_type: "contribution" | "ipr";
  portion: number; // 0-1 (percentage of query this source contributed)
  roc_earned: number | null; // RoC credits earned by contributor
  ipr_cost: number | null; // IPR cost (if content_type === 'ipr')
  chunks_used: number | null;
  dublin_core?: DublinCoreMetadata | null; // Dublin Core metadata for popover
}

interface RocDistribution {
  total_distributed: number;
  contributors_paid: number;
}

interface TransactionDetail {
  id: string;
  transaction_type: TransactionType;
  created_at: string;
  user_id: string | null;
  org_id: string;

  // Common fields
  platform_fee: number | null;
  usage_duration_seconds: number | null;
  hourly_rate: number | null;
  roc_split_percent: number | null;

  // Top-level fields for credit_earned transactions (v1.3.0+)
  source_url?: string | null; // Document URL that earned credits
  source_title?: string | null; // Document title
  dublin_core?: DublinCoreMetadata | null; // Dublin Core metadata
  credits_earned?: number | null; // RoC credits earned

  // Conditional fields (check existence before use)
  query_details?: QueryDetails;
  document_details?: DocumentDetails;
  balance_impact: BalanceImpact;
  sources?: SourceDetail[];
  roc_distribution?: RocDistribution;
}

Component Architecture Pattern

// Recommended: Composition pattern with type-specific sections

function TransactionDetailModal({ transactionId }: { transactionId: string }) {
  const [tx, setTx] = useState<TransactionDetail | null>(null);

  // Fetch transaction detail
  useEffect(() => {
    fetchTransaction(transactionId).then(setTx);
  }, [transactionId]);

  if (!tx) return <LoadingSpinner />;

  return (
    <div className="transaction-detail">
      {/* Always show: Header with type badge and timestamp */}
      <TransactionHeader tx={tx} />

      {/* Always show: Balance impact (if any values present) */}
      {hasBalanceImpact(tx.balance_impact) && (
        <BalanceImpactSection impact={tx.balance_impact} />
      )}

      {/* Conditional: Query details for query_usage/credit_earned */}
      {tx.query_details && <QueryDetailsSection details={tx.query_details} />}

      {/* Conditional: Document details for document operations */}
      {tx.document_details && (
        <DocumentDetailsSection details={tx.document_details} />
      )}

      {/* Conditional: Sources list for queries */}
      {tx.sources && tx.sources.length > 0 && (
        <SourcesSection sources={tx.sources} />
      )}

      {/* Conditional: RoC distribution summary */}
      {tx.roc_distribution && (
        <RocDistributionSection distribution={tx.roc_distribution} />
      )}
    </div>
  );
}

// Helper to check if balance impact has meaningful data
function hasBalanceImpact(impact: BalanceImpact): boolean {
  return (
    impact.balance_before !== null ||
    impact.balance_after !== null ||
    impact.deduction !== null
  );
}

Display Labels by Transaction Type

const TRANSACTION_LABELS: Record<
  TransactionType,
  {
    label: string;
    icon: string;
    color: string;
    description: string;
  }
> = {
  document_add: {
    label: "Document Upload",
    icon: "📄",
    color: "green",
    description: "You uploaded a document to the knowledge base",
  },
  document_delete: {
    label: "Document Removed",
    icon: "🗑️",
    color: "red",
    description: "A document was removed from the knowledge base",
  },
  document_update: {
    label: "Document Updated",
    icon: "📝",
    color: "blue",
    description: "A document was updated in the knowledge base",
  },
  query_usage: {
    label: "Query Made",
    icon: "🔍",
    color: "purple",
    description: "You asked a question using the RAG system",
  },
  credit_earned: {
    label: "RoC Earned",
    icon: "💰",
    color: "gold",
    description: "Your content was used to answer a query",
  },
  credit_spent: {
    label: "Balance Deduction",
    icon: "💳",
    color: "orange",
    description: "Platform fee deducted from your balance",
  },
  ipr_revenue: {
    label: "IPR Revenue",
    icon: "©️",
    color: "teal",
    description: "Royalty earned from intellectual property",
  },
  license_fee: {
    label: "License Fee",
    icon: "📜",
    color: "indigo",
    description: "Fee paid for licensed content usage",
  },
  stripe_payment: {
    label: "Payment Received",
    icon: "✅",
    color: "green",
    description: "Payment added to your account",
  },
  stripe_refund: {
    label: "Refund Issued",
    icon: "↩️",
    color: "gray",
    description: "A refund was processed",
  },
};

Formatting Helpers

// Format RoC credits (minutes)
function formatRocCredits(minutes: number | null): string {
  if (minutes === null) return "—";
  if (minutes < 1) return `${(minutes * 60).toFixed(1)}s`;
  if (minutes < 60) return `${minutes.toFixed(2)} min`;
  return `${(minutes / 60).toFixed(2)} hrs`;
}

// Format platform fee (currency)
function formatPlatformFee(fee: number | null): string {
  if (fee === null) return "—";
  return `$${fee.toFixed(4)}`;
}

// Format duration
function formatDuration(seconds: number | null): string {
  if (seconds === null) return "—";
  if (seconds < 60) return `${seconds}s`;
  return `${Math.floor(seconds / 60)}m ${seconds % 60}s`;
}

// Format relevance score as percentage
function formatRelevance(score: number | null): string {
  if (score === null) return "—";
  return `${(score * 100).toFixed(0)}%`;
}

// Format portion as percentage
function formatPortion(portion: number): string {
  return `${(portion * 100).toFixed(1)}%`;
}

// Truncate question for display
function truncateQuestion(question: string | null, maxLength = 100): string {
  if (!question) return "Question not available";
  if (question.length <= maxLength) return question;
  return question.slice(0, maxLength) + "...";
}

Section Components Reference

QueryDetailsSection - Show when tx.query_details exists:

question - The user's original query (may be long, consider expandable)
model - AI model used (e.g., "gpt-4")
retrieval_method - How documents were retrieved (e.g., "similarity")
relevance_score - Overall relevance (0-1, display as percentage)

DocumentDetailsSection - Show when tx.document_details exists:

source_url - Link to the document
source_title - Document title (fallback to URL if null)
document_count - Number of document chunks
vector_count - Number of vectors stored
source_type - Type of source (e.g., "web", "pdf")
Dublin Core popover - Show metadata on hover (see below)

SourcesSection - Show when tx.sources has items:

Render as a table or list of cards
Show contributor_name (or "Anonymous" if null)
Show source_title with link to source_url
Show portion as percentage (how much this source contributed)
Show roc_earned for credit_earned transactions
Badge for content_type ("contribution" vs "ipr")
Dublin Core popover - Show metadata on hover for each source (see below)

BalanceImpactSection - Show when any balance field is non-null:

Show before → after with arrow
Highlight deduction amount
Color code: green for credits, red for debits

Access Control Notes

Users can only see transactions where they are involved as:
- user_id (they initiated the action)
- contributor_id (their content was used)
- ipr_owner_id (they own IP that was licensed)
- licensee_id (they licensed content)
The API enforces this server-side; 404 returned if no access

Dublin Core Metadata Popovers

For document details and source links, show Dublin Core metadata in Bootstrap popovers on hover. This pattern is already implemented in the RAG client (ui/rag-client/index.html).

API Support: The GET /transactions/:id endpoint now includes a dublin_core object on each source in the sources[] array. Use this data directly to populate popovers - no additional API calls needed.

Reference Implementation: See ui/rag-client/index.html lines 1036-1116

Dublin Core Fields Available

Each source in the transaction detail response includes a dublin_core field (null if no metadata available):

Field	Display Label	Description
`dc_title`	Title	Document title
`dc_creator`	Author	Creator/author of the content
`dc_publisher`	Publisher	Publishing entity
`dc_date`	Date	Publication date
`dc_rights`	Rights	Copyright/license information
`dc_description`	Summary	Brief description (truncate to ~200 chars)
`dc_source`	Source	Original source URL
`dc_identifier`	Identifier	DOI, ISBN, or other identifier

HTML Structure (Bootstrap 5)

<!-- Source link with Dublin Core popover -->
<a
  href="${sourceUrl}"
  target="_blank"
  class="source-link"
  data-bs-toggle="popover"
  data-bs-trigger="hover focus"
  data-bs-html="true"
  data-bs-title="Dublin Core Metadata"
  data-bs-content="${buildDCPopoverContent(metadata)}"
>
  <i class="bi bi-link-45deg"></i> ${sourceTitle || sourceUrl}
</a>

JavaScript Implementation

/**
 * Build popover content from Dublin Core metadata
 * @param {Object} meta - Metadata object with dc_* fields
 * @returns {string} HTML content for popover
 */
function buildDCPopoverContent(meta) {
  if (!meta) return "No metadata available";

  let content = "";

  // Core fields (always show if present)
  if (meta.dc_creator) {
    content += `<strong>Author:</strong> ${escapeHtml(meta.dc_creator)}<br>`;
  }
  if (meta.dc_publisher) {
    content += `<strong>Publisher:</strong> ${escapeHtml(
      meta.dc_publisher
    )}<br>`;
  }
  if (meta.dc_date) {
    content += `<strong>Date:</strong> ${escapeHtml(meta.dc_date)}<br>`;
  }
  if (meta.dc_rights) {
    content += `<strong>Rights:</strong> ${escapeHtml(meta.dc_rights)}<br>`;
  }
  if (meta.dc_identifier) {
    content += `<strong>ID:</strong> ${escapeHtml(meta.dc_identifier)}<br>`;
  }

  // Description (truncate if long)
  if (meta.dc_description) {
    const desc = meta.dc_description;
    const truncated = desc.length > 200 ? desc.substring(0, 200) + "..." : desc;
    content += `<strong>Summary:</strong> ${escapeHtml(truncated)}`;
  }

  return content || "No metadata available";
}

/**
 * Check if metadata has any Dublin Core fields worth showing
 */
function hasDCMetadata(meta) {
  if (!meta) return false;
  return !!(
    meta.dc_creator ||
    meta.dc_publisher ||
    meta.dc_date ||
    meta.dc_rights ||
    meta.dc_description ||
    meta.dc_identifier
  );
}

/**
 * Escape HTML to prevent XSS in popover content
 */
function escapeHtml(str) {
  if (!str) return "";
  return String(str)
    .replace(/&/g, "&amp;")
    .replace(/</g, "&lt;")
    .replace(/>/g, "&gt;")
    .replace(/"/g, "&quot;");
}

// Initialize all popovers after rendering
function initializePopovers(container) {
  const popoverTriggers = container.querySelectorAll(
    '[data-bs-toggle="popover"]'
  );
  popoverTriggers.forEach((el) => {
    new bootstrap.Popover(el, {
      container: "body",
      sanitize: false, // Allow HTML content
    });
  });
}

Dark Mode Styling

Include this CSS for dark mode support (already in ui/shared/theme.css):

[data-bs-theme="dark"] .popover {
  --bs-popover-bg: #2d3748;
  --bs-popover-border-color: #4a5568;
}

[data-bs-theme="dark"] .popover-header {
  background-color: #1a202c;
  border-bottom-color: #4a5568;
  color: #e2e8f0;
}

[data-bs-theme="dark"] .popover-body {
  color: #cbd5e0;
}

Integration Example

// In your SourcesSection component
// source.dublin_core comes directly from the API response
function renderSource(source) {
  const hasMetadata = source.dublin_core !== null;

  return `
    <div class="source-item">
      <a href="${source.source_url}"
         target="_blank"
         ${
           hasMetadata
             ? `
           data-bs-toggle="popover"
           data-bs-trigger="hover focus"
           data-bs-html="true"
           data-bs-title="Document Metadata"
           data-bs-content="${buildDCPopoverContent(source.dublin_core)}"
         `
             : ""
         }>
        ${escapeHtml(source.source_title || source.source_url)}
      </a>
      <span class="badge">${formatPortion(source.portion)}</span>
    </div>
  `;
}

// After rendering sources
initializePopovers(document.getElementById("sources-container"));

Notes

Conditional rendering: Only add popover attributes if source.dublin_core !== null
Performance: Initialize popovers once after all content is rendered, not per-element

Cleanup: Dispose popovers when modal closes to prevent memory leaks:

// On modal close
document.querySelectorAll('[data-bs-toggle="popover"]').forEach((el) => {
  const popover = bootstrap.Popover.getInstance(el);
  if (popover) popover.dispose();
});

Shared code: Consider extracting buildDCPopoverContent, escapeHtml, and initializePopovers to a shared utility file (ui/shared/popovers.js)

Dublin Core Data Flow and Troubleshooting

How Dublin Core Fields Get Populated

Dublin Core metadata flows through two separate paths during document upload, both originating from maintainSource() in documents-manager.ts:

Upload Request (with DC fields)
         │
         ▼
   maintainSource()
         │
         ├──────────────────────────────────┐
         ▼                                  ▼
   Vector Store                      Transaction Log
   (documents_fpp)                   (transactions)
         │                                  │
         ▼                                  ▼
   retriever.addDocuments()     logDocumentUploadTransaction()
   + updateDocuments()                      │
         │                                  ▼
         ▼                           Stores DC fields in
   Stores DC fields in               metadata JSON column
   chunk metadata                    (dc_creator, dc_date, etc.)
         │
         ▼
   Read by RAG endpoint          Read by Dashboard API
   (query-time sources)          (transaction history, content-performance)

Why Dublin Core May Be Missing

1. Upload form didn't provide DC fields

The most common reason. If the upload form/API only sends sourceDate but not dcCreator, dcPublisher, etc., then only dc_date will be stored. Both vector metadata AND transaction metadata will be incomplete because they receive the same input.

Verification: Check what fields the upload form sends:

// In maintainSource options:
{
  sourceDate: "2025-01-15",     // ✅ Always provided
  dcCreator: "Jane Doe",        // ❓ Only if form captures this
  dcPublisher: "Acme Corp",     // ❓ Only if form captures this
  // etc.
}

2. Document uploaded before DC fields were implemented

Documents uploaded before Dublin Core support was added will have no DC metadata in either location.

3. Google Docs URL format mismatch (FIXED in 2025-12-22)

Previously, document_add transactions stored URLs in one format (e.g., /edit) while credit_earned transactions used another (e.g., /export). The lookup failed due to exact URL matching. This is now fixed - the API normalizes by Google Doc ID.

Diagnostic SQL Queries

Check what DC fields exist for a specific document:

-- Check transaction metadata for a source URL
SELECT
  transaction_type,
  source_url,
  metadata->>'dc_title' as dc_title,
  metadata->>'dc_creator' as dc_creator,
  metadata->>'dc_publisher' as dc_publisher,
  metadata->>'dc_date' as dc_date,
  metadata->'dublin_core' as dublin_core_nested
FROM transactions
WHERE source_url LIKE '%your-document-url%'
ORDER BY created_at DESC;

-- Check vector metadata (documents_fpp) for a source URL
SELECT
  id,
  metadata->>'source' as source,
  metadata->>'title' as title,
  metadata->>'dcCreator' as dc_creator,
  metadata->>'dcPublisher' as dc_publisher,
  metadata->>'date' as date
FROM documents_fpp
WHERE metadata->>'source' LIKE '%your-document-url%'
LIMIT 5;

Ensuring DC Fields for New Uploads

To ensure Dublin Core fields appear in popovers for newly uploaded documents:

Update the upload form to capture DC fields (creator, publisher, rights, etc.)

Pass DC fields to maintainSource():

await maintainSource({
  sourceUrl: "https://example.com/doc",
  sourceDate: "2025-01-15",
  sourceTitle: "Document Title",
  dcCreator: "Author Name", // ← Add these
  dcPublisher: "Publisher Name", // ← Add these
  dcRights: "CC-BY-4.0", // ← Add these
  // ... other fields
});

Both storage locations will be populated automatically from these parameters

Backfilling Missing DC Metadata

For existing documents missing DC metadata:

Re-upload the document with DC fields populated (recommended for important documents)
Run the backfill migration 20251220000001_backfill_dublin_core_transactions.sql to restructure existing flat fields into nested dublin_core objects (but this cannot create data that wasn't captured originally)
Manual database update for critical documents (not recommended for bulk operations)

credit_earned Transaction Details

For credit_earned transactions (RoC credits earned when your content is used), the API now provides top-level fields for easy access to source information:

Top-Level Fields (NEW in v1.3.0)

These fields are available directly on the TransactionDetail object for credit_earned transactions:

Field	Type	Description
`source_url`	`string \| null`	URL of the document that earned credits
`source_title`	`string \| null`	Title of the document
`dublin_core`	`DublinCoreMetadata \| null`	Dublin Core metadata for popover display
`credits_earned`	`number \| null`	Amount of RoC credits earned

Example Response

// GET /transactions/123e4567-e89b-12d3-a456-426614174000
{
  id: "123e4567-e89b-12d3-a456-426614174000",
  transaction_type: "credit_earned",
  created_at: "2025-12-19T10:30:00Z",

  // NEW: Top-level source fields for credit_earned
  source_url: "https://docs.google.com/document/d/abc123/view",
  source_title: "Company Strategy Document",
  dublin_core: {
    dc_title: "Company Strategy Document",
    dc_creator: "Jane Doe",
    dc_publisher: "Acme Corp",
    dc_date: "2025-01-15",
    dc_rights: "Confidential"
  },
  credits_earned: 0.0542,

  // Existing fields
  query_details: {
    question: "What is our company strategy?",
    model: "gpt-4.1-mini",
    ...
  },
  sources: [...],  // Full source attribution array
  ...
}

Frontend Usage

// Rendering a credit_earned transaction detail
function renderCreditEarnedTransaction(tx) {
  // Use top-level fields directly (no need to dig into sources[])
  const documentLink = tx.source_url
    ? `<a href="${tx.source_url}" target="_blank">${
        tx.source_title || "View Document"
      }</a>`
    : "Document not available";

  const creditsDisplay = tx.credits_earned
    ? formatRocCredits(tx.credits_earned)
    : "—";

  // Dublin Core popover (if metadata available)
  const popoverAttrs = tx.dublin_core
    ? `data-bs-toggle="popover" data-bs-content="${buildDCPopoverContent(
        tx.dublin_core
      )}"`
    : "";

  return `
    <div class="credit-earned-detail">
      <h4>Your Contributing Document</h4>
      <p ${popoverAttrs}>${documentLink}</p>
      <p>Credits Earned: <strong>${creditsDisplay}</strong></p>
    </div>
  `;
}

Fallback Logic

The API applies this fallback logic for each field:

source_url: tx.source_url → tx.metadata?.sources?.[0]?.source_url
source_title: tx.source_title → tx.metadata?.sources?.[0]?.source_title
dublin_core: tx.metadata?.dublin_core → tx.metadata?.sources?.[0] (extract dc_* fields)
credits_earned: Parsed from tx.credits_earned column

This ensures backward compatibility with older transactions that may have data in different locations.

Change Log

2025-12-19

BUGFIX: Fixed credit_earned transaction detail missing top-level source_url, source_title, dublin_core
- Added source_url, source_title, dublin_core, and credits_earned to TransactionDetail interface
- For credit_earned transactions, these fields now populated at top level (not just in sources[])
- Falls back to metadata.sources[0] if direct column values are null
- Frontend can now render "Your Contributing Document" section correctly
FEATURE: Added SQL functions for ownership transfer with transaction backfill
- admin_transfer_transactions(source_url, new_owner_id) - Transfer transactions for a single document
- admin_transfer_transactions_batch(source_urls[], new_owner_id) - Batch transfer
- admin_transfer_documents_with_transactions(doc_ids[], new_owner_id) - Combined documents + transactions transfer (recommended)
- Updates user_id and contributor_id on historical transactions when document ownership is transferred
- Records audit trail in metadata (previous_user_id, ownership_transferred_at)
- Migration: 20251219000001_admin_transfer_transactions_function.sql

2025-12-22

FEATURE: Added contributor_id and contributor_name to GET /transactions list endpoint
- Document owner's user ID and display name now included for each transaction
- Enables frontend to show contributor info in transaction list popovers
- Fetches names from users table via efficient batch lookup
CHANGE: Removed contributor_id and contributor_name from GET /content-performance
- These fields were redundant since this endpoint already filters by the logged-in user's contributor_id
- All documents shown are the user's own content - no need to display their own name
- Reduces unnecessary database query for user name lookup
FEATURE: Added dublin_core to list endpoints for hover popovers
- GET /transactions now includes dublin_core field on each transaction
- GET /content-performance now includes dublin_core field on each document
- Extraction logic handles multiple storage locations:
  1. metadata.dublin_core (dedicated field, from backfill)
  2. metadata.sources[0].dublin_core (for query_usage/credit_earned)
  3. Flat dc_* fields in metadata (legacy format)
  4. source_title as dc_title fallback
- For content-performance, fetches dublin_core from document_add transactions by source_url
- Field is null if no Dublin Core metadata available
- Frontend popovers now functional on Transaction History and Top Performing Content tables
BUGFIX: Fixed dublin_core returning null for Google Docs in /content-performance
- Google Docs URLs have different formats (edit, view, export) that all refer to the same document
- document_add transactions stored /edit?tab=t.0 format, but credit_earned transactions used /export?format=html
- The exact URL match lookup was failing due to this mismatch
- Now normalizes Google Docs URLs by document ID to match across URL formats
- Also extended lookup to include document_update transactions (metadata can be updated there too)
BUGFIX: Fixed dublin_core returning null for Google Docs in /transactions/:id sources
- Applied same Google Docs URL normalization fix to the sources[] array in transaction details
- The fetchDublinCoreForSources() function extracts document ID from Google Docs URLs
- Uses ilike pattern matching to find dublin_core metadata regardless of URL format variant
- Prefers dublin_core from transaction metadata, falls back to lookup from document_add/update transactions

2025-12-18

BUGFIX: Fixed dublin_core returning null when Dublin Core metadata exists
- The extractDublinCoreMetadata function now handles two formats:
  1. Nested object: src.dublin_core = { dc_title: "...", dc_creator: "..." } (from RAG endpoint)
  2. Flat fields: src.dc_title = "..." (legacy format)
- Previously only checked for flat fields, missing the nested format stored by the RAG endpoint
- Affects GET /transactions/:id endpoint - dublin_core field now correctly populated
BUGFIX: Fixed source_url not being returned for credit_earned transactions
- Added fallback extraction from metadata.sources[0].source_url when column is NULL
- Mirrors existing source_title fallback pattern
- Frontend can now display clickable links to contributing documents in transaction detail modals
- Affects /transactions list endpoint - the source_url field is now populated for credit_earned transactions
Updated test file to auto-create test user (no longer requires manual env var configuration)
Added test case for source_url field on credit_earned transactions
FEATURE: Added unique_documents_added to transaction_counts in /activity-timeline
- document_add counts upload transactions (may include re-uploads of the same document)
- unique_documents_added counts distinct source_url values (unique documents uploaded)
- Enables KPI display of actual content portfolio growth vs activity volume
BUGFIX: Fixed Google Docs source_url storing export URLs instead of view URLs
- Documents from Google Docs are now stored with view URLs (.../view) instead of export URLs (.../export?format=html)
- Clicking on a Google Doc source_url now opens the document for viewing instead of triggering a download
- Note: Existing Google Docs documents need to be re-uploaded to get the corrected URLs
- Affects maintainSource function in documents-manager.ts
FEATURE: Added Dublin Core metadata to transaction detail sources[] response
- New dublin_core field on each source in GET /transactions/:id response
- Includes: dc_title, dc_creator, dc_publisher, dc_date, dc_rights, dc_description, dc_source, dc_identifier
- Field is null if no Dublin Core metadata is available for that source
- Enables frontend to build reusable metadata popover components
FEATURE: Added Dublin Core metadata to RAG endpoint transaction-summary SSE event
- New dublin_core field on each source in the sources[] array of the transaction-summary event
- Same structure as dashboardApi: dc_title, dc_creator, dc_publisher, dc_date, dc_rights, dc_description, dc_source, dc_identifier
- Extracted from vector metadata at query time - works for existing documents if Dublin Core fields were stored during upload
- Enables frontend to display metadata popovers in real-time query results (same component as transaction details)

2025-12-17

NEW: Leaderboard Dashboard (/dashboards/leaderboard/)
- Template: src/dashboards/leaderboard/index.njk
- JavaScript: dist/js/public/leaderboard.js
- 4 KPI cards: Total Contributors, RoC Distributed, Your Rank, Your RoC Earned
- Top 10 Contributors horizontal bar chart (Chart.js)
- Paginated leaderboard table with rank badges (gold/silver/bronze for top 3)
- Current user row highlighting with "You" badge
- "Find Me" button using around_me=true API parameter
- Flash animation when scrolling to user's position
UPDATED: Transactions Dashboard improvements
- Added days parameter to transaction filtering (was only filtering timeline)
- Replaced "RoC Spent" KPI with "IPR Fees" placeholder (coming soon)
- Changed secondary KPI row to show transaction counts by type (Queries, RoC Earned, Uploads, Deletions)
- Added loading state placeholders for all period-dependent KPIs
- Type filter dropdown now available in both Balance and Activity views
UPDATED: Default period changed from 30 days to 7 days across all dashboards
- transactions.js, content-analytics.js, customer-dashboard.js, balance-history.js
- dashboard_controls.njk default updated
UPDATED: Content Analytics improvements
- Simplified document display to clickable links (removed expandable rows)
- Fixed "Content not found" error by checking both source_id and id fields
ADDED: CSS for leaderboard styling
- Rank badges: .rank-gold, .rank-silver, .rank-bronze
- Current user highlighting: .table-row-current-user
- Flash animation: .flash-highlight with dark mode support
FIXED: Transaction detail modal URL label wrapping issue
FIXED: Dark mode readability for rank badges and form-select dropdowns
FEATURE: Added transaction_counts to /activity-timeline response summary
- Provides counts of each transaction type (query_usage, document_add, credit_earned, etc.) for the selected period
- Enables accurate KPI card rendering without relying on paginated transaction counts
- Frontend can use summary.transaction_counts.query_usage etc. for KPI display

2025-12-16

BUGFIX: Fixed query_details.question to read from usage_context column (was incorrectly looking in metadata.question)
Added comprehensive "Building a Reusable Transaction Detail Component" section
- Field availability matrix by transaction type
- TypeScript types ready to copy to frontend
- Component architecture pattern (composition-based)
- Display labels, icons, and colors for all transaction types
- Formatting helpers for RoC credits, fees, duration, relevance
- Section component reference guide
Added Dublin Core Metadata Popovers section
- Bootstrap 5 popover implementation for document/source links
- Reference to existing implementation in ui/rag-client/index.html
- JavaScript helpers: buildDCPopoverContent, hasDCMetadata, escapeHtml, initializePopovers
- Dark mode styling (already in ui/shared/theme.css)
- Memory leak prevention with popover cleanup on modal close
Added front-end integration notes for query_details.question field
- Documents how to access the user's original query in transaction drill-down
- Clarifies availability by endpoint and transaction type

2025-12-15

Added GET /leaderboard endpoint for contributor rankings
- Shows full_name (not email) to enable discovery of subject matter experts
- Supports around_me=true for "Where Am I?" functionality
- Scoped to user's organization by default
Added GET /balance endpoint for account balance KPI
Added source_title to sources[] in transaction details response
Anonymized querier IDs in /roc-breakdown?group_by=querier (privacy)
SQL migration for source_title fallback from metadata
Backfill source_title from vector store metadata
Fixed balance_before/balance_after not being stored in transaction metadata
- Values are now persisted after balance deduction for transaction drill-down
- Affects query-transaction-service.ts (RAG function backend)

2025-12-13

Initial implementation
5 REST endpoints for dashboard data
SQL migration for aggregation functions
TypeScript types for all request/response shapes

CUSTOMER_DASHBOARD_PLAN.md - Dashboard design specification
Business Model Implementation - Transaction logging context
Transactions Table Schema - Database schema

Dashboard API Edge Function

Overview

Base URL

Authentication

Endpoints

1. GET /transactions

2. GET /transactions/:id

3. GET /content-performance

4. GET /activity-timeline

5. GET /balance

6. GET /roc-breakdown

7. GET /leaderboard

Error Responses

Database Requirements

SQL Functions

Indexes

File Structure

Development

Run Locally

Run Tests

Deploy

Front-End Integration Notes

For 11ty/Nunjucks Integration

Building a Reusable Transaction Detail Component

Transaction Type Categories

Response Field Availability by Type

TypeScript Types (Copy to Frontend)

Component Architecture Pattern

Display Labels by Transaction Type

Formatting Helpers

Section Components Reference

Access Control Notes

Dublin Core Metadata Popovers

Dublin Core Fields Available

HTML Structure (Bootstrap 5)

JavaScript Implementation

Dark Mode Styling

Integration Example

Notes

Dublin Core Data Flow and Troubleshooting

How Dublin Core Fields Get Populated

Why Dublin Core May Be Missing

Diagnostic SQL Queries

Ensuring DC Fields for New Uploads

Backfilling Missing DC Metadata

credit_earned Transaction Details

Top-Level Fields (NEW in v1.3.0)

Example Response

Frontend Usage

Fallback Logic

Change Log

2025-12-19

2025-12-22

2025-12-18

2025-12-17

2025-12-16

2025-12-15

2025-12-13

Related Documentation