JSData & Media Start a project

← All connectors

Schema · Entity relationship diagram

Ramp tables and relationships

Corporate cards, reimbursements and bill pay from Ramp: card transactions with their line splits and GL coding, reimbursements, bills and purchase orders with line items, receipts, vendors with spend totals, users, departments, cards, funds (spend limits), spend programs, statements, daily balances and the chart of accounts synced from your accounting system.

Live29 tables · 51 relationships
ConnectPaste access keys
Default schemaramp
Source API docsdocs.ramp.com
DestinationsAmazon Redshift, Google BigQuery, Snowflake, PostgreSQL, Amazon S3
  • All money columns are in major units (dollars); Ramp's minor-unit integers are converted.
  • Child tables are replaced as a whole when their parent is re-read, so removed or re-split lines disappear.
ACCOUNTINGBUSINESSreimbursements.merchant_id → merchants.idusers.business_id → business.idtransactions.card_id → cards.idtransactions.fund_id → funds.idtransactions.spend_program_id → spend_programs.idtransactions.statement_id → statements.idtransactions.entity_id → entities.idtransactions.card_holder_user_id → users.idtransactions.card_holder_department_id → departments.idtransactions.card_holder_location_id → locations.idtransactions.merchant_id → merchants.idtransaction_line_items.transaction_id → transactions.idtransaction_accounting_field_selections.transaction_id → transactions.idtransaction_accounting_field_selections.transaction_id,line_index → transaction_line_items.transaction_id,line_index (line_index is NULL (line_type 'header') for coding on the whole transaction.)reimbursements.user_id → users.idreimbursements.entity_id → entities.idreimbursements.fund_id → funds.idreimbursement_line_items.reimbursement_id → reimbursements.idreimbursement_accounting_field_selections.reimbursement_id → reimbursements.idbills.vendor_id → vendors.idbills.entity_id → entities.idbills.bill_owner_id → users.idbills.purchase_order_id → purchase_orders.idbill_line_items.bill_id → bills.idbill_line_items.purchase_order_line_item_id → purchase_order_line_items.idbill_accounting_field_selections.bill_id → bills.idpurchase_orders.vendor_id → vendors.idpurchase_orders.user_id → users.idpurchase_orders.entity_id → entities.idpurchase_orders.spend_program_id → spend_programs.idpurchase_order_line_items.purchase_order_id → purchase_orders.idpurchase_order_accounting_field_selections.purchase_order_id → purchase_orders.idreceipts.transaction_id → transactions.idreceipts.reimbursement_id → reimbursements.idreceipts.user_id → users.idusers.department_id → departments.idusers.location_id → locations.idusers.entity_id → entities.idusers.manager_id → users.idlocations.entity_id → entities.idcards.user_id → users.idcards.fund_id → funds.idfunds.spend_program_id → spend_programs.idfunds.entity_id → entities.idfund_members.fund_id → funds.idfund_members.user_id → users.idvendors.merchant_id → merchants.idtransaction_accounting_field_selections.external_id joins gl_accounts.id (For type GL_ACCOUNT selections, external_id is the accounting system's account id (gl_accounts.id); for custom fields it matches accounting_field_options.id.)bill_accounting_field_selections.external_id joins gl_accounts.idreimbursement_accounting_field_selections.external_id joins gl_accounts.idaccounting_field_options.accounting_field_ramp_id → accounting_fields.ramp_idtransaction_accounting_field_selections — accounting coding (GL account, class, department...) on the transaction (line_type header, line_index NULL) and on each linetransaction_accounting_field_selections18_jsdata_id VARCHAR(32) · primary key_jsdata_idvarchartransaction_id VARCHAR(64) · → transactions.id · → transaction_line_items.transaction_id,line_indextransaction_idvarcharline_index INTEGER · → transaction_line_items.transaction_id,line_indexline_indexintexternal_id VARCHAR(256) · → gl_accounts.idexternal_idvarcharline_type VARCHAR(32)line_typevarcharselection_index INTEGERselection_indexintid VARCHAR(64)idvarcharexternal_code VARCHAR(256)external_codevarcharname VARCHAR(512)namevarchardisplay_name VARCHAR(512)display_namevarchartype VARCHAR(64)typevarcharprovider_name VARCHAR(128)provider_namevarcharsource_type VARCHAR(64)source_typevarcharcategory_id VARCHAR(64)category_idvarcharcategory_external_id VARCHAR(256)category_external_idvarcharcategory_name VARCHAR(512)category_namevarcharcategory_type VARCHAR(64)category_typevarchar_jsdata_synced TIMESTAMP_jsdata_syncedtimestamptransaction_line_items — transaction line_items[] (amount split). upsert key transaction_id: a re-read transaction replaces its linestransaction_line_items10transaction_id VARCHAR(64) · primary key · → transactions.idtransaction_idvarcharline_index INTEGER · primary keyline_indexint_jsdata_id VARCHAR(32)_jsdata_idvarcharline_item_id VARCHAR(64)line_item_idvarcharamount DECIMAL(18,4)amountdecimalcurrency_code VARCHAR(8)currency_codevarcharconverted_amount DECIMAL(18,4)converted_amountdecimalconverted_currency_code VARCHAR(8)converted_currency_codevarcharmemo VARCHAR(4096)memovarchar_jsdata_synced TIMESTAMP_jsdata_syncedtimestampbills — bill pay (GET /developer/v1/bills, active + archived)bills54id VARCHAR(64) · primary keyidvarcharvendor_id VARCHAR(64) · → vendors.idvendor_idvarcharbill_owner_id VARCHAR(64) · → users.idbill_owner_idvarcharentity_id VARCHAR(64) · → entities.identity_idvarcharpurchase_order_id VARCHAR(64) · → purchase_orders.idpurchase_order_idvarcharinvoice_number VARCHAR(256)invoice_numbervarcharstatus VARCHAR(32)statusvarcharstatus_summary VARCHAR(64)status_summaryvarcharapproval_status VARCHAR(32)approval_statusvarcharsync_status VARCHAR(32)sync_statusvarcharis_archived BOOLEANis_archivedboolcreated_at TIMESTAMPcreated_attimestampissued_at TIMESTAMPissued_attimestampdue_at TIMESTAMPdue_attimestamppaid_at TIMESTAMPpaid_attimestampposting_date TIMESTAMPposting_datetimestampaccounting_date TIMESTAMPaccounting_datetimestamparchived_at TIMESTAMParchived_attimestampdraft_bill_created_at TIMESTAMPdraft_bill_created_attimestampdraft_bill_id VARCHAR(64)draft_bill_idvarcharamount DECIMAL(18,4)amountdecimalcurrency_code VARCHAR(8)currency_codevarcharfx_conversion_rate DECIMAL(18,8)fx_conversion_ratedecimalmemo VARCHAR(4096)memovarcharvendor_memo VARCHAR(4096)vendor_memovarcharvendor_name VARCHAR(512)vendor_namevarcharvendor_type VARCHAR(32)vendor_typevarcharvendor_remote_id VARCHAR(256)vendor_remote_idvarcharvendor_remote_code VARCHAR(256)vendor_remote_codevarcharvendor_remote_name VARCHAR(512)vendor_remote_namevarcharvendor_contact_id VARCHAR(64)vendor_contact_idvarcharbill_owner_first_name VARCHAR(256)bill_owner_first_namevarcharbill_owner_last_name VARCHAR(256)bill_owner_last_namevarcharremote_id VARCHAR(256)remote_idvarchardeep_link_url VARCHAR(1024)deep_link_urlvarcharenable_accounting_sync BOOLEANenable_accounting_syncboolis_payment_sync_ready BOOLEANis_payment_sync_readyboolis_withholding_release BOOLEANis_withholding_releaseboolpayment_id VARCHAR(64)payment_idvarcharpayment_amount DECIMAL(18,4)payment_amountdecimalpayment_currency_code VARCHAR(8)payment_currency_codevarcharpayment_method VARCHAR(64)payment_methodvarcharpayment_date TIMESTAMPpayment_datetimestamppayment_effective_date TIMESTAMPpayment_effective_datetimestampcustomer_friendly_payment_id VARCHAR(128)customer_friendly_payment_idvarcharpayment_trace_id VARCHAR(128)payment_trace_idvarcharpayment_trace_id_descriptor VARCHAR(256)payment_trace_id_descriptorvarcharpayment_details VARCHAR(65535)payment_detailsvarcharinvoice_urls VARCHAR(65535)invoice_urlsvarcharitem_receipt_ids VARCHAR(65535)item_receipt_idsvarcharapplied_vendor_credits VARCHAR(65535)applied_vendor_creditsvarcharline_item_count INTEGERline_item_countintinventory_line_item_count INTEGERinventory_line_item_countint_jsdata_synced TIMESTAMP_jsdata_syncedtimestamptransactions — card transactions (GET /developer/v1/transactions, state=ALL). amount = major units; declined rows kept, filter statetransactions58id VARCHAR(64) · primary keyidvarcharcard_id VARCHAR(64) · → cards.idcard_idvarcharfund_id VARCHAR(64) · → funds.idfund_idvarcharspend_program_id VARCHAR(64) · → spend_programs.idspend_program_idvarcharstatement_id VARCHAR(64) · → statements.idstatement_idvarcharentity_id VARCHAR(64) · → entities.identity_idvarcharcard_holder_user_id VARCHAR(64) · → users.idcard_holder_user_idvarcharcard_holder_department_id VARCHAR(64) · → departments.idcard_holder_department_idvarcharcard_holder_location_id VARCHAR(64) · → locations.idcard_holder_location_idvarcharmerchant_id VARCHAR(64) · → merchants.idmerchant_idvarcharuser_transaction_time TIMESTAMPuser_transaction_timetimestampsettlement_date TIMESTAMPsettlement_datetimestampaccounting_date TIMESTAMPaccounting_datetimestampupdated_at TIMESTAMPupdated_attimestampsynced_at TIMESTAMPsynced_attimestampstate VARCHAR(32)statevarcharamount DECIMAL(18,4)amountdecimalcurrency_code VARCHAR(8)currency_codevarcharoriginal_transaction_amount DECIMAL(18,4)original_transaction_amountdecimaloriginal_transaction_currency_code VARCHAR(8)original_transaction_currency_codevarcharmerchant_amount DECIMAL(18,4)merchant_amountdecimalmerchant_currency VARCHAR(8)merchant_currencyvarcharentity_amount DECIMAL(18,4)entity_amountdecimalentity_currency VARCHAR(8)entity_currencyvarcharcard_present BOOLEANcard_presentbooltrip_id VARCHAR(64)trip_idvarchartrip_name VARCHAR(512)trip_namevarcharcard_holder_first_name VARCHAR(256)card_holder_first_namevarcharcard_holder_last_name VARCHAR(256)card_holder_last_namevarcharcard_holder_employee_id VARCHAR(128)card_holder_employee_idvarcharcard_holder_department_name VARCHAR(256)card_holder_department_namevarcharcard_holder_location_name VARCHAR(256)card_holder_location_namevarcharmerchant_name VARCHAR(512)merchant_namevarcharmerchant_descriptor VARCHAR(512)merchant_descriptorvarcharmerchant_category_code VARCHAR(16)merchant_category_codevarcharmerchant_category_code_description VARCHAR(256)merchant_category_code_descriptionvarcharnetwork_merchant_id VARCHAR(128)network_merchant_idvarcharmerchant_location_city VARCHAR(256)merchant_location_cityvarcharmerchant_location_state VARCHAR(64)merchant_location_statevarcharmerchant_location_postal_code VARCHAR(32)merchant_location_postal_codevarcharmerchant_location_country VARCHAR(64)merchant_location_countryvarcharsk_category_id INTEGERsk_category_idintsk_category_name VARCHAR(256)sk_category_namevarcharmemo VARCHAR(4096)memovarcharsync_status VARCHAR(32)sync_statusvarcharall_requirements_met_and_approved BOOLEANall_requirements_met_and_approvedboolrequires_accounting_vendor_creation_to_sync BOOLEANrequires_accounting_vendor_creation_to_syncbooloriginal_transaction_id VARCHAR(64)original_transaction_idvarchardecline_reason VARCHAR(128)decline_reasonvarchardeclined_amount DECIMAL(18,4)declined_amountdecimalreceipt_ids VARCHAR(65535)receipt_idsvarcharreceipt_count INTEGERreceipt_countintattendees VARCHAR(65535)attendeesvarcharpolicy_violations VARCHAR(65535)policy_violationsvarchardisputes VARCHAR(65535)disputesvarcharaccounting_categories VARCHAR(65535)accounting_categoriesvarcharline_item_count INTEGERline_item_countint_jsdata_synced TIMESTAMP_jsdata_syncedtimestampreceipts — receipt images attached to transactions / reimbursements (metadata + URL)receipts7id VARCHAR(64) · primary keyidvarchartransaction_id VARCHAR(64) · → transactions.idtransaction_idvarcharreimbursement_id VARCHAR(64) · → reimbursements.idreimbursement_idvarcharuser_id VARCHAR(64) · → users.iduser_idvarcharcreated_at TIMESTAMPcreated_attimestampreceipt_url VARCHAR(2048)receipt_urlvarchar_jsdata_synced TIMESTAMP_jsdata_syncedtimestampbusiness — the Ramp business (one row)business14id VARCHAR(64) · primary keyidvarcharbusiness_name_legal VARCHAR(512)business_name_legalvarcharbusiness_name_on_card VARCHAR(512)business_name_on_cardvarcharactive BOOLEANactiveboolcreated_time TIMESTAMPcreated_timetimestampenforce_sso BOOLEANenforce_ssoboolinitial_approved_limit DECIMAL(18,4)initial_approved_limitdecimalis_integrated_with_slack BOOLEANis_integrated_with_slackboolis_reimbursements_enabled BOOLEANis_reimbursements_enabledboollimit_locked BOOLEANlimit_lockedboolphone VARCHAR(64)phonevarcharwebsite VARCHAR(1024)websitevarcharbilling_address VARCHAR(65535)billing_addressvarchar_jsdata_synced TIMESTAMP_jsdata_syncedtimestampstatements — card statements with opening/closing balancesstatements14id VARCHAR(64) · primary keyidvarcharstart_date TIMESTAMPstart_datetimestampend_date TIMESTAMPend_datetimestamppreceding_statement_id VARCHAR(64)preceding_statement_idvarcharstatement_url VARCHAR(2048)statement_urlvarcharopening_balance DECIMAL(18,4)opening_balancedecimalcharges DECIMAL(18,4)chargesdecimalcredits DECIMAL(18,4)creditsdecimalpayments DECIMAL(18,4)paymentsdecimalending_balance DECIMAL(18,4)ending_balancedecimalcurrency_code VARCHAR(8)currency_codevarcharbalance_sections VARCHAR(65535)balance_sectionsvarcharstatement_line_count INTEGERstatement_line_countint_jsdata_synced TIMESTAMP_jsdata_syncedtimestampgl_accounts — general ledger accounts synced from the accounting system (GET /developer/v1/accounting/accounts); key ramp_idgl_accounts16ramp_id VARCHAR(64) · primary keyramp_idvarcharid VARCHAR(256)idvarcharaccounting_connection_id VARCHAR(64)accounting_connection_idvarcharcode VARCHAR(128)codevarcharname VARCHAR(512)namevarcharclassification VARCHAR(64)classificationvarcharis_active BOOLEANis_activeboolvisibility VARCHAR(32)visibilityvarcharprovider_name VARCHAR(128)provider_namevarchargl_account_category_id VARCHAR(256)gl_account_category_idvarchargl_account_category_name VARCHAR(512)gl_account_category_namevarchargl_account_category_ramp_id VARCHAR(64)gl_account_category_ramp_idvarcharentity_remote_ids VARCHAR(65535)entity_remote_idsvarcharcreated_at TIMESTAMPcreated_attimestampupdated_at TIMESTAMPupdated_attimestamp_jsdata_synced TIMESTAMP_jsdata_syncedtimestampbill_accounting_field_selections — accounting coding on bills and their linesbill_accounting_field_selections18_jsdata_id VARCHAR(32) · primary key_jsdata_idvarcharbill_id VARCHAR(64) · → bills.idbill_idvarcharexternal_id VARCHAR(256) · → gl_accounts.idexternal_idvarcharline_type VARCHAR(32)line_typevarcharline_index INTEGERline_indexintselection_index INTEGERselection_indexintid VARCHAR(64)idvarcharexternal_code VARCHAR(256)external_codevarcharname VARCHAR(512)namevarchardisplay_name VARCHAR(512)display_namevarchartype VARCHAR(64)typevarcharprovider_name VARCHAR(128)provider_namevarcharsource_type VARCHAR(64)source_typevarcharcategory_id VARCHAR(64)category_idvarcharcategory_external_id VARCHAR(256)category_external_idvarcharcategory_name VARCHAR(512)category_namevarcharcategory_type VARCHAR(64)category_typevarchar_jsdata_synced TIMESTAMP_jsdata_syncedtimestampentities — business entities (subsidiaries); accounts = GL mapping of the entity's Ramp accountsentities13id VARCHAR(64) · primary keyidvarcharentity_name VARCHAR(512)entity_namevarcharcurrency VARCHAR(8)currencyvarcharis_primary BOOLEANis_primaryboolconnected_subsidiary_id VARCHAR(256)connected_subsidiary_idvarcharconnected_subsidiary_name VARCHAR(512)connected_subsidiary_namevarcharconnected_subsidiary_external_id VARCHAR(256)connected_subsidiary_external_idvarchardefault_bill_pay_payment_account_id VARCHAR(64)default_bill_pay_payment_account_idvarcharlocation_ids VARCHAR(65535)location_idsvarcharaccounts VARCHAR(65535)accountsvarcharpayment_accounts VARCHAR(65535)payment_accountsvarcharcustom_record_fields VARCHAR(65535)custom_record_fieldsvarchar_jsdata_synced TIMESTAMP_jsdata_syncedtimestampdepartments — departmentsdepartments3id VARCHAR(64) · primary keyidvarcharname VARCHAR(512)namevarchar_jsdata_synced TIMESTAMP_jsdata_syncedtimestampvendors — vendors (GET /developer/v1/vendors) with Ramp spend totalsvendors40id VARCHAR(64) · primary keyidvarcharmerchant_id VARCHAR(64) · → merchants.idmerchant_idvarcharname VARCHAR(512)namevarcharname_legal VARCHAR(512)name_legalvarchardescription VARCHAR(4096)descriptionvarcharvendor_type VARCHAR(32)vendor_typevarcharstate VARCHAR(64)statevarcharis_active BOOLEANis_activeboolis_deletable BOOLEANis_deletableboolcountry VARCHAR(64)countryvarcharbilling_frequency VARCHAR(32)billing_frequencyvarcharfederal_tax_classification VARCHAR(64)federal_tax_classificationvarcharparent_vendor_id VARCHAR(64)parent_vendor_idvarchardefault_entity_id VARCHAR(64)default_entity_idvarcharvendor_owner_id VARCHAR(64)vendor_owner_idvarcharexternal_vendor_id VARCHAR(256)external_vendor_idvarcharaccounting_vendor_remote_id VARCHAR(256)accounting_vendor_remote_idvarcharsk_category_id INTEGERsk_category_idintsk_category_name VARCHAR(256)sk_category_namevarcharcreated_at TIMESTAMPcreated_attimestampcontact_ids VARCHAR(65535)contact_idsvarcharsubsidiary VARCHAR(65535)subsidiaryvarcharaddress_address_line_1 VARCHAR(512)address_address_line_1varcharaddress_address_line_2 VARCHAR(512)address_address_line_2varcharaddress_city VARCHAR(256)address_cityvarcharaddress_state VARCHAR(64)address_statevarcharaddress_postal_code VARCHAR(32)address_postal_codevarcharaddress_country VARCHAR(64)address_countryvarchartax_address_address_line_1 VARCHAR(512)tax_address_address_line_1varchartax_address_address_line_2 VARCHAR(512)tax_address_address_line_2varchartax_address_city VARCHAR(256)tax_address_cityvarchartax_address_state VARCHAR(64)tax_address_statevarchartax_address_postal_code VARCHAR(32)tax_address_postal_codevarchartax_address_country VARCHAR(64)tax_address_countryvarchartotal_spend_all_time DECIMAL(18,4)total_spend_all_timedecimaltotal_spend_last_30_days DECIMAL(18,4)total_spend_last_30_daysdecimaltotal_spend_last_365_days DECIMAL(18,4)total_spend_last_365_daysdecimaltotal_spend_ytd DECIMAL(18,4)total_spend_ytddecimaltotal_spend_currency_code VARCHAR(8)total_spend_currency_codevarchar_jsdata_synced TIMESTAMP_jsdata_syncedtimestampusers — Ramp users (employees), including suspended onesusers18id VARCHAR(64) · primary keyidvarcharmanager_id VARCHAR(64) · → users.idmanager_idvarchardepartment_id VARCHAR(64) · → departments.iddepartment_idvarcharlocation_id VARCHAR(64) · → locations.idlocation_idvarcharentity_id VARCHAR(64) · → entities.identity_idvarcharbusiness_id VARCHAR(64) · → business.idbusiness_idvarcharfirst_name VARCHAR(256)first_namevarcharlast_name VARCHAR(256)last_namevarcharemail VARCHAR(320)emailvarcharphone VARCHAR(64)phonevarcharemployee_id VARCHAR(128)employee_idvarcharrole VARCHAR(64)rolevarcharstatus VARCHAR(32)statusvarcharis_manager BOOLEANis_managerboolscheduled_deactivation_date TIMESTAMPscheduled_deactivation_datetimestampscheduled_invitation_date TIMESTAMPscheduled_invitation_datetimestampcustom_fields VARCHAR(65535)custom_fieldsvarchar_jsdata_synced TIMESTAMP_jsdata_syncedtimestamplocations — locationslocations4id VARCHAR(64) · primary keyidvarcharentity_id VARCHAR(64) · → entities.identity_idvarcharname VARCHAR(512)namevarchar_jsdata_synced TIMESTAMP_jsdata_syncedtimestampcards — physical + virtual cards (active and terminated). Only last_four; no card number, expiry, or shipping streetcards24id VARCHAR(64) · primary keyidvarcharuser_id VARCHAR(64) · → users.iduser_idvarcharfund_id VARCHAR(64) · → funds.idfund_idvarcharcard_type VARCHAR(16)card_typevarchardisplay_name VARCHAR(512)display_namevarcharlast_four VARCHAR(4)last_fourvarcharcardholder_name VARCHAR(256)cardholder_namevarcharstate VARCHAR(32)statevarcharis_physical BOOLEANis_physicalboolis_primary BOOLEANis_primaryboolis_suspended BOOLEANis_suspendedboolis_terminated BOOLEANis_terminatedboolhas_program_overridden BOOLEANhas_program_overriddenboolautomatic_routing_enabled BOOLEANautomatic_routing_enabledboolcreated_at TIMESTAMPcreated_attimestampfulfillment_status VARCHAR(64)fulfillment_statusvarcharshipping_date TIMESTAMPshipping_datetimestampshipping_eta TIMESTAMPshipping_etatimestampshipping_method VARCHAR(64)shipping_methodvarcharshipping_tracking_url VARCHAR(1024)shipping_tracking_urlvarcharshipping_city VARCHAR(256)shipping_cityvarcharshipping_state VARCHAR(64)shipping_statevarcharshipping_country VARCHAR(64)shipping_countryvarchar_jsdata_synced TIMESTAMP_jsdata_syncedtimestampfund_members — users who can spend from each fund; key fund_idfund_members7fund_id VARCHAR(64) · primary key · → funds.idfund_idvarcharuser_id VARCHAR(64) · primary key · → users.iduser_idvarchar_jsdata_id VARCHAR(32)_jsdata_idvarcharsuspended_at TIMESTAMPsuspended_attimestampsuspended_by_ramp BOOLEANsuspended_by_rampboolsuspended_by_user_id VARCHAR(64)suspended_by_user_idvarchar_jsdata_synced TIMESTAMP_jsdata_syncedtimestampmerchants — merchants seen on card spendmerchants5id VARCHAR(64) · primary keyidvarcharmerchant_name VARCHAR(512)merchant_namevarcharsk_category_name VARCHAR(256)sk_category_namevarcharis_auto_approved BOOLEANis_auto_approvedbool_jsdata_synced TIMESTAMP_jsdata_syncedtimestampbill_line_items — bill line_items[] (line_type line_item) and inventory_line_items[] (line_type inventory_line_item); key bill_idbill_line_items16bill_id VARCHAR(64) · primary key · → bills.idbill_idvarcharline_type VARCHAR(32) · primary keyline_typevarcharline_index INTEGER · primary keyline_indexintpurchase_order_line_item_id VARCHAR(64) · → purchase_order_line_items.idpurchase_order_line_item_idvarchar_jsdata_id VARCHAR(32)_jsdata_idvarcharamount DECIMAL(18,4)amountdecimalcurrency_code VARCHAR(8)currency_codevarcharmemo VARCHAR(4096)memovarcharquantity DECIMAL(18,4)quantitydecimalunit_price DECIMAL(18,4)unit_pricedecimalunit_price_currency_code VARCHAR(8)unit_price_currency_codevarcharitem_receipt_line_item_ids VARCHAR(65535)item_receipt_line_item_idsvarcharwithholding_gross_amount DECIMAL(18,4)withholding_gross_amountdecimalwithholding_amount DECIMAL(18,4)withholding_amountdecimalwithholding_percentage DECIMAL(18,6)withholding_percentagedecimal_jsdata_synced TIMESTAMP_jsdata_syncedtimestamppurchase_orders — purchase orders (GET /developer/v1/purchase-orders, include_archived)purchase_orders44id VARCHAR(64) · primary keyidvarcharentity_id VARCHAR(64) · → entities.identity_idvarcharvendor_id VARCHAR(64) · → vendors.idvendor_idvarcharuser_id VARCHAR(64) · → users.iduser_idvarcharspend_program_id VARCHAR(64) · → spend_programs.idspend_program_idvarcharpurchase_order_number VARCHAR(128)purchase_order_numbervarcharname VARCHAR(512)namevarcharmemo VARCHAR(4096)memovarcharcreated_at TIMESTAMPcreated_attimestamparchived_at TIMESTAMParchived_attimestamppromise_date TIMESTAMPpromise_datetimestampspend_start_date TIMESTAMPspend_start_datetimestampspend_end_date TIMESTAMPspend_end_datetimestampamount DECIMAL(18,4)amountdecimalcurrency_code VARCHAR(8)currency_codevarcharspend_total_amount DECIMAL(18,4)spend_total_amountdecimalspend_total_currency_code VARCHAR(8)spend_total_currency_codevarcharbilling_status VARCHAR(64)billing_statusvarcharreceipt_status VARCHAR(64)receipt_statusvarcharcreation_source VARCHAR(64)creation_sourcevarcharowner_id VARCHAR(64)owner_idvarcharspend_request_id VARCHAR(64)spend_request_idvarcharexternal_id VARCHAR(256)external_idvarcharremote_id VARCHAR(256)remote_idvarcharnet_payment_terms INTEGERnet_payment_termsintthree_way_match_enabled BOOLEANthree_way_match_enabledboolwithholding_default_rate DECIMAL(18,6)withholding_default_ratedecimalramp_url VARCHAR(1024)ramp_urlvarcharship_to_company_name VARCHAR(512)ship_to_company_namevarcharship_to_address1 VARCHAR(512)ship_to_address1varcharship_to_address2 VARCHAR(512)ship_to_address2varcharship_to_city VARCHAR(256)ship_to_cityvarcharship_to_state VARCHAR(64)ship_to_statevarcharship_to_postal_code VARCHAR(32)ship_to_postal_codevarcharship_to_country VARCHAR(64)ship_to_countryvarcharshipping_contact_first_name VARCHAR(256)shipping_contact_first_namevarcharshipping_contact_last_name VARCHAR(256)shipping_contact_last_namevarcharshipping_contact_email VARCHAR(320)shipping_contact_emailvarcharshipping_contact_phone_number VARCHAR(64)shipping_contact_phone_numbervarcharbill_ids VARCHAR(65535)bill_idsvarchartransaction_ids VARCHAR(65535)transaction_idsvarcharitem_receipt_ids VARCHAR(65535)item_receipt_idsvarcharline_item_count INTEGERline_item_countint_jsdata_synced TIMESTAMP_jsdata_syncedtimestampspend_programs — spend programs (fund templates)spend_programs21id VARCHAR(64) · primary keyidvarchardisplay_name VARCHAR(512)display_namevarchardescription VARCHAR(4096)descriptionvarcharicon VARCHAR(64)iconvarcharis_shareable BOOLEANis_shareableboolissue_physical_card_if_needed BOOLEANissue_physical_card_if_neededboolprimary_card_enabled BOOLEANprimary_card_enabledboolreimbursements_enabled BOOLEANreimbursements_enabledboollimit_amount DECIMAL(18,4)limit_amountdecimallimit_currency_code VARCHAR(8)limit_currency_codevarcharinterval VARCHAR(32)intervalvarchartemporary_limit_amount DECIMAL(18,4)temporary_limit_amountdecimaltransaction_amount_limit DECIMAL(18,4)transaction_amount_limitdecimalauto_lock_date TIMESTAMPauto_lock_datetimestampnext_interval_reset TIMESTAMPnext_interval_resettimestampstart_of_interval TIMESTAMPstart_of_intervaltimestampallowed_categories VARCHAR(65535)allowed_categoriesvarcharblocked_categories VARCHAR(65535)blocked_categoriesvarcharallowed_vendors VARCHAR(65535)allowed_vendorsvarcharblocked_vendors VARCHAR(65535)blocked_vendorsvarchar_jsdata_synced TIMESTAMP_jsdata_syncedtimestampfunds — funds (spend limits): limit, interval, restrictions and current balancefunds35id VARCHAR(64) · primary keyidvarcharentity_id VARCHAR(64) · → entities.identity_idvarcharspend_program_id VARCHAR(64) · → spend_programs.idspend_program_idvarchardisplay_name VARCHAR(512)display_namevarcharstate VARCHAR(32)statevarcharis_terminated BOOLEANis_terminatedboolcreated_at TIMESTAMPcreated_attimestampis_shareable BOOLEANis_shareablebooloverrides_spend_program BOOLEANoverrides_spend_programboolis_exempt_from_policy_agent BOOLEANis_exempt_from_policy_agentboolphysical_card_enabled BOOLEANphysical_card_enabledboolvirtual_card_enabled BOOLEANvirtual_card_enabledboolreimbursements_enabled BOOLEANreimbursements_enabledboollimit_amount DECIMAL(18,4)limit_amountdecimallimit_currency_code VARCHAR(8)limit_currency_codevarcharinterval VARCHAR(32)intervalvarchartemporary_limit_amount DECIMAL(18,4)temporary_limit_amountdecimaltransaction_amount_limit DECIMAL(18,4)transaction_amount_limitdecimalauto_lock_date TIMESTAMPauto_lock_datetimestampnext_interval_resets_at TIMESTAMPnext_interval_resets_attimestampstart_of_interval TIMESTAMPstart_of_intervaltimestampbalance_cleared DECIMAL(18,4)balance_cleareddecimalbalance_pending DECIMAL(18,4)balance_pendingdecimalbalance_total DECIMAL(18,4)balance_totaldecimalbalance_currency_code VARCHAR(8)balance_currency_codevarcharallowed_category_codes VARCHAR(65535)allowed_category_codesvarcharblocked_category_codes VARCHAR(65535)blocked_category_codesvarcharallowed_vendor_ids VARCHAR(65535)allowed_vendor_idsvarcharblocked_vendor_ids VARCHAR(65535)blocked_vendor_idsvarcharsuspended_at TIMESTAMPsuspended_attimestampsuspended_by_ramp BOOLEANsuspended_by_rampboolsuspended_by_user_id VARCHAR(64)suspended_by_user_idvarcharcard_ids VARCHAR(65535)card_idsvarcharmember_count INTEGERmember_countint_jsdata_synced TIMESTAMP_jsdata_syncedtimestamppurchase_order_accounting_field_selections — accounting coding on purchase orders and their linespurchase_order_accounting_field_selections18_jsdata_id VARCHAR(32) · primary key_jsdata_idvarcharpurchase_order_id VARCHAR(64) · → purchase_orders.idpurchase_order_idvarcharline_type VARCHAR(32)line_typevarcharline_index INTEGERline_indexintselection_index INTEGERselection_indexintid VARCHAR(64)idvarcharexternal_id VARCHAR(256)external_idvarcharexternal_code VARCHAR(256)external_codevarcharname VARCHAR(512)namevarchardisplay_name VARCHAR(512)display_namevarchartype VARCHAR(64)typevarcharprovider_name VARCHAR(128)provider_namevarcharsource_type VARCHAR(64)source_typevarcharcategory_id VARCHAR(64)category_idvarcharcategory_external_id VARCHAR(256)category_external_idvarcharcategory_name VARCHAR(512)category_namevarcharcategory_type VARCHAR(64)category_typevarchar_jsdata_synced TIMESTAMP_jsdata_syncedtimestampreimbursement_line_items — reimbursement line_items[]; key reimbursement_idreimbursement_line_items7reimbursement_id VARCHAR(64) · primary key · → reimbursements.idreimbursement_idvarcharline_index INTEGER · primary keyline_indexint_jsdata_id VARCHAR(32)_jsdata_idvarcharamount DECIMAL(18,4)amountdecimalcurrency_code VARCHAR(8)currency_codevarcharmemo VARCHAR(4096)memovarchar_jsdata_synced TIMESTAMP_jsdata_syncedtimestampreimbursements — reimbursements (GET /developer/v1/reimbursements), incremental on updated_afterreimbursements43id VARCHAR(64) · primary keyidvarcharuser_id VARCHAR(64) · → users.iduser_idvarcharmerchant_id VARCHAR(64) · → merchants.idmerchant_idvarcharentity_id VARCHAR(64) · → entities.identity_idvarcharfund_id VARCHAR(64) · → funds.idfund_idvarcharuser_full_name VARCHAR(256)user_full_namevarcharuser_email VARCHAR(320)user_emailvarcharemployee_id VARCHAR(128)employee_idvarcharcreated_at TIMESTAMPcreated_attimestampsubmitted_at TIMESTAMPsubmitted_attimestampapproved_at TIMESTAMPapproved_attimestampupdated_at TIMESTAMPupdated_attimestamptransaction_date DATEtransaction_datedateaccounting_date TIMESTAMPaccounting_datetimestampsynced_at TIMESTAMPsynced_attimestamppayment_processed_at TIMESTAMPpayment_processed_attimestampstate VARCHAR(64)statevarchartype VARCHAR(32)typevarchardirection VARCHAR(32)directionvarcharamount DECIMAL(18,4)amountdecimalcurrency VARCHAR(8)currencyvarcharmerchant_amount DECIMAL(18,4)merchant_amountdecimalmerchant_currency VARCHAR(8)merchant_currencyvarcharentity_amount DECIMAL(18,4)entity_amountdecimalentity_currency VARCHAR(8)entity_currencyvarcharpayee_amount DECIMAL(18,4)payee_amountdecimalpayee_currency_code VARCHAR(8)payee_currency_codevarcharmerchant VARCHAR(512)merchantvarcharmemo VARCHAR(4096)memovarchartrip_id VARCHAR(64)trip_idvarcharpayment_id VARCHAR(64)payment_idvarcharpayment_batch_id VARCHAR(64)payment_batch_idvarchardistance DECIMAL(18,4)distancedecimalstart_location VARCHAR(1024)start_locationvarcharend_location VARCHAR(1024)end_locationvarcharwaypoints VARCHAR(65535)waypointsvarcharsync_status VARCHAR(32)sync_statusvarchartrace_id VARCHAR(128)trace_idvarchartrace_id_descriptor VARCHAR(256)trace_id_descriptorvarcharreceipt_ids VARCHAR(65535)receipt_idsvarcharattendees VARCHAR(65535)attendeesvarcharline_item_count INTEGERline_item_countint_jsdata_synced TIMESTAMP_jsdata_syncedtimestampreimbursement_accounting_field_selections — accounting coding on reimbursements and their linesreimbursement_accounting_field_selections18_jsdata_id VARCHAR(32) · primary key_jsdata_idvarcharreimbursement_id VARCHAR(64) · → reimbursements.idreimbursement_idvarcharexternal_id VARCHAR(256) · → gl_accounts.idexternal_idvarcharline_type VARCHAR(32)line_typevarcharline_index INTEGERline_indexintselection_index INTEGERselection_indexintid VARCHAR(64)idvarcharexternal_code VARCHAR(256)external_codevarcharname VARCHAR(512)namevarchardisplay_name VARCHAR(512)display_namevarchartype VARCHAR(64)typevarcharprovider_name VARCHAR(128)provider_namevarcharsource_type VARCHAR(64)source_typevarcharcategory_id VARCHAR(64)category_idvarcharcategory_external_id VARCHAR(256)category_external_idvarcharcategory_name VARCHAR(512)category_namevarcharcategory_type VARCHAR(64)category_typevarchar_jsdata_synced TIMESTAMP_jsdata_syncedtimestamppurchase_order_line_items — purchase order line_items[]; key purchase_order_idpurchase_order_line_items16purchase_order_id VARCHAR(64) · primary key · → purchase_orders.idpurchase_order_idvarcharline_index INTEGER · primary keyline_indexintid VARCHAR(64)idvarchar_jsdata_id VARCHAR(32)_jsdata_idvarchardescription VARCHAR(4096)descriptionvarcharquantity DECIMAL(18,4)quantitydecimalunit_price DECIMAL(18,4)unit_pricedecimalunit_price_currency_code VARCHAR(8)unit_price_currency_codevarcharamount DECIMAL(18,4)amountdecimalcurrency_code VARCHAR(8)currency_codevarcharstart_date DATEstart_datedateend_date DATEend_datedateexternal_id VARCHAR(256)external_idvarcharremote_id VARCHAR(256)remote_idvarcharwithholding_rate DECIMAL(18,6)withholding_ratedecimal_jsdata_synced TIMESTAMP_jsdata_syncedtimestampaccounting_field_options — options of each custom accounting field; key ramp_idaccounting_field_options14ramp_id VARCHAR(64) · primary keyramp_idvarcharaccounting_field_ramp_id VARCHAR(64) · → accounting_fields.ramp_idaccounting_field_ramp_idvarcharid VARCHAR(256)idvarcharaccounting_connection_id VARCHAR(64)accounting_connection_idvarcharcode VARCHAR(128)codevarcharvalue VARCHAR(1024)valuevarchardisplay_name VARCHAR(1024)display_namevarcharis_active BOOLEANis_activeboolvisibility VARCHAR(32)visibilityvarcharprovider_name VARCHAR(128)provider_namevarcharentity_remote_ids VARCHAR(65535)entity_remote_idsvarcharcreated_at TIMESTAMPcreated_attimestampupdated_at TIMESTAMPupdated_attimestamp_jsdata_synced TIMESTAMP_jsdata_syncedtimestampbusiness_balances — daily snapshot of GET /developer/v1/business/balance (limits and balances, major units); key as_of_date (UTC)business_balances18as_of_date DATE · primary keyas_of_datedatecaptured_at TIMESTAMPcaptured_attimestampnext_billing_date DATEnext_billing_datedateprev_billing_date DATEprev_billing_datedatecard_limit DECIMAL(18,4)card_limitdecimalavailable_card_limit DECIMAL(18,4)available_card_limitdecimalbalance_including_pending DECIMAL(18,4)balance_including_pendingdecimalcard_balance_including_pending DECIMAL(18,4)card_balance_including_pendingdecimalcard_balance_excluding_pending DECIMAL(18,4)card_balance_excluding_pendingdecimalstatement_balance DECIMAL(18,4)statement_balancedecimalflex_limit DECIMAL(18,4)flex_limitdecimalavailable_flex_limit DECIMAL(18,4)available_flex_limitdecimalflex_balance DECIMAL(18,4)flex_balancedecimalfloat_balance_excluding_pending DECIMAL(18,4)float_balance_excluding_pendingdecimalglobal_limit DECIMAL(18,4)global_limitdecimalmax_balance DECIMAL(18,4)max_balancedecimalcurrency_code VARCHAR(8)currency_codevarchar_jsdata_synced TIMESTAMP_jsdata_syncedtimestampaccounting_fields — custom accounting fields (class, department, project...); key ramp_idaccounting_fields13ramp_id VARCHAR(64) · primary keyramp_idvarcharid VARCHAR(256)idvarcharaccounting_connection_id VARCHAR(64)accounting_connection_idvarcharname VARCHAR(512)namevarchardisplay_name VARCHAR(512)display_namevarcharinput_type VARCHAR(32)input_typevarcharis_active BOOLEANis_activeboolis_splittable BOOLEANis_splittableboolis_required_for VARCHAR(65535)is_required_forvarcharprovider_name VARCHAR(128)provider_namevarcharcreated_at TIMESTAMPcreated_attimestampupdated_at TIMESTAMPupdated_attimestamp_jsdata_synced TIMESTAMP_jsdata_syncedtimestamp
Primary keyForeign keyReferencesJoins on (not a key)

Drag to pan, scroll or pinch to zoom, click a table to see what it joins to.

Table reference

29 tables · click a table for its columns
transactions58 columns · Card spendshow in diagram

One row per card transaction, including declined ones (filter state <> 'DECLINED' for spend). amount is in the card currency, major units.

Primary key: id

ColumnTypeKeyDescription
idPrimary key.varchar(64)PK Primary key.
user_transaction_timetimestamp
settlement_datetimestamp
accounting_datetimestamp
updated_attimestamp
synced_attimestamp
stateCLEARED, PENDING, DECLINED, ... Declined transactions are kept.varchar(32)CLEARED, PENDING, DECLINED, ... Declined transactions are kept.
amountTransaction amount in currency_code, major units (dollars).decimal(18,4)Transaction amount in currency_code, major units (dollars).
currency_codevarchar(8)
original_transaction_amountdecimal(18,4)
original_transaction_currency_codevarchar(8)
merchant_amountdecimal(18,4)
merchant_currencyvarchar(8)
entity_amountdecimal(18,4)
entity_currencyvarchar(8)
card_idReferences cards.id.varchar(64)FKReferences cards.id.
card_presentboolean
fund_idReferences funds.id.varchar(64)FKReferences funds.id.
spend_program_idReferences spend_programs.id.varchar(64)FKReferences spend_programs.id.
statement_idReferences statements.id.varchar(64)FKReferences statements.id.
entity_idReferences entities.id.varchar(64)FKReferences entities.id.
trip_idvarchar(64)
trip_namevarchar(512)
card_holder_user_idReferences users.id.varchar(64)FKReferences users.id.
card_holder_first_namevarchar(256)
card_holder_last_namevarchar(256)
card_holder_employee_idvarchar(128)
card_holder_department_idReferences departments.id.varchar(64)FKReferences departments.id.
card_holder_department_namevarchar(256)
card_holder_location_idReferences locations.id.varchar(64)FKReferences locations.id.
card_holder_location_namevarchar(256)
merchant_idReferences merchants.id.varchar(64)FKReferences merchants.id.
merchant_namevarchar(512)
merchant_descriptorvarchar(512)
merchant_category_codevarchar(16)
merchant_category_code_descriptionvarchar(256)
network_merchant_idvarchar(128)
merchant_location_cityvarchar(256)
merchant_location_statevarchar(64)
merchant_location_postal_codevarchar(32)
merchant_location_countryvarchar(64)
sk_category_idinteger
sk_category_namevarchar(256)
memovarchar(4096)
sync_statusvarchar(32)
all_requirements_met_and_approvedboolean
requires_accounting_vendor_creation_to_syncboolean
original_transaction_idvarchar(64)
decline_reasonvarchar(128)
declined_amountdecimal(18,4)
receipt_idsvarchar(65535)
receipt_countinteger
attendeesvarchar(65535)
policy_violationsvarchar(65535)
disputesvarchar(65535)
accounting_categoriesvarchar(65535)
line_item_countinteger
_jsdata_syncedWhen jsdata last wrote this row.timestampWhen jsdata last wrote this row.
transaction_line_items10 columns · Card spendshow in diagram

How a transaction is split. amount is in the original (merchant) currency; converted_amount is in the transaction currency.

Primary key: transaction_id, line_index

ColumnTypeKeyDescription
_jsdata_idRow id: md5 of the natural key.varchar(32)Row id: md5 of the natural key.
transaction_idPrimary key (with line_index). References transactions.id.varchar(64)PK FKPrimary key (with line_index). References transactions.id.
line_indexPrimary key (with transaction_id).integerPK Primary key (with transaction_id).
line_item_idvarchar(64)
amountdecimal(18,4)
currency_codevarchar(8)
converted_amountdecimal(18,4)
converted_currency_codevarchar(8)
memovarchar(4096)
_jsdata_syncedWhen jsdata last wrote this row.timestampWhen jsdata last wrote this row.
transaction_accounting_field_selections18 columns · Card spendshow in diagram

The accounting coding (GL account, class, department, project...) on a transaction (line_type header) or on each line.

Primary key: _jsdata_id

ColumnTypeKeyDescription
_jsdata_idPrimary key.varchar(32)PK Primary key.
transaction_idReferences transactions.id. References transaction_line_items.transaction_id,line_index (line_index is NULL (line_type 'header') for coding on the whole transaction.).varchar(64)FKReferences transactions.id. References transaction_line_items.transaction_id,line_index (line_index is NULL (line_type 'header') for coding on the whole transaction.).
line_typevarchar(32)
line_indexReferences transaction_line_items.transaction_id,line_index (line_index is NULL (line_type 'header') for coding on the whole transaction.).integerFKReferences transaction_line_items.transaction_id,line_index (line_index is NULL (line_type 'header') for coding on the whole transaction.).
selection_indexinteger
idvarchar(64)
external_idJoins to gl_accounts.id (For type GL_ACCOUNT selections, external_id is the accounting system's account id (gl_accounts.id); for custom fields it matches accounting_field_options.id.).varchar(256)FKJoins to gl_accounts.id (For type GL_ACCOUNT selections, external_id is the accounting system's account id (gl_accounts.id); for custom fields it matches accounting_field_options.id.).
external_codevarchar(256)
namevarchar(512)
display_namevarchar(512)
typeGL_ACCOUNT, CLASS, DEPARTMENT, MERCHANT, ... (the accounting system's field).varchar(64)GL_ACCOUNT, CLASS, DEPARTMENT, MERCHANT, ... (the accounting system's field).
provider_namevarchar(128)
source_typevarchar(64)
category_idvarchar(64)
category_external_idvarchar(256)
category_namevarchar(512)
category_typevarchar(64)
_jsdata_syncedWhen jsdata last wrote this row.timestampWhen jsdata last wrote this row.
receipts7 columns · Card spendshow in diagram

receipt images attached to transactions / reimbursements (metadata + URL)

Primary key: id

ColumnTypeKeyDescription
idPrimary key.varchar(64)PK Primary key.
transaction_idReferences transactions.id.varchar(64)FKReferences transactions.id.
reimbursement_idReferences reimbursements.id.varchar(64)FKReferences reimbursements.id.
user_idReferences users.id.varchar(64)FKReferences users.id.
created_attimestamp
receipt_urlvarchar(2048)
_jsdata_syncedWhen jsdata last wrote this row.timestampWhen jsdata last wrote this row.
merchants5 columns · Card spendshow in diagram

merchants seen on card spend

Primary key: id

ColumnTypeKeyDescription
idPrimary key.varchar(64)PK Primary key.
merchant_namevarchar(512)
sk_category_namevarchar(256)
is_auto_approvedboolean
_jsdata_syncedWhen jsdata last wrote this row.timestampWhen jsdata last wrote this row.
statements14 columns · Card spendshow in diagram

card statements with opening/closing balances

Primary key: id

ColumnTypeKeyDescription
idPrimary key.varchar(64)PK Primary key.
start_datetimestamp
end_datetimestamp
preceding_statement_idvarchar(64)
statement_urlvarchar(2048)
opening_balancedecimal(18,4)
chargesdecimal(18,4)
creditsdecimal(18,4)
paymentsdecimal(18,4)
ending_balancedecimal(18,4)
currency_codevarchar(8)
balance_sectionsvarchar(65535)
statement_line_countinteger
_jsdata_syncedWhen jsdata last wrote this row.timestampWhen jsdata last wrote this row.
reimbursements43 columns · Reimbursementsshow in diagram

reimbursements (GET /developer/v1/reimbursements), incremental on updated_after

Primary key: id

ColumnTypeKeyDescription
idPrimary key.varchar(64)PK Primary key.
user_idReferences users.id.varchar(64)FKReferences users.id.
user_full_namevarchar(256)
user_emailvarchar(320)
employee_idvarchar(128)
created_attimestamp
submitted_attimestamp
approved_attimestamp
updated_attimestamp
transaction_datedate
accounting_datetimestamp
synced_attimestamp
payment_processed_attimestamp
statevarchar(64)
typevarchar(32)
directionvarchar(32)
amountdecimal(18,4)
currencyvarchar(8)
merchant_amountdecimal(18,4)
merchant_currencyvarchar(8)
entity_amountdecimal(18,4)
entity_currencyvarchar(8)
payee_amountdecimal(18,4)
payee_currency_codevarchar(8)
merchantvarchar(512)
merchant_idReferences merchants.id.varchar(64)FKReferences merchants.id.
memovarchar(4096)
entity_idReferences entities.id.varchar(64)FKReferences entities.id.
fund_idReferences funds.id.varchar(64)FKReferences funds.id.
trip_idvarchar(64)
payment_idvarchar(64)
payment_batch_idvarchar(64)
distancedecimal(18,4)
start_locationvarchar(1024)
end_locationvarchar(1024)
waypointsvarchar(65535)
sync_statusvarchar(32)
trace_idvarchar(128)
trace_id_descriptorvarchar(256)
receipt_idsvarchar(65535)
attendeesvarchar(65535)
line_item_countinteger
_jsdata_syncedWhen jsdata last wrote this row.timestampWhen jsdata last wrote this row.
reimbursement_line_items7 columns · Reimbursementsshow in diagram

reimbursement line_items[]; key reimbursement_id

Primary key: reimbursement_id, line_index

ColumnTypeKeyDescription
_jsdata_idRow id: md5 of the natural key.varchar(32)Row id: md5 of the natural key.
reimbursement_idPrimary key (with line_index). References reimbursements.id.varchar(64)PK FKPrimary key (with line_index). References reimbursements.id.
line_indexPrimary key (with reimbursement_id).integerPK Primary key (with reimbursement_id).
amountdecimal(18,4)
currency_codevarchar(8)
memovarchar(4096)
_jsdata_syncedWhen jsdata last wrote this row.timestampWhen jsdata last wrote this row.
reimbursement_accounting_field_selections18 columns · Reimbursementsshow in diagram

accounting coding on reimbursements and their lines

Primary key: _jsdata_id

ColumnTypeKeyDescription
_jsdata_idPrimary key.varchar(32)PK Primary key.
reimbursement_idReferences reimbursements.id.varchar(64)FKReferences reimbursements.id.
line_typevarchar(32)
line_indexinteger
selection_indexinteger
idvarchar(64)
external_idJoins to gl_accounts.id.varchar(256)FKJoins to gl_accounts.id.
external_codevarchar(256)
namevarchar(512)
display_namevarchar(512)
typevarchar(64)
provider_namevarchar(128)
source_typevarchar(64)
category_idvarchar(64)
category_external_idvarchar(256)
category_namevarchar(512)
category_typevarchar(64)
_jsdata_syncedWhen jsdata last wrote this row.timestampWhen jsdata last wrote this row.
bills54 columns · Bill payshow in diagram

bill pay (GET /developer/v1/bills, active + archived)

Primary key: id

ColumnTypeKeyDescription
idPrimary key.varchar(64)PK Primary key.
invoice_numbervarchar(256)
statusvarchar(32)
status_summaryvarchar(64)
approval_statusvarchar(32)
sync_statusvarchar(32)
is_archivedboolean
created_attimestamp
issued_attimestamp
due_attimestamp
paid_attimestamp
posting_datetimestamp
accounting_datetimestamp
archived_attimestamp
draft_bill_created_attimestamp
draft_bill_idvarchar(64)
amountdecimal(18,4)
currency_codevarchar(8)
fx_conversion_ratedecimal(18,8)
memovarchar(4096)
vendor_memovarchar(4096)
vendor_idReferences vendors.id.varchar(64)FKReferences vendors.id.
vendor_namevarchar(512)
vendor_typevarchar(32)
vendor_remote_idvarchar(256)
vendor_remote_codevarchar(256)
vendor_remote_namevarchar(512)
vendor_contact_idvarchar(64)
bill_owner_idReferences users.id.varchar(64)FKReferences users.id.
bill_owner_first_namevarchar(256)
bill_owner_last_namevarchar(256)
entity_idReferences entities.id.varchar(64)FKReferences entities.id.
purchase_order_idReferences purchase_orders.id.varchar(64)FKReferences purchase_orders.id.
remote_idvarchar(256)
deep_link_urlvarchar(1024)
enable_accounting_syncboolean
is_payment_sync_readyboolean
is_withholding_releaseboolean
payment_idvarchar(64)
payment_amountdecimal(18,4)
payment_currency_codevarchar(8)
payment_methodvarchar(64)
payment_datetimestamp
payment_effective_datetimestamp
customer_friendly_payment_idvarchar(128)
payment_trace_idvarchar(128)
payment_trace_id_descriptorvarchar(256)
payment_detailsvarchar(65535)
invoice_urlsvarchar(65535)
item_receipt_idsvarchar(65535)
applied_vendor_creditsvarchar(65535)
line_item_countinteger
inventory_line_item_countinteger
_jsdata_syncedWhen jsdata last wrote this row.timestampWhen jsdata last wrote this row.
bill_line_items16 columns · Bill payshow in diagram

Expense lines (line_type line_item) and inventory lines (line_type inventory_line_item) of a bill.

Primary key: bill_id, line_type, line_index

ColumnTypeKeyDescription
_jsdata_idRow id: md5 of the natural key.varchar(32)Row id: md5 of the natural key.
bill_idPrimary key (with line_type, line_index). References bills.id.varchar(64)PK FKPrimary key (with line_type, line_index). References bills.id.
line_typePrimary key (with bill_id, line_index).varchar(32)PK Primary key (with bill_id, line_index).
line_indexPrimary key (with bill_id, line_type).integerPK Primary key (with bill_id, line_type).
amountdecimal(18,4)
currency_codevarchar(8)
memovarchar(4096)
quantitydecimal(18,4)
unit_pricedecimal(18,4)
unit_price_currency_codevarchar(8)
purchase_order_line_item_idReferences purchase_order_line_items.id.varchar(64)FKReferences purchase_order_line_items.id.
item_receipt_line_item_idsvarchar(65535)
withholding_gross_amountdecimal(18,4)
withholding_amountdecimal(18,4)
withholding_percentagedecimal(18,6)
_jsdata_syncedWhen jsdata last wrote this row.timestampWhen jsdata last wrote this row.
bill_accounting_field_selections18 columns · Bill payshow in diagram

accounting coding on bills and their lines

Primary key: _jsdata_id

ColumnTypeKeyDescription
_jsdata_idPrimary key.varchar(32)PK Primary key.
bill_idReferences bills.id.varchar(64)FKReferences bills.id.
line_typevarchar(32)
line_indexinteger
selection_indexinteger
idvarchar(64)
external_idJoins to gl_accounts.id.varchar(256)FKJoins to gl_accounts.id.
external_codevarchar(256)
namevarchar(512)
display_namevarchar(512)
typevarchar(64)
provider_namevarchar(128)
source_typevarchar(64)
category_idvarchar(64)
category_external_idvarchar(256)
category_namevarchar(512)
category_typevarchar(64)
_jsdata_syncedWhen jsdata last wrote this row.timestampWhen jsdata last wrote this row.
purchase_orders44 columns · Bill payshow in diagram

purchase orders (GET /developer/v1/purchase-orders, include_archived)

Primary key: id

ColumnTypeKeyDescription
idPrimary key.varchar(64)PK Primary key.
purchase_order_numbervarchar(128)
namevarchar(512)
memovarchar(4096)
created_attimestamp
archived_attimestamp
promise_datetimestamp
spend_start_datetimestamp
spend_end_datetimestamp
amountdecimal(18,4)
currency_codevarchar(8)
spend_total_amountdecimal(18,4)
spend_total_currency_codevarchar(8)
billing_statusvarchar(64)
receipt_statusvarchar(64)
creation_sourcevarchar(64)
entity_idReferences entities.id.varchar(64)FKReferences entities.id.
vendor_idReferences vendors.id.varchar(64)FKReferences vendors.id.
user_idReferences users.id.varchar(64)FKReferences users.id.
owner_idvarchar(64)
spend_program_idReferences spend_programs.id.varchar(64)FKReferences spend_programs.id.
spend_request_idvarchar(64)
external_idvarchar(256)
remote_idvarchar(256)
net_payment_termsinteger
three_way_match_enabledboolean
withholding_default_ratedecimal(18,6)
ramp_urlvarchar(1024)
ship_to_company_namevarchar(512)
ship_to_address1varchar(512)
ship_to_address2varchar(512)
ship_to_cityvarchar(256)
ship_to_statevarchar(64)
ship_to_postal_codevarchar(32)
ship_to_countryvarchar(64)
shipping_contact_first_namevarchar(256)
shipping_contact_last_namevarchar(256)
shipping_contact_emailvarchar(320)
shipping_contact_phone_numbervarchar(64)
bill_idsvarchar(65535)
transaction_idsvarchar(65535)
item_receipt_idsvarchar(65535)
line_item_countinteger
_jsdata_syncedWhen jsdata last wrote this row.timestampWhen jsdata last wrote this row.
purchase_order_line_items16 columns · Bill payshow in diagram

purchase order line_items[]; key purchase_order_id

Primary key: purchase_order_id, line_index

ColumnTypeKeyDescription
_jsdata_idRow id: md5 of the natural key.varchar(32)Row id: md5 of the natural key.
purchase_order_idPrimary key (with line_index). References purchase_orders.id.varchar(64)PK FKPrimary key (with line_index). References purchase_orders.id.
line_indexPrimary key (with purchase_order_id).integerPK Primary key (with purchase_order_id).
idvarchar(64)
descriptionvarchar(4096)
quantitydecimal(18,4)
unit_pricedecimal(18,4)
unit_price_currency_codevarchar(8)
amountdecimal(18,4)
currency_codevarchar(8)
start_datedate
end_datedate
external_idvarchar(256)
remote_idvarchar(256)
withholding_ratedecimal(18,6)
_jsdata_syncedWhen jsdata last wrote this row.timestampWhen jsdata last wrote this row.
purchase_order_accounting_field_selections18 columns · Bill payshow in diagram

accounting coding on purchase orders and their lines

Primary key: _jsdata_id

ColumnTypeKeyDescription
_jsdata_idPrimary key.varchar(32)PK Primary key.
purchase_order_idReferences purchase_orders.id.varchar(64)FKReferences purchase_orders.id.
line_typevarchar(32)
line_indexinteger
selection_indexinteger
idvarchar(64)
external_idvarchar(256)
external_codevarchar(256)
namevarchar(512)
display_namevarchar(512)
typevarchar(64)
provider_namevarchar(128)
source_typevarchar(64)
category_idvarchar(64)
category_external_idvarchar(256)
category_namevarchar(512)
category_typevarchar(64)
_jsdata_syncedWhen jsdata last wrote this row.timestampWhen jsdata last wrote this row.
vendors40 columns · Bill payshow in diagram

vendors (GET /developer/v1/vendors) with Ramp spend totals

Primary key: id

ColumnTypeKeyDescription
idPrimary key.varchar(64)PK Primary key.
namevarchar(512)
name_legalvarchar(512)
descriptionvarchar(4096)
vendor_typevarchar(32)
statevarchar(64)
is_activeboolean
is_deletableboolean
countryvarchar(64)
billing_frequencyvarchar(32)
federal_tax_classificationvarchar(64)
merchant_idReferences merchants.id.varchar(64)FKReferences merchants.id.
parent_vendor_idvarchar(64)
default_entity_idvarchar(64)
vendor_owner_idvarchar(64)
external_vendor_idvarchar(256)
accounting_vendor_remote_idvarchar(256)
sk_category_idinteger
sk_category_namevarchar(256)
created_attimestamp
contact_idsvarchar(65535)
subsidiaryvarchar(65535)
address_address_line_1varchar(512)
address_address_line_2varchar(512)
address_cityvarchar(256)
address_statevarchar(64)
address_postal_codevarchar(32)
address_countryvarchar(64)
tax_address_address_line_1varchar(512)
tax_address_address_line_2varchar(512)
tax_address_cityvarchar(256)
tax_address_statevarchar(64)
tax_address_postal_codevarchar(32)
tax_address_countryvarchar(64)
total_spend_all_timedecimal(18,4)
total_spend_last_30_daysdecimal(18,4)
total_spend_last_365_daysdecimal(18,4)
total_spend_ytddecimal(18,4)
total_spend_currency_codevarchar(8)
_jsdata_syncedWhen jsdata last wrote this row.timestampWhen jsdata last wrote this row.
users18 columns · People and controlsshow in diagram

Ramp users (employees), including suspended ones

Primary key: id

ColumnTypeKeyDescription
idPrimary key.varchar(64)PK Primary key.
first_namevarchar(256)
last_namevarchar(256)
emailvarchar(320)
phonevarchar(64)
employee_idvarchar(128)
rolevarchar(64)
statusvarchar(32)
is_managerboolean
manager_idReferences users.id.varchar(64)FKReferences users.id.
department_idReferences departments.id.varchar(64)FKReferences departments.id.
location_idReferences locations.id.varchar(64)FKReferences locations.id.
entity_idReferences entities.id.varchar(64)FKReferences entities.id.
business_idReferences business.id.varchar(64)FKReferences business.id.
scheduled_deactivation_datetimestamp
scheduled_invitation_datetimestamp
custom_fieldsvarchar(65535)
_jsdata_syncedWhen jsdata last wrote this row.timestampWhen jsdata last wrote this row.
departments3 columns · People and controlsshow in diagram

departments

Primary key: id

ColumnTypeKeyDescription
idPrimary key.varchar(64)PK Primary key.
namevarchar(512)
_jsdata_syncedWhen jsdata last wrote this row.timestampWhen jsdata last wrote this row.
locations4 columns · People and controlsshow in diagram

locations

Primary key: id

ColumnTypeKeyDescription
idPrimary key.varchar(64)PK Primary key.
namevarchar(512)
entity_idReferences entities.id.varchar(64)FKReferences entities.id.
_jsdata_syncedWhen jsdata last wrote this row.timestampWhen jsdata last wrote this row.
entities13 columns · People and controlsshow in diagram

business entities (subsidiaries); accounts = GL mapping of the entity's Ramp accounts

Primary key: id

ColumnTypeKeyDescription
idPrimary key.varchar(64)PK Primary key.
entity_namevarchar(512)
currencyvarchar(8)
is_primaryboolean
connected_subsidiary_idvarchar(256)
connected_subsidiary_namevarchar(512)
connected_subsidiary_external_idvarchar(256)
default_bill_pay_payment_account_idvarchar(64)
location_idsvarchar(65535)
accountsvarchar(65535)
payment_accountsvarchar(65535)
custom_record_fieldsvarchar(65535)
_jsdata_syncedWhen jsdata last wrote this row.timestampWhen jsdata last wrote this row.
cards24 columns · People and controlsshow in diagram

physical + virtual cards (active and terminated). Only last_four; no card number, expiry, or shipping street

Primary key: id

ColumnTypeKeyDescription
idPrimary key.varchar(64)PK Primary key.
card_typevarchar(16)
display_namevarchar(512)
last_fourOnly the last four digits are stored; never the card number or expiry.varchar(4)Only the last four digits are stored; never the card number or expiry.
user_idReferences users.id.varchar(64)FKReferences users.id.
cardholder_namevarchar(256)
fund_idReferences funds.id.varchar(64)FKReferences funds.id.
statevarchar(32)
is_physicalboolean
is_primaryboolean
is_suspendedboolean
is_terminatedboolean
has_program_overriddenboolean
automatic_routing_enabledboolean
created_attimestamp
fulfillment_statusvarchar(64)
shipping_datetimestamp
shipping_etatimestamp
shipping_methodvarchar(64)
shipping_tracking_urlvarchar(1024)
shipping_cityvarchar(256)
shipping_statevarchar(64)
shipping_countryvarchar(64)
_jsdata_syncedWhen jsdata last wrote this row.timestampWhen jsdata last wrote this row.
funds35 columns · People and controlsshow in diagram

Funds are Ramp's spend limits (formerly called limits): limit, interval, restrictions and current balance.

Primary key: id

ColumnTypeKeyDescription
idPrimary key.varchar(64)PK Primary key.
display_namevarchar(512)
statevarchar(32)
is_terminatedboolean
entity_idReferences entities.id.varchar(64)FKReferences entities.id.
spend_program_idReferences spend_programs.id.varchar(64)FKReferences spend_programs.id.
created_attimestamp
is_shareableboolean
overrides_spend_programboolean
is_exempt_from_policy_agentboolean
physical_card_enabledboolean
virtual_card_enabledboolean
reimbursements_enabledboolean
limit_amountdecimal(18,4)
limit_currency_codevarchar(8)
intervalvarchar(32)
temporary_limit_amountdecimal(18,4)
transaction_amount_limitdecimal(18,4)
auto_lock_datetimestamp
next_interval_resets_attimestamp
start_of_intervaltimestamp
balance_cleareddecimal(18,4)
balance_pendingdecimal(18,4)
balance_totaldecimal(18,4)
balance_currency_codevarchar(8)
allowed_category_codesvarchar(65535)
blocked_category_codesvarchar(65535)
allowed_vendor_idsvarchar(65535)
blocked_vendor_idsvarchar(65535)
suspended_attimestamp
suspended_by_rampboolean
suspended_by_user_idvarchar(64)
card_idsvarchar(65535)
member_countinteger
_jsdata_syncedWhen jsdata last wrote this row.timestampWhen jsdata last wrote this row.
fund_members7 columns · People and controlsshow in diagram

users who can spend from each fund; key fund_id

Primary key: fund_id, user_id

ColumnTypeKeyDescription
_jsdata_idRow id: md5 of the natural key.varchar(32)Row id: md5 of the natural key.
fund_idPrimary key (with user_id). References funds.id.varchar(64)PK FKPrimary key (with user_id). References funds.id.
user_idPrimary key (with fund_id). References users.id.varchar(64)PK FKPrimary key (with fund_id). References users.id.
suspended_attimestamp
suspended_by_rampboolean
suspended_by_user_idvarchar(64)
_jsdata_syncedWhen jsdata last wrote this row.timestampWhen jsdata last wrote this row.
spend_programs21 columns · People and controlsshow in diagram

spend programs (fund templates)

Primary key: id

ColumnTypeKeyDescription
idPrimary key.varchar(64)PK Primary key.
display_namevarchar(512)
descriptionvarchar(4096)
iconvarchar(64)
is_shareableboolean
issue_physical_card_if_neededboolean
primary_card_enabledboolean
reimbursements_enabledboolean
limit_amountdecimal(18,4)
limit_currency_codevarchar(8)
intervalvarchar(32)
temporary_limit_amountdecimal(18,4)
transaction_amount_limitdecimal(18,4)
auto_lock_datetimestamp
next_interval_resettimestamp
start_of_intervaltimestamp
allowed_categoriesvarchar(65535)
blocked_categoriesvarchar(65535)
allowed_vendorsvarchar(65535)
blocked_vendorsvarchar(65535)
_jsdata_syncedWhen jsdata last wrote this row.timestampWhen jsdata last wrote this row.
gl_accounts16 columns · Accountingshow in diagram

general ledger accounts synced from the accounting system (GET /developer/v1/accounting/accounts); key ramp_id

Primary key: ramp_id

ColumnTypeKeyDescription
ramp_idPrimary key.varchar(64)PK Primary key.
idvarchar(256)
accounting_connection_idvarchar(64)
codevarchar(128)
namevarchar(512)
classificationvarchar(64)
is_activeboolean
visibilityvarchar(32)
provider_namevarchar(128)
gl_account_category_idvarchar(256)
gl_account_category_namevarchar(512)
gl_account_category_ramp_idvarchar(64)
entity_remote_idsvarchar(65535)
created_attimestamp
updated_attimestamp
_jsdata_syncedWhen jsdata last wrote this row.timestampWhen jsdata last wrote this row.
accounting_fields13 columns · Accountingshow in diagram

custom accounting fields (class, department, project...); key ramp_id

Primary key: ramp_id

ColumnTypeKeyDescription
ramp_idPrimary key.varchar(64)PK Primary key.
idvarchar(256)
accounting_connection_idvarchar(64)
namevarchar(512)
display_namevarchar(512)
input_typevarchar(32)
is_activeboolean
is_splittableboolean
is_required_forvarchar(65535)
provider_namevarchar(128)
created_attimestamp
updated_attimestamp
_jsdata_syncedWhen jsdata last wrote this row.timestampWhen jsdata last wrote this row.
accounting_field_options14 columns · Accountingshow in diagram

options of each custom accounting field; key ramp_id

Primary key: ramp_id

ColumnTypeKeyDescription
ramp_idPrimary key.varchar(64)PK Primary key.
idvarchar(256)
accounting_field_ramp_idReferences accounting_fields.ramp_id.varchar(64)FKReferences accounting_fields.ramp_id.
accounting_connection_idvarchar(64)
codevarchar(128)
valuevarchar(1024)
display_namevarchar(1024)
is_activeboolean
visibilityvarchar(32)
provider_namevarchar(128)
entity_remote_idsvarchar(65535)
created_attimestamp
updated_attimestamp
_jsdata_syncedWhen jsdata last wrote this row.timestampWhen jsdata last wrote this row.
business14 columns · Businessshow in diagram

the Ramp business (one row)

Primary key: id

ColumnTypeKeyDescription
idPrimary key.varchar(64)PK Primary key.
business_name_legalvarchar(512)
business_name_on_cardvarchar(512)
activeboolean
created_timetimestamp
enforce_ssoboolean
initial_approved_limitdecimal(18,4)
is_integrated_with_slackboolean
is_reimbursements_enabledboolean
limit_lockedboolean
phonevarchar(64)
websitevarchar(1024)
billing_addressvarchar(65535)
_jsdata_syncedWhen jsdata last wrote this row.timestampWhen jsdata last wrote this row.
business_balances18 columns · Businessshow in diagram

One row per day the connector ran: limits and balances at that moment.

Primary key: as_of_date

ColumnTypeKeyDescription
as_of_datePrimary key.datePK Primary key.
captured_attimestamp
next_billing_datedate
prev_billing_datedate
card_limitdecimal(18,4)
available_card_limitdecimal(18,4)
balance_including_pendingdecimal(18,4)
card_balance_including_pendingdecimal(18,4)
card_balance_excluding_pendingdecimal(18,4)
statement_balancedecimal(18,4)
flex_limitdecimal(18,4)
available_flex_limitdecimal(18,4)
flex_balancedecimal(18,4)
float_balance_excluding_pendingdecimal(18,4)
global_limitdecimal(18,4)
max_balancedecimal(18,4)
currency_codevarchar(8)
_jsdata_syncedWhen jsdata last wrote this row.timestampWhen jsdata last wrote this row.

Connect Ramp All connectors