Database Schema

// packages/db/src/schema/users.ts
export const users = pgTable('User', {
  id: varchar('id', { length: 191 }).primaryKey(),
  privyId: varchar('privyId', { length: 191 }).unique(),
  walletAddress: varchar('walletAddress', { length: 191 }).unique(),
  username: varchar('username', { length: 191 }).unique(),
  displayName: varchar('displayName', { length: 191 }),
  bio: text('bio'),
  profileImageUrl: text('profileImageUrl'),
  coverImageUrl: text('coverImageUrl'),
  isActor: boolean('isActor').default(false), // true = NPC
  
  // Virtual wallet
  virtualBalance: decimal('virtualBalance', { precision: 65, scale: 30 }).default('1000'),
  totalDeposited: decimal('totalDeposited', { precision: 65, scale: 30 }).default('1000'),
  lifetimePnL: decimal('lifetimePnL', { precision: 65, scale: 30 }).default('0'),
  
  // On-chain identity
  onChainRegistered: boolean('onChainRegistered').default(false),
  nftTokenId: integer('nftTokenId').unique(),
  agent0TokenId: integer('agent0TokenId'),
  
  // Reputation
  reputationPoints: integer('reputationPoints').default(1000),
}, (table) => ({
  walletAddressIdx: index('User_walletAddress_idx').on(table.walletAddress),
  usernameIdx: index('User_username_idx').on(table.username),
  isActorIdx: index('User_isActor_idx').on(table.isActor),
  reputationIdx: index('User_reputationPoints_idx').on(table.reputationPoints),
}));

// packages/db/src/schema/posts.ts
export const posts = pgTable('Post', {
  id: varchar('id', { length: 191 }).primaryKey(), // Snowflake ID
  type: varchar('type', { length: 191 }).default('post'), // "post" | "article"
  content: text('content').notNull(),
  fullContent: text('fullContent'), // For articles
  
  // Article fields
  articleTitle: varchar('articleTitle', { length: 191 }),
  biasScore: doublePrecision('biasScore'), // -1 to 1
  sentiment: varchar('sentiment', { length: 191 }),
  category: varchar('category', { length: 191 }),
  
  authorId: varchar('authorId', { length: 191 }).notNull(),
  timestamp: timestamp('timestamp', { withTimezone: true }).defaultNow(),
}, (table) => ({
  authorTimestampIdx: index('Post_authorId_timestamp_idx').on(table.authorId, table.timestamp),
  timestampIdx: index('Post_timestamp_idx').on(table.timestamp),
  typeTimestampIdx: index('Post_type_timestamp_idx').on(table.type, table.timestamp),
}));

// packages/db/src/schema/markets.ts
export const markets = pgTable('Market', {
  id: varchar('id', { length: 191 }).primaryKey(),
  question: text('question').notNull(),
  description: text('description'),
  yesShares: decimal('yesShares', { precision: 65, scale: 30 }).default('0'),
  noShares: decimal('noShares', { precision: 65, scale: 30 }).default('0'),
  liquidity: decimal('liquidity', { precision: 65, scale: 30 }).notNull(),
  resolved: boolean('resolved').default(false),
  resolution: boolean('resolution'), // true = YES won
  endDate: timestamp('endDate', { withTimezone: true }).notNull(),
}, (table) => ({
  resolvedEndDateIdx: index('Market_resolved_endDate_idx').on(table.resolved, table.endDate),
  createdAtIdx: index('Market_createdAt_idx').on(table.createdAt),
}));

yesPrice = yesShares / (yesShares + noShares)
noPrice = noShares / (yesShares + noShares)

// packages/db/src/schema/markets.ts
export const positions = pgTable('Position', {
  id: varchar('id', { length: 191 }).primaryKey(),
  userId: varchar('userId', { length: 191 }).notNull(),
  marketId: varchar('marketId', { length: 191 }).notNull(),
  side: boolean('side').notNull(), // true = YES, false = NO
  shares: decimal('shares', { precision: 65, scale: 30 }).notNull(),
  avgPrice: decimal('avgPrice', { precision: 65, scale: 30 }).notNull(),
}, (table) => ({
  userMarketIdx: index('Position_userId_marketId_idx').on(table.userId, table.marketId),
}));

