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

adminReconcile Edge Function

Admin API for data verification and reconciliation operations. Provides endpoints to verify database integrity, check for discrepancies, and apply fixes for balance reconciliation, dublin_core metadata, and ownership transfers.

Authentication

All endpoints require a JWT with access_level >= 9 (admin).

Authorization: Bearer <admin-jwt>

Base URL

https://wgyvdvexejgrkneihhcv.supabase.co/functions/v1/adminReconcile

Units of Measurement

All balance and credit values are in minutes (RoC = Return on Contribution):

Field	Unit	Description
`balance`, `current_balance`, `new_balance`, `old_balance`	minutes	User's spendable balance
`roc_earned`, `credits_earned`, `total_roc_earned`	minutes	RoC credits earned from content usage
`gifts_received`, `total_gifts`, `amount`	minutes	Balance gifts (welcome bonuses, etc.)
`adjustments`, `total_adjustments`, `adjustment`	minutes	Manual balance adjustments
`fees_paid`, `platform_fee`, `total_platform_fees`	minutes	Platform fees deducted for queries
`discrepancy`	minutes	Difference between current and calculated balance
`threshold`	minutes	Minimum discrepancy to report (default: 0.01)

Example: A user with balance: 100.5 has 100.5 minutes of query time available.

Endpoints

Health Check

`GET /` or `GET /health`

Returns service status and lists all available endpoints.

Response:

{
  "status": "ok",
  "service": "adminReconcile",
  "endpoints": {
    "verify": [...],
    "reconcile": [...]
  }
}

Verification Endpoints (GET)

These endpoints check data integrity without making changes. Use these to diagnose issues before applying fixes.

`GET /verify/functions`

Help Text: Check if all required database functions are installed. Run this first after deploying migrations to ensure the system is ready.

Verifies all required admin SQL functions exist in the database.

Response:

{
  "success": true,
  "functions": [
    {
      "name": "admin_recalculate_balance",
      "exists": true,
      "security_definer": true
    },
    {
      "name": "admin_gift_balance_with_transaction",
      "exists": true,
      "security_definer": true
    }
  ],
  "missing": []
}

What to do if functions are missing:

Run the SQL migrations in order (see "Related SQL Migrations" section)
Missing functions will cause other endpoints to fail

`GET /verify/balance?threshold=0.01`

Help Text: Find users whose displayed balance doesn't match their transaction history. A discrepancy means the balance was modified outside the transaction system (e.g., old welcome bonuses, manual database edits).

Finds users whose current balance differs from the calculated value (based on transactions).

Query Parameters:

Parameter	Type	Default	Description
`threshold`	number	0.01	Minimum discrepancy to report (in minutes)

Response:

{
  "success": false,
  "discrepancies": [
    {
      "user_id": "uuid",
      "full_name": "John Doe",
      "current_balance": 100.5,
      "calculated_balance": 50.5,
      "discrepancy": 50.0,
      "roc_earned": 50.5,
      "gifts_received": 0,
      "adjustments": 0,
      "fees_paid": 0
    }
  ],
  "total_users_checked": 42
}

Calculation Formula: calculated_balance = roc_earned + gifts_received + adjustments - fees_paid

Understanding Results:

Positive discrepancy: User has MORE balance than transactions explain (likely from welcome bonus or gift not recorded as transaction)
Negative discrepancy: User has LESS balance than expected (possible bug or manual deduction)
Zero discrepancies: All balances match transaction history perfectly

Next Steps:

For positive discrepancies → Use "Backfill Gifts" to create transaction records
For negative discrepancies → Investigate manually, may need balance adjustment

`GET /verify/dublin-core`

Help Text: Check if all transactions have dublin_core metadata (title, creator, etc.). This metadata is needed for proper attribution display in the dashboard.

Checks the dublin_core metadata backfill status for transactions.

Response:

{
  "success": false,
  "stats": [
    {
      "transaction_type": "document_add",
      "total": 150,
      "with_dublin_core": 145,
      "missing_dublin_core": 5,
      "coverage_percent": 96.67
    },
    {
      "transaction_type": "credit_earned",
      "total": 200,
      "with_dublin_core": 200,
      "missing_dublin_core": 0,
      "coverage_percent": 100
    }
  ],
  "total_missing": 5
}

Understanding Results:

100% coverage: All transactions have metadata - no action needed
< 100% coverage: Some transactions are missing dublin_core metadata
Missing metadata means document titles won't display correctly in transaction history

Next Steps:

Run the dublin_core backfill migration: 20251220000001_backfill_dublin_core_transactions.sql

`GET /preview/balance/:userId`

Help Text: See exactly how a user's balance would be recalculated before actually changing it. Shows breakdown of RoC earned, gifts, fees, and activity counts.

Preview what a balance recalculation would do for a specific user (dry run). Does NOT change any data.

Response:

{
  "user_id": "uuid",
  "current_balance": 100.5,
  "calculated_balance": 50.5,
  "discrepancy": 50.0,
  "roc_earned_as_contributor": 50.5,
  "gifts_received": 0,
  "adjustments": 0,
  "fees_paid_as_user": 0,
  "document_uploads": 10,
  "queries_made": 25,
  "roc_transactions": 15
}

