### Cardano Node Setup Example

Source: https://github.com/emurgo/cardano-db-sync/blob/master/doc/building-running.md

Example commands for setting up and running a local Cardano Node. This involves cloning the repository, checking out a specific tag, and running the node with the mainnet configuration.

```shell
git clone https://github.com/IntersectMBO/cardano-node
cd cardano-node
git checkout <latest-official-tag> -b tag-<latest-official-tag>
nix run .#mainnet/node
```

--------------------------------

### Download Docker Compose Example

Source: https://github.com/emurgo/cardano-db-sync/blob/master/doc/docker.md

Fetches the example docker-compose.yml file from the official repository.

```bash
curl -o docker-compose.yml \
  https://raw.githubusercontent.com/IntersectMBO/cardano-db-sync/master/docker-compose.example.yml
```

--------------------------------

### Install hlint

Source: https://github.com/emurgo/cardano-db-sync/blob/master/doc/hlint-stylish-haskell.md

Install hlint using cabal. Ensure the installation directory is added to your PATH.

```bash
cabal install hlint
```

--------------------------------

### Install secp256k1 Library (Linux/macOS)

Source: https://github.com/emurgo/cardano-db-sync/blob/master/doc/building-running.md

Install the secp256k1 library. Use 'sudo make install' on Linux and 'make install' on macOS.

```shell
# On Linux
sudo make install

# On macOS
make install
```

--------------------------------

### Start cardano-smash-server with Docker

Source: https://github.com/emurgo/cardano-db-sync/blob/master/doc/docker.md

Use this command to start the cardano-smash-server with essential environment variables for network and database connection.

```bash
docker run \
  --env NETWORK=mainnet \
  --env POSTGRES_HOST=postgres \
  --env POSTGRES_PORT=5432 \
  --env SMASH_USER=smash-admin \
  --env SMASH_PASSWORD=smash-password \
  --publish 3100:3100 \
  --volume node-ipc:/node-ipc \
  ghcr.io/IntersectMBO/cardano-smash-server:13.3.0.0
```

--------------------------------

### Build and Install SMASH with Cabal

Source: https://github.com/emurgo/cardano-db-sync/blob/master/doc/smash.md

Use 'cabal build' and 'cabal install' to build and install the cardano-smash-server package.

```bash
cabal build cardano-smash-server
cabal install cardano-smash-server
```

--------------------------------

### Start PostgreSQL Service with Homebrew

Source: https://github.com/emurgo/cardano-db-sync/blob/master/doc/troubleshooting.md

Start the PostgreSQL daemon service using Homebrew. Ensure this is running before attempting to connect.

```bash
brew services start postgresql
```

--------------------------------

### Install Profiling Tools (ghc-debug-brick)

Source: https://github.com/emurgo/cardano-db-sync/blob/master/dev-tools/README.md

Installs the ghc-debug-brick tool for memory profiling. Requires cloning the repository and using cabal.

```bash
# Install ghc-debug-brick
git clone https://gitlab.haskell.org/ghc/ghc-debug.git
cd ghc-debug/brick
cabal install ghc-debug-brick
```

--------------------------------

### Install ghc-debug-brick

Source: https://github.com/emurgo/cardano-db-sync/blob/master/dev-tools/profiling/README.md

Install the ghc-debug-brick package to enable interactive heap profiling. This command automatically handles dependency installation.

```bash
cabal install ghc-debug-brick
```

--------------------------------

### Install GHC and Cabal using GHCup

Source: https://github.com/emurgo/cardano-db-sync/blob/master/doc/installing.md

Installs and sets the specified versions of GHC and Cabal using the GHCup tool. Ensure you open a new terminal after installation.

```bash
ghcup install ghc 9.6.7
ghcup install cabal 3.12.1.0
ghcup set ghc 9.6.7
ghcup set cabal 3.12.1.0
```

--------------------------------

### Install Monitoring Tools (macOS)

Source: https://github.com/emurgo/cardano-db-sync/blob/master/dev-tools/README.md

Installs necessary tools for monitoring on macOS using Homebrew.

```bash
# macOS
brew install tmux prometheus postgres_exporter node_exporter grafana
```

--------------------------------

### Clone cardano-db-sync Repository

Source: https://github.com/emurgo/cardano-db-sync/blob/master/doc/building-running.md

Clone the cardano-db-sync repository from GitHub to start the setup process.

```shell
git clone https://github.com/IntersectMBO/cardano-db-sync
```

--------------------------------

### Install Monitoring Tools (Linux apt)

Source: https://github.com/emurgo/cardano-db-sync/blob/master/dev-tools/README.md

Installs necessary tools for monitoring on Debian/Ubuntu-based Linux systems using apt.

```bash
# Linux (apt)
sudo apt-get install tmux prometheus postgres-exporter prometheus-node-exporter grafana
```