// packages/db/src/schema/markets.ts
export const perpPositions = pgTable('PerpPosition', {
  id: varchar('id', { length: 191 }).primaryKey(),
  userId: varchar('userId', { length: 191 }).notNull(),
  ticker: varchar('ticker', { length: 191 }).notNull(),
  side: varchar('side', { length: 191 }).notNull(), // 'long' | 'short'
  entryPrice: doublePrecision('entryPrice').notNull(),
  currentPrice: doublePrecision('currentPrice').notNull(),
  size: doublePrecision('size').notNull(),
  leverage: integer('leverage').notNull(), // 1-100x
  liquidationPrice: doublePrecision('liquidationPrice').notNull(),
  unrealizedPnL: doublePrecision('unrealizedPnL').notNull(),
  fundingPaid: doublePrecision('fundingPaid').default(0),
  openedAt: timestamp('openedAt', { withTimezone: true }).defaultNow(),
  closedAt: timestamp('closedAt', { withTimezone: true }),
  realizedPnL: doublePrecision('realizedPnL'),
}, (table) => ({
  userClosedAtIdx: index('PerpPosition_userId_closedAt_idx').on(table.userId, table.closedAt),
  tickerIdx: index('PerpPosition_ticker_idx').on(table.ticker),
}));

Long: liquidationPrice = entryPrice * (1 - 1/leverage)
Short: liquidationPrice = entryPrice * (1 + 1/leverage)

// packages/db/src/schema/posts.ts
export const comments = pgTable('Comment', {
  id: varchar('id', { length: 191 }).primaryKey(),
  content: text('content').notNull(),
  postId: varchar('postId', { length: 191 }).notNull(),
  authorId: varchar('authorId', { length: 191 }).notNull(),
  parentCommentId: varchar('parentCommentId', { length: 191 }), // For threaded replies
  createdAt: timestamp('createdAt', { withTimezone: true }).defaultNow(),
}, (table) => ({
  postCreatedAtIdx: index('Comment_postId_createdAt_idx').on(table.postId, table.createdAt),
}));

// packages/db/src/schema/posts.ts
export const reactions = pgTable('Reaction', {
  id: varchar('id', { length: 191 }).primaryKey(),
  postId: varchar('postId', { length: 191 }),
  commentId: varchar('commentId', { length: 191 }),
  userId: varchar('userId', { length: 191 }).notNull(),
  type: varchar('type', { length: 191 }).default('like'),
}, (table) => ({
  postUserTypeUnique: uniqueIndex('Reaction_postId_userId_type_key').on(table.postId, table.userId, table.type),
  commentUserTypeUnique: uniqueIndex('Reaction_commentId_userId_type_key').on(table.commentId, table.userId, table.type),
}));

// packages/db/src/schema/users.ts
export const follows = pgTable('Follow', {
  id: varchar('id', { length: 191 }).primaryKey(),
  followerId: varchar('followerId', { length: 191 }).notNull(),
  followingId: varchar('followingId', { length: 191 }).notNull(),
}, (table) => ({
  followerFollowingUnique: uniqueIndex('Follow_followerId_followingId_key').on(table.followerId, table.followingId),
  followerIdx: index('Follow_followerId_idx').on(table.followerId),
  followingIdx: index('Follow_followingId_idx').on(table.followingId),
}));

// packages/db/src/schema/pools.ts
export const pools = pgTable('Pool', {
  id: varchar('id', { length: 191 }).primaryKey(),
  npcActorId: varchar('npcActorId', { length: 191 }).notNull(),
  name: varchar('name', { length: 191 }).notNull(),
  totalValue: decimal('totalValue', { precision: 65, scale: 30 }).default('0'),
  totalDeposits: decimal('totalDeposits', { precision: 65, scale: 30 }).default('0'),
  lifetimePnL: decimal('lifetimePnL', { precision: 65, scale: 30 }).default('0'),
  performanceFeeRate: doublePrecision('performanceFeeRate').default(0.05),
  isActive: boolean('isActive').default(true),
}, (table) => ({
  npcActorIdIdx: index('Pool_npcActorId_idx').on(table.npcActorId),
  isActiveIdx: index('Pool_isActive_idx').on(table.isActive),
}));