Calculation Formula: calculated_balance = roc_earned + gifts_received + adjustments - fees_paid

Field Explanations:

current_balance: What the user sees now
calculated_balance: What balance SHOULD be based on transactions
discrepancy: Difference (current - calculated)
roc_earned_as_contributor: Total RoC credits earned from content usage
gifts_received: Total from balance_gift transactions (welcome bonuses, etc.)
adjustments: Total from balance_adjustment transactions (can be + or -)
fees_paid_as_user: Total platform fees deducted for queries
document_uploads: Number of documents uploaded
queries_made: Number of RAG queries made
roc_transactions: Number of credit_earned transactions

Reconciliation Endpoints (POST)

These endpoints make changes to fix data issues. Use verification endpoints first to understand what will change.

`POST /reconcile/balance`

Help Text: Recalculate a user's balance from scratch using their transaction history. Use this after ownership transfers or to fix incorrect balances. The new balance = RoC earned + gifts + adjustments - platform fees.

Recalculates and updates balance for one or more users from transaction history.

Request Body:

{
  "userIds": ["uuid-1", "uuid-2"]
}
// OR
{
  "userId": "uuid"
}

Response:

{
  "results": [
    {
      "user_id": "uuid",
      "old_balance": 100.5,
      "new_balance": 50.5,
      "total_roc_earned": 50.5,
      "total_gifts": 0,
      "total_adjustments": 0,
      "total_platform_fees": 0,
      "transactions_counted": 15
    }
  ],
  "total_updated": 1
}

When to Use:

After transferring document ownership (RoC credits move to new owner)
After running "Backfill Gifts" to sync with new transaction records
If balance appears incorrect and you've verified the transactions are correct

Warning: This will REPLACE the user's balance. If there are untracked gifts, they will be lost. Run "Backfill Gifts" first.

`POST /reconcile/gift`

Help Text: Add balance to a user's account with a transaction record. Use for welcome bonuses, promotional credits, or manual balance additions. Creates an audit trail.

Gift balance to a user with a transaction record for audit trail.

Request Body:

{
  "userId": "uuid",
  "amount": 100.0,
  "reason": "Welcome bonus"
}

Response:

{
  "transaction_id": "uuid",
  "user_id": "uuid",
  "amount": 100.0,
  "old_balance": 0,
  "new_balance": 100.0,
  "reason": "Welcome bonus"
}

Common Reasons:

"Welcome bonus" - New user signup credit
"Promotional credit" - Marketing campaign
"Compensation" - Service issue resolution
"Beta tester reward" - Early adopter thanks

`POST /reconcile/adjust`

Help Text: Adjust a user's balance up or down with a transaction record. Use for refunds (negative), corrections, or other manual adjustments. Supports both positive and negative amounts.

Adjust balance (positive or negative) with a transaction record.

Request Body:

{
  "userId": "uuid",
  "adjustment": -5.0,
  "reason": "Refund for failed query"
}

Response:

{
  "transaction_id": "uuid",
  "user_id": "uuid",
  "adjustment": -5.0,
  "old_balance": 100.0,
  "new_balance": 95.0,
  "reason": "Refund for failed query"
}

Common Use Cases:

Negative adjustment: Refund for failed query, correction for over-credited amount
Positive adjustment: Correction for under-credited amount, manual credit

Note: Cannot reduce balance below zero.

`POST /reconcile/transfer`

Help Text: Transfer document ownership and all related transactions (RoC credits, upload records) to a new user. Optionally recalculates balances for both users. Use when content ownership changes.

Transfer document/transaction ownership to a new user and optionally recalculate balances.

Request Body:

{
  "sourceUrl": "https://docs.google.com/document/d/...",
  "newOwnerId": "uuid",
  "recalculateBalances": true
}

Response:

{
  "transfer_result": [
    { "transaction_type": "document_add", "updated_count": 1 },
    { "transaction_type": "credit_earned", "updated_count": 5 }
  ],
  "balance_recalculation": {
    "old_owner_id": "old-uuid",
    "results": [
      { "user_id": "old-uuid", "old_balance": 50, "new_balance": 45 },
      { "user_id": "new-uuid", "old_balance": 0, "new_balance": 5 }
    ]
  }
}

What Gets Transferred:

document_add transactions (upload records)
credit_earned transactions (RoC credits)
Transaction metadata updated with transfer audit trail

Recommendation: Always set recalculateBalances: true to ensure both users' balances reflect the transfer.

`POST /reconcile/dublin-core`

Help Text: Check which transactions need dublin_core metadata backfill. This endpoint reports status; actual backfill is done via SQL migration.

Reports transactions needing dublin_core metadata backfill.

Request Body:

{
  "transactionType": "document_add",
  "limit": 1000
}

Note: This endpoint reports what needs backfilling. Run the SQL migration 20251220000001_backfill_dublin_core_transactions.sql to perform the actual backfill.

`POST /reconcile/backfill-gifts`

