Codat (v1) | Stitch Documentation

Codat is supported by the Singer community
This integration is powered by Singer's Codat tap. For support, visit the GitHub repo or join the Singer Slack.

Codat integration summary

Stitch’s Codat integration replicates data using the Codat API . Refer to the Schema section for a list of objects available for replication.

Codat feature snapshot

A high-level look at Stitch's Codat (v1) integration, including release status, useful links, and the features supported in Stitch.

STITCH
Release status	Released on December 11, 2017	Supported by	Singer Community
Stitch plan	Standard	API availability	Available
Singer GitHub repository	singer-io/tap-codat
REPLICATION SETTINGS
Anchor Scheduling	Supported	Advanced Scheduling	Supported
Table-level reset	Unsupported	Configurable Replication Methods	Unsupported
DATA SELECTION
Table selection	Supported	Column selection	Unsupported
Select all	Supported
TRANSPARENCY
Extraction Logs	Supported	Loading Reports	Supported

Connecting Codat

Codat setup requirements

To set up Codat in Stitch, you need:

Administrator or Developer privileges in Codat. These are required to generate an API key. Refer to Codat’s documentation for more info.

Step 1: Retrieve your Codat API key

Note: This step requires Administrator or Developer privileges in Codat.

Sign into your Codat account.
Click Accounts > Profile in the sidenav.
On the Manage Profile page, locate the API Key field.
Click the copy icon to copy the API key.

Paste the API key somewhere handy - you’ll need it in the next step.

Step 2: Add Codat as a Stitch data source

Sign into your Stitch account.
On the Stitch Dashboard page, click the Add Integration button.
Click the Codat icon.
Enter a name for the integration. This is the name that will display on the Stitch Dashboard for the integration; it’ll also be used to create the schema in your destination.

For example, the name “Stitch Codat” would create a schema called stitch_codat in the destination. Note: Schema names cannot be changed after you save the integration.
In the API Key field, paste the Codat API key you generated in Step 1.
Check the Use Codat UAT environment box if you’re connecting to your UAT (sandbox) environment in Codat.

Note: Use this setting only if you are connecting to a UAT (sandbox) Codat instance. Checking this box when the instance isn’t a sandbox will prevent a successful connection and 401 Bad Credentials errors in the connection check logs.

Step 3: Define the historical replication start date

The Sync Historical Data setting defines the starting date for your Codat integration. This means that data equal to or newer than this date will be replicated to your data warehouse.

Change this setting if you want to replicate data beyond Codat’s default setting of 1 year. For a detailed look at historical replication jobs, check out the Syncing Historical SaaS Data guide.

Step 4: Create a replication schedule

Replication schedules affect the time Extraction begins, not the time to data loaded. Refer to the Replication Scheduling documentation for more information.

In the Replication Frequency section, you’ll create the integration’s replication schedule. An integration’s replication schedule determines how often Stitch runs a replication job, and the time that job begins.

Codat integrations support the following replication scheduling methods:

Replication Frequency
Anchor Scheduling
Advanced Scheduling using Cron (Advanced or Premium plans only)

To keep your row usage low, consider setting the integration to replicate less frequently. See the Understanding and Reducing Your Row Usage guide for tips on reducing your usage.

Step 5: Set objects to replicate

Is an object missing or not replicating? Verify that the object meets the requirements for selection and replication.

The last step is to select the tables you want to replicate. Learn about the available tables for this integration.

Note: If a replication job is currently in progress, new selections won’t be used until the next job starts.

For Codat integrations, you can select:

**Individual tables **
All tables and columns

Click the tabs to view instructions for each selection method.

In the integration’s Tables to Replicate tab, locate a table you want to replicate.
To track a table, click the checkbox next to the table’s name. A blue checkmark means the table is set to replicate.
Repeat this process for all the tables you want to replicate.
When finished, click the Finalize Your Selections button at the bottom of the screen to save your selections.

Important: Using the Select All feature will overwrite any previous selections. However, selections aren’t final until Finalize Your Selections is clicked. Clicking Cancel will restore your previous selections. Refer to the Select All guide for more info about this feature.

Click into the integration from the Stitch Dashboard page.
Click the Tables to Replicate tab.
In the list of tables, click the box next to the Table Names column.
In the menu that displays, click Track all Tables and Fields:
Click the Finalize Your Selections button at the bottom of the page to save your data selections.

Initial and historical replication jobs

After you finish setting up Codat, its Sync Status may show as Pending on either the Stitch Dashboard or in the Integration Details page.

For a new integration, a Pending status indicates that Stitch is in the process of scheduling the initial replication job for the integration. This may take some time to complete.

Initial replication jobs with Anchor Scheduling

If using Anchor Scheduling, an initial replication job may not kick off immediately. This depends on the selected Replication Frequency and Anchor Time. Refer to the Anchor Scheduling documentation for more information.

Free historical data loads

The first seven days of replication, beginning when data is first replicated, are free. Rows replicated from the new integration during this time won’t count towards your quota. Stitch offers this as a way of testing new integrations, measuring usage, and ensuring historical data volumes don’t quickly consume your quota.

Replication will continue after the seven days are over. If you’re no longer interested in this source, be sure to pause or delete the integration to prevent unwanted usage.

Codat table reference

Schemas and versioning

Schemas and naming conventions can change from version to version, so we recommend verifying your integration’s version before continuing.

The schema and info displayed below is for version 1 of this integration.

This is the latest version of the Codat integration.

Table and column names in your destination

Depending on your destination, table and column names may not appear as they are outlined below.

For example: Object names are lowercased in Redshift (CusTomERs > customers), while case is maintained in PostgreSQL destinations (CusTomERs > CusTomERs). Refer to the Loading Guide for your destination for more info.

accounts

The accounts table contains info about the accounts in your Codat instance. In Codat, an account is a category used to record accounting transactions for a business.

Replication Method	Key-based Incremental
Primary Keys	id companyId
Replication Key	modifiedDate
Useful links	Codat documentation accounts schema on GitHub Codat API method

accounts table foreign keys

Join accounts with	on
bank_accounts	accounts.name = bank_accounts.accountName
balance_sheets	accounts.id = balance_sheets.reports.assets.accountId accounts.id = balance_sheets.reports.equity.accountId accounts.id = balance_sheets.reports.liabilities.accountId
profit_and_loss	accounts.id = profit_and_loss.reports.costOfSales.accountId accounts.id = profit_and_loss.reports.expenses.accountId accounts.id = profit_and_loss.reports.income.accountId accounts.id = profit_and_loss.reports.otherExpenses.accountId accounts.id = profit_and_loss.reports.otherIncome.accountId
bills	accounts.id = bills.lineItems.accountRef.id
items	accounts.id = items.billItem.accountRef.id accounts.id = items.invoiceItem.accountRef.id
journal_entries	accounts.id = journal_entries.journalLines.accountRef.id
bank_statement_lines	accounts.name = bank_statement_lines.accountName

accounts table schema

companyId

STRING

currency

STRING

currentBalance

NUMBER

description

