# Import Data

Source: https://fragment.dev/guides/import-data

[Documentation index](https://fragment.dev/llms.txt)

<a id="import-data"></a>

Going live with a Ledger in production often requires importing existing data. You can approach this in two ways:

- a historical import of all existing data
- a balance import of starting balances

[Visual: import-data_diagram. View on the HTML page.](https://fragment.dev/guides/import-data)

Use a historical import when:

- migrating from an existing double-entry ledger
- your funds flow code has been stable and you do not make payments manually
- your historical dataset is small

A balance import may be easier when:

- you have a large amount of historical data
- your existing data has inconsistencies or is incomplete
- you make or have made manual payments

A historical import requires you to produce a Ledger Entry for every historical financial event, which means individually dealing with any inconsistencies or missing data. With a balance import, you can handle issues in bulk. You only need to calculate a consistent set of balances at a chosen point in time.

<a id="historical-imports"></a>

## Historical imports

A historical import involves processing all your existing data and posting Ledger Entries for every financial event. The approach:

1. List Ledger Accounts in your Ledger that represent an external system like a bank account or payment system
2. Export all transactions from those systems
3. Create a list of entries to import with the entry type to post, parameters to use, and the historical timestamp of the entry

<a id="balance-imports"></a>

## Balance imports

With a balance import, you pick a go-live date and consolidate existing data before that date into a set of initial balances. The Ledger will only contain entries from that point forward.

The overall process:

1. Start posting entries to an interim Ledger
2. Choose a go-live date
3. Shortly before the go-live date, switch to a production Ledger
4. Calculate and set initial balances
5. Reverse all entries posted before the go-live date

<a id="balance-imports-go-live-timestamp"></a>

### Go-live timestamp

Pick a specific moment in time to anchor your migration. Go-live timestamps are usually midnight before the start of a month or quarter, so that you can align starting balances with existing balance reports. If you are using balance reports from multiple sources like both your bank and internal systems, ensure their timezones match. If they do not, you need to calculate aligned initial balances from transaction level data.

<a id="balance-imports-interim-ledger"></a>

### Interim Ledger

While you develop your funds flows, direct live traffic to a temporary Ledger in your production workspace. This interim Ledger lets you test your Schema and verify that payments reconcile and balances update as expected. Using an interim Ledger reduces the number of entries you need to reverse later.

Close to your go-live date:

1. Create a second Ledger in the same Project as the interim Ledger
2. Switch live traffic to the new Ledger
3. Once initial balances are set in the new Ledger, delete the interim Ledger

<a id="balance-imports-schema-changes"></a>

### Schema changes

To support setting initial balances, add the following to your Ledger:

- An `asset` account called `initial-balance-offset`
- An `income` account called `initial-retained-earnings`
- A runtime entry called `set-initial-balance`

<a id="balance-imports-calculate-initial-balances"></a>

### Calculate initial balances

Calculate a set of starting balances for leaf asset and liability accounts in your Schema at the go-live timestamp, including account template instances. You do not need to calculate starting balances for income and expense accounts since they track deltas in your assets and liability accounts.

Your Ledger will typically have three categories of asset and liability accounts:

- External: Represent your accounts at banking and payment systems. Extract a starting balance from the external system for these.
- Internal: Represent balances held in your platform like user wallets and loan balances. Calculate these from your existing domain models in your database. You may already have reports or queries for these numbers.
- Clearing: Payments that were initiated before the go-live timestamp but settled after it result in non-zero starting balances in clearing accounts. Use payment system APIs or your internal data to get the list of inflight payments. Map these payments to the entries that would have been posted on initiation. Aggregate the lines on these entries to find the set of starting balances for your clearing accounts.

It's useful to automate this process so that you can run it multiple times as you learn more about transforming your data into a balance snapshot. You'll reuse the same code to validate balances once the import process is completed.

Bring together all asset and liability starting balances into a combined data set. To calculate the balance for the `initial-retained-earnings` account:

`retained_earnings = total(asset_accounts) - total(liability_accounts)`

Add a row to your data set to set this balance on this account.

<a id="balance-imports-set-initial-balances"></a>

### Set initial balances

To set an initial balance, post a `set-initial-balance` runtime entry with these lines:

- posting the initial balance to the target account
- posting the initial balance to the `initial-balance-offset` account

Depending on the type of the target account, you may need to flip the sign when posting to the `initial-balance-offset` account:

| Target account type | Offset account amount |
| ------------------- | --------------------- |
| asset               | Flipped amount        |
| liability           | Original amount       |
| income              | Original amount       |
| expense             | Flipped amount        |

Ensure the `posted` timestamp is set to your go-live timestamp.

For unlinked accounts, use `addLedgerEntry` to set initial balances:

```graphql expandable=true title="addLedgerEntry for initial balance"
mutation AddLedgerEntry($ik: SafeString!, $entry: LedgerEntryInput!) {
  addLedgerEntry(
    ik: $ik
    entry: $entry
  ) {
    __typename
    ... on AddLedgerEntryResult {
      entry {
        id
        ik
        posted
      }
    }
  }
}
```

```json expandable=true title="Variables"
{
  "ik": "initial-balance-unique-id",
  "entry": {
    "ledger": { "id": "ledger-id" },
    "type": "set-initial-balance",
    "posted": "2024-01-01T00:00:00.000Z",
    "lines": [
      {
        "account": { "path": "assets/users/user-123" },
        "amount": "10000"
      },
      {
        "account": { "path": "assets/initial-balance-offset" },
        "amount": "-10000"
      }
    ]
  }
}
```

For linked accounts, you need to call `syncCustomTxs` before you can call `reconcileTx` to set the initial balance. Assuming you have fewer than 200 linked accounts in your Schema, you can make a single call to `syncCustomTxs`:

```graphql expandable=true title="syncCustomTxs for linked accounts"
mutation SyncTransactions(
  $link: LinkMatchInput!
  $txs: [CustomTxInput!]!
) {
  syncCustomTxs(link: $link, txs: $txs) {
    __typename
    ... on SyncCustomTxsResult {
      txs {
        id
        externalId
        amount
        date
        description
      }
    }
    ... on Error {
      code
      message
    }
  }
}
```

```json expandable=true title="Variables"
{
  "link": { "id": "link-id" },
  "txs": [
    {
      "id": "initial-balance-tx",
      "accountId": "account-1",
      "amount": "10000",
      "currency": "USD",
      "timestamp": "2024-01-01T00:00:00.000Z"
    }
  ]
}
```

This adds an initial balance transaction to each account.

Then for each linked account, post the `set-initial-balance` runtime entry with `reconcileTx`:

```graphql expandable=true title="reconcileTx for initial balance"
mutation ReconcileTx(
  $entry: LedgerEntryInput!
) {
  reconcileTx(
    entry: $entry
  ) {
    ... on ReconcileTxResult {
      entry {
        type
        created
        posted
      }
      lines {
        amount
        key
        description
        account {
          path
        }
      }
    }
    ... on Error {
      code
      message
    }
  }
}
```

```json expandable=true title="Variables"
{
  "entry": {
    "type": "set-initial-balance",
    "ledger": {
      "id": "ledger-id"
    },
    "posted": "2024-01-01T00:00:00.000Z",
    "lines": [
      {
        "account": { "path": "assets/users/user-123" },
        "amount": "10000"
      },
      {
        "account": { "path": "assets/initial-balance-offset" },
        "amount": "-10000"
      }
    ]
  }
}
```

Once completed, ensure the balance on the `initial-balance-offset` account is zero to verify all balance setting entries were posted.

<a id="balance-imports-reverse-entries"></a>

### Reverse entries

To avoid double-counting, any entries posted before the go-live timestamp must be reversed. Any balance impacts from these entries will already be included in the initial balances set.

To find entries to reverse, query:

```graphql expandable=true title="Query entries to reverse"
query GetEntriesToReverse(
  $ledger: LedgerMatchInput!
  $filter: LedgerEntriesFilterSet
) {
  ledger(ledger: $ledger) {
    ledgerEntries(filter: $filter) {
      nodes {
        id
        type
        posted
      }
    }
  }
}
```

```json expandable=true title="Variables"
{
  "ledger": { "id": "ledger-id" },
  "filter": {
    "posted": {
      "lessThanOrEqualTo": "2024-01-01T00:00:00.000Z"
    },
    "type": {
      "notEqualTo": "set-initial-balance"
    }
  }
}
```

This query finds all entries posted on or before the cutoff timestamp, while filtering out the entries used to set initial balances.

Save the list of Ledger Entry IDs to reverse. For each, call `reverseLedgerEntry`:

```graphql expandable=true title="reverseLedgerEntry"
mutation ReverseLedgerEntry(
  $id: ID!
) {
  reverseLedgerEntry(
    id: $id
  ) {
    __typename
    ... on ReverseLedgerEntryResult {
      reversingLedgerEntry {
        ik
        id
        type
        posted
        created
        reverses {
          id
          created
        }
        reversalPosition
      }
      reversedLedgerEntry {
        ik
        id
        type
        posted
        created
        reversedBy {
          id
          created
        }
        reversalPosition
      }
    }
    ... on Error {
      code
      message
    }
  }
}
```

```json expandable=true title="Variables"
{
  "id": "entry-id-to-reverse"
}
```

<a id="validation"></a>

## Validation

Once the import process is complete, balances in your Ledger will be a combination of initial balances and recently posted data. To validate, check the balances of your accounts:

- External accounts: Ledger balances should match the balance reports from external systems
- Internal accounts: Rerun the process used to calculate initial balances for these accounts to include the latest data. These should match the Ledger balances
- Clearing accounts: Once a payment has been processed and the appropriate Ledger Entries have been posted, these accounts should return to a zero balance

<a id="cleanup"></a>

## Cleanup

Once the import process is complete you can:

- Delete your interim Ledger
- Disable the `set-initial-balance` entry type
- Disable the `initial-balance-offset` and `initial-retained-earnings` accounts