> ## Documentation Index
> Fetch the complete documentation index at: https://twigz.arqqin.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Database Migrations

> Schema migration functions for evolving the database structure and backfilling data on existing records

## Overview

The TWIGZ backend uses the `@convex-dev/migrations` library to define and run database migrations. Migrations handle schema evolution by backfilling new fields on existing records, ensuring all documents conform to the latest schema requirements.

**Location:** `convex/migrations.ts`

<Note>
  Migrations process records one at a time using `migrateOne`. Each migration only modifies records that are missing the target fields, making them safe to run multiple times (idempotent).
</Note>

## Running Migrations

Migrations are executed using the exported `run` function, which is a Convex mutation that can be invoked from the Convex dashboard or programmatically.

<CodeGroup>
  ```typescript TypeScript theme={null}
  import { api } from "../convex/_generated/api";

  // Run all pending migrations
  await convex.mutation(api.migrations.run);
  ```

  ```bash Convex Dashboard theme={null}
  # Migrations can also be triggered from the Convex dashboard
  # Navigate to Functions > migrations > run
  ```
</CodeGroup>

## Migration Functions

### addStatusToStores

Adds a `status` field to existing store documents that are missing it.

| Property          | Value                                           |
| ----------------- | ----------------------------------------------- |
| **Table**         | `stores`                                        |
| **Field Added**   | `status`                                        |
| **Default Value** | `"pending"`                                     |
| **Condition**     | Only applies to stores without a `status` field |

```typescript theme={null}
export const addStatusToStores = migrations.define({
  table: "stores",
  migrateOne: async (ctx, store) => {
    if (!("status" in store)) {
      return { status: "pending" as const };
    }
  },
});
```

***

### ensureStoreFields

A comprehensive migration that ensures all stores have the required fields. Handles multiple missing fields in a single pass.

| Property           | Value                                                        |
| ------------------ | ------------------------------------------------------------ |
| **Table**          | `stores`                                                     |
| **Fields Added**   | `status`, `phoneNumberVerified`                              |
| **Default Values** | `status: "pending"`, `phoneNumberVerified: false`            |
| **Condition**      | Only updates stores missing one or more of the target fields |

```typescript theme={null}
export const ensureStoreFields = migrations.define({
  table: "stores",
  migrateOne: async (ctx, store) => {
    const updates: any = {};
    if (!("status" in store)) {
      updates.status = "pending" as const;
    }
    if (!("phoneNumberVerified" in store)) {
      updates.phoneNumberVerified = false;
    }
    return Object.keys(updates).length > 0 ? updates : undefined;
  },
});
```

***

### addUsernamesToCustomers

Generates unique usernames for existing customers that do not have one. Derives the username from the customer's name, appending numeric suffixes to resolve conflicts.

| Property          | Value                                                                      |
| ----------------- | -------------------------------------------------------------------------- |
| **Table**         | `customers`                                                                |
| **Field Added**   | `username`                                                                 |
| **Default Value** | Derived from `customer.name` (lowercased, alphanumeric, max 12 chars)      |
| **Condition**     | Only applies to customers without a `username` field or with a falsy value |

**Username generation strategy:**

<Steps>
  <Step title="Base Name">
    Takes the customer's name, converts to lowercase, strips non-alphanumeric characters, and truncates to 12 characters.
  </Step>

  <Step title="Uniqueness Check">
    Queries the `by_username` index to verify availability. If taken, appends an incrementing numeric suffix (e.g., `john1`, `john2`).
  </Step>

  <Step title="Fallback">
    After 999 attempts, falls back to a `user_{timestamp}` format with an optional random suffix for guaranteed uniqueness.
  </Step>
</Steps>

***

### addPrivacyToCustomers

Sets a default privacy setting for existing customers that do not have one.

| Property          | Value                                                                    |
| ----------------- | ------------------------------------------------------------------------ |
| **Table**         | `customers`                                                              |
| **Field Added**   | `isPrivate`                                                              |
| **Default Value** | `true`                                                                   |
| **Condition**     | Only applies to customers without `isPrivate` or where it is `undefined` |

<Warning>
  This migration defaults existing customers to **private** (`isPrivate: true`). New customers created after this field was added to the schema may have a different default depending on the registration flow.
</Warning>

## Migration Summary

| Migration                 | Table       | Fields                          | Default Values       |
| ------------------------- | ----------- | ------------------------------- | -------------------- |
| `addStatusToStores`       | `stores`    | `status`                        | `"pending"`          |
| `ensureStoreFields`       | `stores`    | `status`, `phoneNumberVerified` | `"pending"`, `false` |
| `addUsernamesToCustomers` | `customers` | `username`                      | Derived from name    |
| `addPrivacyToCustomers`   | `customers` | `isPrivate`                     | `true`               |

## Best Practices

<AccordionGroup>
  <Accordion title="When to Create a New Migration">
    Create a new migration whenever you add a required field to the schema that existing documents will not have. This ensures all existing records are backfilled with appropriate default values.
  </Accordion>

  <Accordion title="Idempotency">
    All migrations check whether the target field already exists before modifying a record. This means migrations can safely be re-run without causing duplicate updates or data corruption.
  </Accordion>

  <Accordion title="Running Order">
    The `run` function from `@convex-dev/migrations` handles execution order automatically. Migrations that have already been applied are skipped on subsequent runs.
  </Accordion>
</AccordionGroup>

<Tip>
  Always test migrations in a development environment before running them in production. Use the Convex dashboard to monitor migration progress and verify results.
</Tip>