STRING

fullyQualifiedCategory

STRING

fullyQualifiedName

STRING

isBankAccount

BOOLEAN

modifiedDate

STRING

name

STRING

nominalCode

STRING

sourceModifiedDate

STRING

status

STRING

type

STRING

balance_sheets

The balance_sheets table contains info about the balance sheets in your Codat instance. A balance sheet is a snapshot at a point in time of a company’s accounts.

Replication Method

Full Table

Primary Key

companyId

Useful links

Codat documentation

balance_sheets schema on GitHub

Codat API method

balance_sheets table foreign keys

Join balance_sheets with	on
accounts	balance_sheets.reports.assets.accountId = accounts.id balance_sheets.reports.equity.accountId = accounts.id balance_sheets.reports.liabilities.accountId = accounts.id
profit_and_loss	balance_sheets.reports.assets.accountId = profit_and_loss.reports.costOfSales.accountId balance_sheets.reports.equity.accountId = profit_and_loss.reports.costOfSales.accountId balance_sheets.reports.liabilities.accountId = profit_and_loss.reports.costOfSales.accountId balance_sheets.reports.assets.accountId = profit_and_loss.reports.expenses.accountId balance_sheets.reports.equity.accountId = profit_and_loss.reports.expenses.accountId balance_sheets.reports.liabilities.accountId = profit_and_loss.reports.expenses.accountId balance_sheets.reports.assets.accountId = profit_and_loss.reports.income.accountId balance_sheets.reports.equity.accountId = profit_and_loss.reports.income.accountId balance_sheets.reports.liabilities.accountId = profit_and_loss.reports.income.accountId balance_sheets.reports.assets.accountId = profit_and_loss.reports.otherExpenses.accountId balance_sheets.reports.equity.accountId = profit_and_loss.reports.otherExpenses.accountId balance_sheets.reports.liabilities.accountId = profit_and_loss.reports.otherExpenses.accountId balance_sheets.reports.assets.accountId = profit_and_loss.reports.otherIncome.accountId balance_sheets.reports.equity.accountId = profit_and_loss.reports.otherIncome.accountId balance_sheets.reports.liabilities.accountId = profit_and_loss.reports.otherIncome.accountId balance_sheets.companyId = profit_and_loss.companyId
bills	balance_sheets.reports.assets.accountId = bills.lineItems.accountRef.id balance_sheets.reports.equity.accountId = bills.lineItems.accountRef.id balance_sheets.reports.liabilities.accountId = bills.lineItems.accountRef.id balance_sheets.companyId = bills.companyId
items	balance_sheets.reports.assets.accountId = items.billItem.accountRef.id balance_sheets.reports.equity.accountId = items.billItem.accountRef.id balance_sheets.reports.liabilities.accountId = items.billItem.accountRef.id balance_sheets.reports.assets.accountId = items.invoiceItem.accountRef.id balance_sheets.reports.equity.accountId = items.invoiceItem.accountRef.id balance_sheets.reports.liabilities.accountId = items.invoiceItem.accountRef.id balance_sheets.companyId = items.companyId
journal_entries	balance_sheets.reports.assets.accountId = journal_entries.journalLines.accountRef.id balance_sheets.reports.equity.accountId = journal_entries.journalLines.accountRef.id balance_sheets.reports.liabilities.accountId = journal_entries.journalLines.accountRef.id balance_sheets.companyId = journal_entries.companyId
companies	balance_sheets.companyId = companies.id
bank_statements	balance_sheets.companyId = bank_statements.companyId
bank_statement_lines	balance_sheets.companyId = bank_statement_lines.companyId
bill_payments	balance_sheets.companyId = bill_payments.supplierRef.companyId balance_sheets.companyId = bill_payments.companyId
company_info	balance_sheets.companyId = company_info.companyId
customers	balance_sheets.companyId = customers.companyId
invoices	balance_sheets.companyId = invoices.companyId
payments	balance_sheets.companyId = payments.companyId
suppliers	balance_sheets.companyId = suppliers.companyId
connections	balance_sheets.companyId = connections.companyId
events	balance_sheets.companyId = events.companyId
tax_rates	balance_sheets.companyId = tax_rates.companyId
bank_account_transactions	balance_sheets.companyId = bank_account_transactions.companyId
bank_accounts	balance_sheets.companyId = bank_accounts.companyId

balance_sheets table schema

companyId

STRING

currency

STRING

mostRecentAvailableMonth

STRING

reports

ARRAY

Click to expand reports

assets

ARRAY

Click to expand assets

accountId

STRING

name

STRING

value

NUMBER

date

DATE-TIME

equity

ARRAY

Click to expand equity

accountId

STRING

name

STRING

value

NUMBER

liabilities

ARRAY

Click to expand liabilities

accountId

STRING

name

STRING

value

NUMBER

netAssets

NUMBER

status

OBJECT, STRING

bank_account_transactions

The bank_account_transactions table contains information about transactions for a specified company and bank account in your Codat account.

Replication Method

Full Table

Primary Keys

_transactionIndex

bankAccountId

companyId

Useful links

Codat documentation

bank_account_transactions schema on GitHub

Codat API method

bank_account_transactions table foreign keys

Join bank_account_transactions with	on
bank_accounts	bank_account_transactions.bankAccountId = bank_accounts.id bank_account_transactions.companyId = bank_accounts.companyId
companies	bank_account_transactions.companyId = companies.id
balance_sheets	bank_account_transactions.companyId = balance_sheets.companyId
bank_statements	bank_account_transactions.companyId = bank_statements.companyId
bank_statement_lines	bank_account_transactions.companyId = bank_statement_lines.companyId
bill_payments	bank_account_transactions.companyId = bill_payments.supplierRef.companyId bank_account_transactions.companyId = bill_payments.companyId
bills	bank_account_transactions.companyId = bills.companyId
company_info	bank_account_transactions.companyId = company_info.companyId
customers	bank_account_transactions.companyId = customers.companyId
invoices	bank_account_transactions.companyId = invoices.companyId
payments	bank_account_transactions.companyId = payments.companyId
profit_and_loss	bank_account_transactions.companyId = profit_and_loss.companyId
suppliers	bank_account_transactions.companyId = suppliers.companyId
connections	bank_account_transactions.companyId = connections.companyId
events	bank_account_transactions.companyId = events.companyId
items	bank_account_transactions.companyId = items.companyId
journal_entries	bank_account_transactions.companyId = journal_entries.companyId
tax_rates	bank_account_transactions.companyId = tax_rates.companyId

bank_account_transactions table schema

_transactionIndex

INTEGER

amount

NUMBER

balance

NUMBER

bankAccountId

STRING

companyId

STRING

date

STRING

description

STRING

modifiedDate

STRING

reconciled

BOOLEAN

sourceModifiedDate

STRING

transactionType

STRING

bank_accounts

The bank_accounts table contains informaiton about bank accounts for a given company in your Codat account.

Replication Method

Full Table

Primary Keys

accountName

companyId

connectionId

Useful links

Codat documentation