--------------------------------

### Start Local Hoogle Server

Source: https://github.com/emurgo/cardano-db-sync/blob/master/CONTRIBUTING.rst

Start a local Hoogle server with all dependencies using a specific GHC version from the Nix development shell.

```bash
nix develop .#ghc96 --command hoogle server --local --port 8000
```

--------------------------------

### Install blst Library

Source: https://github.com/emurgo/cardano-db-sync/blob/master/doc/installing.md

Installs the blst library by creating a pkg-config file, copying header files, and copying the static library. Ensure you are in the 'blst' directory before running these commands.

```bash
cat > libblst.pc << EOF
prefix=/usr/local
exec_prefix=\${prefix}
libdir=\${exec_prefix}/lib
includedir=\${prefix}/include

Name: libblst
Description: Multilingual BLS12-381 signature library
URL: https://github.com/supranational/blst
Version: 0.3.10
Cflags: -I\${includedir}
Libs: -L\${libdir} -lblst
EOF

sudo cp libblst.pc /usr/local/lib/pkgconfig/
sudo cp bindings/blst_aux.h bindings/blst.h bindings/blst.hpp  /usr/local/include/
sudo cp libblst.a /usr/local/lib
sudo chmod 644 
  /usr/local/lib/libblst.*
  /usr/local/include/{blst.*,blst_aux.h}
```

--------------------------------

### Install PostgreSQL with Homebrew

Source: https://github.com/emurgo/cardano-db-sync/blob/master/doc/troubleshooting.md

Use this command to install PostgreSQL on macOS using Homebrew if it is not already installed.

```bash
brew install postgres
```

--------------------------------

### Run SMASH Server

Source: https://github.com/emurgo/cardano-db-sync/blob/master/doc/smash.md

Start the SMASH server, specifying the PGPASSFILE, configuration file, port, and admin list. Ensure the PGPASSFILE matches the one used for db-sync.

```bash
PGPASSFILE=config/pgpass-mainnet cardano-smash-server \
     --config config/mainnet-config.yaml \
     --port 3100 \
     --admins admins.txt
```

--------------------------------

### Install Monitoring Tools

Source: https://github.com/emurgo/cardano-db-sync/blob/master/dev-tools/monitoring/README.md

Installs Prometheus, postgres_exporter, node_exporter, and Grafana using package managers for macOS and Linux.

```bash
# macOS
brew install prometheus postgres_exporter node_exporter grafana

# Linux (apt)
sudo apt-get install prometheus postgres-exporter prometheus-node-exporter grafana

# Linux (yum)
sudo yum install prometheus postgres_exporter node_exporter grafana
```

--------------------------------

### Build and Install secp256k1

Source: https://github.com/emurgo/cardano-db-sync/blob/master/doc/installing.md

Compiles and installs the secp256k1 library with schnorr signatures and experimental features enabled. Ensure you are in the 'secp256k1' directory before running these commands.

```bash
./autogen.sh
./configure --enable-module-schnorrsig --enable-experimental
make
make check
sudo make install
```

--------------------------------

### Start Docker Compose Services

Source: https://github.com/emurgo/cardano-db-sync/blob/master/doc/docker.md

Launches cardano-node, postgresql, and cardano-db-sync in detached mode and follows logs.

```bash
docker compose up -d && docker compose logs -f
```

--------------------------------

### Start PostgreSQL with Docker Compose

Source: https://github.com/emurgo/cardano-db-sync/blob/master/doc/docker.md

Starts the PostgreSQL database service using Docker Compose with the specified docker-test.yml configuration file.

```bash
docker compose -f docker-test.yml up
```

--------------------------------

### Start Cardano Node

Source: https://github.com/emurgo/cardano-db-sync/blob/master/doc/running.md

Launches the Cardano Node with specified configuration and database paths. Ensure the config and topology files exist at the specified locations.

```bash
cardano-node run \
    --config ~/src/cardano-environments/config.json
    --topology ~/src/cardano-environments/topology.json \
    --database-path ~/src/cardano-environments/mainnet/db \
    --socket-path node.socket \
    --host-addr 0.0.0.0 
```

--------------------------------

### Install secp256k1 Library

Source: https://github.com/emurgo/cardano-db-sync/blob/master/doc/building-running.md

Install the secp256k1 library, a prerequisite for building with Cabal. The provided git SHA should be validated against ./github/workflows/haskell.yml.

```shell
./scripts/secp256k1-setup.sh ac83be33d0956faf6b7f61a60ab524ef7d6a473a
```

--------------------------------

### Install Cardano DB Sync with Nix Profile

Source: https://github.com/emurgo/cardano-db-sync/blob/master/doc/installing-with-nix.md

Installs the built Cardano DB Sync executable into your Nix profile for system-wide access.

