Plugin Tables Guide

This guide shows plugin developers how to add database tables to their elizaOS plugins. The plugin-sql system automatically handles schema creation, migrations, and namespacing.

Overview

Any elizaOS plugin can define its own database tables by:

Creating table definitions using Drizzle ORM
Exporting a schema property from the plugin
That’s it! Tables are created automatically on startup

Step-by-Step Guide

1. Set Up Your Plugin Structure

packages/my-plugin/
├── src/
│   ├── schema/
│   │   ├── index.ts       # Export all tables
│   │   ├── users.ts       # User table definition
│   │   └── settings.ts    # Settings table definition
│   ├── actions/
│   ├── services/
│   └── index.ts          # Plugin entry point
├── package.json
└── tsconfig.json

2. Define Your Tables

Create table definitions using Drizzle ORM:

// packages/my-plugin/src/schema/users.ts
import { pgTable, uuid, text, timestamp, boolean, jsonb } from 'drizzle-orm/pg-core';
import { sql } from 'drizzle-orm';

export const pluginUsersTable = pgTable('plugin_users', {
  id: uuid('id').primaryKey().defaultRandom(),

  // Basic fields
  username: text('username').notNull().unique(),
  email: text('email').notNull(),
  isActive: boolean('is_active').default(true),

  // JSONB for flexible data
  profile: jsonb('profile')
    .$type<{
      avatar?: string;
      bio?: string;
      preferences?: Record<string, any>;
    }>()
    .default(sql`'{}'::jsonb`),

  // Standard timestamps
  createdAt: timestamp('created_at', { withTimezone: true })
    .default(sql`now()`)
    .notNull(),
  updatedAt: timestamp('updated_at', { withTimezone: true })
    .default(sql`now()`)
    .notNull(),
});

3. Create Schema Index

Export all your tables from a central location:

// packages/my-plugin/src/schema/index.ts
export { pluginUsersTable } from './users';
export { pluginSettingsTable } from './settings';
// Export all other tables...

4. Export Schema from Plugin

The critical step - export your schema from the plugin:

// packages/my-plugin/src/index.ts
import { type Plugin } from '@elizaos/core';
import * as schema from './schema';

export const myPlugin: Plugin = {
  name: '@company/my-plugin',
  description: 'My plugin with custom database tables',

  // This enables automatic migrations!
  schema,

  init: async (config, runtime) => {
    // Plugin initialization
    console.log('Plugin initialized with database tables');
  },

  // Other plugin properties...
  actions: [],
  services: [],
  providers: [],
};

export default myPlugin;

Schema Namespacing

Your plugin’s tables are automatically created in a dedicated PostgreSQL schema:

// Plugin name: @company/my-plugin
// Schema name: company_my_plugin
// Full table name: company_my_plugin.plugin_users

This prevents naming conflicts between plugins.

Working with Foreign Keys

Reference Core Tables

To reference tables from the core plugin:

// Import core schema
import { agentTable } from '@elizaos/plugin-sql/schema';

export const pluginMemoriesTable = pgTable('plugin_memories', {
  id: uuid('id').primaryKey().defaultRandom(),

  // Reference core agent table
  agentId: uuid('agent_id')
    .notNull()
    .references(() => agentTable.id, { onDelete: 'cascade' }),

  content: text('content').notNull(),
  metadata: jsonb('metadata').default(sql`'{}'::jsonb`),
});

Reference Your Own Tables

For relationships within your plugin:

export const pluginPostsTable = pgTable('plugin_posts', {
  id: uuid('id').primaryKey().defaultRandom(),

  // Reference user in same plugin
  authorId: uuid('author_id')
    .notNull()
    .references(() => pluginUsersTable.id, { onDelete: 'cascade' }),

  title: text('title').notNull(),
  content: text('content').notNull(),
});

Cross-Plugin References

To reference tables from other plugins:

// Reference using fully qualified name
userId: uuid('user_id')
  .notNull()
  .references(() => sql`"other_plugin"."users"("id")`),

Table Design Patterns

User Tables

export const pluginUsersTable = pgTable('plugin_users', {
  id: uuid('id').primaryKey().defaultRandom(),
  
  // Link to core agent
  agentId: uuid('agent_id')
    .notNull()
    .references(() => agentTable.id),
  
  // User identification
  externalId: text('external_id').unique(),
  username: text('username').notNull().unique(),
  email: text('email'),
  
  // User state
  status: text('status').default('active'),
  lastSeenAt: timestamp('last_seen_at'),
  
  // Flexible data
  profile: jsonb('profile').default(sql`'{}'::jsonb`),
  settings: jsonb('settings').default(sql`'{}'::jsonb`),
  
  // Timestamps
  createdAt: timestamp('created_at').default(sql`now()`).notNull(),
  updatedAt: timestamp('updated_at').default(sql`now()`).notNull(),
}, (table) => ({
  // Indexes for performance
  agentIdIdx: index('plugin_users_agent_id_idx').on(table.agentId),
  emailIdx: index('plugin_users_email_idx').on(table.email),
}));

