Repository Pattern

Overview

Autonome uses the Repository Pattern to abstract database operations. Repositories provide:

Type-safe queries with Drizzle ORM
Consistent error handling
Reusable business logic
Testable data access layer

All repositories are located in src/server/db/.

Repository Structure

The codebase has three main repositories:

tradingRepository.ts: Models, invocations, tool calls, portfolio snapshots
ordersRepository.server.ts: Orders (positions and trades) - SSOT for trading
positionsRepository.ts: Legacy position helpers (being deprecated in favor of orders)

Trading Repository

Location: src/server/db/tradingRepository.ts

Model Operations

listModels()

Get all AI models:

import { listModels } from "@/server/db/tradingRepository";

const models = await listModels();
// Returns: Model[]

listModelsOrderedAsc()

Get models ordered by name:

const models = await listModelsOrderedAsc();
// Sorted alphabetically by name

searchModels()

Search models by name or OpenRouter model name:

import { searchModels } from "@/server/db/tradingRepository";

const results = await searchModels({
	search: "GPT",
	limit: 10,
});
// Returns: Model[]

Invocation Operations

createInvocationRecord()

Create a new AI invocation record:

import { createInvocationRecord } from "@/server/db/tradingRepository";

const invocation = await createInvocationRecord(modelId);
// Returns: Invocation with auto-generated UUID

updateInvocationRecord()

Update invocation with AI response:

import { updateInvocationRecord } from "@/server/db/tradingRepository";

await updateInvocationRecord({
	id: invocationId,
	response: "AI decided to hold...",
	responsePayload: { /* full AI SDK response */ },
});

Tool Call Operations

createToolCallRecord()

Record an AI tool invocation:

import { 
	createToolCallRecord,
	ToolCallType 
} from "@/server/db/tradingRepository";

const toolCall = await createToolCallRecord({
	invocationId,
	type: ToolCallType.CREATE_POSITION,
	metadata: JSON.stringify({ symbol: "BTC", side: "LONG" }),
});

Tool Call Types:

ToolCallType.CREATE_POSITION: Opened new position
ToolCallType.CLOSE_POSITION: Closed existing position
ToolCallType.HOLDING: No action taken

getRecentToolCallsForModel()

Get recent tool calls for a specific model:

import { 
	getRecentToolCallsForModel,
	ToolCallType 
} from "@/server/db/tradingRepository";

const toolCalls = await getRecentToolCallsForModel({
	modelId,
	type: ToolCallType.CREATE_POSITION,
	limit: 100,
});

getRecentToolCallsWithModel()

Get recent tool calls across all models with model info:

const toolCalls = await getRecentToolCallsWithModel({
	type: ToolCallType.CLOSE_POSITION,
	modelName: "GPT", // Optional filter
	limit: 25,
});
// Returns: { id, createdAt, metadata, modelId, modelName, routerModel }[]

Portfolio Operations

createPortfolioSnapshot()

Record portfolio NAV at a point in time:

import { createPortfolioSnapshot } from "@/server/db/tradingRepository";

const snapshot = await createPortfolioSnapshot(
	modelId,
	"10234.56" // Net portfolio value as TEXT
);

getPortfolioHistory()

Get portfolio history for a model:

import { getPortfolioHistory } from "@/server/db/tradingRepository";

const history = await getPortfolioHistory(modelId);
// Returns: PortfolioSnapshot[] ordered by createdAt ASC

fetchPortfolioSnapshots()

Get recent snapshots with model info:

import { fetchPortfolioSnapshots } from "@/server/db/tradingRepository";

const snapshots = await fetchPortfolioSnapshots({
	modelName: "GPT-4", // Optional filter
	limit: 60,
});
// Returns: { snapshot: PortfolioSnapshot, model: { name, openRouterModelName } }[]

Model Usage Tracking

incrementModelUsage()

Update model usage statistics:

import { incrementModelUsage } from "@/server/db/tradingRepository";

await incrementModelUsage(modelId, {
	invocationCountDelta: 1,
	totalMinutesDelta: 5,
	failedWorkflowCountDelta: 0,
	failedToolCallCountDelta: 1,
});

