Explore feasibility of using sqlite as alernative backend

[sveitser]

It would be great for the dev-node and maybe also otherwise if we could use all features of the query service without using postgres.

Since late last year sqlite has JSONB support: https://sqlite.org/jsonb.html

Note that "index" and "transaction" are reserved in sqlite and alter table cannot be used to add a primary key.

With the below changes to the default schema and sequencer schemas, the schemas can at least be applied successfully with sqlite3 test.db < X.sql

diff --git a/migrations/V10__init_schema.sql b/migrations/V10__init_schema.sql
index 0ed12c67..bd3f3bec 100644
--- a/migrations/V10__init_schema.sql
+++ b/migrations/V10__init_schema.sql
@@ -47,7 +47,7 @@ CREATE TABLE leaf
     qc   JSONB NOT NULL
 );

-CREATE TABLE transaction
+CREATE TABLE "transaction"
 (
     hash VARCHAR NOT NULL,
     -- Block containing this transaction.
@@ -55,11 +55,11 @@ CREATE TABLE transaction
     -- Position within the block. Transaction indices are an application-specific type, so we store
     -- it as a serialized blob. We use JSON instead of a binary format so that the application can
     -- make use of the transaction index in its own SQL queries.
-    index JSONB NOT NULL
+    "index" JSONB NOT NULL,
+    PRIMARY KEY (block_height, "index")
 );
-ALTER TABLE transaction ADD CONSTRAINT transaction_pk PRIMARY KEY (block_height, index);
 -- This index is not unique, because nothing stops HotShot from sequencing duplicate transactions.
-CREATE INDEX transaction_hash ON transaction (hash);
+CREATE INDEX transaction_hash ON "transaction" (hash);

 CREATE TABLE pruned_height (
     id SERIAL PRIMARY KEY,

diff --git a/sequencer/api/migrations/V14__state_tables.sql b/sequencer/api/migrations/V14__state_tables.sql
index d5704006..0dddca90 100644
--- a/sequencer/api/migrations/V14__state_tables.sql
+++ b/sequencer/api/migrations/V14__state_tables.sql
@@ -9,14 +9,10 @@ CREATE TABLE fee_merkle_tree (
   children INT[],
   children_bitvec BIT(256),
   index JSONB,
-  entry JSONB
+  entry JSONB,
+  PRIMARY KEY (path, created)
 );

-ALTER TABLE
-  fee_merkle_tree
-ADD
-  CONSTRAINT fee_merkle_tree_pk PRIMARY KEY (path, created);
-
 CREATE INDEX fee_merkle_tree_created ON fee_merkle_tree (created);

 CREATE TABLE block_merkle_tree (
@@ -26,12 +22,8 @@ CREATE TABLE block_merkle_tree (
   children INT[],
   children_bitvec BIT(3),
   index JSONB,
-  entry JSONB
+  entry JSONB,
+  PRIMARY KEY (path, created)
 );

-ALTER TABLE
-  block_merkle_tree
-ADD
-  CONSTRAINT block_merkle_tree_pk PRIMARY KEY (path, created);
-