Tutorial for persist data somewhere else (in a non-ethereum app) and use dapps to interact with it and verify data integrity

When building a blockchain application what is the best practice for persisting the data in a non-ethereum app for the purpose of displaying state on a website (such as a backing postgres database).

Some specific questions:

Why is Bulk access to lists/arrays/etc is described as being painful in Solidity? relevant
What is the best way to capture blockchain events and update an off-chain database.
What is the best way to verify the integrity of your off-chain database (how frequently etc.)
Can you avoid using a backing database and query the blockchain directly?

In the truffle pet shop tutorial they have a view that returns the entire chain getAdopters() public view returns (address[16]) http://truffleframework.com/tutorials/pet-shop

Solution

Why is Bulk access to lists/arrays/etc is described as being painful in Solidity?

There's a few reasons for this:

There is no built in mechanism for iterating through collections in Solidity. You can do a standard for loop with an array using it's length, but you can't do this with mappings.

Returning complex objects is messy. If your list contains a struct, you can't simply return the struct itself to your client. You have to return the item decomposed. Converting the struct to a decomposed array is ugly:

struct MyStruct {
  uint256 id;
  bytes32 name;
}

MyStruct[] _structs;

function getAllStructs() public constant returns (uint256[], bytes32[]) {
    uint256[] memory ids = new uint256[](_structs.length);
    bytes32[] memory names = new bytes32[](_structs.length);

    for (uint i = 0; i < _structs.length; i++) {
        ids[i] = _structs[i].id;
        names[i] = _structs[i].name;
    }

    return (ids, names);
}

Iterating through arrays still requires gas. Even if you're not paying for the gas (when performing this in a constant function), you can still have out of gas exceptions when your arrays become very large. The Truffle pet shop example gets away with this because it explicitly limits the array to 16 elements.

What is the best way to capture blockchain events and update an off-chain database.

"Best" way depends on your business goals and tolerance for stale data. But, probably the most common way is to set up a watch on contract events. When you receive an event, you update your DB with the custom data you stuff into the event. However, you have to make sure to handle orphaned blocks as well (there is a field in the metadata called removed which tells you if an event is no longer valid because of a block being orphaned). When you receive an orphaned event, remove it from the DB. After 12 subsequent block verifications come in, you can safely assume the event will not be orphaned.

What is the best way to verify the integrity of your off-chain database (how frequently etc.)

Again, this depends on tolerance levels of your business requirements. If you can delay using information from the DB until you're certain of block verifications, then you would simply persist block or timestamp information that lets your app know that the data in your DB mirrors what is verified on the blockchain. If you're concerned about a client process responsible for watching events has failed, you need to have failover watch clients, or allow duplicate persistence (with subsequent deduping), or track verified block numbers as the come in (or some combination of the 3). I'm sure there are plenty of other options you can architect for this as well.

Can you avoid using a backing database and query the blockchain directly?

Yes, it possible, assuming you can avoid gas limit issues as mentioned above with constant functions and you don't have to do any complex post processing of your data inside your contract for your application. Since constant functions run within the local EVM, you just need to make sure your dedicated node is up and running. To do this, you'd most likely want multiple servers running as fully synced nodes.