x DataDirect How-to Guide (Discontinued)

From getting access to running your first SQL query

Suggest Edits

Access your credentials

Your DataDirect credentials can be retrieved via the following API call:

GET /organization/data_direct

The response will be formatted as follows:

{
    "host": "***********.amazonaws.com",
    "password": "**********",
    "port": 5432,
    "username": `
}

Connect to your preferred database client

From your preferred database querying tool, start a new PostgreSQL connection.

We recommend Postico 2 as a good free database tool if you don't have a preferred tool already.

You should be prompted with a form to enter in the credential information you received in the call you made above.

Query Your Data

Now that you have connected DataDirect to your query tool of choice and are aware of which entities you can query, you’re ready to begin building your own queries! Below are the entities available along with their related fields.

Table entity relationships

Checking data freshness

DataDirect data is typically refreshed every hour. To identify the time of the last refresh and the state it represents, you can check for the most recent related record in the refresh_history:

select 
  table_name,
  last_updated_at 
from data_direct.refresh_history;

Available Entities and Fields

As mentioned on the DataDirect page, the data structure used closely mimics the data structure surfaced from the API.

Hence, you can find a definition for each column by finding an attribute with the same name.

Customers

To see the full table, run the following query.

select
	customer_id,
	external_customer_id,
	address_line_one,
	address_line_two,
	address_city,
	address_state,
	address_zip,
	address_country_code,
	name_prefix,
	name_first,
	name_middle,
	name_last,
	name_suffix,
	passport_number,
	passport_country,
	created_at,
	updated_at,
	customer_type,
	external_customer_parent_id,
	customer_parent_id,
	is_borrower_portal_user,
	is_active,
	verification_status,
	title,
	phone_number,
	ssn,
	international_customer_id,
	email,
	date_of_birth,
	customer_num_active_accounts,
	customer_total_balance_cents,
	customer_principal_cents,
	customer_interest_balance_cents,
	customer_am_interest_balance_cents,
	customer_deferred_interest_balance_cents,
	customer_am_deferred_interest_balance_cents,
	customer_fees_balance_cents,
	business_details,
	bloom_metadata
from
	data_direct.customers;

For more information and descriptions of each field, please see the Customer section of our API documentation.

Accounts

To see the full table, run the following query.

select
	external_account_id,
	organization_id,
	customer_id,
	account_status,
	account_status_subtype,
	is_active_scra,
	external_fields,
	effective_at,
	created_at,
	updated_at,
	product_id,
	product_name,
	product_color,
	product_short_description,
	product_long_description,
	product_type,
	close_of_business_time,
	product_time_zone,
	effective_close_of_business,
	first_cycle_interval,
	credit_limit_cents,
	max_approved_credit_limit_cents,
	min_pay_type,
	initial_principal_cents,
	interest_grace_method,
	product_late_fee_cents,
	late_fee_cents,
	late_fee_cap_percent,
	late_fee_grace,
	payment_reversal_fee_cents,
	payment_reversal_fee_cap_percent,
	product_payment_reversal_fee_cents,
	origination_fee_cents,
	origination_fee_percent,
	is_origination_fee_lesser_value,
	is_origination_fee_amortized,
	year_fee_cents,
	month_fee_cents,
	promo_purchase_window_inclusive_start,
	promo_purchase_window_exclusive_end,
	promo_purchase_window_len,
	promo_inclusive_start,
	promo_exclusive_end,
	promo_impl_interest_rate_percent,
	promo_len,
	product_promo_len,
	promo_default_product_interest_rate,
	promo_default_product_len,
	is_promo_interest_deferred,
	product_promo_interest_rate,
	loan_end_date,
	post_promo_len,
	post_promo_inclusive_start,
	post_promo_exclusive_end,
	post_promo_impl_interest_rate_percent,
	post_promo_default_product_interest_rate,
	post_promo_default_product_len,
	product_post_promo_len,
	product_post_promo_interest_rate,
	interest_accrual_interval,
	loan_discount_cents,
	loan_discount_at,
	recurring_fee_interval,
	recurring_fee_amount_cents,
	initial_delay_offset,
	is_fee_interval_floored,
	recurring_fee_label,
	principal_balance_cents,
	amortization_deferred_interest_balance_cents,
	amortization_interest_balance_cents,
	available_credit_cents,
	deferred_interest_balance_cents,
	fees_balance_cents,
	interest_balance_cents,
	open_to_buy_cents,
	total_balance_cents,
	total_paid_to_date_cents,
	total_interest_paid_to_date_cents,
	total_payment_supertype_to_date_cents,
	total_original_amount_cents,
	total_principal_original_amount_cents,
	interest_rate_percent,
	total_payoff_cents,
	min_pay_due_at,
	min_pay_cents,
	unpaid_min_pay_cents,
	current_min_pay_cents,
	min_pay_fees_cents,
	statement_min_pay_cents,
	statement_min_pay_due_at,
	statement_min_pay_charges_principal_cents,
	min_pay_loans_principal_cents,
	statement_min_pay_interest_cents,
	statement_min_pay_deferred_interest_cents,
	statement_min_pay_amortization_interest_cents,
	statement_min_pay_amortization_deferred_interest_cents,
	statement_min_pay_fees_cents,
	statement_unpaid_min_pay_cents,
	statement_current_min_pay_cents,
	statement_cycle_payments_cents,
	statement_previous_min_pay_cents,
	default_payment_processor_method,
	autopay_enabled,
	ach_payment_processor_name,
	ach_token,
	ach_last_four,
	debit_card_payment_processor_name,
	debit_card_token,
	debit_last_four,
	credit_card_payment_processor_name,
	checkout_dotcom_token_source_id,
	checkout_dotcom_token_card_token,
	checkout_dotcom_token_last_four,
	checkout_dotcom_token_expires_on,
	canopy_nacha_bank_routing_number,
	canopy_nacha_bank_account_number,
	canopy_nacha_bank_account_type,
	payouts_config,
	issuer_processor_details,
	disbursement_source_payout_entity_external_id,
	payout_entities,
	payment_processor_config,
	partner_entity,
	attributes,
	plaid_access_token_valid,
	plaid_account_id_valid,
	plaid_check_balance_enabled,
	bloom_metadata
from
	data_direct.accounts;

For more information and descriptions of each field, please see the Accounts section of our API documentation.

Line_items

To see the full table, run the following query.

select
	line_item_id,
	external_line_item_id,
	product_id,
	account_id,
	effective_at,
	created_at,
	updated_at,
	valid_at,
	line_item_status,
	line_item_type,
	description,
	allocation,
	original_amount_cents,
	balance_cents,
	principal_cents,
	interest_balance_cents,
	am_interest_balance_cents,
	am_fees_balance_cents,
	deferred_interest_balance_cents,
	am_deferred_interest_balance_cents,
	total_interest_paid_to_date_cents,
	merchant_id,
	merchant_name,
	merchant_mcc_code,
	merchant_phone_number,
	external_fields,
	line_item_relationships
from
	data_direct.line_items;

For more information and descriptions of each field, please see the Line Items section of our API documentation.

Am_forecast_public

To see the full table, run the following query

SELECT line_item_id,
	cycle_exclusive_end,
	min_pay_due_at,
	am_min_pay_due_cents,
	am_cycle_payment_cents,
	am_interest_cents,
	am_deferred_cents,
	am_principal_cents,
	am_fees_cents,
	am_start_principal_balance_cents,
	am_end_principal_balance_cents,
	am_start_total_balance_cents,
	am_end_total_balance_cents,
	paid_on_time
FROM data_direct.am_forecast_public;

For more information and descriptions of each field, please see the Amortization Schedule section of our API documentation.

Statements

To see the full table, run the following query

select
	statement_id,
	external_statement_id,
	effective_at,
	account_id,
	account_promo_purchase_window_inclusive_start,
	account_promo_purchase_window_inclusive_end,
	account_promo_inclusive_start,
	account_promo_inclusive_end,
	account_status,
	account_status_subtype,
	account_payout_entities,
	credit_limit_cents,
	total_charges_cents,
	available_credit_cents,
	open_to_buy_cents,
	min_pay_cents,
	min_pay_due_at,
	min_pay_charges_principal_cents,
	min_pay_loans_principal_cents,
	min_pay_revolving_principal_cents,
	min_pay_interest_cents,
	min_pay_deferred_cents,
	min_pay_am_deferred_interest_cents,
	min_pay_am_interest_cents,
	min_pay_fees_cents,
	min_pay_am_fees_cents,
	min_pay_past_due_interest_cents,
	min_pay_past_due_deferred_cents,
	previous_min_pay_cents,
	unpaid_min_pay_cents,
	current_min_pay_cents,
	min_pay_floor_excess_cents,
	cycle_inclusive_start,
	cycle_exclusive_end,
	cycle_length_days,
	cycle_charges_cents,
	cycle_loans_cents,
	cycle_charge_returns_cents,
	cycle_refunds_cents,
	cycle_payments_cents,
	cycle_payment_reversals_cents,
	cycle_debit_adjustments_cents,
	cycle_credit_adjustments_cents,
	cycle_total_credits_cents,
	cycle_interest_cents,
	cycle_deferred_interest_cents,
	cycle_am_deferred_interest_cents,
	cycle_am_interest_cents,
	cycle_total_interest_cents,
	cycle_late_fees_cents,
	cycle_fees_total_cents,
	cycle_payment_reversals_fees_cents,
	cycle_waived_deferred_interest_cents,
	charges_principal_cents,
	loans_principal_cents,
	principal_balance_cents,
	interest_balance_cents,
	deferred_interest_balance_cents,
	am_deferred_interest_balance_cents,
	am_interest_balance_cents,
	fees_balance_cents,
	total_balance_cents,
	previous_total_balance_cents,
	expected_remaining_payment_amount_cents,
	total_payoff_cents,
	inception_to_date_payments_total_cents,
	inception_to_date_purchases_total_cents,
	inception_to_date_credits_total_cents,
	inception_to_date_credits_and_payments_total_cents,
	inception_to_date_interest_total_cents,
	inception_to_date_fees_total_cents,
	inception_to_date_interest_prior_total,
	year_to_date_interest_total_cents,
	year_to_date_fees_total_cents
from
	data_direct.statements;

For more information and descriptions of each field, please see the Statements section of our API documentation.

Statement_line_items

To see the full table, run the following query

select
	line_item_id,
	external_line_item_id,
	statement_id,
	product_id,
	effective_at,
	created_at,
	valid_at,
	line_item_status,
	line_item_type,
	description,
	allocation,
	original_amount_cents,
	balance_cents,
	principal_cents,
	interest_balance_cents,
	am_interest_balance_cents,
	am_fees_balance_cents,
	deferred_interest_balance_cents,
	am_deferred_interest_balance_cents,
	total_interest_paid_to_date_cents,
	merchant_id,
	merchant_name,
	merchant_mcc_code,
	merchant_phone_number,
	external_fields
from
	data_direct.statement_line_items;

For more information and descriptions of each field, please see the Statement Line Items section of our API documentation.

Cards

To see the full table, run the following query

SELECT
	card_id,
  external_card_id,
  account_id,
  spend_limit,
  token,
  card_program_token,
  last_four,
  card_type,
  state,
  memo
FROM data_direct.cards;

For more information and descriptions of each field, please see the Cards section of our API documentation.

Card_transactions

To see the full table, run the following query

SELECT
	card_transaction_id,
  external_card_transaction_id,
  card_id,
  line_item_id,
  issuer_processor,
  attributes
FROM data_direct.card_transactions;

Pre-built reports

Pre-built reports within DataDirect are reports common to lending operations that we have pre-written the SQL and surfaced in table form for ease of use.

It’s important to note that because these are strictly pre-written and run SQL queries, they are distinctly different than the other entities within DataDirect.

Past_due_report (a.k.a. delinquency_report)

This report strictly queries your data to find the oldest cycle due date that has not yet been paid in full. It uses this information along with the current date and time of execution to calculate the number of days past due.

Given this information, its important to note that the Past_due_report (previously known as delinquency_report) does not take into consideration account sub-statuses (such as Delinquent), late fee grace periods, or any other operationally defined workflows that you may have in place.

🚧

Naming Deprecation

In an effort to reduce the possible confusion cased by the conflation of these terms, we will soon be deprecating the delinquency_report name in favor of more aptly naming it the past_due_report.

SELECT
	organization_id,
  external_account_id,
  delinquency_bucket,
  days_past_due,
  cure_payment_cents,
  stabilization_payment_cents,
  minimum_payment_due_date timestamptz,
  delinquent_as_of_date timestamptz
  
FROM data_direct.delinquency_report;

Field Definitions

  • organization_id: Unique ID value associated with your organization.
  • account id: Unique ID value of the related account.
  • days_past_due: number of days since the oldest statement in which the minimum due balance was not paid in full.
  • delinquency_bucket: derived from days past due.
    Possible values: ‘Current’ (for not delinquent), 0-29, 30-59, 60-89, 90-119, 120-149, 150-179, 180
  • cure_payment_cents: amount needed to completely exit delinquency.
  • stabilization_payment_cents: amount needed to maintain current delinquency level; this is the oldest minimum payment minus the payments made since then.
  • minimum_payment_due_date: The most recent statement’s minimum payment due date.
    delinquent_as_of_date: The oldest missed payment due date for the account.

NACHA_entries

This table is relevant for customers who use Canopy NACHA payment processor payment processor

SELECT
	nacha_entry_id,
  external_nacha_entry_id,
  subject_account_id,
  external_subject_account_id,
  external_subject_line_item_id,
  nacha_file_reference_code,
  receiving_identification_number,
  created_at
  
FROM data_direct.nacha_entries;

For more information and descriptions of each field, please see the NACHA section of our API documentation.

Updated 8 months ago