```bash
nix profile install .
```

--------------------------------

### Install Monitoring Tools (Linux yum)

Source: https://github.com/emurgo/cardano-db-sync/blob/master/dev-tools/README.md

Installs necessary tools for monitoring on Red Hat-based Linux systems using yum.

```bash
# Linux (yum)
sudo yum install tmux prometheus postgres_exporter node_exporter grafana
```

--------------------------------

### Install Cardano DB Sync Binary

Source: https://github.com/emurgo/cardano-db-sync/blob/master/doc/installing.md

Copy the executable cardano-db-sync binary to `~/.local/bin`. Ensure this directory is in your system's PATH.

```bash
mkdir -p ~/.local/bin
cp -p \
  "$(find . -name cardano-db-sync -executable -type f)" \
  ~/.local/bin/
```

--------------------------------

### Start PostgreSQL Service

Source: https://github.com/emurgo/cardano-db-sync/blob/master/doc/upgrading-postgresql.md

Starts the PostgreSQL database service after the schema upgrade is complete.

```bash
sudo systemctl start postgresql
```

--------------------------------

### Start Grafana Service

Source: https://github.com/emurgo/cardano-db-sync/blob/master/dev-tools/monitoring/README.md

Starts the Grafana server service on macOS or Linux systems.

```bash
# macOS
brew services start grafana

# Linux
sudo systemctl start grafana-server
```

--------------------------------

### Build and Install libsodium

Source: https://github.com/emurgo/cardano-db-sync/blob/master/doc/installing.md

Compiles and installs the libsodium library after checking out a specific revision. Ensure you are in the 'libsodium' directory before running these commands.

```bash
./autogen.sh
./configure
make
make check
sudo make install
```

--------------------------------

### Start Profiling and Analyze Memory

Source: https://github.com/emurgo/cardano-db-sync/blob/master/dev-tools/README.md

Starts the profiling script and then attaches ghc-debug-brick to analyze memory. The analysis step should be run in a separate terminal after several hours.

```bash
cd dev-tools/profiling
./scripts/start-profiling.sh

# In another terminal (after several hours):
ghc-debug-brick /tmp/cardano-db-sync.ghc-debug
```

--------------------------------

### Install New PostgreSQL Version (Debian/Ubuntu)

Source: https://github.com/emurgo/cardano-db-sync/blob/master/doc/upgrading-postgresql.md

Installs a new PostgreSQL version alongside the existing one using the PostgreSQL APT repository.

```bash
sudo apt install postgresql-15
```

--------------------------------

### Setup PostgreSQL Monitoring User

Source: https://github.com/emurgo/cardano-db-sync/blob/master/dev-tools/monitoring/README.md

Creates a dedicated user for monitoring PostgreSQL, granting necessary privileges.

```sql
-- Connect to your database
psql -U postgres -d cexplorer

-- Create monitoring user
CREATE USER postgres_exporter WITH PASSWORD 'secure_password';
GRANT pg_monitor TO postgres_exporter;
GRANT CONNECT ON DATABASE cexplorer TO postgres_exporter;
```

--------------------------------

### Start Cardano DB Sync Node

Source: https://github.com/emurgo/cardano-db-sync/blob/master/doc/running.md

Starts the Cardano DB Sync service, linking it to the Cardano Node via a socket path and specifying configuration and state directories. The PGPASSFILE environment variable must be set correctly.

```bash
PGPASSFILE=config/pgpass-mainnet cardano-db-sync -- \
    --config ~/src/cardano-environments/db-sync-config.json \
    --socket-path ~/src/cardano-environments/mainnet/node.socket \
    --state-dir ~/src/cardano-environments/mainnet/ledger-state \
    --schema-dir schema/
```

--------------------------------

### Start Monitoring Service

Source: https://github.com/emurgo/cardano-db-sync/blob/master/dev-tools/README.md

Starts the monitoring script for Prometheus and Grafana. Access Prometheus at http://localhost:9090 and Grafana at http://localhost:3000.

```bash
cd dev-tools/monitoring
./scripts/start-monitoring.sh
```

--------------------------------

### Install Xcode Command Line Tools on macOS

Source: https://github.com/emurgo/cardano-db-sync/blob/master/doc/installing.md

Installs Xcode command line tools on macOS, which may resolve issues related to missing header files like 'stdlib.h'.

```bash
xcode-select --install
```

--------------------------------

### Verify Cabal Installation Path

Source: https://github.com/emurgo/cardano-db-sync/blob/master/doc/installing.md

Checks if the 'cabal' executable is being used from the GHCup installation directory. The expected path should be similar to '/home/<user>/.ghcup/bin/cabal'.

```bash
which cabal
```

--------------------------------

### Connect to Other Networks with Docker Compose

Source: https://github.com/emurgo/cardano-db-sync/blob/master/doc/docker.md