bank_accounts schema on GitHub

Codat API method

bank_accounts table foreign keys

Join bank_accounts with	on
accounts	bank_accounts.accountName = accounts.name
bank_statement_lines	bank_accounts.accountName = bank_statement_lines.accountName bank_accounts.companyId = bank_statement_lines.companyId
bank_account_transactions	bank_accounts.id = bank_account_transactions.bankAccountId bank_accounts.companyId = bank_account_transactions.companyId
companies	bank_accounts.companyId = companies.id
balance_sheets	bank_accounts.companyId = balance_sheets.companyId
bank_statements	bank_accounts.companyId = bank_statements.companyId
bill_payments	bank_accounts.companyId = bill_payments.supplierRef.companyId bank_accounts.companyId = bill_payments.companyId
bills	bank_accounts.companyId = bills.companyId
company_info	bank_accounts.companyId = company_info.companyId
customers	bank_accounts.companyId = customers.companyId
invoices	bank_accounts.companyId = invoices.companyId
payments	bank_accounts.companyId = payments.companyId
profit_and_loss	bank_accounts.companyId = profit_and_loss.companyId
suppliers	bank_accounts.companyId = suppliers.companyId
connections	bank_accounts.companyId = connections.companyId bank_accounts.connectionId = connections.id
events	bank_accounts.companyId = events.companyId
items	bank_accounts.companyId = items.companyId
journal_entries	bank_accounts.companyId = journal_entries.companyId
tax_rates	bank_accounts.companyId = tax_rates.companyId

bank_accounts table schema

accountName

STRING

accountNumber

STRING

availableBalance

NUMBER

balance

NUMBER

companyId

STRING

connectionId

STRING

currency

STRING

fromDate

STRING

iban

STRING

modifiedDate

STRING

overdraftLimit

NUMBER

sortCode

STRING

sourceModifiedDate

STRING

toDate

STRING

bank_statement_lines

The bank_statement_lines table contains info about lines included in bank statements.

Replication Method

Full Table

Primary Keys

accountName

companyId

_lineIndex

Useful links

Codat documentation

bank_statement_lines schema on GitHub

Codat API method

bank_statement_lines table foreign keys

Join bank_statement_lines with	on
accounts	bank_statement_lines.accountName = accounts.name
bank_accounts	bank_statement_lines.accountName = bank_accounts.accountName bank_statement_lines.companyId = bank_accounts.companyId
companies	bank_statement_lines.companyId = companies.id
balance_sheets	bank_statement_lines.companyId = balance_sheets.companyId
bank_statements	bank_statement_lines.companyId = bank_statements.companyId
bill_payments	bank_statement_lines.companyId = bill_payments.supplierRef.companyId bank_statement_lines.companyId = bill_payments.companyId
bills	bank_statement_lines.companyId = bills.companyId
company_info	bank_statement_lines.companyId = company_info.companyId
customers	bank_statement_lines.companyId = customers.companyId
invoices	bank_statement_lines.companyId = invoices.companyId
payments	bank_statement_lines.companyId = payments.companyId
profit_and_loss	bank_statement_lines.companyId = profit_and_loss.companyId
suppliers	bank_statement_lines.companyId = suppliers.companyId
connections	bank_statement_lines.companyId = connections.companyId
events	bank_statement_lines.companyId = events.companyId
items	bank_statement_lines.companyId = items.companyId
journal_entries	bank_statement_lines.companyId = journal_entries.companyId
tax_rates	bank_statement_lines.companyId = tax_rates.companyId
bank_account_transactions	bank_statement_lines.companyId = bank_account_transactions.companyId

bank_statement_lines table schema

_lineIndex

INTEGER

accountName

STRING

amount

NUMBER

balance

NUMBER

companyId

STRING

date

DATE-TIME

description

STRING

reconciled

BOOLEAN

bank_statements

The bank_statements table contains info about bank statement report data for a company over a time period.

Replication Method

Key-based Incremental

Primary Keys

companyId

Useful links

Codat documentation

bank_statements schema on GitHub

Codat API method

bank_statements table foreign keys

Join bank_statements with	on
companies	bank_statements.companyId = companies.id
balance_sheets	bank_statements.companyId = balance_sheets.companyId
bank_statement_lines	bank_statements.companyId = bank_statement_lines.companyId
bill_payments	bank_statements.companyId = bill_payments.supplierRef.companyId bank_statements.companyId = bill_payments.companyId
bills	bank_statements.companyId = bills.companyId
company_info	bank_statements.companyId = company_info.companyId
customers	bank_statements.companyId = customers.companyId
invoices	bank_statements.companyId = invoices.companyId
payments	bank_statements.companyId = payments.companyId
profit_and_loss	bank_statements.companyId = profit_and_loss.companyId
suppliers	bank_statements.companyId = suppliers.companyId
connections	bank_statements.companyId = connections.companyId
events	bank_statements.companyId = events.companyId
items	bank_statements.companyId = items.companyId
journal_entries	bank_statements.companyId = journal_entries.companyId
tax_rates	bank_statements.companyId = tax_rates.companyId
bank_account_transactions	bank_statements.companyId = bank_account_transactions.companyId
bank_accounts	bank_statements.companyId = bank_accounts.companyId

bank_statements table schema

accountName

STRING

accountNumber

STRING

availableBalance

STRING, NUMBER

balance

STRING, NUMBER

companyId

STRING

currency

STRING

fromDate

DATE-TIME

iban

STRING

sortCode

STRING

toDate

DATE-TIME

bill_payments

The bill_payments table contains information about company bill payments in your Codat account.

Replication Method	Key-based Incremental
Primary Keys	companyId id
Replication Key	modifiedDate
Useful links	Codat documentation bill_payments schema on GitHub Codat API method

bill_payments table foreign keys

Join bill_payments with	on
companies	bill_payments.supplierRef.companyId = companies.id bill_payments.companyId = companies.id
balance_sheets	bill_payments.supplierRef.companyId = balance_sheets.companyId bill_payments.companyId = balance_sheets.companyId
bank_statements	bill_payments.supplierRef.companyId = bank_statements.companyId bill_payments.companyId = bank_statements.companyId
bank_statement_lines	bill_payments.supplierRef.companyId = bank_statement_lines.companyId bill_payments.companyId = bank_statement_lines.companyId
bills	bill_payments.supplierRef.companyId = bills.companyId bill_payments.companyId = bills.companyId bill_payments.supplierRef.id = bills.supplierRef
company_info	bill_payments.supplierRef.companyId = company_info.companyId bill_payments.companyId = company_info.companyId
customers	bill_payments.supplierRef.companyId = customers.companyId bill_payments.companyId = customers.companyId
invoices	bill_payments.supplierRef.companyId = invoices.companyId bill_payments.companyId = invoices.companyId
payments	bill_payments.supplierRef.companyId = payments.companyId bill_payments.companyId = payments.companyId
profit_and_loss	bill_payments.supplierRef.companyId = profit_and_loss.companyId bill_payments.companyId = profit_and_loss.companyId
suppliers	bill_payments.supplierRef.companyId = suppliers.companyId bill_payments.companyId = suppliers.companyId
connections	bill_payments.supplierRef.companyId = connections.companyId bill_payments.companyId = connections.companyId
events	bill_payments.supplierRef.companyId = events.companyId bill_payments.companyId = events.companyId
items	bill_payments.supplierRef.companyId = items.companyId bill_payments.companyId = items.companyId
journal_entries	bill_payments.supplierRef.companyId = journal_entries.companyId bill_payments.companyId = journal_entries.companyId
tax_rates	bill_payments.supplierRef.companyId = tax_rates.companyId bill_payments.companyId = tax_rates.companyId
bank_account_transactions	bill_payments.supplierRef.companyId = bank_account_transactions.companyId bill_payments.companyId = bank_account_transactions.companyId
bank_accounts	bill_payments.supplierRef.companyId = bank_accounts.companyId bill_payments.companyId = bank_accounts.companyId