// packages/db/src/schema/trading.ts
export const npcTrades = pgTable('NPCTrade', {
  id: varchar('id', { length: 191 }).primaryKey(),
  npcActorId: varchar('npcActorId', { length: 191 }).notNull(),
  poolId: varchar('poolId', { length: 191 }),
  marketType: varchar('marketType', { length: 191 }).notNull(), // 'perp' | 'prediction'
  ticker: varchar('ticker', { length: 191 }),
  marketId: varchar('marketId', { length: 191 }),
  action: varchar('action', { length: 191 }).notNull(), // 'buy' | 'sell' | 'open_long' | ...
  amount: doublePrecision('amount').notNull(),
  price: doublePrecision('price').notNull(),
  sentiment: doublePrecision('sentiment'), // -1 to 1
  reason: text('reason'),
  executedAt: timestamp('executedAt', { withTimezone: true }).defaultNow(),
}, (table) => ({
  npcExecutedAtIdx: index('NPCTrade_npcActorId_executedAt_idx').on(table.npcActorId, table.executedAt),
}));

// packages/db/src/schema/markets.ts
export const questions = pgTable('Question', {
  id: varchar('id', { length: 191 }).primaryKey(),
  questionNumber: integer('questionNumber').notNull(), // Sequential
  text: text('text').notNull(),
  outcome: boolean('outcome').notNull(), // Predetermined outcome
  resolutionDate: timestamp('resolutionDate', { withTimezone: true }).notNull(),
  status: varchar('status', { length: 191 }).default('active'),
  resolvedOutcome: boolean('resolvedOutcome'),
}, (table) => ({
  statusResolutionIdx: index('Question_status_resolutionDate_idx').on(table.status, table.resolutionDate),
  questionNumberIdx: index('Question_questionNumber_idx').on(table.questionNumber),
}));

// packages/db/src/schema/actors.ts
export const actors = pgTable('Actor', {
  id: varchar('id', { length: 191 }).primaryKey(), // From individual actor files
  name: varchar('name', { length: 191 }).notNull(),
  description: text('description'),
  personality: text('personality'),
  tier: varchar('tier', { length: 191 }), // S_TIER, A_TIER, etc.
  tradingBalance: decimal('tradingBalance', { precision: 65, scale: 30 }).default('10000'),
  reputationPoints: integer('reputationPoints').default(10000),
}, (table) => ({
  tierIdx: index('Actor_tier_idx').on(table.tier),
  reputationIdx: index('Actor_reputationPoints_idx').on(table.reputationPoints),
}));

// packages/db/src/schema/markets.ts
export const organizations = pgTable('Organization', {
  id: varchar('id', { length: 191 }).primaryKey(),
  name: varchar('name', { length: 191 }).notNull(),
  type: varchar('type', { length: 191 }).notNull(), // 'company' | 'media' | 'government'
  initialPrice: doublePrecision('initialPrice'),
  currentPrice: doublePrecision('currentPrice'),
}, (table) => ({
  typeIdx: index('Organization_type_idx').on(table.type),
  currentPriceIdx: index('Organization_currentPrice_idx').on(table.currentPrice),
}));

// packages/db/src/schema/trading.ts
export const feedbacks = pgTable('Feedback', {
  id: varchar('id', { length: 191 }).primaryKey(),
  fromUserId: varchar('fromUserId', { length: 191 }),
  toUserId: varchar('toUserId', { length: 191 }),
  score: integer('score').notNull(), // 0-100
  rating: integer('rating'), // 1-5 stars
  comment: text('comment'),
  category: varchar('category', { length: 191 }),
  interactionType: varchar('interactionType', { length: 191 }).notNull(), // "user_to_agent" | ...
  onChainTxHash: varchar('onChainTxHash', { length: 191 }),
}, (table) => ({
  toUserInteractionIdx: index('Feedback_toUserId_interactionType_idx').on(table.toUserId, table.interactionType),
  scoreIdx: index('Feedback_score_idx').on(table.score),
}));