Starts services using Docker Compose for a specified network, e.g., 'preprod'.

```bash
NETWORK=preprod docker compose up -d && docker compose logs -f
```

--------------------------------

### Run Monitoring and Profiling Together

Source: https://github.com/emurgo/cardano-db-sync/blob/master/dev-tools/README.md

Starts both the monitoring and profiling services concurrently in separate terminals.

```bash
# Terminal 1: Monitoring
cd dev-tools/monitoring && ./scripts/start-monitoring.sh

# Terminal 2: Profiling
cd dev-tools/profiling && ./scripts/start-profiling.sh
```

--------------------------------

### Check Cardano DB Sync Version

Source: https://github.com/emurgo/cardano-db-sync/blob/master/doc/installing-with-nix.md

Verifies the installed version of Cardano DB Sync by running the executable with the --version flag.

```bash
cardano-db-sync --version
```

--------------------------------

### Create Build Directory with Nix

Source: https://github.com/emurgo/cardano-db-sync/blob/master/doc/installing-with-nix.md

Sets up the necessary directory structure for building the project using Nix.

```bash
mkdir -p ~/src
cd ~/src
```

--------------------------------

### Set up PostgreSQL Database

Source: https://github.com/emurgo/cardano-db-sync/blob/master/doc/building-running.md

Set up the PostgreSQL database using the provided script and the configuration in `config/pgpass-mainnet`. The `--createdb` flag is used to create the database.

```shell
PGPASSFILE=config/pgpass-mainnet scripts/postgresql-setup.sh --createdb
```

--------------------------------

### Build SMASH with Nix

Source: https://github.com/emurgo/cardano-db-sync/blob/master/doc/smash.md

Build the cardano-smash-server using Nix and output the executable to 'smash-server'.

```bash
nix build .#cardano-smash-server -o smash-server
```

--------------------------------

### Navigate to Build Directory

Source: https://github.com/emurgo/cardano-db-sync/blob/master/doc/installing.md

Change to the directory where you will perform your builds.

```bash
cd ~/
src
```

--------------------------------

### Execute Snapshot Creation Script

Source: https://github.com/emurgo/cardano-db-sync/blob/master/doc/state-snapshot.md

This is the command printed by `cardano-db-tool prepare-snapshot`. It uses `scripts/postgresql-setup.sh` to perform the actual snapshot creation. Adapt the schema version and architecture as needed.

```bash
PGPASSFILE=config/pgpass-mainnet scripts/postgresql-setup.sh \
  --create-snapshot db-sync-snapshot-schema-13.7-block-5796064-x86_64 <state-dir>/<slot>
```

--------------------------------

### Create State Snapshot with cardano-db-tool

Source: https://github.com/emurgo/cardano-db-sync/blob/master/doc/state-snapshot.md

Use this command to prepare and create a state snapshot. Ensure `db-sync` is stopped before execution. The command prints the actual snapshot creation command.

```bash
PGPASSFILE=config/pgpass-mainnet cardano-db-tool prepare-snapshot --state-dir <state-dir>
```

--------------------------------

### Create Configuration Directory

Source: https://github.com/emurgo/cardano-db-sync/blob/master/doc/running.md

Creates a directory structure to store configuration files for different Cardano network environments.

```bash
mkdir -p ~/src/cardano-environments/{mainnet,preprod,preview,sanchonet}
cd ~/src/cardano-environments
```

--------------------------------

### Create Working Directory

Source: https://github.com/emurgo/cardano-db-sync/blob/master/doc/docker.md

Sets up the necessary directory structure for Docker Compose.

```bash
mkdir ~/src
cd ~/src
```

--------------------------------

### Configure PostgreSQL Credentials for Testing

Source: https://github.com/emurgo/cardano-db-sync/blob/master/doc/docker.md

Creates and sets up a pgpass-test file with PostgreSQL credentials for testing. Ensures the file has restricted permissions and sets the PGPASSFILE environment variable.

```bash
echo "localhost:5432:cexplorer:postgres:v8hlDV0yMAHHlIurYupj" > config/pgpass-test
chmod 0600 config/pgpass-test
export PGPASSFILE=$PWD/config/pgpass-test
```

--------------------------------

### DbInfo Configuration Options for SomeTable

Source: https://github.com/emurgo/cardano-db-sync/blob/master/doc/database-encode-decode.md

Demonstrates various configuration options for a DbInfo instance, including custom table and column names, unique constraints, JSONB fields, enum types, generated fields, and bulk operation parameters.