bill_payments table schema

companyId

STRING

currency

STRING

currencyRate

NUMBER

date

STRING

lines

ARRAY

Click to expand lines

amount

NUMBER

links

ARRAY

Click to expand links

STRING

type

STRING

modifiedDate

STRING

note

STRING

sourceModifiedDate

STRING

supplierRef

OBJECT

Click to expand supplierRef

addresses

ARRAY

Click to expand addresses

city

STRING

country

STRING

line1

STRING

line2

STRING

postalCode

STRING

region

STRING

type

STRING

companyId

STRING

contactName

STRING

emailAddress

STRING

phone

STRING

registrationNumber

STRING

status

STRING

supplierName

STRING

taxNumber

STRING

totalAmount

NUMBER

bills

The bills table contains info about the bills in your Codat account. A bill is an itemized record of goods or services purchased from a supplier.

Replication Method

Key-based Incremental

Primary Keys

companyId

Useful links

Codat documentation

bills schema on GitHub

Codat API method

bills table foreign keys

Join bills with	on
accounts	bills.lineItems.accountRef.id = accounts.id
balance_sheets	bills.lineItems.accountRef.id = balance_sheets.reports.assets.accountId bills.lineItems.accountRef.id = balance_sheets.reports.equity.accountId bills.lineItems.accountRef.id = balance_sheets.reports.liabilities.accountId bills.companyId = balance_sheets.companyId
profit_and_loss	bills.lineItems.accountRef.id = profit_and_loss.reports.costOfSales.accountId bills.lineItems.accountRef.id = profit_and_loss.reports.expenses.accountId bills.lineItems.accountRef.id = profit_and_loss.reports.income.accountId bills.lineItems.accountRef.id = profit_and_loss.reports.otherExpenses.accountId bills.lineItems.accountRef.id = profit_and_loss.reports.otherIncome.accountId bills.companyId = profit_and_loss.companyId
items	bills.lineItems.accountRef.id = items.billItem.accountRef.id bills.lineItems.accountRef.id = items.invoiceItem.accountRef.id bills.companyId = items.companyId bills.lineItems.itemRef.id = items.id bills.lineItems.taxRateRef.id = items.billItem.taxRateRef.id bills.lineItems.taxRateRef.id = items.invoiceItem.taxRateRef.id
journal_entries	bills.lineItems.accountRef.id = journal_entries.journalLines.accountRef.id bills.companyId = journal_entries.companyId
companies	bills.companyId = companies.id
bank_statements	bills.companyId = bank_statements.companyId
bank_statement_lines	bills.companyId = bank_statement_lines.companyId
bill_payments	bills.companyId = bill_payments.supplierRef.companyId bills.companyId = bill_payments.companyId bills.supplierRef = bill_payments.supplierRef.id
company_info	bills.companyId = company_info.companyId
customers	bills.companyId = customers.companyId
invoices	bills.companyId = invoices.companyId
payments	bills.companyId = payments.companyId
suppliers	bills.companyId = suppliers.companyId bills.supplierRef = suppliers.id
connections	bills.companyId = connections.companyId
events	bills.companyId = events.companyId
tax_rates	bills.companyId = tax_rates.companyId bills.lineItems.taxRateRef.id = tax_rates.id
bank_account_transactions	bills.companyId = bank_account_transactions.companyId
bank_accounts	bills.companyId = bank_accounts.companyId

bills table schema

amountDue

NUMBER

companyId

STRING

currency

STRING

dueDate

DATE-TIME

STRING

issueDate

DATE-TIME

lineItems

ARRAY

Click to expand lineItems

accountRef

OBJECT

Click to expand accountRef

STRING

name

STRING

description

STRING

discountAmount

NUMBER

discountPercentage

NUMBER

itemRef

OBJECT

Click to expand itemRef

STRING

name

STRING

quantity

NUMBER

subTotal

NUMBER

taxAmount

NUMBER

taxRateRef

OBJECT

Click to expand taxRateRef

STRING

name

STRING

totalAmount

NUMBER

unitAmount

NUMBER

reference

STRING

status

STRING

subTotal

NUMBER

supplierRef

OBJECT

Click to expand supplierRef

addresses

ARRAY

Click to expand addresses

city

STRING

country

STRING

line1

STRING

line2

STRING

postalCode

STRING

region

STRING

type

STRING

companyId

STRING

contactName

STRING

emailAddress

STRING

phone

STRING

registrationNumber

STRING

status

STRING

supplierName

STRING

taxNumber

STRING

taxAmount

NUMBER

totalAmount

NUMBER

companies

The companies table contains info about the companies in your Codat instance. A company represents a business who can share the connection to their financial data sources.

Replication Method

Full Table

Primary Key

Useful links

Codat documentation

companies schema on GitHub

Codat API method

companies table foreign keys

Join companies with	on
balance_sheets	companies.id = balance_sheets.companyId
bank_statements	companies.id = bank_statements.companyId
bank_statement_lines	companies.id = bank_statement_lines.companyId
bill_payments	companies.id = bill_payments.supplierRef.companyId companies.id = bill_payments.companyId
bills	companies.id = bills.companyId
company_info	companies.id = company_info.companyId
customers	companies.id = customers.companyId
invoices	companies.id = invoices.companyId
payments	companies.id = payments.companyId
profit_and_loss	companies.id = profit_and_loss.companyId
suppliers	companies.id = suppliers.companyId
connections	companies.id = connections.companyId
events	companies.id = events.companyId
items	companies.id = items.companyId
journal_entries	companies.id = journal_entries.companyId
tax_rates	companies.id = tax_rates.companyId
bank_account_transactions	companies.id = bank_account_transactions.companyId
bank_accounts	companies.id = bank_accounts.companyId

companies table schema

STRING

lastSync

DATE-TIME

name

STRING

platform

STRING

redirect

STRING

status

STRING

company_info

The company_info table contains company info. In Codat, company info includes information about a linked company such as address, phone number and company registration.

Replication Method

Full Table

Primary Key

companyId

Useful links

Codat documentation

company_info schema on GitHub

Codat API method

company_info table foreign keys