Help Text: Create balance_gift transactions for existing balance discrepancies. This converts "unexplained" balance into tracked transactions, enabling accurate future reconciliation. Always run with dryRun:true first to preview changes.

Create balance_gift transactions for existing balance discrepancies (to enable future reconciliation).

Request Body:

{
  "dryRun": true
}

Response (dry run):

{
  "dry_run": true,
  "users_with_discrepancy": 5,
  "total_discrepancy": 500.0,
  "backfilled": 0,
  "details": [
    { "user_id": "uuid", "full_name": "John Doe", "discrepancy": 100.0 }
  ]
}

Response (actual run with dryRun: false):

{
  "dry_run": false,
  "users_with_discrepancy": 5,
  "total_discrepancy": 500.0,
  "backfilled": 5,
  "details": [
    {
      "user_id": "uuid",
      "full_name": "John Doe",
      "discrepancy": 100.0,
      "transaction_id": "tx-uuid"
    }
  ]
}

Workflow:

Run with dryRun: true to see which users would be affected
Review the list - positive discrepancies are usually welcome bonuses
Run with dryRun: false to create the balance_gift transactions
Run "Verify Balances" to confirm all discrepancies are resolved

Important: Only processes POSITIVE discrepancies (users with more balance than transactions explain). Negative discrepancies require manual investigation.

Common Workflows

Initial Setup Verification

After deploying the SQL migrations, verify everything is set up correctly:

# 1. Check all SQL functions exist
curl -H "Authorization: Bearer $JWT" \
  "$BASE_URL/verify/functions"

# 2. Check for balance discrepancies
curl -H "Authorization: Bearer $JWT" \
  "$BASE_URL/verify/balance"

# 3. Check dublin_core coverage
curl -H "Authorization: Bearer $JWT" \
  "$BASE_URL/verify/dublin-core"

Backfill Existing Balance Discrepancies

# 1. Preview what will be backfilled (dry run)
curl -X POST -H "Authorization: Bearer $JWT" \
  -H "Content-Type: application/json" \
  -d '{"dryRun": true}' \
  "$BASE_URL/reconcile/backfill-gifts"

# 2. If results look correct, run the actual backfill
curl -X POST -H "Authorization: Bearer $JWT" \
  -H "Content-Type: application/json" \
  -d '{"dryRun": false}' \
  "$BASE_URL/reconcile/backfill-gifts"

# 3. Verify no more discrepancies
curl -H "Authorization: Bearer $JWT" \
  "$BASE_URL/verify/balance"

After Ownership Transfer

When transferring document ownership from one user to another:

# Transfer documents and recalculate balances for both users
curl -X POST -H "Authorization: Bearer $JWT" \
  -H "Content-Type: application/json" \
  -d '{
    "sourceUrl": "https://docs.google.com/document/d/...",
    "newOwnerId": "new-user-uuid",
    "recalculateBalances": true
  }' \
  "$BASE_URL/reconcile/transfer"

Gift Balance to New User

curl -X POST -H "Authorization: Bearer $JWT" \
  -H "Content-Type: application/json" \
  -d '{
    "userId": "user-uuid",
    "amount": 100,
    "reason": "Welcome bonus"
  }' \
  "$BASE_URL/reconcile/gift"

Error Responses

All endpoints return errors in this format:

{
  "error": "Error message here"
}

Status Code	Meaning
400	Bad Request - Missing or invalid parameters
401	Unauthorized - Missing or invalid JWT
403	Forbidden - Insufficient access level (need >= 9)
404	Not Found - Unknown endpoint or user not found
500	Internal Server Error - Database or server error

This function depends on the following SQL migrations being run:

20251219000001_admin_transfer_transactions_function.sql - Transaction transfer functions
20251220000001_backfill_dublin_core_transactions.sql - Dublin Core backfill
20251220000002_admin_recalculate_balance.sql - Balance recalculation functions
20251220000003_balance_gift_transactions.sql - Gift/adjustment transaction functions

Change Log

Date	Change
2025-12-22	Added detailed help text for all endpoints for frontend integration
2025-12-22	Initial release with verify and reconcile endpoints

adminReconcile Edge Function

Authentication

Base URL

Units of Measurement

Endpoints

Health Check

GET / or GET /health

Verification Endpoints (GET)

GET /verify/functions

GET /verify/balance?threshold=0.01

GET /verify/dublin-core

GET /preview/balance/:userId

Reconciliation Endpoints (POST)

POST /reconcile/balance

POST /reconcile/gift

POST /reconcile/adjust

POST /reconcile/transfer

POST /reconcile/dublin-core

POST /reconcile/backfill-gifts

Common Workflows

Initial Setup Verification

Backfill Existing Balance Discrepancies

After Ownership Transfer

Gift Balance to New User

Error Responses

Related SQL Migrations

Change Log

`GET /` or `GET /health`

`GET /verify/functions`

`GET /verify/balance?threshold=0.01`

`GET /verify/dublin-core`

`GET /preview/balance/:userId`

`POST /reconcile/balance`

`POST /reconcile/gift`

`POST /reconcile/adjust`

`POST /reconcile/transfer`

`POST /reconcile/dublin-core`

`POST /reconcile/backfill-gifts`