Event/Log Tables

export const pluginEventsTable = pgTable('plugin_events', {
  id: uuid('id').primaryKey().defaultRandom(),
  
  // Event classification
  type: text('type').notNull(),
  category: text('category'),
  severity: text('severity').default('info'),
  
  // Event context
  userId: uuid('user_id').references(() => pluginUsersTable.id),
  agentId: uuid('agent_id').references(() => agentTable.id),
  
  // Event data
  data: jsonb('data').notNull(),
  metadata: jsonb('metadata').default(sql`'{}'::jsonb`),
  
  // Timestamp (no updatedAt needed for immutable logs)
  createdAt: timestamp('created_at').default(sql`now()`).notNull(),
}, (table) => ({
  // Indexes for querying
  typeIdx: index('plugin_events_type_idx').on(table.type),
  userIdIdx: index('plugin_events_user_id_idx').on(table.userId),
  createdAtIdx: index('plugin_events_created_at_idx').on(table.createdAt),
}));

Configuration Tables

export const pluginConfigTable = pgTable('plugin_config', {
  id: uuid('id').primaryKey().defaultRandom(),
  
  // Scope the configuration
  agentId: uuid('agent_id')
    .notNull()
    .references(() => agentTable.id, { onDelete: 'cascade' }),
  
  // Configuration identification
  key: text('key').notNull(),
  namespace: text('namespace').default('default'),
  
  // Configuration data
  value: jsonb('value').notNull(),
  description: text('description'),
  
  // Configuration metadata
  isSecret: boolean('is_secret').default(false),
  isActive: boolean('is_active').default(true),
  
  // Timestamps
  createdAt: timestamp('created_at').default(sql`now()`).notNull(),
  updatedAt: timestamp('updated_at').default(sql`now()`).notNull(),
}, (table) => ({
  // Unique constraint for key per agent/namespace
  uniqueKeyPerAgent: unique('plugin_config_agent_namespace_key_unique')
    .on(table.agentId, table.namespace, table.key),
}));

Advanced Features

Composite Primary Keys

export const pluginUserRolesTable = pgTable('plugin_user_roles', {
  userId: uuid('user_id')
    .notNull()
    .references(() => pluginUsersTable.id, { onDelete: 'cascade' }),
  
  roleId: uuid('role_id')
    .notNull()
    .references(() => pluginRolesTable.id, { onDelete: 'cascade' }),
  
  assignedAt: timestamp('assigned_at').default(sql`now()`).notNull(),
  assignedBy: uuid('assigned_by').references(() => pluginUsersTable.id),
}, (table) => ({
  // Composite primary key
  pk: primaryKey({ columns: [table.userId, table.roleId] }),
}));

Check Constraints

export const pluginProductsTable = pgTable('plugin_products', {
  id: uuid('id').primaryKey().defaultRandom(),
  name: text('name').notNull(),
  price: numeric('price', { precision: 10, scale: 2 }).notNull(),
  discountPrice: numeric('discount_price', { precision: 10, scale: 2 }),
}, (table) => ({
  // Ensure discount price is less than regular price
  priceCheck: check(
    'plugin_products_price_check',
    sql`${table.discountPrice} < ${table.price} OR ${table.discountPrice} IS NULL`
  ),
}));

Generated Columns

export const pluginOrdersTable = pgTable('plugin_orders', {
  id: uuid('id').primaryKey().defaultRandom(),
  
  // Regular columns
  subtotal: numeric('subtotal').notNull(),
  tax: numeric('tax').notNull(),
  shipping: numeric('shipping').notNull(),
  
  // Generated column
  total: numeric('total').generatedAlwaysAs(
    sql`${subtotal} + ${tax} + ${shipping}`
  ),
});

Querying Your Tables

Once your tables are created, you can query them using Drizzle:

import { db } from '@elizaos/plugin-sql';
import { pluginUsersTable } from './schema/users';

// In your plugin service or action
export class UserService {
  async createUser(data: any) {
    const [user] = await db
      .insert(pluginUsersTable)
      .values({
        username: data.username,
        email: data.email,
        profile: data.profile,
      })
      .returning();
    
    return user;
  }