Join company_info with	on
companies	company_info.companyId = companies.id
balance_sheets	company_info.companyId = balance_sheets.companyId
bank_statements	company_info.companyId = bank_statements.companyId
bank_statement_lines	company_info.companyId = bank_statement_lines.companyId
bill_payments	company_info.companyId = bill_payments.supplierRef.companyId company_info.companyId = bill_payments.companyId
bills	company_info.companyId = bills.companyId
customers	company_info.companyId = customers.companyId
invoices	company_info.companyId = invoices.companyId
payments	company_info.companyId = payments.companyId
profit_and_loss	company_info.companyId = profit_and_loss.companyId
suppliers	company_info.companyId = suppliers.companyId
connections	company_info.companyId = connections.companyId
events	company_info.companyId = events.companyId
items	company_info.companyId = items.companyId
journal_entries	company_info.companyId = journal_entries.companyId
tax_rates	company_info.companyId = tax_rates.companyId
bank_account_transactions	company_info.companyId = bank_account_transactions.companyId
bank_accounts	company_info.companyId = bank_accounts.companyId

company_info table schema

accountingPlatformRef

STRING

addresses

ARRAY

Click to expand addresses

city

STRING

country

STRING

line1

STRING

line2

STRING

postalCode

STRING

region

STRING

type

STRING

baseCurrency

STRING

companyId

STRING

companyLegalName

STRING

companyName

STRING

createdDate

STRING

financialYearStartDate

DATE-TIME

phoneNumbers

ARRAY

Click to expand phoneNumbers

number

STRING

type

STRING

registrationNumber

STRING

taxNumber

STRING

webLinks

ARRAY

Click to expand webLinks

type

STRING

url

STRING

connections

The connections table contains information about company connections in your Codat account.

Replication Method

Full Table

Primary Keys

companyId

Useful links

connections schema on GitHub

Codat API method

connections table foreign keys

Join connections with	on
companies	connections.companyId = companies.id
balance_sheets	connections.companyId = balance_sheets.companyId
bank_statements	connections.companyId = bank_statements.companyId
bank_statement_lines	connections.companyId = bank_statement_lines.companyId
bill_payments	connections.companyId = bill_payments.supplierRef.companyId connections.companyId = bill_payments.companyId
bills	connections.companyId = bills.companyId
company_info	connections.companyId = company_info.companyId
customers	connections.companyId = customers.companyId
invoices	connections.companyId = invoices.companyId
payments	connections.companyId = payments.companyId
profit_and_loss	connections.companyId = profit_and_loss.companyId
suppliers	connections.companyId = suppliers.companyId
events	connections.companyId = events.companyId
items	connections.companyId = items.companyId
journal_entries	connections.companyId = journal_entries.companyId
tax_rates	connections.companyId = tax_rates.companyId
bank_account_transactions	connections.companyId = bank_account_transactions.companyId
bank_accounts	connections.companyId = bank_accounts.companyId connections.id = bank_accounts.connectionId

connections table schema

companyId

STRING

integrationId

STRING

linkUrl

STRING

platformName

STRING

sourceId

STRING

status

STRING

credit_notes

The credit_notes table contains info about the credit notes in your Codat instance.

Replication Method

Key-based Incremental

Primary Keys

companyId

Useful links

Codat documentation

credit_notes schema on GitHub

Codat API method

credit_notes table foreign keys

Join credit_notes with

payments

credit_notes.id = payments.lines.links.id

credit_notes.customerRef.id = payments.customerRef.id

customers

credit_notes.customerRef.id = customers.id

credit_notes table schema

companyId

STRING

creditNoteNumber

STRING

currency

STRING

customerRef

OBJECT

Click to expand customerRef

companyName

STRING

issueDate

DATE-TIME

paymentAllocations

ARRAY

remainingCredit

NUMBER

status

STRING

totalAmount

NUMBER

customers

The customers table contains info about the customers in your Codat instance. A customer is a person or organisation that buys goods or services.

Replication Method	Key-based Incremental
Primary Keys	companyId id
Replication Key	modifiedDate
Useful links	Codat documentation customers schema on GitHub Codat API method

customers table foreign keys

Join customers with	on
companies	customers.companyId = companies.id
balance_sheets	customers.companyId = balance_sheets.companyId
bank_statements	customers.companyId = bank_statements.companyId
bank_statement_lines	customers.companyId = bank_statement_lines.companyId
bill_payments	customers.companyId = bill_payments.supplierRef.companyId customers.companyId = bill_payments.companyId
bills	customers.companyId = bills.companyId
company_info	customers.companyId = company_info.companyId
invoices	customers.companyId = invoices.companyId
payments	customers.companyId = payments.companyId customers.id = payments.customerRef.id
profit_and_loss	customers.companyId = profit_and_loss.companyId
suppliers	customers.companyId = suppliers.companyId
connections	customers.companyId = connections.companyId
events	customers.companyId = events.companyId
items	customers.companyId = items.companyId
journal_entries	customers.companyId = journal_entries.companyId
tax_rates	customers.companyId = tax_rates.companyId
bank_account_transactions	customers.companyId = bank_account_transactions.companyId
bank_accounts	customers.companyId = bank_accounts.companyId
credit_notes	customers.id = credit_notes.customerRef.id

customers table schema

addresses

ARRAY

Click to expand addresses

city

STRING

country

STRING

line1

STRING

line2

STRING

postalCode

STRING

region

STRING

type

STRING

companyId

STRING

contactName

STRING

contacts

ARRAY

Click to expand contacts

address

OBJECT

Click to expand address

city

STRING

country

STRING

line1

STRING

line2

STRING

postalCode

STRING

region

STRING

type

STRING

email

STRING

modifiedDate

DATE-TIME

name

STRING

phone

ARRAY

Click to expand phone

number

STRING

type

STRING

status

STRING

customerName

STRING

defaultCurrency

STRING

emailAddress

STRING

modifiedDate

DATE-TIME

phone

STRING

registrationNumber

STRING

status

STRING

taxNumber

STRING

events

The events table contains information about a given company’s events in your Codat account.

Replication Method

Full Table

Primary Keys

companyId

eventTimeUtc

Useful links

events schema on GitHub

Codat API method

events table foreign keys

Join events with	on
companies	events.companyId = companies.id
balance_sheets	events.companyId = balance_sheets.companyId
bank_statements	events.companyId = bank_statements.companyId
bank_statement_lines	events.companyId = bank_statement_lines.companyId
bill_payments	events.companyId = bill_payments.supplierRef.companyId events.companyId = bill_payments.companyId
bills	events.companyId = bills.companyId
company_info	events.companyId = company_info.companyId
customers	events.companyId = customers.companyId
invoices	events.companyId = invoices.companyId
payments	events.companyId = payments.companyId
profit_and_loss	events.companyId = profit_and_loss.companyId
suppliers	events.companyId = suppliers.companyId
connections	events.companyId = connections.companyId
items	events.companyId = items.companyId
journal_entries	events.companyId = journal_entries.companyId
tax_rates	events.companyId = tax_rates.companyId
bank_account_transactions	events.companyId = bank_account_transactions.companyId
bank_accounts	events.companyId = bank_accounts.companyId

