2️⃣Customer Liability Extract
2. Creating the Customer Liability Extract (CLE) Summary
This section is made up of 6 preparatory steps, all aimed at creating a list of customer account balances (by each individual customer). This is likely the most time-consuming part of the preparation processes. Typically, the engineering team has to create a "custom" report to be used by the finance team for Proof of Reserves purposes. However, once the report has been configured, the parameters can be used for all future Proof of Reserves.
2a. Overview of the CLE
The Customer Liability Extract (CLE) is a crucial component of the Proof of Reserve Attestation process. Simply put, the CLE is an output of all in-scope platform users and their account balances. The CLE is the source file for the Merkle Tree generated as part of the PoR, which is what users will query to confirm inclusion with the PoR. Once the Merkle Tree is generated with a Merkle Root (i.e. hash of ALL customer data included), the data will effectively be “sealed.” This Merkle Root is what will be included within the attestation report, effectively linking the attestation data to the data that users will query.
Any future changes made after generating a CLE will alter the Merkle Root hash of the resulting Merkle Tree, thereby breaking the link between the Merkle Root listed on the attestation report and the public-facing, searchable Merkle Tree. This process can we complicated, so we recommend a Test Run!
The CLE is usually an output from your database. You should be prepared to generate the CLE in TXT, CSV, or Excel format and to provide to the attestation provider.
The columns on the CLE should include a hash of some data point unique to each customer and each of the in-scope platform liabilities they hold with you as of the “snapshot” time of the PoR.
While it may sound simple, there are many different components of the CLE generation process. We will go over each of the key components within the upcoming sections.
2a. Management Considerations & Reminders
2a. Example Scenarios
CLE Output File Formatting:
CLE Merkle Root per Attestation Report & Searchable Merkle Tree:
2b. Aggregating & Segregating Customer Liabilities
The important next step involves deciding on the format for presenting customer liabilities (illustrated with the example of BTC) on our platform. The options include:
Popular Option #1: Aggregating and summing all liabilities that are denominated in the same asset.
Eg. All spot, margin, staked, futures and other BTC-denominated liabilities are aggregated into a single “BTC-liability” value for each user
Popular Option #2: Disaggregating each individual liability type, even if the same asset denominated
Eg. All spot, margin staked, futures and other BTC-denominated liabilities are kept separate.
For more complex organizations, a combination of approaches may have to be used. Other options are available, just not as popular as the aforementioned two.
2b. Key Considerations
This choice will determine how many “columns” your Merkle Tree has (1 column per liability). This is the same number of “balances” each user will have when searching in the Merkle Tree.
If an exchange does not segregate underlying assets by liability type (i.e. margin vs. spot vs. futures), it may be impossible to include/exclude some liabilities and not others if the corresponding underlying assets are commingled.
2b. Management Outputs
2c. Example Scenarios
Customer Liabilities in Aggregated Format
fasdgr9qw
0.05
1.25
50.50
gy2thw3ef
0.01
5.50
100000.00
5jusj983dd
0.50
2.30
10.05
9ktrsdfcws
0.00
0.00
0.00
Customer Liabilities in Segregated Format
fasdgr9qw
0.03
0.01
0.01
1.25
0.00
gy2thw3ef
0.01
0.00
0.00
4.50
1.00
5jusj983dd
0.50
0.00
0.00
1.00
1.30
9ktrsdfcws
0.00
0.00
0.00
0.00
0.00
Regardless of the presentation format you select, the overall liability balance will remain unchanged. The key difference lies in the display method - a crucial aspect as it impacts how users interpret their account balances during the verification process using our widget's Merkle Verification Form.
Widget Balance Verification & Merkle Tree in Aggregated Format
Widget Balance Verification & Merkle Tree in Segregated Format (see ADA and DOT balances below)
2c. Creating the Hashed User IDs
A 'Hashed User ID' (”HUID”) is a unique identifier assigned to each customer who is part of the Proof of Reserves. Your task is to generate this Hashed User ID for every customer, which should then be included as the first column in the Customer Liability Extract. The full Merkle Tree may be made public, so it is a priority to keep customer information private, which is why we use a HUID.
Theoretically, an HUID can simply be any unique identifier that is mapped to a unique customer. However, most of the time, it is derived from some elements of customer information so the customers can be easily mapped to HUIDs and HUIDs can be created/recreated with ease.
The most popular strategy to create a Hashed User ID is to use the following formula, however, any variation or formula may be used.
SHA256[Customer ID, PoR #, Salt Value]
The output is a string of alphanumeric characters that should be provided to the Customer when they log into their account.
This HUID is then concatenated with the Customers account balances to create their individual Merkle Leaf, which is then hashed with all other Customer Merkle Leafs to derive the Merkle Root.
Customer Merkle Leaf = SHA256[HashedUserID, Balance1, Balance 2, Balance 3,…]
Merkle Root = SHA256 [SHA256[Concat[Merkle Leaf 1, Merkle Leaf 2], Hash[Concat[Merkle Leaf 3, Merkle Leaf 4],…..]
Usually, a user’s Hashed User ID is different for each Proof of Reserves. However, they don’t necessarily need to be different across data sets.
2c. Management Output
2c. Example Scenarios
Hashed User ID Calculation Showcase:
CUST1234567890
P006
a34527c365b10f9a4c8f5bcc77f36a53
a1e5f17c7259047ee1d8eda85d9555a84d3a6bd2ee792b029b7bd83a15a2d771
CUST1234567891
P006
0611b56ff5f2a81efe2eeb29668aacf5
f669453c2d4027541f28e13c3b0804a604d5b1eab2c564add6b76dcc3294ce77
CUST1234567892
P006
1090a9b1cc423d46ce494a0c6c93a1e8
8df3fa10a701526e591ed43cb3f431db099c6c0f15979e99017a54ca0dee2669
CUST1234567893
P006
c2bb396c10a2c3e2f89ae0978a970d2b
4fc97f0dd1e7bc33a58531549468d49a4b6f16d52aed22771c3b6fdebc08f0e3
CUST1234567894
P006
8b8dde215196d33cf3a10870779ae13c
002c8b0440e56e84fa306d443b0eb157a7cdfe8bd82b43c6bd1173f1284cef62
Example Calculation HUID Calculation with a SHA256 Calculator:
Sample Merkle Leaf Calculations
a1e5f17c7259047ee1d8eda85d9555a84d3a6bd2ee792b029b7bd83a15a2d771
0.05
1.25
50.50
bbc2ccdabd4126e5
f669453c2d4027541f28e13c3b0804a604d5b1eab2c564add6b76dcc3294ce77
0.01
5.50
100000.00
313d80f15dc6619a
8df3fa10a701526e591ed43cb3f431db099c6c0f15979e99017a54ca0dee2669
0.50
2.30
10.05
962d645552ac10e5
4fc97f0dd1e7bc33a58531549468d49a4b6f16d52aed22771c3b6fdebc08f0e3
0.00
0.00
0.00
e738eb9e529c6443
002c8b0440e56e84fa306d443b0eb157a7cdfe8bd82b43c6bd1173f1284cef62
1.02
3.19
8562.18
5c740cffe7b1ad4a
Merkle Leaf Calculation Showcase:
2d. Script(s) to Extract Customer Liability Extract (CLE)
To product a CLE, one typically has to extract the information from the their platform database using some sort of logic or script(s). However, the script/logic should be unique to the purpose of producing CLE. Typically, you must construct key logic points to include/exclude specific information. Some of the most important components of the logic are noted below
Historical Balance Query: The script should include logic to extract customer information as of a specific point in time. Sometimes, custodians are unable to extract historical snapshots. In that case, the report generation/observation with the attest provider must be exactly at the snapshot date. Building in the capabilities to extract historical information is very beneficial. This enables the company/attestor to more flexibility plan the CLE generation and reduces the possibility of having to discard a CLE generated due to bugs or error.
Exclusion of Certain Accounts: At times, it may be necessary to exclude certain platform balances, such as internal accounts, that do not represent true liabilities to customers. These exclusions should be taken into account during the script creation process. Furthermore, it's important to maintain substantiation for the reasons behind excluding these specific accounts.
In-Scope Liability Filtering: Ensure that your script only outputs the data required for the Proof of Reserves.
These are the primary considerations to bear in mind. However, your specific setup and script may have unique considerations.
Beforehand and afterwards, you should be ready to provide the script to the attest provider so that they can substantiate the reasonableness of the logic. During the extraction process, the attest provider will observe you use the script to generate the CLE.
2d. Management Outputs
2d. Example Scenario
Testing Accounts
To test new features or check existing functionalities
An account filled with fictitious funds used to simulate customer activity during testing
Administrative Accounts
For various managerial tasks
An account used by an internal team member to audit transaction records
Operational Accounts
For daily operational transactions
An account used for paying employee salaries or settling payments with suppliers
These are all examples of internal accounts. Their balances and transactions are typically excluded from reports on customer assets and liabilities as they do not represent liabilities to customers or the financial condition of the platform.
2e. Formatting the Customer Liability Extract (CLE)
After the “core” of the script(s) logic has been created for the CLE, the you should “clean up” the file with the following formatting adjustments to ensure a consistent balance presentation and proper format for ingestion into the Merkle Tree Generator.
Detailed Formatting Notes
No Scientific Notation: 1E-07 » 0.0
Chop off excess zeros to the right of the decimal. For example, 0.58390000 » 0.5839
If 0 balances, present as 0.0. For example, 0.000 » 0.0
If the balance is less than 1 but more than 0, have 1 zero to the left of the decimal. For example, .532 » 0.532
Delimit columns by commas
No commas in balance figures (since we are using commas as the delimiter in the csv/txt file). For example, 1,700 » 1700.
File Format: CSV or TXT
You can use this test file as a wholistic example that implements all of these concepts:
2e. Management Outputs
2e. Example Scenario
2f. Preparing for the Attestation Observation
Once you are comfortable with the CLE after internal test runs, the next step is to schedule an observation with the attestation provider to observe the report generation process. While only one “Production” observation is necessary, it is recommend to perform a “Test Run” with the attest provider as well.The main purpose of the observation process is to ensure the completeness and accuracy of the Customer Liability Extract. The largest risk (from the attest provider’s perspective) is that the engaging party could “cheat” by manipulating the data by under-reporting the liabilities owed to customers. To mitigate this risk, the attest provider has already reviewed the Architecture Diagram provided. It is during the observation process that the attest provider will:
Corroborate the understanding of the system architecture, including the underlying databases and tables that the data originates that is exported as the CLE
The logic of the script(s), including the key functions, exclusions, exclusion rationale (and supporting evidence), and other key elements of the script(s)
Observe the report generation of the CLE
Confirm the totals and a number of line items per the CLE tie and agree to the details in the scripting terminal
Observe the delivery of the CLE to the attest provider via secured communication line.
Note that additional procedures may be performed, however the items noted above are the “standard” set of procedures. Only the CLE from the “Production Run” will be input into the Merkle Tree Generator for processing. These procedures will typically be disclosed within the attestation report.
2f. Management Outputs
2f. Example Scenario
Example of Procedures and Reporting Findings within a Final Attestation Report
Last updated
