Skip to content

Instantly share code, notes, and snippets.

@alexklibisz

alexklibisz/0-firefly-grafana-example.md

Last active January 31, 2025 09:59
Show Gist options
  • Save alexklibisz/144d731e34e3d5c5e1112242eec84678 to your computer and use it in GitHub Desktop.
Save alexklibisz/144d731e34e3d5c5e1112242eec84678 to your computer and use it in GitHub Desktop.
Download ZIP
Firefly + Grafana Example
Raw
0-firefly-grafana-example.md
Raw
1-docker-compose.yaml
# This is part of the docker-compose I use to run Firefly on a Digital Ocean droplet.
version: '3.9'
services:
firefly_postgresql:
container_name: firefly_postgresql
image: bitnami/postgresql:12.6.0
volumes:
- 'firefly_postgresql_data:/bitnami/postgresql'
- './postgresql/init.sh:/docker-entrypoint-initdb.d/init.sh'
ports:
- 5432:5432
environment:
# Standard bitnami/postgresql env vars.
- POSTGRESQL_POSTGRES_PASSWORD=${POSTGRESQL_POSTGRES_PASSWORD}
- POSTGRESQL_DATABASE=firefly
- POSTGRESQL_USERNAME=firefly
- POSTGRESQL_PASSWORD=${POSTGRESQL_FIREFLY_PASSWORD}
# Env vars used by init scripts.
- INIT_ANALYSIS_PASSWORD=${POSTGRESQL_ANALYSIS_PASSWORD}
grafana:
container_name: grafana
image: grafana/grafana:6.5.0
volumes:
- 'grafana_data:/var/lib/grafana'
- './grafana/datasources.yaml:/etc/grafana/provisioning/datasources/custom.yaml'
ports:
- 3000:3000
environment:
# Set this when starting from scratch.
# - GF_SECURITY_DISABLE_INITIAL_ADMIN_CREATION=false
- GF_SERVER_ROOT_URL=https://grafana.example.com
- POSTGRESQL_ANALYSIS_PASSWORD=${POSTGRESQL_ANALYSIS_PASSWORD}
volumes:
firefly_postgresql_data:
name: firefly_postgresql_data
driver: local
grafana_data:
name: grafana_data
driver: local
Raw
2-init.sh
# Postgres Init script that creates a limited-access user for Grafana to read from the database.
#!/bin/sh
set -e
export POSTGRESQL_PASSWORD=$POSTGRESQL_POSTGRES_PASSWORD
psql -h localhost -p 5432 -U postgres -d $POSTGRESQL_DATABASE -v ON_ERROR_STOP=1 <<EOF
revoke all on schema public from public;
grant all on schema public to firefly;
-- user analysis can select from public schema and create tables in analysis schema.
create schema analysis;
create user analysis with password '$INIT_ANALYSIS_PASSWORD';
grant create on schema analysis to analysis;
grant all on schema analysis to analysis;
grant usage on schema public to analysis;
-- user firefly will create new tables in schema public; user analysis can select from them.
alter default privileges for role firefly in schema public grant select on tables to analysis;
EOF
Raw
3-datasources.yaml
# Datasources file used by grafana to read from Firefly Postgres container.
apiVersion: 1
datasources:
- name: firefly-postgres
type: postgres
url: firefly_postgresql:5432
database: firefly
user: analysis
secureJsonData:
password: $POSTGRESQL_ANALYSIS_PASSWORD
jsonData:
sslmode: "disable"
Raw
4-analysis_views.sql
# Postgres views used in the Grafana dashboard.
# These are created by manually executing the queries... nothing fancy.
-- This view gives a single table of transactions which is more convenient to use in the dashboard.
create or replace view analysis.txs as (
select date(tj.date),
u.email,
a.name account,
c.name category,
case when c.name in ('Income',
'Investment 401k',
'Investment Roth IRA',
'Investment Foo',
'Investment Bar',
'Investment Baz',
'Charity'
) then c.name::text
when tt.type in ('Opening balance') then null
when c.name is not null then 'Expense'::text
end
as meta_category,
tt.type transaction_type,
round(t.amount, 2) amount
from transactions t
join transaction_journals tj on t.transaction_journal_id = tj.id
join transaction_types tt on tt.id = tj.transaction_type_id
left join category_transaction_journal ctj on tj.id = ctj.transaction_journal_id
left join categories c on ctj.category_id = c.id
join accounts a on a.id = t.account_id
join account_types at on a.account_type_id = at.id
join users u on a.user_id = u.id
where a.active = true
and a.deleted_at is null
and t.deleted_at is null
and at.type = 'Asset account'
order by tj.date desc
);
-- This view removes a couple category/account pairs which would otherwise cancel out to 0 due to double-entry accounting.
create or replace view analysis.cash_flow as (
select *
from txs
where not (meta_category in ('Investment Foo',
'Investment Bar',
'Investment Baz')
and account like 'INV%'
and transaction_type = 'Transfer')
);
Raw
5-dashboard.json
# This is the grafana dashboard exported to JSON.
{
"annotations": {
"list": [
{
"builtIn": 1,
"datasource": "-- Grafana --",
"enable": true,
"hide": true,
"iconColor": "rgba(0, 211, 255, 1)",
"name": "Annotations & Alerts",
"type": "dashboard"
}
]
},
"editable": true,
"gnetId": null,
"graphTooltip": 0,
"id": 1,
"iteration": 1622481314456,
"links": [],
"panels": [
{
"cacheTimeout": null,
"colorBackground": false,
"colorValue": true,
"colors": [
"#d44a3a",
"rgba(237, 129, 40, 0.89)",
"#299c46"
],
"datasource": "firefly-postgres",
"decimals": null,
"description": "Total Net Worth at End of Period\nThis should equal the net worth on the Firefly home page.",
"format": "currencyUSD",
"gauge": {
"maxValue": 100,
"minValue": 0,
"show": false,
"thresholdLabels": false,
"thresholdMarkers": true
},
"gridPos": {
"h": 4,
"w": 4,
"x": 0,
"y": 0
},
"id": 37,
"interval": null,
"links": [],
"mappingType": 1,
"mappingTypes": [
{
"name": "value to text",
"value": 1
},
{
"name": "range to text",
"value": 2
}
],
"maxDataPoints": 100,
"nullPointMode": "connected",
"nullText": null,
"options": {},
"postfix": "",
"postfixFontSize": "50%",
"prefix": "",
"prefixFontSize": "70%",
"rangeMaps": [
{
"from": "null",
"text": "N/A",
"to": "null"
}
],
"sparkline": {
"fillColor": "rgba(31, 118, 189, 0.18)",
"full": false,
"lineColor": "rgb(31, 120, 193)",
"show": false,
"ymax": null,
"ymin": null
},
"tableColumn": "",
"targets": [
{
"format": "table",
"group": [],
"metricColumn": "none",
"rawQuery": true,
"rawSql": "select sum(amount)\nfrom txs\nwhere email = '$email'\n and date <= $__timeTo()\n",
"refId": "B",
"select": [
[
{
"params": [
"value"
],
"type": "column"
}
]
],
"timeColumn": "time",
"where": [
{
"name": "$__timeFilter",
"params": [],
"type": "macro"
}
]
}
],
"thresholds": "0,0",
"timeFrom": null,
"timeShift": null,
"title": "Net Worth",
"transparent": true,
"type": "singlestat",
"valueFontSize": "80%",
"valueMaps": [
{
"op": "=",
"text": "N/A",
"value": "null"
}
],
"valueName": "total"
},
{
"cacheTimeout": null,
"colorBackground": false,
"colorValue": true,
"colors": [
"#d44a3a",
"rgba(237, 129, 40, 0.89)",
"#299c46"
],
"datasource": "firefly-postgres",
"description": "",
"format": "currencyUSD",
"gauge": {
"maxValue": 100,
"minValue": 0,
"show": false,
"thresholdLabels": false,
"thresholdMarkers": true
},
"gridPos": {
"h": 4,
"w": 4,
"x": 4,
"y": 0
},
"id": 40,
"interval": null,
"links": [],
"mappingType": 1,
"mappingTypes": [
{
"name": "value to text",
"value": 1
},
{
"name": "range to text",
"value": 2
}
],
"maxDataPoints": 100,
"nullPointMode": "connected",
"nullText": null,
"options": {},
"postfix": "",
"postfixFontSize": "50%",
"prefix": "",
"prefixFontSize": "50%",
"rangeMaps": [
{
"from": "null",
"text": "N/A",
"to": "null"
}
],
"sparkline": {
"fillColor": "rgba(31, 118, 189, 0.18)",
"full": false,
"lineColor": "rgb(31, 120, 193)",
"show": false,
"ymax": null,
"ymin": null
},
"tableColumn": "",
"targets": [
{
"format": "table",
"group": [],
"metricColumn": "none",
"rawQuery": true,
"rawSql": "select sum(amount)\nfrom txs\nwhere meta_category not like 'Investment%'\n and email = '$email'\n and date <= $__timeTo()",
"refId": "A",
"select": [
[
{
"params": [
"value"
],
"type": "column"
}
]
],
"timeColumn": "time",
"where": [
{
"name": "$__timeFilter",
"params": [],
"type": "macro"
}
]
}
],
"thresholds": "0,0",
"timeFrom": null,
"timeShift": null,
"title": "Cash",
"transparent": true,
"type": "singlestat",
"valueFontSize": "80%",
"valueMaps": [
{
"op": "=",
"text": "N/A",
"value": "null"
}
],
"valueName": "first"
},
{
"aliasColors": {},
"bars": false,
"dashLength": 10,
"dashes": false,
"datasource": "firefly-postgres",
"fill": 1,
"fillGradient": 0,
"gridPos": {
"h": 7,
"w": 16,
"x": 8,
"y": 0
},
"hiddenSeries": false,
"id": 34,
"legend": {
"avg": false,
"current": false,
"max": false,
"min": false,
"show": true,
"total": false,
"values": false
},
"lines": true,
"linewidth": 2,
"nullPointMode": "null",
"options": {
"dataLinks": []
},
"percentage": false,
"pointradius": 2,
"points": false,
"renderer": "flot",
"seriesOverrides": [],
"spaceLength": 10,
"stack": false,
"steppedLine": false,
"targets": [
{
"format": "time_series",
"group": [],
"metricColumn": "none",
"rawQuery": true,
"rawSql": "select $__time(d), sum(amount), 'Total'\nfrom generate_series($__timeTo(), '2019-01-01'::date, '-7 days'::interval) d\njoin txs on txs.date <= d\nwhere email = '$email'\ngroup by d\norder by d\n\n",
"refId": "B",
"select": [
[
{
"params": [
"value"
],
"type": "column"
}
]
],
"timeColumn": "time",
"where": [
{
"name": "$__timeFilter",
"params": [],
"type": "macro"
}
]
},
{
"format": "time_series",
"group": [],
"hide": false,
"metricColumn": "none",
"rawQuery": true,
"rawSql": "select $__time(d), sum(amount), 'Cash' as tags\nfrom generate_series($__timeTo(), '2019-01-01'::date, '-7 days'::interval) d\njoin txs on txs.date <= d\nwhere meta_category not like 'Investment%'\n and email = '$email'\ngroup by d\norder by d\n\n",
"refId": "A",
"select": [
[
{
"params": [
"value"
],
"type": "column"
}
]
],
"timeColumn": "time",
"where": [
{
"name": "$__timeFilter",
"params": [],
"type": "macro"
}
]
}
],
"thresholds": [],
"timeFrom": null,
"timeRegions": [],
"timeShift": null,
"title": "Net Worth",
"tooltip": {
"shared": true,
"sort": 1,
"value_type": "individual"
},
"transparent": true,
"type": "graph",
"xaxis": {
"buckets": null,
"mode": "time",
"name": null,
"show": true,
"values": []
},
"yaxes": [
{
"format": "currencyUSD",
"label": null,
"logBase": 1,
"max": null,
"min": "-1",
"show": true
},
{
"format": "currencyUSD",
"label": null,
"logBase": 1,
"max": null,
"min": null,
"show": false
}
],
"yaxis": {
"align": false,
"alignLevel": null
}
},
{
"cacheTimeout": null,
"colorBackground": false,
"colorValue": true,
"colors": [
"#d44a3a",
"rgba(237, 129, 40, 0.89)",
"#299c46"
],
"datasource": "firefly-postgres",
"decimals": null,
"description": "Total Net Worth at End of Period",
"format": "currencyUSD",
"gauge": {
"maxValue": 100,
"minValue": 0,
"show": false,
"thresholdLabels": false,
"thresholdMarkers": true
},
"gridPos": {
"h": 3,
"w": 8,
"x": 0,
"y": 4
},
"id": 38,
"interval": null,
"links": [],
"mappingType": 1,
"mappingTypes": [
{
"name": "value to text",
"value": 1
},
{
"name": "range to text",
"value": 2
}
],
"maxDataPoints": 100,
"nullPointMode": "connected",
"nullText": null,
"options": {},
"postfix": "",
"postfixFontSize": "50%",
"prefix": "",
"prefixFontSize": "70%",
"rangeMaps": [
{
"from": "null",
"text": "N/A",
"to": "null"
}
],
"sparkline": {
"fillColor": "rgba(31, 118, 189, 0.18)",
"full": false,
"lineColor": "rgb(31, 120, 193)",
"show": false,
"ymax": null,
"ymin": null
},
"tableColumn": "",
"targets": [
{
"format": "table",
"group": [],
"hide": false,
"metricColumn": "none",
"rawQuery": true,
"rawSql": "select sum(amount)\n from txs\n where email = '$email'\n and $__timeFilter(date)",
"refId": "A",
"select": [
[
{
"params": [
"value"
],
"type": "column"
}
]
],
"timeColumn": "time",
"where": [
{
"name": "$__timeFilter",
"params": [],
"type": "macro"
}
]
}
],
"thresholds": "0,0",
"timeFrom": null,
"timeShift": null,
"title": "Net Worth Increase",
"transparent": true,
"type": "singlestat",
"valueFontSize": "80%",
"valueMaps": [
{
"op": "=",
"text": "N/A",
"value": "null"
}
],
"valueName": "total"
},
{
"cacheTimeout": null,
"columns": [],
"datasource": "firefly-postgres",
"description": "Where does income go?",
"fontSize": "100%",
"gridPos": {
"h": 9,
"w": 8,
"x": 0,
"y": 7
},
"id": 21,
"links": [],
"options": {},
"pageSize": null,
"pluginVersion": "6.5.0",
"showHeader": true,
"sort": {
"col": 1,
"desc": true
},
"styles": [
{
"alias": "Meta Category",
"dateFormat": "YYYY-MM-DD HH:mm:ss",
"mappingType": 1,
"pattern": "meta_category",
"preserveFormat": false,
"type": "string"
},
{
"alias": "Amount",
"colorMode": null,
"colors": [
"rgba(245, 54, 54, 0.9)",
"rgba(237, 129, 40, 0.89)",
"rgba(50, 172, 45, 0.97)"
],
"decimals": 2,
"pattern": "amount",
"thresholds": [],
"type": "number",
"unit": "currencyUSD"
},
{
"alias": "% of Income",
"colorMode": null,
"colors": [
"rgba(245, 54, 54, 0.9)",
"rgba(237, 129, 40, 0.89)",
"rgba(50, 172, 45, 0.97)"
],
"dateFormat": "YYYY-MM-DD HH:mm:ss",
"decimals": 0,
"mappingType": 1,
"pattern": "percentage_of_income",
"thresholds": [],
"type": "number",
"unit": "percent"
}
],
"targets": [
{
"format": "table",
"group": [],
"metricColumn": "none",
"rawQuery": true,
"rawSql": "with grouped as (\nselect meta_category, sum(amount) amount\nfrom analysis.cash_flow\nwhere email = '$email'\n and $__timeFilter(date)\ngroup by meta_category\n)\nselect meta_category,\n amount,\n round(100.0 * abs(amount) / (select sum(amount) from grouped where meta_category = 'Income'))\n as percentage_of_income\nfrom grouped\nwhere meta_category = 'Income' or amount < 0\norder by 2 desc;",
"refId": "B",
"select": [
[
{
"params": [
"value"
],
"type": "column"
}
]
],
"timeColumn": "time",
"where": [
{
"name": "$__timeFilter",
"params": [],
"type": "macro"
}
]
}
],
"timeFrom": null,
"timeShift": null,
"title": "Cash Flow",
"transform": "table",
"transparent": true,
"type": "table"
},
{
"aliasColors": {},
"bars": false,
"dashLength": 10,
"dashes": false,
"datasource": "firefly-postgres",
"fill": 0,
"fillGradient": 0,
"gridPos": {
"h": 9,
"w": 16,
"x": 8,
"y": 7
},
"hiddenSeries": false,
"id": 33,
"legend": {
"alignAsTable": false,
"avg": false,
"current": false,
"max": false,
"min": false,
"rightSide": false,
"show": true,
"total": false,
"values": false
},
"lines": true,
"linewidth": 2,
"nullPointMode": "connected",
"options": {
"dataLinks": []
},
"percentage": false,
"pointradius": 1,
"points": false,
"renderer": "flot",
"seriesOverrides": [],
"spaceLength": 10,
"stack": false,
"steppedLine": false,
"targets": [
{
"format": "time_series",
"group": [],
"hide": false,
"metricColumn": "none",
"rawQuery": true,
"rawSql": "with\ndates as (\nselect generate_series($__timeFrom(), $__timeTo(), '7 days'::interval) d\n)\nselect date(d) as time, round(abs(sum(amount)), 2) amount, meta_category as tags\n from dates\n join analysis.cash_flow \n on date_trunc('month', date) = date_trunc('month', d)\n and meta_category is not null\n and email = '$email'\n group by d, meta_category\n order by d, meta_category;",
"refId": "A",
"select": [
[
{
"params": [
"value"
],
"type": "column"
}
]
],
"timeColumn": "time",
"where": [
{
"name": "$__timeFilter",
"params": [],
"type": "macro"
}
]
}
],
"thresholds": [],
"timeFrom": null,
"timeRegions": [],
"timeShift": null,
"title": "Cash Flow (30 day totals)",
"tooltip": {
"shared": true,
"sort": 0,
"value_type": "individual"
},
"transparent": true,
"type": "graph",
"xaxis": {
"buckets": null,
"mode": "time",
"name": null,
"show": true,
"values": []
},
"yaxes": [
{
"format": "currencyUSD",
"label": null,
"logBase": 1,
"max": null,
"min": null,
"show": true
},
{
"format": "currencyUSD",
"label": null,
"logBase": 1,
"max": null,
"min": null,
"show": false
}
],
"yaxis": {
"align": false,
"alignLevel": null
}
},
{
"columns": [],
"datasource": "firefly-postgres",
"fontSize": "100%",
"gridPos": {
"h": 14,
"w": 8,
"x": 0,
"y": 16
},
"id": 16,
"options": {},
"pageSize": null,
"showHeader": true,
"sort": {
"col": 1,
"desc": true
},
"styles": [
{
"alias": "Account",
"dateFormat": "YYYY-MM-DD HH:mm:ss",
"pattern": "account",
"type": "string"
},
{
"alias": "Balance",
"colorMode": null,
"colors": [
"rgba(245, 54, 54, 0.9)",
"rgba(237, 129, 40, 0.89)",
"rgba(50, 172, 45, 0.97)"
],
"decimals": 2,
"pattern": "balance",
"thresholds": [],
"type": "number",
"unit": "currencyUSD"
}
],
"targets": [
{
"format": "table",
"group": [],
"metricColumn": "none",
"rawQuery": true,
"rawSql": "select account, sum(amount) as Balance\n from txs\n where email = '$email'\n and date <= $__timeTo()\ngroup by account\nhaving sum(amount) != 0\norder by 2 desc",
"refId": "A",
"select": [
[
{
"params": [
"value"
],
"type": "column"
}
]
],
"timeColumn": "time",
"where": [
{
"name": "$__timeFilter",
"params": [],
"type": "macro"
}
]
}
],
"timeFrom": null,
"timeShift": null,
"title": "Account Balances at End of Period",
"transform": "table",
"transparent": true,
"type": "table"
},
{
"columns": [],
"datasource": "firefly-postgres",
"description": "",
"fontSize": "100%",
"gridPos": {
"h": 16,
"w": 15,
"x": 8,
"y": 16
},
"id": 36,
"options": {},
"pageSize": 15,
"showHeader": true,
"sort": {
"col": 1,
"desc": false
},
"styles": [
{
"alias": "Category",
"colorMode": null,
"colors": [
"rgba(245, 54, 54, 0.9)",
"rgba(237, 129, 40, 0.89)",
"rgba(50, 172, 45, 0.97)"
],
"decimals": 2,
"pattern": "category",
"thresholds": [],
"type": "string",
"unit": "short"
},
{
"alias": "Balance",
"colorMode": null,
"colors": [
"rgba(245, 54, 54, 0.9)",
"rgba(237, 129, 40, 0.89)",
"rgba(50, 172, 45, 0.97)"
],
"dateFormat": "YYYY-MM-DD HH:mm:ss",
"decimals": 2,
"mappingType": 1,
"pattern": "balance",
"thresholds": [],
"type": "number",
"unit": "currencyUSD"
}
],
"targets": [
{
"format": "table",
"group": [],
"metricColumn": "none",
"rawQuery": true,
"rawSql": "select category, sum(amount) as balance\nfrom txs\nwhere email = '$email'\n and category is not null\n and $__timeFilter(date)\ngroup by category\n",
"refId": "A",
"select": [
[
{
"params": [
"value"
],
"type": "column"
}
]
],
"timeColumn": "time",
"where": [
{
"name": "$__timeFilter",
"params": [],
"type": "macro"
}
]
}
],
"timeFrom": null,
"timeShift": null,
"title": "Balance by Category",
"transform": "table",
"transparent": true,
"type": "table"
}
],
"refresh": false,
"schemaVersion": 21,
"style": "dark",
"tags": [],
"templating": {
"list": [
{
"allValue": null,
"current": {
"tags": [],
"text": "[email protected]",
"value": "[email protected]"
},
"datasource": "firefly-postgres",
"definition": "select email from users order by email",
"hide": 0,
"includeAll": false,
"label": "Account Email",
"multi": false,
"name": "email",
"options": [
{
"selected": true,
"text": "[email protected]",
"value": "[email protected]"
},
{
"selected": false,
"text": "[email protected]",
"value": "[email protected]"
}
],
"query": "select email from users order by email",
"refresh": 0,
"regex": "",
"skipUrlSync": false,
"sort": 0,
"tagValuesQuery": "",
"tags": [],
"tagsQuery": "",
"type": "query",
"useTags": false
}
]
},
"time": {
"from": "now-2y",
"to": "now"
},
"timepicker": {
"hidden": false,
"refresh_intervals": [
"1d"
]
},
"timezone": "browser",
"title": "Firefly Overview",
"uid": "6QlEG2qGk",
"version": 76
}
@fascinatedcow
Copy link