events table schema

companyId

STRING

description

STRING

eventTimeUtc

STRING

type

STRING

invoices

The invoices table contains info about the invoices in your Codat instance. An invoice is an itemized record of goods or services sold to a customer.

Replication Method	Key-based Incremental
Primary Keys	companyId id
Replication Key	modifiedDate
Useful links	Codat documentation invoices schema on GitHub Codat API method

invoices table foreign keys

Join invoices with	on
companies	invoices.companyId = companies.id
balance_sheets	invoices.companyId = balance_sheets.companyId
bank_statements	invoices.companyId = bank_statements.companyId
bank_statement_lines	invoices.companyId = bank_statement_lines.companyId
bill_payments	invoices.companyId = bill_payments.supplierRef.companyId invoices.companyId = bill_payments.companyId
bills	invoices.companyId = bills.companyId
company_info	invoices.companyId = company_info.companyId
customers	invoices.companyId = customers.companyId
payments	invoices.companyId = payments.companyId invoices.id = payments.lines.links.id
profit_and_loss	invoices.companyId = profit_and_loss.companyId
suppliers	invoices.companyId = suppliers.companyId
connections	invoices.companyId = connections.companyId
events	invoices.companyId = events.companyId
items	invoices.companyId = items.companyId
journal_entries	invoices.companyId = journal_entries.companyId
tax_rates	invoices.companyId = tax_rates.companyId
bank_account_transactions	invoices.companyId = bank_account_transactions.companyId
bank_accounts	invoices.companyId = bank_accounts.companyId

invoices table schema

amountDue

NUMBER

companyId

STRING

currency

STRING

customerRef

OBJECT

Click to expand customerRef

companyName

STRING

dueDate

DATE-TIME

STRING

invoiceNumber

STRING

issueDate

DATE-TIME

lineItems

ARRAY

Click to expand lineItems

description

STRING

discountAmount

NUMBER

quantity

NUMBER

subTotal

NUMBER

taxAmount

NUMBER

totalAmount

NUMBER

unitAmount

NUMBER

modifiedDate

DATE-TIME

paidOnDate

DATE-TIME

paymentAllocations

ARRAY

status

STRING

subTotal

NUMBER

totalAmount

NUMBER

totalDiscount

NUMBER

totalTaxAmount

NUMBER

items

The items table contains information about items for a given company in your Codat account.

Replication Method	Key-based Incremental
Primary Keys	companyId id
Replication Key	modifiedDate
Useful links	Codat documentation items schema on GitHub Codat API method

items table foreign keys

Join items with	on
accounts	items.billItem.accountRef.id = accounts.id items.invoiceItem.accountRef.id = accounts.id
balance_sheets	items.billItem.accountRef.id = balance_sheets.reports.assets.accountId items.invoiceItem.accountRef.id = balance_sheets.reports.assets.accountId items.billItem.accountRef.id = balance_sheets.reports.equity.accountId items.invoiceItem.accountRef.id = balance_sheets.reports.equity.accountId items.billItem.accountRef.id = balance_sheets.reports.liabilities.accountId items.invoiceItem.accountRef.id = balance_sheets.reports.liabilities.accountId items.companyId = balance_sheets.companyId
profit_and_loss	items.billItem.accountRef.id = profit_and_loss.reports.costOfSales.accountId items.invoiceItem.accountRef.id = profit_and_loss.reports.costOfSales.accountId items.billItem.accountRef.id = profit_and_loss.reports.expenses.accountId items.invoiceItem.accountRef.id = profit_and_loss.reports.expenses.accountId items.billItem.accountRef.id = profit_and_loss.reports.income.accountId items.invoiceItem.accountRef.id = profit_and_loss.reports.income.accountId items.billItem.accountRef.id = profit_and_loss.reports.otherExpenses.accountId items.invoiceItem.accountRef.id = profit_and_loss.reports.otherExpenses.accountId items.billItem.accountRef.id = profit_and_loss.reports.otherIncome.accountId items.invoiceItem.accountRef.id = profit_and_loss.reports.otherIncome.accountId items.companyId = profit_and_loss.companyId
bills	items.billItem.accountRef.id = bills.lineItems.accountRef.id items.invoiceItem.accountRef.id = bills.lineItems.accountRef.id items.companyId = bills.companyId items.id = bills.lineItems.itemRef.id items.billItem.taxRateRef.id = bills.lineItems.taxRateRef.id items.invoiceItem.taxRateRef.id = bills.lineItems.taxRateRef.id
journal_entries	items.billItem.accountRef.id = journal_entries.journalLines.accountRef.id items.invoiceItem.accountRef.id = journal_entries.journalLines.accountRef.id items.companyId = journal_entries.companyId
companies	items.companyId = companies.id
bank_statements	items.companyId = bank_statements.companyId
bank_statement_lines	items.companyId = bank_statement_lines.companyId
bill_payments	items.companyId = bill_payments.supplierRef.companyId items.companyId = bill_payments.companyId
company_info	items.companyId = company_info.companyId
customers	items.companyId = customers.companyId
invoices	items.companyId = invoices.companyId
payments	items.companyId = payments.companyId
suppliers	items.companyId = suppliers.companyId
connections	items.companyId = connections.companyId
events	items.companyId = events.companyId
tax_rates	items.companyId = tax_rates.companyId items.billItem.taxRateRef.id = tax_rates.id items.invoiceItem.taxRateRef.id = tax_rates.id
bank_account_transactions	items.companyId = bank_account_transactions.companyId
bank_accounts	items.companyId = bank_accounts.companyId

items table schema

billItem

OBJECT

Click to expand billItem

accountRef

OBJECT

Click to expand accountRef

STRING

description

STRING

taxRateRef

OBJECT

Click to expand taxRateRef

STRING

unitPrice

NUMBER

code

STRING

companyId

STRING

invoiceItem

OBJECT

Click to expand invoiceItem

accountRef

OBJECT

Click to expand accountRef

STRING

description

STRING

taxRateRef

OBJECT

Click to expand taxRateRef

STRING

unitPrice

NUMBER

isBillItem

BOOLEAN

isInvoiceItem

BOOLEAN

itemStatus

STRING

modifiedDate

STRING

name

STRING

sourceModifiedDate

STRING

journal_entries

The journal_entries table contains information about journal entries for a given company in your Codat account.

Replication Method	Key-based Incremental
Primary Keys	companyId id
Replication Key	modifiedDate
Useful links	Codat documentation journal_entries schema on GitHub Codat API method

journal_entries table foreign keys