```haskell
instance DbInfo SomeTable where
  -- Table name (default: snake_case of type name)
  tableName _ = "custom_table_name"
  
  -- Column names (default: derived from field names)
  columnNames _ = NE.fromList ["col1", "col2", "col3"]
  
  -- Unique constraints
  uniqueFields _ = ["col1", "col2"]  -- Multi-column unique constraint
  
  -- JSONB columns (require ::jsonb casting)
  jsonbFields _ = ["metadata", "config"]
  
  -- Enum columns with their types
  enumFields _ = [("status", "status_type"), ("priority", "priority_type")]
  
  -- Generated columns (excluded from inserts)
  generatedFields _ = ["created_at", "updated_at"]
  
  -- Bulk operation parameters
  unnestParamTypes _ =
    [ ("col1", "bigint[]")
    , ("col2", "text[]")
    , ("col3", "boolean[]")
    ]
```

--------------------------------

### Get Stake Distribution per Pool for an Epoch

Source: https://github.com/emurgo/cardano-db-sync/blob/master/doc/interesting-queries.md

Retrieves the total stake amount for each pool in a specific epoch. Use the second query to get pool identifiers in Bech32 format.

```sql
select pool_id, sum (amount) from epoch_stake
    where epoch_no = 216 group by pool_id ;
```

```sql
 pool_id |       sum
--------+-----------------
       1 |  25326935163066
       2 | 112825285842751
...
    1172 |       498620686
    1173 |     15024987189
(1114 rows)
```

```sql
select pool_hash.view, sum (amount) as lovelace from epoch_stake
    inner join pool_hash on epoch_stake.pool_id = pool_hash.id
    where epoch_no = 216 group by pool_hash.id ;
```

```sql
                           view                           |    lovelace
----------------------------------------------------------+-----------------
 pool10p6wd9k0fwk2zqkqnqr8efyr7gms627ujk9dxgk6majskhawr6r |    789466838780
 pool1vvh72z8dktfy2x965w0yp5psmnyv3845pmm37nerhl6jk6m2njw |   1427697660218
...
 pool1tq03j8aa5myedlr8xj6tltstdsn5eprxq4cd4qr54mhv25unsyk |     50297430457
 pool1nux6acnlx0du7ss9fhg2phjlaqe87l4wcurln5r6f0k8xreluez |   5401615743207
(1114 rows)
```

--------------------------------

### Run cardano-db-sync with Profiling Enabled

Source: https://github.com/emurgo/cardano-db-sync/blob/master/dev-tools/profiling/README.md

Launch cardano-db-sync with the profiling flag enabled. The application will output the ghc-debug socket path upon startup. Use the provided script for tmux integration or run manually.

```bash
# Using the start script (launches in tmux)
./dev-tools/profiling/scripts/start-profiling.sh
```

```bash
# Or run manually with your usual command
PGPASSFILE=config/pgpass-mainnet cabal run cardano-db-sync -- \
    --config config/mainnet-config.yaml \
    --socket-path /path/to/node.socket \
    --state-dir ledger-state/mainnet \
    --schema-dir schema/
```

--------------------------------

### Build Profiled Executable

Source: https://github.com/emurgo/cardano-db-sync/blob/master/CONTRIBUTING.rst

Build a profiled version of the cardano-db-sync or cardano-smash-server executable using Nix.

```bash
nix build .#profiled.cardano-db-sync
```

```bash
nix build .#profiled.cardano-smash-server
```

--------------------------------

### Get Transaction Outputs by Hash

Source: https://github.com/emurgo/cardano-db-sync/blob/master/doc/interesting-queries.md

Retrieves all transaction outputs associated with a given transaction hash.

```sql
select tx_out.* from tx_out inner join tx on tx_out.tx_id = tx.id
    where tx.hash = '\xf9c0997afc8159dbe0568eadf0823112e0cc29cd097c8dc939ff44c372388bc0' ;
```

```sql
   id    |  tx_id  | index |         address         |    value     |        address_raw        | payment_cred
---------+---------+-------+-------------------------+--------------+---------------------------+--------------
 2205593 | 1000000 |     1 | DdzFFzCqrh...u6v9fWDrML | 149693067531 | \x82d8185842...1a20a42e6f |
 2205592 | 1000000 |     0 | DdzFFzCqrh...DoV2nEACWf |   8991998000 | \x82d8185842...1a150033dc |


```

--------------------------------

### Connect with ghc-debug-brick

Source: https://github.com/emurgo/cardano-db-sync/blob/master/dev-tools/profiling/README.md

Launch the ghc-debug-brick client in a separate terminal to connect to the running cardano-db-sync process. Ensure cardano-db-sync is already running.

```bash
ghc-debug-brick
```

--------------------------------

### Get Size of Cardano DB Sync Database

Source: https://github.com/emurgo/cardano-db-sync/blob/master/doc/interesting-queries.md

Returns the human-readable size of the 'cexplorer' database.

```sql
select pg_size_pretty (pg_database_size ('cexplorer'));
```

--------------------------------

### Get Size of 'block' Table

