Two-Phase Transaction Flow

Database transactions on NCOG Earth Chain go through two phases: 

Phase 1: Local Endorsement (DDB Consensus). A designated subset of validators, called DDB validators, maintain the PostgreSQL database and handle SQL execution. When a user or smart contract submits a transaction that involves a database operation (for example, calling a stored procedure on the database), the following occurs: 

1. Transaction Routing: The transaction is identified as a database operation and is dispatched to the DDB validator nodes. (Non-database validators can ignore the contents of this tx for now, as they cannot execute it without the database state.) The DDB validators hold the transaction in a separate DB mempool queue for processing. 

2. Execution: One of the DDB validators (e.g., the one that received it first, or an elected leader among them) executes the transaction on its local PostgreSQL instance. This could be an SQL stored procedure or a batch of SQL statements defined by the dApp. Because all DDB validators maintain the same state, any validator could execute it – but to avoid duplication, one takes the lead. The execution is done within a standard ACID transaction on Postgres. For example, if the procedure is transferFunds(Alice, Bob, 50), the validator will perform the necessary SELECT/UPDATE on its database. 

3. WAL Extraction: After execution, the lead validator extracts the Write-Ahead Log (WAL) entries produced by PostgreSQL for this transaction. The WAL is a binary log that describes exactly what changes were made to the database pages. Using the WAL ensures determinism – it’s like a database delta that, when replayed, will reproduce the same result on any other Postgres instance that was in the same prior state. For example, the WAL might record “decrement Alice’s balance by 50, increment Bob’s balance by 50” (along with row IDs, etc.). 

4. Proposal to DDB Committee: The lead node then packages the result as a proposal to the other DDB validators. This proposal includes the original transaction details (or a hash of them), the WAL delta and a hash of the new state (or WAL), and the lead’s signature. 

5. Endorsement Voting: The other DDB validators receive the proposal and verify it: 

• They check the transaction’s authenticity (user’s signature) and that it was authorized (correct permissions, sufficient funds for any fees, etc.). 

• They apply the WAL to their own database copy (in an isolated manner, or by replaying it in memory) to confirm that it yields a consistent result. They could also re-run the stored procedure independently to double-check, but applying the WAL directly is much faster and ensures they get the exact same outcome. 

• They verify that any business rules or constraints hold (e.g., no account balance went negative unless allowed, triggers fired correctly, etc., which the database itself would enforce during execution). 

• If everything matches, each validator signs an endorsement: a digital signature attesting “Transaction X with result Y is valid according to the database state.” 

6. Quorum Agreement: If a supermajority (at least ⅔) of the DDB validators sign off, the transaction is considered endorsed by the DDB committee. This is analogous to a transaction being “pre-committed” – the database nodes agree on the outcome. At this point, the database changes can be tentatively applied to each validator’s Postgres, but marked as unfinalized. (If endorsements fail – e.g. one node got a different result – the transaction is aborted here and will not proceed to the main chain.) 

Now Phase 1 is complete: a majority of database nodes agreed on the transaction’s effect. Next is to broadcast this outcome to the whole network for final ordering: 

Phase 2: Global Finalization (Main Chain Consensus). The endorsed result from Phase 1 is submitted to the main blockchain (Forest consensus) so that all validators (including those not running the database) can finalize it and record it on-chain: 

1. Packaging the Proof: The DDB validators construct a commit transaction containing: 

• A reference to the executed database procedure call (e.g., an identifier of the stored procedure and the parameters, or simply the transaction hash). 

• The WAL hash or state hash representing the database changes. 

• The set of endorsement signatures from the DDB validators (or a threshold signature). 

• The original caller’s signature and any metadata (like a timestamp or nonce). 

This commit transaction is then gossiped into the Main Chain mempool (the pool for normal blockchain transactions) for inclusion in a block. 

1. Main Chain Validation: When this transaction is picked up by validators in a new block, every validator (DDB and non-DDB alike) verifies: 

• The endorsement proofs: Are there signatures from at least the required number of DDB validators? Are they authentic and do they correspond to this transaction’s hash? The public keys of the authorized DDB validator set are known (stored on-chain in a system contract or genesis config). Each validator checks that, say, ≥7 out of 10 signatures are present and valid, meeting the BFT threshold. 

• The user’s signature on the original request (to ensure the user indeed authorized this database action). 

• (Optionally) any read-write conflict check: If the DDB transaction read certain data versions, the main chain can ensure none of those data items were changed by a previously finalized transaction that the DDB nodes didn’t know about. (This is analogous to Fabric’s read-set validation. In NCOG, such conflicts are rare because ordering at the main chain will naturally serialize them. If a conflict is detected, the later transaction can be marked invalid on-chain.) 

Importantly, the non-DDB validators do not need to execute the SQL or have the DB locally – they just trust the endorsements. The cryptographic proof from Phase 1 is sufficient. Essentially, the heavy computation has been done by the DDB committee, and the whole network needs only to verify the committee’s signatures (which is fast, even considering PQ signatures are a few KB). 

1. Inclusion in Block: Once validated, this commit transaction is included in a block and ordered via the normal Forest Protocol consensus. When an Atropos block finalizes, it will include this transaction, thereby finalizing the database update globally. At this moment, the blockchain has an immutable record that “StoredProcedure X was executed with result Y at block Z”. 

2. Commit to Database: The DDB validators observe the finalized block and now mark the transaction as committed in their Postgres instances. Since they had applied it pending, it now becomes permanent. (If for some reason the transaction did not make it on-chain – e.g., the main chain rejected it due to a conflict or it never got included – the DDB nodes would roll back the tentative change to avoid divergence. Rollback can be done via standard database means or by keeping the transaction in a prepared state until commit. In practice, with correct protocol, such rollbacks are rare.) 

After Phase 2, the update is fully confirmed: all nodes agree on the new database state. Non-DDB nodes might not store the data, but they at least store the hash of the change and the proof, which is sufficient for audit or verification purposes. Any external observer can see on the blockchain that the operation happened and was endorsed by the required parties. 

Example 

To make this concrete, consider a simple scenario: updating a user’s balance in a decentralized finance application. Suppose there is a table Accounts(id, balance) in the DDB, and a stored procedure creditAccount(userID, amount). 

• A user calls creditAccount(42, 100) to add 100 units to account #42. This call is submitted as a transaction. 

• Phase 1: DDB validator A executes the procedure: it updates the row for user 42, increasing balance by 100. It produces a WAL record summarizing this change. Validators A, B, C (and others) in the DDB committee endorse it, all sign the hash of “Account42 balance +=100” with the new balance value. Now say 4 out of 5 DDB validators have signed, meeting the ≥⅔ threshold. 

• Phase 2: They package: “Tx: creditAccount(42,100), NewBalanceHash = H, Endorsements = {signatures of A,B,C,D}” and send that to the main chain. All validators verify those signatures correspond to authorized DB validators and that 4/5 ≥ 2/3. A block including this tx is finalized. The outcome “Account 42 credited with 100” is now on-chain. 

• The database state on DDB nodes is now committed with account 42’s balance increased by 100. If later someone queries the balance of account 42 (which can be done by a read-only query to any DDB node), they will get the updated value, and they could also fetch a proof (the latest state hash or an inclusion proof) tying that value to the blockchain state. 

This design ensures strong consistency: the database behaves like a single system even though it’s replicated across many nodes. Every database write goes through consensus, so two conflicting writes won’t both commit. The final source of truth is always the blockchain ledger. In effect, NCOG Earth Chain’s ledger not only records token transfers but also a log of database transactions (via their hashes and proofs). This provides a verifiable audit trail for all data modifications.