1. 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:
json{ "host": "***********.amazonaws.com", "password": "**********", "port": 5432, "username": "*********" }
2. Connect
From your preferred database querying tool, start a new PostgreSQL connection.
Example free querying tool: DBeaver
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.
Entity Relationships
Available Entities and Fields
Customers
💡
For more information and descriptions of each field, please see the Customer section of our API documentation.
🔗 : Customer API Documentation
sqlSELECT customer_id, 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, 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, is_borrower_portal_user, is_active, verification_status, title, phone_number, ssn, international_customer_id, email, date_of_birth FROM data_direct.customers_table;
Accounts
💡
For more information and descriptions of each field, please see the Accounts section of our API documentation.
🔗 : Accounts API Documentation
sqlSELECT account_id, organization_id, account_status, account_status_subtype, 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_len, product_promo_len, is_promo_interest_deferred, product_promo_interest_rate, loan_end_date, post_promo_len, product_post_promo_len, product_post_promo_interest_rat, interest_accrual_interval, loan_discount_cents, loan_discount_at, 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, 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_charges_principal_cents, min_pay_loans_principal_cents, statement_min_pay_interest_cents, statement_min_pay_deferred_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 FROM data_direct.accounts_table;
Line Items
💡
For more information and descriptions of each field, please see the Line Items section of our API documentation.
🔗 : Line Item API Documentation
sqlSELECT line_item_id, product_id, account_id, effective_at, created_at, updated_at, line_item_status, line_item_type, description, original_amount_cents, balance_cents, principal_cents, interest_balance_cents, am_interest_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.line_items_table;
Amortization Schedule
💡
For more information and descriptions of each field, please see the Amortization Schedule section of our API documentation.
🔗 : Amortization Schedule API Documentation
sqlSELECT 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_table;
Statements
💡
For more information and descriptions of each field, please see the Statements section of our API documentation.
🔗 : Statements API Documentation
sqlSELECT 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, 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_table;
Statement Line Items
💡
For more information and descriptions of each field, please see the Statement Line Items section of our API documentation.
🔗 : Statement Line Items API Documentation
sqlSELECT statement_id, line_item_id, product_id, effective_at, created_at, line_item_status, line_item_type, description, original_amount_cents, balance_cents, principal_cents, interest_balance_cents, am_interest_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_table;
Cards
sqlSELECT card_id, external_card_id, account_id, spend_limit, token, card_program_token, last_four, card_type, state, memo FROM data_direct.cards_table;
Card Transactions
sqlSELECT card_transaction_id, external_card_transaction_id, card_id, line_item_id, issuer_processor, attributes FROM data_direct.card_transactions_table;
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.
Delinquency Report
⚠️
The delinquency 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 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.
Field Definitions
organization_id: Unique ID value associated with your organization.account id: Unique ID value of the related account.days_past_due: amount of days since account entered delinquency from the oldest statement it’s delinquent on.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, which is the highest minimum payment past due.stabilization_payment_cents: amount needed to maintain current delinquency level; this is the oldest minimum payment minus the payments made since then relative to that minimum payment.minimum_payment_due_date: The latest statement’s minimum payment due date.delinquent_as_of_date: The oldest missed payment due date for the account. Query
sqlSELECT 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;