[Feature]: Note the canonical bitcoin blocks in the database

[djordon]

Feature - Note canonical bitcoin and stacks blocks in DB

1. Description

As the block-observer writes blocks into the database, it should figure out which blocks are still on the canonical chain.

This ticket is only about the part where we note the canonical chain in the database. Once this is there, we could move on to updating each of our queries.

2. Technical Details

Determining the canonical chain in the database is relatively straightforward. This could be done with a query that looks like so:

WITH RECURSIVE canonical_chain AS (
    -- Start from the chain tip
    SELECT
        block_hash
      , block_height
      , parent_hash
    FROM sbtc_signer.bitcoin_blocks
    WHERE block_hash = $1

    UNION ALL

    -- Recursively get parent blocks
    SELECT
        parent.block_hash
      , parent.block_height
      , parent.parent_hash
    FROM sbtc_signer.bitcoin_blocks AS parent
    JOIN canonical_chain AS child
      ON parent.block_hash = child.parent_hash
)
UPDATE sbtc_signer.bitcoin_blocks
SET is_canonical = (block_hash IN (
    SELECT block_hash FROM canonical_chain
))

The above query is quite inefficient, but gets the job done. The actual implementation should do something smarter where we only update blocks that need updating, but the principle is the same.

2.1 Acceptance Criteria

• The signers take note of which blocks are on the canonical bitcoin blockchain whenever they observe a new bitcoin chain tip.

3. Related Issues and Pull Requests (optional)

Relates to #1888

4. Addendum

The particular approach that I have in mind is to add a boolean is_canonical column to the bitcoin_blocks table that defaults to NULL and is updated to either TRUE of FALSE. When you get find a new bitcoin chain tip you:

1. Insert all unknown blocks into the database
2. Recurse back, starting at the tail of the new blocks, until you find another block where is_canonical is TRUE.
3. Use a query similar to the one above, just filtering for blocks with height that is greater than the one found at step (2).