Source: https://github.com/emurgo/cardano-db-sync/blob/master/doc/interesting-queries.md

Returns the human-readable size of the 'block' table, including indexes and data.

```sql
select pg_size_pretty (pg_total_relation_size ('block'));
```

--------------------------------

### Run Cardano DB Sync for Mainnet

Source: https://github.com/emurgo/cardano-db-sync/blob/master/doc/docker.md

Launches cardano-db-sync container for the mainnet, configuring PostgreSQL connection and data volumes.

```bash
docker run \
  --env NETWORK=mainnet \
  --env POSTGRES_HOST=postgres \
  --env POSTGRES_PORT=5432 \
  --volume db-sync-data:/var/lib/cexplorer \
  --volume node-ipc \
  ghcr.io/IntersectMBO/cardano-db-sync:13.3.0.0
```

--------------------------------

### Query Chain Metadata

Source: https://github.com/emurgo/cardano-db-sync/blob/master/doc/interesting-queries.md

Retrieves all metadata records from the 'meta' table, including the start time and network name.

```sql
select * from meta ;
```

--------------------------------

### Advanced Usage: Run with Command Line Arguments

Source: https://github.com/emurgo/cardano-db-sync/blob/master/doc/docker.md

Executes cardano-db-sync directly, passing runtime configurations like config file path and socket path as command-line arguments.

```bash
docker run \
  --volume db-sync-data:/var/lib/cexplorer \
  --volume node-ipc:/node-ipc \
  --volume $PWD/environments/mainnet:/config \
  ghcr.io/IntersectMBO/cardano-db-sync:13.3.0.0 \
  run --config /config/db-sync-config.json --socket-path /node-ipc/node.socket
```

--------------------------------

### Get Block Count per Epoch for a Pool

Source: https://github.com/emurgo/cardano-db-sync/blob/master/doc/interesting-queries.md

Counts the number of blocks created by a specified pool for each epoch.

```sql
select block.epoch_no, count (*) as block_count
    from block inner join slot_leader on block.slot_leader_id = slot_leader.id
    inner join pool_hash on slot_leader.pool_hash_id = pool_hash.id
    where pool_hash.view = 'pool1nux6acnlx0du7ss9fhg2phjlaqe87l4wcurln5r6f0k8xreluez'
    group by block.epoch_no, pool_hash.view ;

```

```sql
 epoch_no | block_count
----------+-------------
      212 |           1
      213 |           2
      214 |           2
      215 |           4
....

```

--------------------------------

### Get Transaction Inputs by Hash

Source: https://github.com/emurgo/cardano-db-sync/blob/master/doc/interesting-queries.md

Retrieves transaction inputs for a specified transaction hash, joining with transaction outputs.

```sql
select tx_out.* from tx_out
    inner join tx_in on tx_out.tx_id = tx_in.tx_out_id
    inner join tx on tx.id = tx_in.tx_in_id and tx_in.tx_out_index = tx_out.index
    where tx.hash = '\xf9c0997afc8159dbe0568eadf0823112e0cc29cd097c8dc939ff44c372388bc0' ;
```

```sql
   id    | tx_id  | index |        address          |    value     |        address_raw        | payment_cred
---------+--------+-------+-------------------------+--------------+---------------------------+--------------
 2195714 | 996126 |     4 | DdzFFzCqrh...dtq1FQQSCN | 158685237964 | \x82d8185842...1a330b42df |

```

--------------------------------

### Install Git Pre-commit Hook

Source: https://github.com/emurgo/cardano-db-sync/blob/master/doc/hlint-stylish-haskell.md

Symlink the provided git pre-commit hook script to enable automatic checks before commits.

```bash
(cd .git/hooks/ && ln -s ../../scripts/git-pre-commit-hook pre-commit)
```

--------------------------------

### Build Cardano DB Sync with Nix

Source: https://github.com/emurgo/cardano-db-sync/blob/master/doc/installing-with-nix.md

Compiles Cardano DB Sync using Nix, placing the executable in the `./result` directory.

```bash
nix build .
```

--------------------------------

### Navigate to Repository Directory

Source: https://github.com/emurgo/cardano-db-sync/blob/master/doc/building-running.md

Change the current directory to the cloned cardano-db-sync repository.

```shell
cd cardano-db-sync
```

--------------------------------

### Get Transaction Fee by Hash

Source: https://github.com/emurgo/cardano-db-sync/blob/master/doc/interesting-queries.md

Retrieves the transaction ID and fee for a specified transaction hash. The hash should be provided in a hexadecimal format.

```sql
select tx.id, tx.fee from tx
    where tx.hash = '\xf9c0997afc8159dbe0568eadf0823112e0cc29cd097c8dc939ff44c372388bc0' ;
```

--------------------------------

### Enter Nix Development Shell