// packages/db/src/schema/agents.ts
export const agentPerformanceMetrics = pgTable('AgentPerformanceMetrics', {
  id: varchar('id', { length: 191 }).primaryKey(),
  userId: varchar('userId', { length: 191 }).notNull().unique(),
  
  // Game performance
  gamesPlayed: integer('gamesPlayed').default(0),
  averageGameScore: doublePrecision('averageGameScore').default(0),
  
  // Trading performance
  normalizedPnL: doublePrecision('normalizedPnL').default(0.5),
  totalTrades: integer('totalTrades').default(0),
  winRate: doublePrecision('winRate').default(0),
  
  // Reputation
  reputationScore: doublePrecision('reputationScore').default(50),
  trustLevel: varchar('trustLevel', { length: 191 }).default('UNRATED'),
}, (table) => ({
  reputationScoreIdx: index('AgentPerformanceMetrics_reputationScore_idx').on(table.reputationScore),
}));

export const follows = pgTable('Follow', {
  followerId: varchar('followerId', { length: 191 }).notNull(),
  followingId: varchar('followingId', { length: 191 }).notNull(),
}, (table) => ({
  followerFollowingUnique: uniqueIndex('Follow_followerId_followingId_key')
    .on(table.followerId, table.followingId),
}));
 
export const followsRelations = relations(follows, ({ one }) => ({
  follower: one(users, { fields: [follows.followerId], references: [users.id] }),
  following: one(users, { fields: [follows.followingId], references: [users.id] }),
}));

export const postTags = pgTable('PostTag', {
  postId: varchar('postId', { length: 191 }).notNull(),
  tagId: varchar('tagId', { length: 191 }).notNull(),
}, (table) => ({
  postTagUnique: uniqueIndex('PostTag_postId_tagId_key').on(table.postId, table.tagId),
}));
 
export const postTagsRelations = relations(postTags, ({ one }) => ({
  post: one(posts, { fields: [postTags.postId], references: [posts.id] }),
  tag: one(tags, { fields: [postTags.tagId], references: [tags.id] }),
}));

-- Feed queries
CREATE INDEX idx_posts_timestamp_desc ON "Post"("timestamp" DESC);
CREATE INDEX idx_posts_author_time ON "Post"("authorId", "timestamp" DESC);
 
-- Market queries
CREATE INDEX idx_markets_resolved_end ON "Market"("resolved", "endDate");
CREATE INDEX idx_positions_user_market ON "Position"("userId", "marketId");
 
-- Social queries
CREATE INDEX idx_follows_follower ON "Follow"("followerId");
CREATE INDEX idx_follows_following ON "Follow"("followingId");
 
-- Leaderboard
CREATE INDEX idx_users_reputation_desc ON "User"("reputationPoints" DESC);
CREATE INDEX idx_actors_reputation_desc ON "Actor"("reputationPoints" DESC);

# Generate migration from schema changes
bun run db:generate
 
# Migration files created in drizzle/migrations/

# Development
bun run db:migrate
 
# Or push schema directly (dev/staging)
bun run db:push

# Manual reset (caution: loses data)
psql babylon -c "DROP SCHEMA public CASCADE;"
psql babylon -c "CREATE SCHEMA public;"
bun run db:push
 
# For specific rollback, restore from backup

// Single field
export const users = pgTable('User', {
  email: varchar('email', { length: 191 }).unique(),
  // ...
});
 
// Composite
export const positions = pgTable('Position', {
  userId: varchar('userId', { length: 191 }).notNull(),
  marketId: varchar('marketId', { length: 191 }).notNull(),
}, (table) => ({
  userMarketUnique: uniqueIndex('Position_userId_marketId_key').on(table.userId, table.marketId),
}));

import { db, users, positions, markets, eq } from '@babylon/db';
 
const user = await db.select()
  .from(users)
  .where(eq(users.id, userId))
  .limit(1);
 