  async getUserById(id: string) {
    const [user] = await db
      .select()
      .from(pluginUsersTable)
      .where(eq(pluginUsersTable.id, id));
    
    return user;
  }
}

Best Practices

1. Prefix Table Names

Use a consistent prefix for your plugin’s tables:

// Good
export const pluginUsersTable = pgTable('plugin_users', {...});
export const pluginSettingsTable = pgTable('plugin_settings', {...});

// Avoid generic names
export const usersTable = pgTable('users', {...}); // Too generic

2. Always Include Timestamps

createdAt: timestamp('created_at', { withTimezone: true })
  .default(sql`now()`)
  .notNull(),
updatedAt: timestamp('updated_at', { withTimezone: true })
  .default(sql`now()`)
  .notNull(),

3. Use JSONB Wisely

JSONB is great for flexibility but don’t overuse it:

// Good - structured data with flexibility
profile: jsonb('profile').$type<{
  avatar?: string;
  bio?: string;
  social?: {
    twitter?: string;
    github?: string;
  };
}>(),

// Avoid - everything in JSONB
data: jsonb('data'), // Too vague

4. Index Foreign Keys

Always index columns used in joins:

(table) => ({
  userIdIdx: index('plugin_posts_user_id_idx').on(table.userId),
  createdAtIdx: index('plugin_posts_created_at_idx').on(table.createdAt),
})

5. Handle Cascading Deletes

Be explicit about deletion behavior:

// Cascade delete - removes dependent records
.references(() => userTable.id, { onDelete: 'cascade' })

// Set null - preserves records but clears reference
.references(() => userTable.id, { onDelete: 'set null' })

// Restrict - prevents deletion if dependencies exist
.references(() => userTable.id, { onDelete: 'restrict' })

Troubleshooting

Tables Not Created

Ensure your plugin exports the schema:

export const plugin: Plugin = {
  schema, // Required!
};

Check the logs for migration errors:

[ERROR] Failed to run migrations for plugin @company/my-plugin

Verify table names don’t conflict with PostgreSQL keywords

Foreign Key Errors

Ensure referenced tables exist
Check that data types match exactly
Verify the referenced column has a unique constraint

Performance Issues

Add indexes for frequently queried columns
Use partial indexes for filtered queries
Consider partitioning for large tables

Getting Started

Core Plugins

LLM Providers

Platform Integrations

DeFi Plugins

Overview

Step-by-Step Guide

1. Set Up Your Plugin Structure

2. Define Your Tables

3. Create Schema Index

4. Export Schema from Plugin

Schema Namespacing

Working with Foreign Keys

Reference Core Tables

Reference Your Own Tables

Cross-Plugin References

Table Design Patterns

User Tables

Event/Log Tables

Configuration Tables

Advanced Features

Composite Primary Keys

Check Constraints

Generated Columns

Querying Your Tables

Best Practices

1. Prefix Table Names

2. Always Include Timestamps

3. Use JSONB Wisely

4. Index Foreign Keys

5. Handle Cascading Deletes

Troubleshooting

Tables Not Created

Foreign Key Errors

Performance Issues

Getting Started

Core Plugins

LLM Providers

Platform Integrations

DeFi Plugins

​Overview

​Step-by-Step Guide

​1. Set Up Your Plugin Structure

​2. Define Your Tables

​3. Create Schema Index

​4. Export Schema from Plugin

​Schema Namespacing

​Working with Foreign Keys

​Reference Core Tables

​Reference Your Own Tables

​Cross-Plugin References

​Table Design Patterns

​User Tables

​Event/Log Tables

​Configuration Tables

​Advanced Features

​Composite Primary Keys

​Check Constraints

​Generated Columns

​Querying Your Tables

​Best Practices

​1. Prefix Table Names

​2. Always Include Timestamps

​3. Use JSONB Wisely

​4. Index Foreign Keys

​5. Handle Cascading Deletes

​Troubleshooting

​Tables Not Created

​Foreign Key Errors

​Performance Issues

Overview

Step-by-Step Guide

1. Set Up Your Plugin Structure

2. Define Your Tables

3. Create Schema Index

4. Export Schema from Plugin

Schema Namespacing

Working with Foreign Keys

Reference Core Tables

Reference Your Own Tables

Cross-Plugin References

Table Design Patterns

User Tables

Event/Log Tables

Configuration Tables

Advanced Features

Composite Primary Keys

Check Constraints

Generated Columns

Querying Your Tables

Best Practices

1. Prefix Table Names

2. Always Include Timestamps

3. Use JSONB Wisely

4. Index Foreign Keys

5. Handle Cascading Deletes

Troubleshooting

Tables Not Created

Foreign Key Errors

Performance Issues