How to Add a New Table That Needs to Be Synced with PowerSync in Thunderbolt

To add a new PowerSync-synced table in Thunderbolt, you must define the SQLite schema in the frontend, mirror it in the PostgreSQL backend, register it in the shared table registry, expose it to the PowerSync driver, update the sync rules, and create migrations—always merging backend changes before frontend changes to prevent silent sync failures.

Thunderbolt uses PowerSync to maintain bidirectional synchronization between the backend PostgreSQL database and the frontend SQLite store. When you need to add a new table that needs to be synced with PowerSync, the process involves coordinated changes across seven distinct areas of the codebase to ensure data consistency, proper user scoping, and React-Query integration.

Step 1: Define the Frontend SQLite Table Schema

In src/db/tables.ts, define the local SQLite table using Drizzle's sqliteTable helper. Every PowerSync-synced table must include a user_id column to enforce row-level security and a deleted_at column if you plan to support soft deletes.

// src/db/tables.ts
export const myNewTable = sqliteTable('my_new_table', {
  id: text('id').primaryKey(),
  title: text('title'),
  content: text('content', { mode: 'json' }).$type<string>(),
  createdAt: text('created_at').default(sql`(datetime('now'))`),
  updatedAt: text('updated_at').default(sql`(datetime('now'))`),
  userId: text('user_id'),               // required for PowerSync
  deletedAt: text('deleted_at'),         // optional soft-delete flag
})

Step 2: Create the Backend PostgreSQL Mirror

In backend/src/db/powersync-schema.ts, create the PostgreSQL equivalent using the powersyncSchema helper. The backend schema must mirror the frontend columns exactly and include a composite primary key on (id, user_id) plus an index on user_id for performance.

// backend/src/db/powersync-schema.ts
export const myNewTable = powersyncSchema.table('my_new_table', {
  id: text('id').notNull(),
  title: text('title'),
  content: text('content'),
  createdAt: timestamp('created_at').defaultNow(),
  updatedAt: timestamp('updated_at').defaultNow(),
  userId: text('user_id')
    .notNull()
    .references(() => user.id, { onDelete: 'cascade' }),
}, (t) => [
  primaryKey({ columns: [t.id, t.userId] }),
  index('idx_my_new_table_user_id').on(t.userId)
])

Step 3: Register the Table in the Shared Registry

In shared/powersync-tables.ts, add the table name to the central registry. This file serves as the single source of truth for table names, React-Query invalidation keys, and type-checking across the monorepo.

// shared/powersync-tables.ts
export const powersyncTableNames = [
  /* existing names … */
  'devices',
  'my_new_table',          // ← new entry
] as const

export const powersyncTableToQueryKeys = {
  /* … existing mappings … */
  my_new_table: [['myNewTable']],
} as const

Step 4: Expose the Table to the PowerSync Driver

In src/db/powersync/schema.ts, expose the table to the PowerSync driver by adding it to the drizzleSchema object. This satisfies TypeScript type checking against the PowerSyncTableName union.

// src/db/powersync/schema.ts
export const drizzleSchema = {
  /* … existing tables … */
  my_new_table: tables.myNewTable,
} satisfies Record<PowerSyncTableName, unknown>

Step 5: Update PowerSync Sync Rules

In powersync-service/config/config.yaml, add a SELECT statement that defines the bucket for the new table. This tells PowerSync which rows to replicate for each user based on the user_id column.

# powersync-service/config/config.yaml

- SELECT * FROM powersync.my_new_table WHERE my_new_table.user_id = bucket.user_id

Step 6: Generate Drizzle Migrations

Generate migration files for both environments to persist the schema changes.

For the frontend, run the Drizzle generation command:

bun db generate

For the backend, create a new migration file under backend/migrations/ that creates the PostgreSQL table with the same columns and indexes defined in Step 2.

Step 7: Follow the Required PR Flow

To prevent silent sync failures, you must split the changes into two separate pull requests and merge them in a specific order. This workflow is documented in docs/powersync-account-devices.md under "PR flow for adding tables".

PR 1 – Backend (Merge First):

• Add the table definition and migration from Step 2.
• Update shared/powersync-tables.ts and config.yaml from Steps 3 and 5.
• After deployment, update the PowerSync Cloud dashboard with the new sync rule.

PR 2 – Frontend (Merge Second):

• Add the SQLite table from Step 1.
• Update drizzleSchema from Step 4.
• Add any UI or feature code that consumes the table.

Summary

• Seven areas require updates when you add a new table that needs to be synced with PowerSync: frontend schema, backend schema, shared registry, PowerSync driver schema, sync rules, migrations, and PR workflow.
• Always merge backend changes first to prevent silent sync failures caused by mismatched sync rules and table schemas.
• Include user_id in every synced table to enforce row-level security and proper bucket assignment in PowerSync.
• Register tables in shared/powersync-tables.ts to enable React-Query invalidation and maintain type safety across the stack.

Frequently Asked Questions

What happens if I merge the frontend PR before the backend PR?

Merging the frontend first creates a mismatch between the PowerSync sync rules—which reference tables that may not yet exist in the backend PostgreSQL schema—and the actual database state. This causes silent sync failures where data does not replicate between the client and server, leading to inconsistent user experiences and potential data loss.

Why does every synced table need a user_id column?

PowerSync uses bucket-based replication scoped to individual users. The user_id column acts as the partition key that determines which rows belong to which user's bucket. Without this column, PowerSync cannot route data to the correct client devices, breaking the sync model and potentially exposing data to unauthorized users.

How do I handle soft deletes in PowerSync-synced tables?

Add a deleted_at column to your table schema in both the frontend and backend definitions. Include this column in the PowerSync sync rules so that tombstone rows replicate to clients. Your application logic can then filter out records where deleted_at is not null, maintaining data integrity across the distributed system while allowing for recovery of deleted data.

What is the purpose of shared/powersync-tables.ts?

This file serves as the single source of truth for all PowerSync table names in the Thunderbolt monorepo. It exports a const array of table names for TypeScript type safety and a mapping of table names to React-Query invalidation keys. Centralizing these definitions prevents typos, ensures frontend and backend stay synchronized when table names change, and powers the cache invalidation logic used throughout the application.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}