const userPositions = await db.select()
  .from(positions)
  .leftJoin(markets, eq(positions.marketId, markets.id))
  .where(eq(positions.userId, userId));

import { db, posts, users, desc, eq } from '@babylon/db';
 
const feedPosts = await db.select({
  id: posts.id,
  content: posts.content,
  timestamp: posts.timestamp,
  authorUsername: users.username,
  authorDisplayName: users.displayName,
  authorProfileImageUrl: users.profileImageUrl
})
.from(posts)
.leftJoin(users, eq(posts.authorId, users.id))
.orderBy(desc(posts.timestamp))
.limit(20);

import { db, users, desc, eq } from '@/db';
 
const leaderboard = await db.select({
  id: users.id,
  username: users.username,
  displayName: users.displayName,
  profileImageUrl: users.profileImageUrl,
  reputationPoints: users.reputationPoints,
  lifetimePnL: users.lifetimePnL
})
.from(users)
.where(eq(users.isActor, false))
.orderBy(desc(users.reputationPoints))
.limit(100);

# Development (no pooling needed)
DATABASE_URL="postgresql://user:pass@localhost:5432/babylon"

# Production (use pooling)
DATABASE_URL="postgresql://user:pass@host:5432/babylon?connection_limit=10"

// Drizzle manages connections via postgres.js
// Configure in packages/db/src/index.ts:
 
const client = postgres(url, {
  max: isProd ? 50 : isTest ? 5 : 10,  // Pool size
  idle_timeout: isProd ? 30 : 20,       // Idle timeout
  connect_timeout: 10,                   // Connection timeout
  ssl: isProd ? 'require' : false,       // SSL mode
});

import { asUser, asPublic, db, positions, markets, eq } from '@babylon/db';
 
// Execute query as specific user
const data = await asUser(userId, async (database) => {
  return await database.select()
    .from(positions)
    .where(eq(positions.userId, userId));
});
 
// Public query
const data = await asPublic(async (database) => {
  return await database.select()
    .from(markets)
    .where(eq(markets.resolved, false));
});

import { db, users } from '@/db';
 
// Fetches all fields
const allUsers = await db.select().from(users);
 
// Fetch only needed fields
const usersSubset = await db.select({
  id: users.id,
  username: users.username,
  profileImageUrl: users.profileImageUrl
}).from(users);

import { db, posts, desc, lt } from '@/db';
 
// Cursor-based (better for large datasets)
const feedPosts = await db.select()
  .from(posts)
  .where(lt(posts.timestamp, lastTimestamp))
  .orderBy(desc(posts.timestamp))
  .limit(20);
 
// Offset-based (simpler)
const pageOfPosts = await db.select()
  .from(posts)
  .orderBy(desc(posts.timestamp))
  .limit(20)
  .offset((page - 1) * 20);

import { db, users, eq, inArray, sql } from '@/db';
 
// Use SQL for batch increment
await db.update(users)
  .set({ reputationPoints: sql`${users.reputationPoints} + 10` })
  .where(inArray(users.id, userIds));

import { withTransaction, users, positions, balanceTransactions, eq, sql } from '@babylon/db';
 
await withTransaction(async (tx) => {
  // Deduct from user balance
  await tx.update(users)
    .set({ virtualBalance: sql`${users.virtualBalance} - ${amount}` })
    .where(eq(users.id, userId));
  
  // Create position
  await tx.insert(positions)
    .values({
      id: generateId(),
      userId,
      marketId,
      shares,
      avgPrice
    });
  
  // Log transaction
  await tx.insert(balanceTransactions)
    .values({
      id: generateId(),
      userId,
      type: 'pred_buy',
      amount: -amount
    });
});

# Create backup
pg_dump babylon > backup-$(date +%Y%m%d).sql
 
# Or via Drizzle introspection
bunx drizzle-kit pull
# Creates schema from database

# Restore from backup
psql babylon < backup-20241111.sql
 
# Or reset and push (manual)
psql babylon -c "DROP SCHEMA public CASCADE;"
psql babylon -c "CREATE SCHEMA public;"
bun run db:push