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

> Learn how to add a new PowerSync synced table in Thunderbolt. Define schema, mirror PostgreSQL, register tables, expose to driver, update rules, and create migrations.

- Repository: [Thunderbird/thunderbolt](https://github.com/thunderbird/thunderbolt)
- Tags: how-to-guide
- Published: 2026-04-19

---

**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`](https://github.com/thunderbird/thunderbolt/blob/main/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.

```typescript
// 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`](https://github.com/thunderbird/thunderbolt/blob/main/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.

```typescript
// 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`](https://github.com/thunderbird/thunderbolt/blob/main/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.

```typescript
// 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`](https://github.com/thunderbird/thunderbolt/blob/main/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.

```typescript
// 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`](https://github.com/thunderbird/thunderbolt/blob/main/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.

```yaml

# 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:

```bash
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`](https://github.com/thunderbird/thunderbolt/blob/main/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`](https://github.com/thunderbird/thunderbolt/blob/main/shared/powersync-tables.ts) and [`config.yaml`](https://github.com/thunderbird/thunderbolt/blob/main/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`](https://github.com/thunderbird/thunderbolt/blob/main/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`](https://github.com/thunderbird/thunderbolt/blob/main/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.