Use case: Called after each AI invocation to track metrics.

Orders Repository

Location: src/server/db/ordersRepository.server.ts The Orders repository is the single source of truth for all trading positions and completed trades.

Order Lifecycle

Create order → status = OPEN (active position)
Close order → status = CLOSED (completed trade)

Creating Orders

createOrder()

Open a new position:

import { createOrder } from "@/server/db/ordersRepository.server";

const order = await createOrder({
	modelId,
	symbol: "BTC",
	side: "LONG",
	quantity: "0.5",
	entryPrice: "50000",
	leverage: "10",
	exitPlan: {
		stop: 48000,
		target: 52000,
		confidence: 0.8,
		invalidation: "Break below support",
		invalidationPrice: 47500,
		timeExit: null,
		cooldownUntil: null,
	},
});
// Returns: Order with status = "OPEN"

Querying Orders

getOpenOrdersByModel()

Get all active positions for a model:

import { getOpenOrdersByModel } from "@/server/db/ordersRepository.server";

const positions = await getOpenOrdersByModel(modelId);
// Returns: Order[] where status = "OPEN"

getAllOpenOrders()

Get all active positions across all models:

import { getAllOpenOrders } from "@/server/db/ordersRepository.server";

const positions = await getAllOpenOrders();
// Returns: OrderWithModel[] (includes model name)

getOpenOrderBySymbol()

Get specific open position:

import { getOpenOrderBySymbol } from "@/server/db/ordersRepository.server";

const order = await getOpenOrderBySymbol(modelId, "BTC");
// Returns: Order | undefined

Note: A model can only have one open position per symbol.

getClosedOrdersByModel()

Get completed trades for a model:

import { getClosedOrdersByModel } from "@/server/db/ordersRepository.server";

const trades = await getClosedOrdersByModel(modelId, 100);
// Returns: Order[] where status = "CLOSED", ordered by closedAt DESC

getAllClosedOrders()

Get completed trades across all models:

import { getAllClosedOrders } from "@/server/db/ordersRepository.server";

const trades = await getAllClosedOrders(100);
// Returns: OrderWithModel[]

getOrderById()

Get order by ID:

import { getOrderById } from "@/server/db/ordersRepository.server";

const order = await getOrderById(orderId);
// Returns: Order | undefined

Closing Orders

closeOrder()

Close a position (mark as completed trade):

import { closeOrder } from "@/server/db/ordersRepository.server";

const order = await closeOrder({
	orderId,
	exitPrice: "51000",
	realizedPnl: "500", // $500 profit
	closeTrigger: "TARGET", // "STOP" | "TARGET" | null
});
// Sets status = "CLOSED", closedAt = now

Close Triggers:

null: Manual close by user
"STOP": Auto-closed by stop-loss
"TARGET": Auto-closed by take-profit

closeOrdersByIds()

Bulk close multiple orders:

import { closeOrdersByIds } from "@/server/db/ordersRepository.server";

const exitPrices = new Map([
	[orderId1, { price: "51000", pnl: "500", trigger: "TARGET" }],
	[orderId2, { price: "49000", pnl: "-100", trigger: "STOP" }],
]);

const closedOrders = await closeOrdersByIds(
	[orderId1, orderId2],
	exitPrices
);

Updating Orders

updateOrderExitPlan()

Update exit plan for an open position:

import { updateOrderExitPlan } from "@/server/db/ordersRepository.server";

const order = await updateOrderExitPlan({
	orderId,
	exitPlan: {
		stop: 49000,      // New stop-loss
		target: 53000,    // New take-profit
		confidence: 0.9,
		invalidation: "Updated thesis",
		invalidationPrice: 48500,
		timeExit: null,
		cooldownUntil: null,
	},
});

scaleIntoOrder()

Scale into existing position (add to position):

import { scaleIntoOrder } from "@/server/db/ordersRepository.server";

// Existing: 0.5 BTC @ $50,000
// Adding:   0.3 BTC @ $51,000
// Result:   0.8 BTC @ $50,375 (weighted avg)

const order = await scaleIntoOrder({
	orderId,
	additionalQuantity: "0.3",
	newEntryPrice: "51000",
	newAvgEntryPrice: "50375", // Calculate this first
	exitPlan: { /* optional: update exit plan */ },
});

Weighted average formula:

const prevNotional = parseFloat(prevQty) * parseFloat(prevPrice);
const newNotional = parseFloat(addQty) * parseFloat(newPrice);
const totalQty = parseFloat(prevQty) + parseFloat(addQty);
const newAvgEntry = (prevNotional + newNotional) / totalQty;

SL/TP Order Management

For live trading mode, track real stop-loss/take-profit orders placed on exchange:

updateSlTpOrders()

Store exchange order indices:

import { updateSlTpOrders } from "@/server/db/ordersRepository.server";

await updateSlTpOrders({
	orderId,
	slOrderIndex: "123456",    // Exchange SL order ID
	tpOrderIndex: "123457",    // Exchange TP order ID
	slTriggerPrice: "48000",
	tpTriggerPrice: "52000",
});

getSlTpOrders()

Retrieve SL/TP order indices:

import { getSlTpOrders } from "@/server/db/ordersRepository.server";

const slTp = await getSlTpOrders(orderId);
// Returns: { slOrderIndex: string | null, tpOrderIndex: string | null }

clearSlTpOrders()

Clear SL/TP indices after canceling on exchange:

import { clearSlTpOrders } from "@/server/db/ordersRepository.server";

await clearSlTpOrders(orderId);
// Sets slOrderIndex, tpOrderIndex, slTriggerPrice, tpTriggerPrice to null

getOrdersWithExchangeSlTp()

Get orders with active SL/TP on exchange:

import { getOrdersWithExchangeSlTp } from "@/server/db/ordersRepository.server";

const orders = await getOrdersWithExchangeSlTp();
// Returns: Order[] where slOrderIndex or tpOrderIndex is not null

Analytics Helpers

getTotalRealizedPnl()

Calculate total realized P&L for a model:

import { getTotalRealizedPnl } from "@/server/db/ordersRepository.server";

const pnl = await getTotalRealizedPnl(modelId);
// Returns: number (sum of all closed order realizedPnl)

getOrdersWithExitPlans()

Get orders that need auto-close monitoring:

import { getOrdersWithExitPlans } from "@/server/db/ordersRepository.server";

const orders = await getOrdersWithExitPlans();
// Returns: OrderWithModel[] where exitPlan.stop or exitPlan.target is set

Use case: Auto-close scheduler checks these orders against current prices.

Positions Repository (Legacy)

Location: src/server/db/positionsRepository.ts ⚠️ Deprecation Notice: This repository is being phased out in favor of ordersRepository.server.ts. Use Orders repository for new code.

Key Differences

Feature	Positions Repo	Orders Repo
Table	`Orders`	`Orders`
Status tracking	Deletes on close	Sets status = CLOSED
History	No trade history	Full trade history
Realized P&L	Not stored	Stored in DB
Exit details	Not tracked	exitPrice, closeTrigger

Migration path: Use Orders repository for all new features. Positions repository remains for backward compatibility.

Best Practices

1. Use TypeScript Types

Always import and use generated types:

import type { Order, Model } from "@/db/schema";
import type { OrderWithModel } from "@/server/db/ordersRepository.server";

function processOrder(order: Order) {
	// Type-safe access
	const symbol = order.symbol;
	const price = parseFloat(order.entryPrice);
}

2. Store Money as TEXT

Always use strings for monetary values:

// ✅ Correct
const order = await createOrder({
	quantity: "0.5",
	entryPrice: "50000.00",
	leverage: "10",
});

// ❌ Incorrect (loses precision)
const order = await createOrder({
	quantity: 0.5,
	entryPrice: 50000.00,
});

3. Handle NULL Values

Many fields are nullable - always check:

const order = await getOrderById(orderId);

if (order?.exitPlan?.stop) {
	const stopLoss = order.exitPlan.stop;
	// Use stop-loss
}

4. Use Repository Functions

