FairPay's fairpay_sql_populate program utilises six relational database tables for storage of IPN data from PayPal. In the tables below, SQL character data is labeled as "char", and numeric types are labelled as "decimal" or "integer", depending on which is appropriate. This is to abstract the data types for general use, independant of which database type you're using, as the low-level engine types differ between engines.

NOTE: Always check the flt_verified field for any transaction. Even if a transaction is shown as INVALID, it will be stored, along with all its details, both on the off-chance that there is a bug that caused a false negative, and so that you can track attempted payment spoofs. In your business rules, only actually financially process transactions marked as VERIFIED

In any table, any field for which there is no data will be a NULL field.

The layout of the tables is as follows, accompanied by explanations of what contents may be expected within each column:

fairpay_transactions - The heart of the FairPay database relation system, the majority of your transaction information will end up in this table. Any IPN transaction that has a tranaction ID will have a record here.

Column Name		Type/Length		Information and Details
txn_id		char (17)		A unique ID designated by PayPal to identify the transaction.
parent_txn_id		char (17)		The unique ID of the transaction for which the current transaction is a child. The presence of this ID usually indicates a refund or other kind of reversal.
txn_type		char (14)		Type of transaction. There are five types: web_accept cart send_money masspay subscr_payment Reversals will not have a transaction type.
payment_type		char (7)		Either instant or echeck depending on the method of payment.
payment_date		char (25)		Date of payment. The format is: HH:MM:SS MON X, YYYY TZN
payment_status		char (17)		One of several results: Cancelled_Reversal - Reversal was cancelled. Completed - Initial payment completed, funds available. Denied - Merchant (you) denied the transaction. Failed - Payment failed. (Buyer bank account had insufficient funds.) Pending - Payment pending. See pending_reason. Refunded - Merchant (you) refunded the transaction. Reversed - Chargeback or other kind of reversal. See reason_code as well. Processed - Mass Payment has been processed but not yet completed (this result is for a txn_typeof masspay only).
pending_reason		char (14)		One of several results: address - No confirmed shipping address as specified by merchant's preferences. echeck - Payment made by an eCheck which has not yet cleared. intl - Merchant is a non-US account and has no withdrawal method. multi_currency - Currency was not native and merchant does not have the conversion option enabled. unilateral - Payment made by non-confirmed or non-registered email address. upgrade - Payment made to an account that must be upgraded to Premiere or Business status. verify - Merchant account is not yet verified. other - Other reason for pending payments.
reason_code		char (14)		One of several results: buyer_complaint chargeback guarantee refund other
mc_gross		decimal		The gross amount of payment made in mc_currency.
mc_fee		decimal		The fee charged made in mc_currency.
mc_currency		char (3)		One of several results: USD - U.S. Dollars GBP - British Pounds Sterling EUR - Euros CAD - Canadian Dollars JPY - Japanese Yen
mc_handling		decimal		The amount of shipping paid in mc_currency. NOTE: This is not currently split out on "Buy Now" buttons!
mc_shipping		decimal		The amount of shipping paid in mc_currency. NOTE: This is not currently split out on "Buy Now" buttons!
settle_amount		decimal		The amount deposited after automatic currency conversion.
settle_currency		char (3)		The currency used for settle_amount
exchange_rate		decimal		Exchange rate for conversion.
payment_gross		decimal		Gross payment in USD. This is a legacy field.
payment_fee		decimal		Fee in USD. This is a legacy field.
business		char (127)		Email address of the recipient. Identical to receiver_email if payment was made to primary address for the account.
receiver_email		char (127)		Primary email address of the payment recipient.
receiver_id		char (13)		Unique ID assigned by PayPal to the recipient. Equivalent to the recipient's referral ID.
item_name		char (127)		Item name as passed by the merchant.
item_number		char (127)		Item number as passed by the merchant.
quantity		integer		Quantity of item purchased.
invoice		char (255)		Passthrough variable for invoice tracking. Omitted if not supplied by merchant.
custom		char (255)		Custom variables passed to merchant which are never seen by the customer.
memo		char (255)		Memo as entered on PayPal's site in the Note field.
tax		decimal		Tax charged on entire payment.
note		char (4000)		Extra note field.
option_name1		char (64)		Descriptive name of first of two possible (optional) options.
option_selection1		char (200)		Value of first of two possible (optional) options.
option_name2		char (64)		Descriptive name of second of two possible (optional) options.
option_selection2		char (200)		Value of second of two possible (optional) options.
notify_version		char (10)		Version number of PayPal's IPN service.
payer_id		char (13)		Unique ID assigned by PayPal to the payer's account.
num_cart_items		integer		Number of line items present in a shopping cart transaction.
for_auction		char (4)		Set to true if transaction is from an auction.
flt_record_processed		char (1)		A one character field to help any business-rules software determine if a record has been processed. Defaults to "N".
flt_created		char (14)		Time the record was created. Format is YYYYMMDDHHMMSS
flt_verified		char (8)		Either INVALID or VERIFIED depending on whether the IPN was validated by PayPal as authentic and generated by their system.
flt_id		char (64)		Unique ID assigned by flipna with which you may look up all records related to one IPN submission.