Source: https://github.com/emurgo/cardano-db-sync/blob/master/CONTRIBUTING.rst

Use this command to enter the Nix development shell, which provides the complete toolchain for building repository artifacts.

```bash
nix develop .
```

--------------------------------

### Configure Tx Out Bootstrap

Source: https://github.com/emurgo/cardano-db-sync/blob/master/doc/configuration.md

Uses a similar schema to 'prune' but syncs faster by delaying UTxO insertion until the chain tip. Requires Babbage or Conway era and may leave tx.fee and redeemer.script_hash fields empty until ledger state migration.

```json
"tx_out": {
  "value": "bootstrap"
}
```

--------------------------------

### Get Transaction Withdrawals by Hash

Source: https://github.com/emurgo/cardano-db-sync/blob/master/doc/interesting-queries.md

Retrieves withdrawal information for a specified transaction hash. Withdrawals are a feature of Shelley era transactions and later.

```sql
select withdrawal.* from withdrawal
    inner join tx on withdrawal.tx_id = tx.id
    where tx.hash = '\x0b8c5be678209bb051a02904dd18896a929f9aca8aecd48850939a590175f7e8' ;
```

```sql
  id   | addr_id |  amount   |  tx_id
-------+---------+-----------+---------
 27684 |   30399 | 154619825 | 2788211

```

--------------------------------

### Get Pool Metadata

Source: https://github.com/emurgo/cardano-db-sync/blob/master/doc/smash.md

Fetches metadata for a specific pool using its hash and another identifier. This endpoint is used to retrieve information about a pool.

```APIDOC
## GET /api/v1/metadata/{poolHash}/{identifier}

### Description
Retrieves metadata associated with a specific Cardano pool identified by its hash and another unique identifier.

### Method
GET

### Endpoint
/api/v1/metadata/{poolHash}/{identifier}

### Parameters
#### Path Parameters
- **poolHash** (string) - Required - The unique hash of the pool.
- **identifier** (string) - Required - An additional identifier for the pool's metadata.

### Response
#### Success Response (200)
- **metadata** (object) - Description of the pool's metadata.

### Response Example
{
  "example": "{\"poolId\": \"8517fa7042cb9494818861c53c87780b4975c0bd402e3ed85168aa66\", \"metadata\": {}}"
}
```

--------------------------------

### Download Mainnet Configuration Files

Source: https://github.com/emurgo/cardano-db-sync/blob/master/doc/running.md

Downloads all necessary configuration files for the Cardano mainnet environment using curl. Ensure you have the correct URL for the environments.

```bash
curl --remote-name-all --output-dir mainnet \
    https://book.world.dev.cardano.org/environments/mainnet/config.json \
    https://book.world.dev.cardano.org/environments/mainnet/db-sync-config.json \
    https://book.world.dev.cardano.org/environments/mainnet/submit-api-config.json \
    https://book.world.dev.cardano.org/environments/mainnet/topology.json \
    https://book.world.dev.cardano.org/environments/mainnet/byron-genesis.json \
    https://book.world.dev.cardano.org/environments/mainnet/shelley-genesis.json \
    https://book.world.dev.cardano.org/environments/mainnet/alonzo-genesis.json \
    https://book.world.dev.cardano.org/environments/mainnet/conway-genesis.json
```

--------------------------------

### Configure OpenSSL for macOS (Homebrew)

Source: https://github.com/emurgo/cardano-db-sync/blob/master/doc/installing.md

Create symbolic links for OpenSSL if installed via Homebrew on macOS to ensure it's found by the build process.

```bash
sudo mkdir -p /usr/local/opt/openssl
sudo ln -s /opt/homebrew/opt/openssl@3/lib /usr/local/opt/openssl/lib
sudo ln -s /opt/homebrew/opt/openssl@3/include /usr/local/opt/openssl/include
```

--------------------------------

### Run Database Integration Tests

Source: https://github.com/emurgo/cardano-db-sync/blob/master/CONTRIBUTING.rst

Execute the database integration tests for Cardano DB Sync.

```bash
cabal test cardano-chain-gen:test:cardano-chain-gen
```

```bash
cabal test cardano-db:test:test-db
```

--------------------------------

### Get Delegation History for a Stake Address

Source: https://github.com/emurgo/cardano-db-sync/blob/master/doc/interesting-queries.md

Retrieves the epoch number and pool view for each delegation associated with a given stake address, ordered by epoch.

```sql
select delegation.active_epoch_no, pool_hash.view from delegation
    inner join stake_address on delegation.addr_id = stake_address.id
    inner join pool_hash on delegation.pool_hash_id = pool_hash.id
    where stake_address.view = 'stake1u8gsndukzghdukmqdsd7r7wd6kvamvjv2pzcgag8v6jd69qfqyl5h'
    order by active_epoch_no asc;
```