Hey. Thanks for sharing this, it's awesome. I have used the queries to assist with building my own view and dashboard for my particular use case. One thing I thought you (and maybe others) could be interested in is having balance over time. Here's the query I ended up with for my view. The important bit is the sum over partition:

 SELECT tj.date AS "time",
    tt.type,
    a.name AS acct,
    tj.description,
    string_agg(tags.tag::text, ','::text) AS tags,
    t.amount,
    sum(t.amount) OVER (PARTITION BY a.id ORDER BY tj.date, tj.id) AS balance,
    sum(t.amount) OVER (PARTITION BY a.id ORDER BY tj.date) AS eod_balance
   FROM transactions t
     JOIN transaction_journals tj ON t.transaction_journal_id = tj.id
     JOIN accounts a ON a.id = t.account_id
     JOIN account_types at ON a.account_type_id = at.id
     JOIN users u ON a.user_id = u.id
     JOIN transaction_types tt ON tj.transaction_type_id = tt.id
     LEFT JOIN tag_transaction_journal ttj ON tj.id = ttj.transaction_journal_id
     LEFT JOIN tags ON ttj.tag_id = tags.id
  WHERE a.active = true AND a.deleted_at IS NULL AND t.deleted_at IS NULL AND at.type::text = 'Asset account'::text
  GROUP BY tj.id, a.id, t.id, tt.id
  ORDER BY tj.date DESC;

Hope that's useful to someone.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment