Database Schema

Overview

Autonome uses PostgreSQL with Drizzle ORM to manage trading data, model invocations, and portfolio history. The schema is designed with these critical principles:

Quoted identifiers: Table names use PascalCase (e.g., "Models", "Orders")
TEXT for money: Store monetary values as TEXT, cast to NUMERIC for calculations
UUID generation: Primary keys use crypto.randomUUID() for consistency
Orders table is single source of truth: OPEN = active positions, CLOSED = completed trades

Tables

Models

Stores AI model configurations and runtime statistics.

export const models = pgTable(
	"Models",
	{
		id: text("id").primaryKey().$defaultFn(() => crypto.randomUUID()),
		name: text("name").notNull(),
		openRouterModelName: text("openRouterModelName").notNull(),
		variant: variantEnum("variant").notNull().default("Apex"),
		lighterApiKey: text("lighterApiKey").notNull().default("0"),
		invocationCount: integer("invocationCount").notNull().default(0),
		totalMinutes: integer("totalMinutes").notNull().default(0),
		accountIndex: text("accountIndex").notNull().default("0"),
		failedWorkflowCount: integer("failedWorkflowCount").notNull().default(0),
		failedToolCallCount: integer("failedToolCallCount").notNull().default(0),
	},
	(table) => ({
		nameIdx: index("Models_name_idx").on(table.name),
		nameVariantUnique: uniqueIndex("Models_name_variant_key").on(table.name, table.variant),
	}),
);

Key Fields:

id: UUID primary key
name: Model display name (e.g., “GPT-4”, “Claude”)
variant: Trading strategy variant (Apex, Trendsurfer, Contrarian, Sovereign)
lighterApiKey: Account index for Lighter exchange API
invocationCount: Total number of AI invocations
totalMinutes: Cumulative runtime across all invocations

Unique Constraint: Each model can have up to 5 variants (one per strategy). The unique index on (name, variant) enforces this.

Invocations

Records each AI model invocation with the response and payload.

export const invocations = pgTable(
	"Invocations",
	{
		id: text("id").primaryKey(),
		modelId: text("modelId")
			.notNull()
			.references(() => models.id, {
				onDelete: "restrict",
				onUpdate: "cascade",
			}),
		response: text("response").notNull(),
		responsePayload: jsonb("responsePayload"),
		createdAt: timestamp("createdAt").defaultNow().notNull(),
		updatedAt: timestamp("updatedAt").defaultNow().notNull(),
	},
	(table) => ({
		modelIdx: index("Invocations_modelId_idx").on(table.modelId),
	}),
);

Key Fields:

id: UUID generated via randomUUID()
modelId: Foreign key to Models table
response: Raw AI response text
responsePayload: JSONB containing full AI SDK response structure

ToolCalls

Tracks tool invocations made by the AI during trading decisions.

export const toolCalls = pgTable(
	"ToolCalls",
	{
		id: text("id").primaryKey(),
		invocationId: text("invocationId")
			.notNull()
			.references(() => invocations.id, {
				onDelete: "restrict",
				onUpdate: "cascade",
			}),
		toolCallType: toolCallTypeEnum("toolCallType").notNull(),
		metadata: text("metadata").notNull(),
		createdAt: timestamp("createdAt").defaultNow().notNull(),
		updatedAt: timestamp("updatedAt").defaultNow().notNull(),
	},
);

Tool Call Types:

export const toolCallTypeEnum = pgEnum("ToolCallType", [
	"CREATE_POSITION",
	"CLOSE_POSITION",
	"HOLDING",
]);

CREATE_POSITION: AI opened a new trade
CLOSE_POSITION: AI closed an existing position
HOLDING: AI decided to hold (no action)

PortfolioSize

Time-series data tracking portfolio net asset value (NAV) over time.

export const portfolioSize = pgTable(
	"PortfolioSize",
	{
		id: text("id").primaryKey(),
		modelId: text("modelId")
			.notNull()
			.references(() => models.id, {
				onDelete: "restrict",
				onUpdate: "cascade",
			}),
		netPortfolio: numeric("netPortfolio", { precision: 18, scale: 2 }).notNull(),
		createdAt: timestamp("createdAt").defaultNow().notNull(),
		updatedAt: timestamp("updatedAt").defaultNow().notNull(),
	},
	(table) => ({
		modelIdx: index("PortfolioSize_modelId_idx").on(table.modelId),
		modelCreatedAtIdx: index("PortfolioSize_modelId_createdAt_idx").on(table.modelId, table.createdAt),
		createdAtIdx: index("PortfolioSize_createdAt_idx").on(table.createdAt),
	}),
);

Critical Rules:

Store as NUMERIC in DB for precision
Use TEXT in application code, cast to NUMERIC for calculations
Multiple indexes support efficient time-range queries and aggregations

Orders (Single Source of Truth)

The Orders table is the single source of truth for all positions and trades.

/**
 * Orders table - single source of truth for positions
 *
 * OPEN orders = active positions (shown in Positions tab)
 * CLOSED orders = completed trades (shown in Trades tab)
 *
 * Unrealized P&L is calculated live from current prices, not stored.
 * When an order is closed, exitPrice and realizedPnl are populated.
 */
export const orders = pgTable(
	"Orders",
	{
		id: text("id").primaryKey().$defaultFn(() => crypto.randomUUID()),
		modelId: text("modelId")
			.notNull()
			.references(() => models.id, {
				onDelete: "restrict",
				onUpdate: "cascade",
			}),
		// Position details
		symbol: text("symbol").notNull(),
		side: orderSideEnum("side").notNull(),
		quantity: numeric("quantity", { precision: 18, scale: 8 }).notNull(),
		leverage: numeric("leverage", { precision: 10, scale: 2 }),
		// Entry details
		entryPrice: numeric("entryPrice", { precision: 18, scale: 8 }).notNull(),
		// Exit plan (stop-loss, take-profit, confidence in the plan)
		exitPlan: jsonb("exitPlan").$type<{
			stop: number | null;
			target: number | null;
			invalidation: string | null;
			invalidationPrice: number | null;
			confidence: number | null;
			timeExit: string | null;
			cooldownUntil: string | null;
		}>(),
		// Status: OPEN = active position, CLOSED = completed trade
		status: orderStatusEnum("status").notNull().default("OPEN"),
		// Exit details (populated when closed)
		exitPrice: numeric("exitPrice", { precision: 18, scale: 8 }),
		realizedPnl: numeric("realizedPnl", { precision: 18, scale: 2 }),
		// Auto-close trigger (null = manual close, "STOP" or "TARGET" = auto)
		closeTrigger: text("closeTrigger"),
		// Lighter exchange order indices for real SL/TP orders
		slOrderIndex: text("slOrderIndex"),
		tpOrderIndex: text("tpOrderIndex"),
		// Trigger prices for SL/TP orders (stored for reference)
		slTriggerPrice: numeric("slTriggerPrice", { precision: 18, scale: 8 }),
		tpTriggerPrice: numeric("tpTriggerPrice", { precision: 18, scale: 8 }),
		// Timestamps
		openedAt: timestamp("openedAt").defaultNow().notNull(),
		closedAt: timestamp("closedAt"),
		updatedAt: timestamp("updatedAt").defaultNow().notNull(),
	},
	(table) => ({
		modelIdx: index("Orders_modelId_idx").on(table.modelId),
		statusIdx: index("Orders_status_idx").on(table.status),
		modelStatusIdx: index("Orders_modelId_status_idx").on(table.modelId, table.status),
		symbolIdx: index("Orders_symbol_idx").on(table.symbol),
	}),
);

Status Enum:

export const orderStatusEnum = pgEnum("OrderStatus", ["OPEN", "CLOSED"]);
export const orderSideEnum = pgEnum("OrderSide", ["LONG", "SHORT"]);

Key Concepts:

OPEN vs CLOSED:
- OPEN: Active position (appears in Positions tab)
- CLOSED: Completed trade (appears in Trades tab)
Derived Values (NOT stored):
- entryNotional = quantity * entryPrice
- exitNotional = quantity * exitPrice
- Unrealized P&L calculated live from current market price

Exit Plan Structure:

{
  stop: 45000,              // Stop-loss price
  target: 52000,            // Take-profit price
  invalidation: "...",      // AI reasoning for invalidation
  invalidationPrice: 44000, // Price that invalidates thesis
  confidence: 0.75,         // AI confidence in this plan (0-1)
  timeExit: "2024-12-31",   // Time-based exit date
  cooldownUntil: "...",     // Cooldown period for re-entry
}

Real SL/TP Orders:
- slOrderIndex / tpOrderIndex: Exchange order IDs for real stop-loss/take-profit
- slTriggerPrice / tpTriggerPrice: Actual trigger prices set on exchange
- Used for live trading mode (not simulator)

Enums

Variant Enum

Trading strategy variants (derived from SSOT in @/core/shared/variants):

export const variantEnum = pgEnum("Variant", VARIANT_IDS);
// ["Apex", "Trendsurfer", "Contrarian", "Sovereign"]

Relations

Drizzle ORM relations enable easy joins:

export const modelRelations = relations(models, ({ many }) => ({
	invocations: many(invocations),
	portfolioSnapshots: many(portfolioSize),
	orders: many(orders),
}));

export const invocationRelations = relations(invocations, ({ one, many }) => ({
	model: one(models, {
		fields: [invocations.modelId],
		references: [models.id],
	}),
	toolCalls: many(toolCalls),
}));

export const orderRelations = relations(orders, ({ one }) => ({
	model: one(models, {
		fields: [orders.modelId],
		references: [models.id],
	}),
}));

Type Exports

Generate TypeScript types from schema:

export type Model = typeof models.$inferSelect;
export type Invocation = typeof invocations.$inferSelect;
export type ToolCall = typeof toolCalls.$inferSelect;
export type PortfolioSnapshot = typeof portfolioSize.$inferSelect;
export type Order = typeof orders.$inferSelect;
export type NewOrder = typeof orders.$inferInsert;

Critical Schema Rules

1. Quoted Identifiers

PostgreSQL requires quoting for capitalized identifiers:

// ✅ Correct
await db.select().from(models);
// Generates: SELECT * FROM "Models"

// ❌ Incorrect (would look for lowercase "models")
SELECT * FROM models;

2. TEXT for Money

Store money as TEXT in application code, cast to NUMERIC for DB operations:

// ✅ Store as TEXT
const netPortfolio = "10234.56";

// ✅ Cast to NUMERIC for math
await db.execute(
	sql`SELECT CAST("netPortfolio" AS NUMERIC) * 1.05 FROM "PortfolioSize"`
);

Why? JavaScript number precision issues. TEXT preserves exact decimal values.

3. UUID Generation

Use crypto.randomUUID() consistently:

// ✅ Correct
id: text("id").primaryKey().$defaultFn(() => crypto.randomUUID())

// ❌ Don't use database-side uuid generation
id: uuid("id").defaultRandom()

4. Timestamps

All tables with user data have createdAt and updatedAt:

createdAt: timestamp("createdAt").defaultNow().notNull(),
updatedAt: timestamp("updatedAt").defaultNow().notNull(),

Manually set updatedAt in update queries:

await db.update(orders)
	.set({ 
		exitPlan: newPlan,
		updatedAt: new Date() // ← Manual update
	})
	.where(eq(orders.id, orderId));

Next Steps

Migration Workflow - Learn how to generate and apply schema changes
Repositories - Explore data access patterns and helpers

Architecture

Database

Configuration

Development

Overview

Tables

Models

Invocations

ToolCalls

PortfolioSize

Orders (Single Source of Truth)

Enums

Variant Enum

Relations

Type Exports

Critical Schema Rules

1. Quoted Identifiers

2. TEXT for Money

3. UUID Generation

4. Timestamps

Next Steps

​Overview

​Tables

​Models

​Invocations

​ToolCalls

​PortfolioSize

​Orders (Single Source of Truth)

​Enums

​Variant Enum

​Relations

​Type Exports

​Critical Schema Rules

​1. Quoted Identifiers

​2. TEXT for Money

​3. UUID Generation

​4. Timestamps

​Next Steps

Overview

Tables

Models

Invocations

ToolCalls

PortfolioSize

Orders (Single Source of Truth)

Enums

Variant Enum

Relations

Type Exports

Critical Schema Rules

1. Quoted Identifiers

2. TEXT for Money

3. UUID Generation

4. Timestamps

Next Steps