NOTES:

• The txn_id field is used to relate transactions to all other tables except fairpay_buyers.
• The flt_id field is used to look up the main transaction to get to the point where you know what the txn_id value is for all tables' parts of any transaction.
• To relate a transaction to a buyer, use the payer_id field.

---

fairpay_buyers - Every transaction (even of the Mass-Payment type) has a payer_id field. This is unique to every user. Relation between transactions and buyer information should be based on this field. All buyer-specific details will appear in this table, and will be kept as current and complete as possible.

Column Name		Type/Length		Information and Details
payer_id		char (13)		A Unique customer ID assigned to the payer's account by PayPal.
first_name		char (64)		The payer's first name.
last_name		char (64)		The payer's last name.
payer_business_name		char (127)		The business name of the payer, if given.
address_name		char (128)		The name of to be used for the shipping address if Gift Address is provided.
address_street		char (200)		Street address for shipping.
address_city		char (40)		City for shipping.
address_state		char (40)		State for shipping.
address_zip		char (20)		ZIP code for shipping.
address_country		char (64)		Country for shipping.
address_status		char (11)		One of two results: confirmed unconfirmed
payer_email		char (127)		Primary email address of the payer.
payer_status		char (10)		One of two results: verified unverified
flt_record_processed		char (1)		A one character field to help any business-rules software determine if a record has been processed. Defaults to "N".
flt_created		char (14)		Time the record was created. Format is YYYYMMDDHHMMSS
flt_id		char (64)		Unique ID assigned by flipna with which you may look up all records related to one IPN submission. Any time the buyer record is updated with more current information, this will be the last flipna ID that triggered such an update.

NOTES:

• The payer_id field is used to relate buyers to transactions.
• The flt_id field is updated any time a transaction is made with the same payer_id.

---

fairpay_auction - Information that is auction-specific will be stored in this table.

Column Name		Type/Length		Information and Details
txn_id		char (17)		A unique ID designated by PayPal to identify the transaction.
auction_buyer_id		char (64)		The eBay auction ID of the buyer. This is their screen name on eBay.
auction_closing_date		char (25)		The closing date of the auction. The format is: HH:MM:SS MON X, YYYY TZN
auction_multi_item		integer		A field that indicates which item number from an auction is being referenced. NOTE: This field is not currently used. The PayPal Integration Guide is currently incorrect about how auction data is presented via IPN. We have confirmed this with PayPal as of January 25, 2005, and they agree there is a discrepancy between the documentation and the actual functionality. The reality is that all auction line items are presented as an individual transaction with its own txn_id, and all the data not in this table is currently tracked in fairpay_transactions. The guide says that every IPN from an auction will have the same txn_id, mc_gross, and payment_gross. Again, PayPal has verified for us that this is incorrect at this time.
flt_record_processed		char (1)		A one character field to help any business-rules software determine if a record has been processed. Defaults to "N".
flt_created		char (14)		Time the record was created. Format is YYYYMMDDHHMMSS
flt_id		char (64)		Unique ID assigned by flipna with which you may look up all records related to one IPN submission.

NOTES:

None.

---

fairpay_cart - All shopping cart-specific information will be stored in this table.

Column Name		Type/Length		Information and Details
txn_id		char (17)		A unique ID designated by PayPal to identify the transaction.
cart_item_num		integer		The line number of the item in the shopping cart.
tax		decimal		The tax collected on the cart line item, if the item was taxed separately.
item_name		char (127)		The item number of cart line item.
item_number		char (127)		The item name of the cart line item.
option_name1		char (64)		Descriptive name of first of two possible (optional) options for the cart line item.
option_selection1		char (200)		Value of first of two possible (optional) options for the cart line item.
option_name2		char (64)		Descriptive name of first of two possible (optional) options for the cart line item.
option_selection2		char (200)		Value of first of two possible (optional) options for the cart line item.
quantity		integer		Quantity of the cart line item that was purchased.
mc_gross		decimal		The gross amount paid for the cart line item in mc_currency..
mc_handling		decimal		The amount of handling paid in mc_currency.
mc_shipping		decimal		The amount of shipping of handling paid in mc_currency.
flt_record_processed		char (1)		A one character field to help any business-rules software determine if a record has been processed. Defaults to "N".
flt_created		char (14)		Time the record was created. Format is YYYYMMDDHHMMSS
flt_id		char (64)		Unique ID assigned by flipna with which you may look up all records related to one IPN submission.

NOTES:

None.

---

fairpay_masspay - All information related to mass payments will be stored in this table,