Join journal_entries with	on
accounts	journal_entries.journalLines.accountRef.id = accounts.id
balance_sheets	journal_entries.journalLines.accountRef.id = balance_sheets.reports.assets.accountId journal_entries.journalLines.accountRef.id = balance_sheets.reports.equity.accountId journal_entries.journalLines.accountRef.id = balance_sheets.reports.liabilities.accountId journal_entries.companyId = balance_sheets.companyId
profit_and_loss	journal_entries.journalLines.accountRef.id = profit_and_loss.reports.costOfSales.accountId journal_entries.journalLines.accountRef.id = profit_and_loss.reports.expenses.accountId journal_entries.journalLines.accountRef.id = profit_and_loss.reports.income.accountId journal_entries.journalLines.accountRef.id = profit_and_loss.reports.otherExpenses.accountId journal_entries.journalLines.accountRef.id = profit_and_loss.reports.otherIncome.accountId journal_entries.companyId = profit_and_loss.companyId
bills	journal_entries.journalLines.accountRef.id = bills.lineItems.accountRef.id journal_entries.companyId = bills.companyId
items	journal_entries.journalLines.accountRef.id = items.billItem.accountRef.id journal_entries.journalLines.accountRef.id = items.invoiceItem.accountRef.id journal_entries.companyId = items.companyId
companies	journal_entries.companyId = companies.id
bank_statements	journal_entries.companyId = bank_statements.companyId
bank_statement_lines	journal_entries.companyId = bank_statement_lines.companyId
bill_payments	journal_entries.companyId = bill_payments.supplierRef.companyId journal_entries.companyId = bill_payments.companyId
company_info	journal_entries.companyId = company_info.companyId
customers	journal_entries.companyId = customers.companyId
invoices	journal_entries.companyId = invoices.companyId
payments	journal_entries.companyId = payments.companyId
suppliers	journal_entries.companyId = suppliers.companyId
connections	journal_entries.companyId = connections.companyId
events	journal_entries.companyId = events.companyId
tax_rates	journal_entries.companyId = tax_rates.companyId
bank_account_transactions	journal_entries.companyId = bank_account_transactions.companyId
bank_accounts	journal_entries.companyId = bank_accounts.companyId

journal_entries table schema

companyId

STRING

createdOn

STRING

journalLines

ARRAY

Click to expand journalLines

accountRef

OBJECT

Click to expand accountRef

STRING

name

STRING

currency

STRING

description

STRING

grossAmount

NUMBER

STRING

netAmount

NUMBER

taxAmount

NUMBER

modifiedDate

STRING

postedOn

STRING

sourceModifiedDate

STRING

payments

The payments table contains info about the payments, or Accounts Receivable transactions, in your Codat instance. This includes details about invoices and credit notes.

Replication Method

Key-based Incremental

Primary Keys

companyId

Useful links

Codat documentation

payments schema on GitHub

Codat API method

payments table foreign keys

Join payments with	on
companies	payments.companyId = companies.id
balance_sheets	payments.companyId = balance_sheets.companyId
bank_statements	payments.companyId = bank_statements.companyId
bank_statement_lines	payments.companyId = bank_statement_lines.companyId
bill_payments	payments.companyId = bill_payments.supplierRef.companyId payments.companyId = bill_payments.companyId
bills	payments.companyId = bills.companyId
company_info	payments.companyId = company_info.companyId
customers	payments.companyId = customers.companyId payments.customerRef.id = customers.id
invoices	payments.companyId = invoices.companyId payments.lines.links.id = invoices.id
profit_and_loss	payments.companyId = profit_and_loss.companyId
suppliers	payments.companyId = suppliers.companyId
connections	payments.companyId = connections.companyId
events	payments.companyId = events.companyId
items	payments.companyId = items.companyId
journal_entries	payments.companyId = journal_entries.companyId
tax_rates	payments.companyId = tax_rates.companyId
bank_account_transactions	payments.companyId = bank_account_transactions.companyId
bank_accounts	payments.companyId = bank_accounts.companyId
credit_notes	payments.lines.links.id = credit_notes.id payments.customerRef.id = credit_notes.customerRef.id

payments table schema

companyId

STRING

currency

STRING

customerRef

OBJECT

Click to expand customerRef

companyName

STRING

date

DATE-TIME

STRING

lines

ARRAY

Click to expand lines

amount

NUMBER

links

ARRAY

Click to expand links

STRING

type

STRING

note

STRING

totalAmount

NUMBER

profit_and_loss

The profit_and_loss table contains profit and loss report data for a company over a time period.

Replication Method

Full Table

Primary Key

companyId

Useful links

Codat documentation

profit_and_loss schema on GitHub

Codat API method

profit_and_loss table foreign keys

Join profit_and_loss with	on
accounts	profit_and_loss.reports.costOfSales.accountId = accounts.id profit_and_loss.reports.expenses.accountId = accounts.id profit_and_loss.reports.income.accountId = accounts.id profit_and_loss.reports.otherExpenses.accountId = accounts.id profit_and_loss.reports.otherIncome.accountId = accounts.id
balance_sheets	profit_and_loss.reports.costOfSales.accountId = balance_sheets.reports.assets.accountId profit_and_loss.reports.expenses.accountId = balance_sheets.reports.assets.accountId profit_and_loss.reports.income.accountId = balance_sheets.reports.assets.accountId profit_and_loss.reports.otherExpenses.accountId = balance_sheets.reports.assets.accountId profit_and_loss.reports.otherIncome.accountId = balance_sheets.reports.assets.accountId profit_and_loss.reports.costOfSales.accountId = balance_sheets.reports.equity.accountId profit_and_loss.reports.expenses.accountId = balance_sheets.reports.equity.accountId profit_and_loss.reports.income.accountId = balance_sheets.reports.equity.accountId profit_and_loss.reports.otherExpenses.accountId = balance_sheets.reports.equity.accountId profit_and_loss.reports.otherIncome.accountId = balance_sheets.reports.equity.accountId profit_and_loss.reports.costOfSales.accountId = balance_sheets.reports.liabilities.accountId profit_and_loss.reports.expenses.accountId = balance_sheets.reports.liabilities.accountId profit_and_loss.reports.income.accountId = balance_sheets.reports.liabilities.accountId profit_and_loss.reports.otherExpenses.accountId = balance_sheets.reports.liabilities.accountId profit_and_loss.reports.otherIncome.accountId = balance_sheets.reports.liabilities.accountId profit_and_loss.companyId = balance_sheets.companyId
bills	profit_and_loss.reports.costOfSales.accountId = bills.lineItems.accountRef.id profit_and_loss.reports.expenses.accountId = bills.lineItems.accountRef.id profit_and_loss.reports.income.accountId = bills.lineItems.accountRef.id profit_and_loss.reports.otherExpenses.accountId = bills.lineItems.accountRef.id profit_and_loss.reports.otherIncome.accountId = bills.lineItems.accountRef.id profit_and_loss.companyId = bills.companyId
items	profit_and_loss.reports.costOfSales.accountId = items.billItem.accountRef.id profit_and_loss.reports.expenses.accountId = items.billItem.accountRef.id profit_and_loss.reports.income.accountId = items.billItem.accountRef.id profit_and_loss.reports.otherExpenses.accountId = items.billItem.accountRef.id profit_and_loss.reports.otherIncome.accountId = items.billItem.accountRef.id profit_and_loss.reports.costOfSales.accountId = items.invoiceItem.accountRef.id profit_and_loss.reports.expenses.accountId = items.invoiceItem.accountRef.id profit_and_loss.reports.income.accountId = items.invoiceItem.accountRef.id profit_and_loss.reports.otherExpenses.accountId = items.invoiceItem.accountRef.id profit_and_loss.reports.otherIncome.accountId = items.invoiceItem.accountRef.id profit_and_loss.companyId = items.companyId
journal_entries	profit_and_loss.reports.costOfSales.accountId = journal_entries.journalLines.accountRef.id profit_and_loss.reports.expenses.accountId = journal_entries.journalLines.accountRef.id profit_and_loss.reports.income.accountId = journal_entries.journalLines.accountRef.id profit_and_loss.reports.otherExpenses.accountId = journal_entries.journalLines.accountRef.id profit_and_loss.reports.otherIncome.accountId = journal_entries.journalLines.accountRef.id profit_and_loss.companyId = journal_entries.companyId
companies	profit_and_loss.companyId = companies.id
bank_statements	profit_and_loss.companyId = bank_statements.companyId
bank_statement_lines	profit_and_loss.companyId = bank_statement_lines.companyId
bill_payments	profit_and_loss.companyId = bill_payments.supplierRef.companyId profit_and_loss.companyId = bill_payments.companyId
company_info	profit_and_loss.companyId = company_info.companyId
customers	profit_and_loss.companyId = customers.companyId
invoices	profit_and_loss.companyId = invoices.companyId
payments	profit_and_loss.companyId = payments.companyId
suppliers	profit_and_loss.companyId = suppliers.companyId
connections	profit_and_loss.companyId = connections.companyId
events	profit_and_loss.companyId = events.companyId
tax_rates	profit_and_loss.companyId = tax_rates.companyId
bank_account_transactions	profit_and_loss.companyId = bank_account_transactions.companyId
bank_accounts	profit_and_loss.companyId = bank_accounts.companyId