```sql
 active_epoch_no |                           view
-----------------+----------------------------------------------------------
             212 | pool1hwlghkwnjsjk8370qt3dvp23d7urwm36f95fmxcz3np2kghknj9
             214 | pool1xxhs2zw5xa4g54d5p62j46nlqzwp8jklqvuv2agjlapwjx9qkg9
             216 | pool1hwlghkwnjsjk8370qt3dvp23d7urwm36f95fmxcz3np2kghknj9
             217 | pool15yyxtkhz64p7a8cnax9l7u82s9t9hdhyxsa3tdm977qhgpnsuhq
(4 rows)
```

--------------------------------

### Get Epoch Performance Statistics

Source: https://github.com/emurgo/cardano-db-sync/blob/master/doc/interesting-queries.md

Calculates epoch performance statistics including sync time, transaction count, and size. This query is optimized for performance.

```sql
select epoch_no, max (sync_secs) as sync_secs, sum (tx_count) as tx_count, sum (sum_tx_size) as sum_tx_size,
    sum (reward_count) as reward_count, sum (stake_count) as stake_count
  from (
    select earned_epoch as epoch_no, 0 as sync_secs, 0 as tx_count, 0 as sum_tx_size, count (reward) as reward_count,
        0 as stake_count from reward group by earned_epoch
    union
    select epoch_no, 0 as sync_secs, 0 as tx_count, 0 as sum_tx_size, 0 as reward_count,
        count (epoch_stake) as stake_count from epoch_stake group by epoch_no
    union
    select epoch_no, 0 as sync_secs, count (tx) as tx_count, sum (tx.size) as tx_sum_size, 0 as reward_count,
        0 as stake_count
      from block inner join tx on tx.block_id = block.id
      where epoch_no is not null
      group by epoch_no
    union
    select no as epoch_no, seconds, 0 as tx_count, 0 as tx_sum_size, 0 as reward_count,
        0 as stake_count
      from epoch_sync_time
  )
  as derived_table group by epoch_no ;
```

```sql
 epoch_no |   sync_secs    | tx_count | sum_tx_size | reward_count | stake_count
----------+----------------+----------+-------------+--------------+-------------
        0 |              0 |       33 |        6093 |            0 |           0
        1 |   28.256384637 |    12870 |     2256995 |            0 |           0
        2 |   19.462634986 |     4292 |      830307 |            0 |           0
        3 |   18.302536512 |     3293 |      658490 |            0 |           0
...      209 |  177.122253524 |    36916 |    19098427 |            0 |           0
      210 |  188.630659101 |    36267 |    19694637 |            0 |       17305
      211 |  160.841826393 |    29083 |    16330473 |        17988 |       24252
      212 |  146.277991679 |    24691 |    13503603 |        24421 |       30628
...
      268 | 3491.985000071 |   208164 |   127281166 |       527806 |      557805
      269 | 3234.034316171 |   197254 |   118772706 |            0 |      577352
      270 |              0 |   120754 |    70076543 |            0 |      595592
(271 rows)

```

--------------------------------

### Run Cardano Node as Relay

Source: https://github.com/emurgo/cardano-db-sync/blob/master/doc/smash.md

Start the Cardano node as a relay or passive node. Ensure the genesis file, socket path, and configuration are correctly specified.

```bash
cardano-node --genesis-file genesis.json --socket-path node.socket --config config.yaml
```

--------------------------------

### Build cardano-db-sync with Nix

Source: https://github.com/emurgo/cardano-db-sync/blob/master/doc/building-running.md

Build the cardano-db-sync project using Nix. The `-o db-sync-node` flag outputs the build result to the `db-sync-node` directory.

```shell
nix build -v .#cardano-db-sync -o db-sync-node
```

--------------------------------

### Get Delegated Amount by Epoch for a Specific Address

Source: https://github.com/emurgo/cardano-db-sync/blob/master/doc/interesting-queries.md

Query the total amount delegated by a given stake address for each epoch. This is useful for tracking delegation history.

```sql
select stake_address.view as stake_address, epoch_stake.epoch_no, epoch_stake.amount
    from stake_address inner join epoch_stake on stake_address.id = epoch_stake.addr_id
    where stake_address.view = 'stake1u8mt5gqclkq0swmvzx9lvq4jgwsnx9yh030yrxwqwllu0mq2m0l4n' ;
```

```sql
                        stake_address                        | epoch_no |   amount
-------------------------------------------------------------+----------+-------------
 stake1u8mt5gqclkq0swmvzx9lvq4jgwsnx9yh030yrxwqwllu0mq2m0l4n |      211 |  1561003730
 stake1u8mt5gqclkq0swmvzx9lvq4jgwsnx9yh030yrxwqwllu0mq2m0l4n |      212 |  1561003730
...

```