Don’t write raw queries - use repository functions:

// ✅ Correct
import { getOpenOrdersByModel } from "@/server/db/ordersRepository.server";
const orders = await getOpenOrdersByModel(modelId);

// ❌ Incorrect (bypasses abstraction)
import { db } from "@/db";
const orders = await db.select().from(orders).where(...);

Why? Repository functions handle:

Type casting (TEXT → number)
NULL handling
Consistent error messages
Business logic validation

5. Validate Exit Plans

Exit plan structure is strictly typed:

import type { Order } from "@/db/schema";

type ExitPlan = NonNullable<Order["exitPlan"]>;

function validateExitPlan(plan: ExitPlan) {
	if (plan.stop && plan.target && plan.stop >= plan.target) {
		throw new Error("Stop-loss must be below take-profit for LONG");
	}
}

6. Wrap in Transactions

For multi-step operations, use transactions:

import { db } from "@/db";

await db.transaction(async (tx) => {
	// Step 1: Close position
	const order = await closeOrder({ orderId, exitPrice, realizedPnl });
	
	// Step 2: Update model stats
	await incrementModelUsage(modelId, { 
		failedToolCallCountDelta: order.closeTrigger === "STOP" ? 1 : 0 
	});
	
	// Both succeed or both rollback
});

Error Handling

Repositories throw errors for:

Missing records
Constraint violations
Database connection issues

Always wrap in try/catch:

try {
	const order = await closeOrder({
		orderId: "nonexistent",
		exitPrice: "50000",
		realizedPnl: "100",
	});
} catch (error) {
	if (error.message.includes("not found")) {
		// Handle missing order
	} else {
		// Handle other errors
	}
}

Testing Repositories

Use test database for unit tests:

import { describe, it, expect, beforeEach } from "vitest";
import { createOrder, getOpenOrdersByModel } from "@/server/db/ordersRepository.server";

describe("ordersRepository", () => {
	beforeEach(async () => {
		// Reset test database
		await db.delete(orders);
	});

	it("creates and retrieves open order", async () => {
		const order = await createOrder({
			modelId: "test-model",
			symbol: "BTC",
			side: "LONG",
			quantity: "1",
			entryPrice: "50000",
		});

		const orders = await getOpenOrdersByModel("test-model");
		expect(orders).toHaveLength(1);
		expect(orders[0].symbol).toBe("BTC");
	});
});

Next Steps

Database Schema - Review table structure
Migrations - Learn how to modify schema
oRPC Procedures - Call repositories from API endpoints

​Overview

​Repository Structure

​Trading Repository

​Model Operations

​listModels()

​listModelsOrderedAsc()

​searchModels()

​Invocation Operations

​createInvocationRecord()

​updateInvocationRecord()

​Tool Call Operations

​createToolCallRecord()

​getRecentToolCallsForModel()

​getRecentToolCallsWithModel()

​Portfolio Operations

​createPortfolioSnapshot()

​getPortfolioHistory()

​fetchPortfolioSnapshots()

​Model Usage Tracking

​incrementModelUsage()

​Orders Repository

​Order Lifecycle

​Creating Orders

​createOrder()

​Querying Orders

​getOpenOrdersByModel()

​getAllOpenOrders()

​getOpenOrderBySymbol()

​getClosedOrdersByModel()

​getAllClosedOrders()

​getOrderById()

​Closing Orders

​closeOrder()

​closeOrdersByIds()

​Updating Orders

​updateOrderExitPlan()

​scaleIntoOrder()

​SL/TP Order Management

​updateSlTpOrders()

​getSlTpOrders()

​clearSlTpOrders()

​getOrdersWithExchangeSlTp()

​Analytics Helpers

​getTotalRealizedPnl()

​getOrdersWithExitPlans()

​Positions Repository (Legacy)

​Key Differences

​Best Practices

​1. Use TypeScript Types

​2. Store Money as TEXT

​3. Handle NULL Values

​4. Use Repository Functions

​5. Validate Exit Plans

​6. Wrap in Transactions

​Error Handling

​Testing Repositories

​Next Steps