profit_and_loss table schema

companyId

STRING

currency

STRING

mostRecentAvailableMonth

STRING

reportBasis

STRING

reports

ARRAY

Click to expand reports

costOfSales

ARRAY

Click to expand costOfSales

accountId

STRING

name

STRING

value

NUMBER

expenses

ARRAY

Click to expand expenses

accountId

STRING

name

STRING

value

NUMBER

fromDate

STRING

grossProfit

NUMBER

income

ARRAY

Click to expand income

accountId

STRING

name

STRING

value

NUMBER

netOperatingProfit

NUMBER

netOtherIncome

NUMBER

netProfit

NUMBER

otherExpenses

ARRAY

Click to expand otherExpenses

accountId

STRING

name

STRING

value

NUMBER

otherIncome

ARRAY

Click to expand otherIncome

accountId

STRING

name

STRING

value

NUMBER

toDate

STRING

suppliers

The suppliers table contains info about the suppliers in your Codat instance. A supplier is a person or organization that provides a product or service.

Replication Method

Key-based Incremental

Primary Keys

companyId

Useful links

Codat documentation

suppliers schema on GitHub

Codat API method

suppliers table foreign keys

Join suppliers with	on
companies	suppliers.companyId = companies.id
balance_sheets	suppliers.companyId = balance_sheets.companyId
bank_statements	suppliers.companyId = bank_statements.companyId
bank_statement_lines	suppliers.companyId = bank_statement_lines.companyId
bill_payments	suppliers.companyId = bill_payments.supplierRef.companyId suppliers.companyId = bill_payments.companyId
bills	suppliers.companyId = bills.companyId suppliers.id = bills.supplierRef
company_info	suppliers.companyId = company_info.companyId
customers	suppliers.companyId = customers.companyId
invoices	suppliers.companyId = invoices.companyId
payments	suppliers.companyId = payments.companyId
profit_and_loss	suppliers.companyId = profit_and_loss.companyId
connections	suppliers.companyId = connections.companyId
events	suppliers.companyId = events.companyId
items	suppliers.companyId = items.companyId
journal_entries	suppliers.companyId = journal_entries.companyId
tax_rates	suppliers.companyId = tax_rates.companyId
bank_account_transactions	suppliers.companyId = bank_account_transactions.companyId
bank_accounts	suppliers.companyId = bank_accounts.companyId

suppliers table schema

addresses

ARRAY

Click to expand addresses

city

STRING

country

STRING

line1

STRING

line2

STRING

postalCode

STRING

region

STRING

type

STRING

companyId

STRING

contactName

STRING

emailAddress

STRING

phone

STRING

registrationNumber

STRING

status

STRING

supplierName

STRING

taxNumber

STRING

tax_rates

The tax_rates table contains information about tax rates for a given company in your Codat account.

Replication Method	Key-based Incremental
Primary Keys	companyId id
Replication Key	modifiedDate
Useful links	Codat documentation tax_rates schema on GitHub Codat API method

tax_rates table foreign keys

Join tax_rates with	on
companies	tax_rates.companyId = companies.id
balance_sheets	tax_rates.companyId = balance_sheets.companyId
bank_statements	tax_rates.companyId = bank_statements.companyId
bank_statement_lines	tax_rates.companyId = bank_statement_lines.companyId
bill_payments	tax_rates.companyId = bill_payments.supplierRef.companyId tax_rates.companyId = bill_payments.companyId
bills	tax_rates.companyId = bills.companyId tax_rates.id = bills.lineItems.taxRateRef.id
company_info	tax_rates.companyId = company_info.companyId
customers	tax_rates.companyId = customers.companyId
invoices	tax_rates.companyId = invoices.companyId
payments	tax_rates.companyId = payments.companyId
profit_and_loss	tax_rates.companyId = profit_and_loss.companyId
suppliers	tax_rates.companyId = suppliers.companyId
connections	tax_rates.companyId = connections.companyId
events	tax_rates.companyId = events.companyId
items	tax_rates.companyId = items.companyId tax_rates.id = items.billItem.taxRateRef.id tax_rates.id = items.invoiceItem.taxRateRef.id
journal_entries	tax_rates.companyId = journal_entries.companyId
bank_account_transactions	tax_rates.companyId = bank_account_transactions.companyId
bank_accounts	tax_rates.companyId = bank_accounts.companyId

tax_rates table schema

code

STRING

companyId

STRING

components

ARRAY

Click to expand components

isCompound

BOOLEAN

name

STRING

rate

NUMBER

effectiveTaxRate

NUMBER

STRING

modifiedDate

STRING

name

STRING

sourceModifiedDate

STRING

totalTaxRate

NUMBER

Questions? Feedback?

Did this article help? If you have questions or feedback, feel free to submit a pull request with your suggestions, open an issue on GitHub, or reach out to us.

Related	Troubleshooting
Destination & Integration Compatibility Replication Scheduling Syncing Historical SaaS Data Resetting Replication Keys Nested Data Structures & Row Count Impact	Third-Party Downtime Understanding & Reducing Your Usage Re-Authorizing Integrations Replication Issues