Column Name		Type/Length		Information and Details
txn_id		char (17)		A unique ID designated by PayPal to identify the transaction.
receiver_email		char (127)		Email address of the payment recipient.
mc_gross		decimal		Gross amount paid in mc_currency.
mc_fee		decimal		Fee paid in mc_currency.
mc_currency		char (3)		One of several results: USD - U.S. Dollars GBP - British Pounds Sterling EUR - Euros CAD - Canadian Dollars JPY - Japanese Yen
payment_gross		decimal		Gross payment in USD. This is a legacy field.
status		char (9)		One of several results: Completed - Payment processed. failed - Insufficient funds. reversed - Payee refused payment or unilateral payments were unclaimed for 30 days. pending - Unilateral payments still pending.
payment_fee		decimal		Fee in USD. This is a legacy field.
unique_id		char (17)		Unique ID assigned to this recipient by the merchant during the Mass Pay process.
flt_record_processed		char (1)		A one character field to help any business-rules software determine if a record has been processed. Defaults to "N".
flt_created		char (14)		Time the record was created. Format is YYYYMMDDHHMMSS
flt_verified		char (8)		Either INVALID or VERIFIED depending on whether the IPN was validated by PayPal as authentic and generated by their system.
flt_id		char (64)		Unique ID assigned by flipna with which you may look up all records related to one IPN submission.

NOTES:

• Each Mass-Pay record has a matching record in fairpay_transactions. You can track the overall payment_status of the record as opposed to the Mass-Pay status of the individual transaction (as well as other information) with a simple relation via txn_id.

---

fairpay_subscriptions - All subscription information will be stored in this table. This includes IPN transactions that will not show up in the fairpay_transactions table, as only payments themselves will have transaction ID's, and there are five other types of subscription IPN types.

Column Name		Type/Length		Information and Details
txn_id		char (17)		A unique ID designated by PayPal to identify the transaction.
txn_type		char (14)		One of several results: subscr_signup - Sign-up subscr_cancel - Cancellation subscr_failed - Payment failure subscr_payment - Subscription Payment subscr_eot - End of Term subscr_modify - User modifiction of subscription
subscr_date		char (25)		Date of subscription. The format is: HH:MM:SS MON X, YYYY TZN
subscr_effective		char (25)		Date on which subscriber modification will become effective. The format is: HH:MM:SS MON X, YYYY TZN
period1		char (10)		Length of first trial period. An integer, followed by a space and a single character: D - days W - weeks M - months Y - years
period2		char (10)		Length of second trial period. An integer, followed by a space and a single character: D - days W - weeks M - months Y - years
period3		char (10)		Length of regular subscription period. An integer, followed by a space and a single character: D - days W - weeks M - months Y - years
amount1		decimal		Amount for first trial period in USD. This is a legacy field.
amount2		decimal		Amount for first trial period in USD. This is a legacy field.
amount3		decimal		Amount for regular subscription period in USD. This is a legacy field.
mc_amount1		decimal		Amount for first trial period in mc_currency
mc_amount2		decimal		Amount for second trial period in mc_currency
mc_amount3		decimal		Amount for regular subscripton period in mc_currency
mc_currency		char (3)		One of several results: USD - U.S. Dollars GBP - British Pounds Sterling EUR - Euros CAD - Canadian Dollars JPY - Japanese Yen
recurring		char (1)		Is the subscription recurring at the regular rate (mc_amount3 and period3)? "1" means yes.
reattempt		char (1)		Retry on failed payments? A "1" means yes.
retry_at		char (25)		The date at which a retry will be attempted after a failed payment.
recur_times		integer		The number of payment installments that will occur at the regular rate (mc_amount3 and period3).
username		char (64)		Username generated by PayPal for subscription access, if the account generation feature is enabled.
password		char (127)		Password generated by PayPal for subscription access, if the account generation feature is enabled.
subscr_id		char (19)		Unique ID assigned by PayPal for an individual subscription.
payer_id		char (13)		A Unique customer ID assigned to the payer's account by PayPal.
item_name		char (127)		Item name as passed by the merchant.
item_number		char (127)		Item number as passed by the merchant.
option_name1		char (64)		Descriptive name of first of two possible (optional) options.
option_selection1		char (200)		Value of first of two possible (optional) options.
option_name2		char (64)		Descriptive name of second of two possible (optional) options.
option_selection2		char (200)		Value of second of two possible (optional) options.
flt_record_processed		char (1)		A one character field to help any business-rules software determine if a record has been processed. Defaults to "N".
flt_created		char (14)		Time the record was created. Format is YYYYMMDDHHMMSS
flt_verified		char (8)		Either INVALID or VERIFIED depending on whether the IPN was validated by PayPal as authentic and generated by their system.
flt_id		char (64)		Unique ID assigned by flipna with which you may look up all records related to one IPN submission.

NOTES:

• Only subscription transactions of type subscr_payment will generate a matching entry in fairpay_transactions. All other subscription transactions appear only in this table.