Skip to content

Grafana で支出を可視化する（学習用）

関連: PostgreSQL / 支出の保存 / アーキテクチャ

学習用

このドキュメントは 手順を理解しながら手を動かす 前提。 完成ダッシュボードの JSON 一式を押し込むより、「なぜその設定か」を優先する。

構成（今回の前提）

ブラウザ
  → Grafana VM  192.168.40.220
       →（LAN）PostgreSQL  192.168.40.212:5432
            DB: finance_manager
            ユーザー: finance_readonly（SELECT のみ）
役割 IP（想定） やること
finance-manager 192.168.40.212 PG を LAN から読めるようにする・読み取りユーザー
Grafana 192.168.40.220 データソース追加・パネル作成

アプリ用の finance_app は 書込あり なので、Grafana には渡さない。

全体の流れ

  1. PG 上に 読み取り専用ユーザー を作る
  2. PG が LAN からの接続を受け付ける ようにする（listen_addresses / pg_hba.conf）
  3. finance-manager 上から「220 相当」の接続を試験する（任意だが学習向き）
  4. Grafana UI で PostgreSQL データソース を追加する
  5. パネルを SQL で作る（今日・今週・大分類）

手順 1: 読み取り専用ユーザー

なぜ分けるか

ユーザー 用途
finance_app Bot が INSERT/UPDATE/DELETE
finance_readonly Grafana が SELECT だけ

可視化用に書込権限を渡すと、パネルや誤操作でデータを壊す余地が残る。

作業場所

finance-manager VM（192.168.40.212）に SSH。

強いパスワードを自分で決めてメモする（Git に書かない）。

sudo -u postgres psql -d finance_manager

psql 内で（パスワードは自分のものに置換）:

-- ユーザー作成（既にあればスキップか ALTER でパスワード更新）
CREATE USER finance_readonly WITH PASSWORD 'ここに強いパスワード';

-- この DB に接続できる
GRANT CONNECT ON DATABASE finance_manager TO finance_readonly;

-- public スキーマを使える
GRANT USAGE ON SCHEMA public TO finance_readonly;

-- 既存テーブルを読める
GRANT SELECT ON ALL TABLES IN SCHEMA public TO finance_readonly;

-- 今後作られるテーブルもデフォルトで SELECT 可能に（任意だが便利）
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO finance_readonly;

確認:

\du finance_readonly
SET ROLE finance_readonly;
SELECT COUNT(*) FROM expenses;
RESET ROLE;
\q

SELECT が通ればよい。INSERT が拒否されることも、余裕があれば試すと理解が深まる。

リポジトリにも同じ内容の参考 SQL がある: sql/grafana_readonly_grants.sql（パスワードは自分で入れる）。

手順 2: PostgreSQL を LAN から届くようにする

いま Bot は同じマシンの 127.0.0.1 だけで足りている。
Grafana（220）から読むには、PG が外部（LAN）の接続を listen し、かつ許可する必要がある。

用語

ファイル 役割
postgresql.conf どこで待つか（listen_addresses）
pg_hba.conf 誰をどういう認証で入れるか（ホストベースアクセス）

Debian だとだいたい:

# バージョン番号は環境で違う（例: 17）
ls /etc/postgresql/*/main/

2-1. listen_addresses

# 実際のパスは ls で確認
sudo nano /etc/postgresql/17/main/postgresql.conf

listen_addresses を探す。

値 意味
localhost（デフォルトに近い） 同じマシンからだけ
* または 0.0.0.0 全インターフェース
192.168.40.212 その IP だけ

学習・自宅 LAN なら、まずは:

listen_addresses = '*'

（インターネットに 5432 を晒す話ではない。ルータでポート開放しないこと。）

2-2. pg_hba.conf

sudo nano /etc/postgresql/17/main/pg_hba.conf

末尾あたりに、Grafana VM だけを許可する行を足す（推奨）:

# Grafana (192.168.40.220) → finance_manager 読み取り
host    finance_manager    finance_readonly    192.168.40.220/32    scram-sha-256
列 意味
host TCP 接続
finance_manager DB 名
finance_readonly ユーザー
192.168.40.220/32 Grafana の IP だけ
scram-sha-256 パスワード認証

192.168.40.0/24 全体を開けても動くが、学習のうちは 220 だけの方が意図が明確。

既存の local / 127.0.0.1 の行は消さない（Bot が壊れる）。

2-3. 反映

sudo systemctl reload postgresql
# または
sudo systemctl restart postgresql

listen_addresses 変更は restart が必要なことがある。reload で足りなければ restart。

Bot は systemd なので、PG 再起動後に一瞬エラー→Restart=always で復帰しがち。念のため:

sudo systemctl status finance-manager

2-4. ファイアウォール

Debian で ufw 等が有効なら、220 から 5432 を許可する必要がある場合がある。

sudo ufw status
# 有効なら例:
# sudo ufw allow from 192.168.40.220 to any port 5432 proto tcp

無効（inactive）なら、この段はスキップでよいことが多い。

手順 3: 接続テスト（finance-manager 上）

「設定したつもり」と「届く」を分ける。

# 同じ VM から、わざと LAN IP 経由で readonly 接続
psql "postgresql://finance_readonly:PASSWORD@192.168.40.212:5432/finance_manager" \
  -c 'SELECT COUNT(*) FROM expenses;'

成功すれば、listen / hba / ユーザーはだいたい正しい。
（本当の「220 から」は Grafana VM に入って同じコマンドを叩くとより確実。）

Grafana VM に SSH できるなら:

psql "postgresql://finance_readonly:PASSWORD@192.168.40.212:5432/finance_manager" \
  -c 'SELECT 1;'

psql が無ければ sudo apt-get install -y postgresql-client。

手順 4: Grafana にデータソースを追加

ブラウザで Grafana（220）を開く。

  1. Connections → Data sources → Add data source
  2. PostgreSQL を選択
  3. だいたい次を入れる:
項目 値
Host 192.168.40.212:5432
Database finance_manager
User finance_readonly
Password （手順1で決めたもの）
TLS / SSL 自宅 LAN なら disable で始めることが多い
Version 自分の PG に近いもの
  1. Save & test → 成功メッセージが出れば OK

失敗したら:

  • Host が 127.0.0.1 になっていないか（それは Grafana 自身を指す）
  • pg_hba の IP が本当に 220 か
  • パスワード・DB 名

手順 5: ダッシュボード（予算残りが主目的）

方針の詳細は 月次予算。
Discord と同じ定義:

変動枠 M = ceiling_yen − savings_yen − fixed_costs_yen
         （= budget_months.variable_pool_yen）

固定費はマスタ定額。変動カテゴリ枠は budget_month_categories のみ。未割当・月末実残は別枠貯金（surplus_ledger）。

Dashboard → New → パネル追加 → データソース PostgreSQL → Query は Code。
Stat / Table / Gauge は Format Table。

前提:

  1. 007_budget_months.sql + 010_fixed_and_surplus.sql 適用済み（sudo systemctl restart finance-manager）
  2. 当月の budget_months 行がある（Bot 起動または Discord で自動作成）
  3. 固定費マスタを入れるなら sql/budget_seed.example.sql を参考に seed
  4. finance_readonly に SELECT 権限

月次 / 週次 / 日次で見たいもの（定義）

変動枠 M = variable_pool_yen を期間に割る（または各変動カテゴリ枠を按分）。

period 期間の変動予算 期間の使用 見たい指標
day M ÷ その月の日数 今日の変動支出 1日分の枠に対して今日どれだけ使ったか
week M × 7 ÷ 日数 今週（月曜〜）の変動支出 1週分の枠に対して今週どれだけ使ったか
month M 今月の変動支出 変動枠に対してどれだけ使ったか

大分類も同じ按分（例: 食費月6万 → 日次は 60000÷日数）。固定費マスタに紐づく expense_category の支出は変動消化から外す。

右上の時間ピッカー（Last 1 year 等）は 使わない。変数 period だけ見る。

変数の作り方

  1. ダッシュボード設定 → Variables → Add
  2. Name: period
  3. Type: Custom
  4. Values は 次のどちらか（混在注意）:

おすすめ（シンプル）:

day, week, month

日本語ラベル付きにする場合は SQL 側で 日次/週次/月次 も分岐する（下のクエリ済み）。
Grafana によっては ${period} に ラベル（日次） が入ることがあり、WHEN 'day' だけだと常に月次になる。

値が変わらないとき（よくある原因）

サマリ表で「期間」列が 日次/週次 なのに「この期間の予算」が月次と同じ 230000 のまま →
変数の値が日本語ラベル（日次）なのに、SQL が WHEN 'day' しか見ていない。

その結果 CASE が全部 ELSE（月次）になり、予算按分も支出期間も月のままになる。

対処（どちらか）:

  1. 変数 Values を day, week, month だけにする（ラベル無し）して SQL を貼り直す
  2. または下の SQL（day と 日次 両対応）に パネルごと差し替え

確認: 日次にしたとき「この期間の予算」≈ 230000÷その月の日数（例: 約7400）。週次なら ≈ 230000×7÷日数。

パネル A: 期間サマリ（Table・まずこれを置く）

period を変えると数値がはっきり変わるはずの本命。

WITH bounds AS (
  SELECT
    date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo')::date AS ym,
    CASE ${period:singlequote}
      WHEN 'day' THEN date_trunc('day', NOW() AT TIME ZONE 'Asia/Tokyo')
      WHEN '日次' THEN date_trunc('day', NOW() AT TIME ZONE 'Asia/Tokyo')
      WHEN 'week' THEN date_trunc('week', NOW() AT TIME ZONE 'Asia/Tokyo')
      WHEN '週次' THEN date_trunc('week', NOW() AT TIME ZONE 'Asia/Tokyo')
      ELSE date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo')
    END AS range_start,
    CASE ${period:singlequote}
      WHEN 'day' THEN date_trunc('day', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '1 day'
      WHEN '日次' THEN date_trunc('day', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '1 day'
      WHEN 'week' THEN date_trunc('week', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '7 days'
      WHEN '週次' THEN date_trunc('week', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '7 days'
      ELSE date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '1 month'
    END AS range_end,
    CASE ${period:singlequote}
      WHEN 'day' THEN 1::numeric
      WHEN '日次' THEN 1::numeric
      WHEN 'week' THEN 7::numeric
      WHEN '週次' THEN 7::numeric
      ELSE EXTRACT(
        DAY FROM (
          date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '1 month'
          - date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo')
        )
      )::numeric
    END AS period_days,
    EXTRACT(
      DAY FROM (
        date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '1 month'
        - date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo')
      )
    )::numeric AS month_days
),
bm AS (
  SELECT m.variable_pool_yen::numeric AS month_spendable
  FROM budget_months m, bounds b
  WHERE m.year_month = b.ym
),
fixed_cats AS (
  SELECT DISTINCT expense_category AS category
  FROM fixed_cost_items
  WHERE active AND expense_category IS NOT NULL AND expense_category <> ''
),
spent AS (
  SELECT COALESCE(SUM(e.amount_yen), 0)::numeric AS spent_yen
  FROM expenses e, bounds b
  WHERE (COALESCE(e.paid_at, e.created_at) AT TIME ZONE 'Asia/Tokyo') >= b.range_start
    AND (COALESCE(e.paid_at, e.created_at) AT TIME ZONE 'Asia/Tokyo') < b.range_end
    AND COALESCE(e.category, 'その他') NOT IN (SELECT category FROM fixed_cats)
)
SELECT
  ${period:singlequote} AS "期間",
  ROUND((SELECT month_spendable FROM bm), 0) AS "月次の変動枠",
  ROUND(
    (SELECT month_spendable FROM bm)
      * (SELECT period_days FROM bounds)
      / (SELECT month_days FROM bounds),
    0
  ) AS "この期間の予算",
  ROUND((SELECT spent_yen FROM spent), 0) AS "この期間の使用",
  ROUND(
    (SELECT month_spendable FROM bm)
      * (SELECT period_days FROM bounds)
      / (SELECT month_days FROM bounds)
    - (SELECT spent_yen FROM spent),
    0
  ) AS "この期間の残り",
  ROUND(
    100.0 * (SELECT spent_yen FROM spent)
      / NULLIF(
        (SELECT month_spendable FROM bm)
          * (SELECT period_days FROM bounds)
          / (SELECT month_days FROM bounds),
        0
      ),
    1
  ) AS "消化率%"
;

Visualization: Table / Format: Table

例（イメージ）: 変動枠が 130000 のとき

  • 月次: 期間予算 = 130000、使用 = 今月の変動支出
  • 日次: 期間予算 = 130000÷31 ≈ 4194、使用 = 今日だけ
  • 週次: 期間予算 = 130000×7÷31 ≈ 29355、使用 = 今週だけ

パネル 0: 給与→固定→変動の内訳（Table）

SELECT
  m.ceiling_yen AS "前月給与",
  m.savings_yen AS "固定貯金",
  m.fixed_costs_yen AS "固定費",
  m.variable_pool_yen AS "変動枠",
  m.unallocated_to_surplus_yen AS "未割当→別枠(確定時)",
  m.status AS "状態"
FROM budget_months m
WHERE m.year_month = date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo')::date;

パネル 1: 月次の変動枠（Stat・参考）

SELECT variable_pool_yen AS month_variable_pool_yen
FROM budget_months
WHERE year_month = date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo')::date;

パネル 2: この期間の予算（Stat）

WITH bounds AS (
  SELECT
    date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo')::date AS ym,
    CASE ${period:singlequote}
      WHEN 'day' THEN 1::numeric
      WHEN '日次' THEN 1::numeric
      WHEN 'week' THEN 7::numeric
      WHEN '週次' THEN 7::numeric
      ELSE EXTRACT(
        DAY FROM (
          date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '1 month'
          - date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo')
        )
      )::numeric
    END AS period_days,
    EXTRACT(
      DAY FROM (
        date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '1 month'
        - date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo')
      )
    )::numeric AS month_days
),
bm AS (
  SELECT m.variable_pool_yen::numeric AS month_spendable
  FROM budget_months m, bounds b
  WHERE m.year_month = b.ym
)
SELECT ROUND(
  (SELECT month_spendable FROM bm)
    * (SELECT period_days FROM bounds)
    / (SELECT month_days FROM bounds),
  0
) AS period_budget_yen;

パネル 3: この期間の使用（Stat）

WITH bounds AS (
  SELECT
    CASE ${period:singlequote}
      WHEN 'day' THEN date_trunc('day', NOW() AT TIME ZONE 'Asia/Tokyo')
      WHEN '日次' THEN date_trunc('day', NOW() AT TIME ZONE 'Asia/Tokyo')
      WHEN 'week' THEN date_trunc('week', NOW() AT TIME ZONE 'Asia/Tokyo')
      WHEN '週次' THEN date_trunc('week', NOW() AT TIME ZONE 'Asia/Tokyo')
      ELSE date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo')
    END AS range_start,
    CASE ${period:singlequote}
      WHEN 'day' THEN date_trunc('day', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '1 day'
      WHEN '日次' THEN date_trunc('day', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '1 day'
      WHEN 'week' THEN date_trunc('week', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '7 days'
      WHEN '週次' THEN date_trunc('week', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '7 days'
      ELSE date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '1 month'
    END AS range_end
)
SELECT COALESCE(SUM(e.amount_yen), 0) AS period_spent_yen
FROM expenses e, bounds b
WHERE (COALESCE(e.paid_at, e.created_at) AT TIME ZONE 'Asia/Tokyo') >= b.range_start
  AND (COALESCE(e.paid_at, e.created_at) AT TIME ZONE 'Asia/Tokyo') < b.range_end;

パネル 3b: この期間の消化率%（Stat / Gauge）

全体予算の期間按分に対する使用率。Gauge なら Min=0 / Max=100。

WITH bounds AS (
  SELECT
    date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo')::date AS ym,
    CASE ${period:singlequote}
      WHEN 'day' THEN date_trunc('day', NOW() AT TIME ZONE 'Asia/Tokyo')
      WHEN '日次' THEN date_trunc('day', NOW() AT TIME ZONE 'Asia/Tokyo')
      WHEN 'week' THEN date_trunc('week', NOW() AT TIME ZONE 'Asia/Tokyo')
      WHEN '週次' THEN date_trunc('week', NOW() AT TIME ZONE 'Asia/Tokyo')
      ELSE date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo')
    END AS range_start,
    CASE ${period:singlequote}
      WHEN 'day' THEN date_trunc('day', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '1 day'
      WHEN '日次' THEN date_trunc('day', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '1 day'
      WHEN 'week' THEN date_trunc('week', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '7 days'
      WHEN '週次' THEN date_trunc('week', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '7 days'
      ELSE date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '1 month'
    END AS range_end,
    CASE ${period:singlequote}
      WHEN 'day' THEN 1::numeric
      WHEN '日次' THEN 1::numeric
      WHEN 'week' THEN 7::numeric
      WHEN '週次' THEN 7::numeric
      ELSE EXTRACT(
        DAY FROM (
          date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '1 month'
          - date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo')
        )
      )::numeric
    END AS period_days,
    EXTRACT(
      DAY FROM (
        date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '1 month'
        - date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo')
      )
    )::numeric AS month_days
),
bm AS (
  SELECT m.variable_pool_yen::numeric AS month_spendable
  FROM budget_months m, bounds b
  WHERE m.year_month = b.ym
),
spent AS (
  SELECT COALESCE(SUM(e.amount_yen), 0)::numeric AS spent_yen
  FROM expenses e, bounds b
  WHERE (COALESCE(e.paid_at, e.created_at) AT TIME ZONE 'Asia/Tokyo') >= b.range_start
    AND (COALESCE(e.paid_at, e.created_at) AT TIME ZONE 'Asia/Tokyo') < b.range_end
)
SELECT ROUND(
  100.0 * (SELECT spent_yen FROM spent)
    / NULLIF(
      (SELECT month_spendable FROM bm)
        * (SELECT period_days FROM bounds)
        / (SELECT month_days FROM bounds),
      0
    ),
  1
) AS period_usage_pct;

パネル 4: 大分類ごと「予算 / 使った / 残り」（Table）

WITH bounds AS (
  SELECT
    date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo')::date AS ym,
    CASE ${period:singlequote}
      WHEN 'day' THEN date_trunc('day', NOW() AT TIME ZONE 'Asia/Tokyo')
      WHEN '日次' THEN date_trunc('day', NOW() AT TIME ZONE 'Asia/Tokyo')
      WHEN 'week' THEN date_trunc('week', NOW() AT TIME ZONE 'Asia/Tokyo')
      WHEN '週次' THEN date_trunc('week', NOW() AT TIME ZONE 'Asia/Tokyo')
      ELSE date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo')
    END AS range_start,
    CASE ${period:singlequote}
      WHEN 'day' THEN date_trunc('day', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '1 day'
      WHEN '日次' THEN date_trunc('day', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '1 day'
      WHEN 'week' THEN date_trunc('week', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '7 days'
      WHEN '週次' THEN date_trunc('week', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '7 days'
      ELSE date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '1 month'
    END AS range_end,
    CASE ${period:singlequote}
      WHEN 'day' THEN 1
      WHEN '日次' THEN 1
      WHEN 'week' THEN 7
      WHEN '週次' THEN 7
      ELSE EXTRACT(
        DAY FROM (
          date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '1 month'
          - date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo')
        )
      )::int
    END AS period_days,
    EXTRACT(
      DAY FROM (
        date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '1 month'
        - date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo')
      )
    )::int AS month_days
),
bm AS (
  SELECT m.variable_pool_yen AS spendable_yen
  FROM budget_months m, bounds b
  WHERE m.year_month = b.ym
),
explicit AS (
  SELECT
    c.category,
    ROUND(c.monthly_limit_yen::numeric * b.period_days / b.month_days, 0)::int AS period_limit_yen
  FROM budget_month_categories c, bounds b
  WHERE c.year_month = b.ym
),
range_spent AS (
  SELECT
    COALESCE(e.category, 'その他') AS category,
    SUM(e.amount_yen) AS spent_yen
  FROM expenses e, bounds b
  WHERE (COALESCE(e.paid_at, e.created_at) AT TIME ZONE 'Asia/Tokyo') >= b.range_start
    AND (COALESCE(e.paid_at, e.created_at) AT TIME ZONE 'Asia/Tokyo') < b.range_end
  GROUP BY 1
)
SELECT
  x.category AS "大分類",
  x.period_limit_yen AS "予算",
  COALESCE(s.spent_yen, 0) AS "使用額",
  x.period_limit_yen - COALESCE(s.spent_yen, 0) AS "残予算"
FROM explicit x
LEFT JOIN range_spent s ON s.category = x.category
ORDER BY "残予算" ASC;

パネル 4g: 大分類の消化率（Gauge・半円）

未使用＝弧が空、使い込むほど弧が伸びる、にするため 数値は消化率%（0〜100）にする。
残予算は下のラベルに出す（中央は消化率）。

設定（ここを間違えると 41.9 なのに赤くなる）

  1. Visualization: Gauge
  2. Format: Table
  3. Value options → Show: All values
  4. Standard options → Min: 0 / Max: 100（必須）
    Max を空（Auto）にすると、系列の最大値（例: 41.9）が満タンになる。
    しきい値 80% は「Max の 80%」なので、Auto だと 41.9×0.8≈33.5 超で赤になる。
  5. Thresholds: 絶対値で 80（または Absolute / Percentage を確認）。消化率なら 80 = 80%
    Percentage モードにしきい値 80 を入れると「スケールの 80%」になり、Max=Auto と組み合わさってさらに混乱する → Max=100 + しきい値は Absolute の 80 が安全

SQL（${period} 対応）

WITH bounds AS (
  SELECT
    date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo')::date AS ym,
    CASE ${period:singlequote}
      WHEN 'day' THEN date_trunc('day', NOW() AT TIME ZONE 'Asia/Tokyo')
      WHEN '日次' THEN date_trunc('day', NOW() AT TIME ZONE 'Asia/Tokyo')
      WHEN 'week' THEN date_trunc('week', NOW() AT TIME ZONE 'Asia/Tokyo')
      WHEN '週次' THEN date_trunc('week', NOW() AT TIME ZONE 'Asia/Tokyo')
      ELSE date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo')
    END AS range_start,
    CASE ${period:singlequote}
      WHEN 'day' THEN date_trunc('day', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '1 day'
      WHEN '日次' THEN date_trunc('day', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '1 day'
      WHEN 'week' THEN date_trunc('week', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '7 days'
      WHEN '週次' THEN date_trunc('week', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '7 days'
      ELSE date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '1 month'
    END AS range_end,
    CASE ${period:singlequote}
      WHEN 'day' THEN 1
      WHEN '日次' THEN 1
      WHEN 'week' THEN 7
      WHEN '週次' THEN 7
      ELSE EXTRACT(
        DAY FROM (
          date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '1 month'
          - date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo')
        )
      )::int
    END AS period_days,
    EXTRACT(
      DAY FROM (
        date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '1 month'
        - date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo')
      )
    )::int AS month_days
),
bm AS (
  SELECT m.variable_pool_yen AS spendable_yen
  FROM budget_months m, bounds b
  WHERE m.year_month = b.ym
),
explicit AS (
  SELECT
    c.category,
    ROUND(c.monthly_limit_yen::numeric * b.period_days / b.month_days, 0)::int AS period_limit_yen
  FROM budget_month_categories c, bounds b
  WHERE c.year_month = b.ym
),
range_spent AS (
  SELECT
    COALESCE(e.category, 'その他') AS category,
    SUM(e.amount_yen) AS spent_yen
  FROM expenses e, bounds b
  WHERE (COALESCE(e.paid_at, e.created_at) AT TIME ZONE 'Asia/Tokyo') >= b.range_start
    AND (COALESCE(e.paid_at, e.created_at) AT TIME ZONE 'Asia/Tokyo') < b.range_end
  GROUP BY 1
)
SELECT
  x.category
    || E'\n残り '
    || (x.period_limit_yen - COALESCE(s.spent_yen, 0))::text
    || '円' AS "ラベル",
  ROUND(
    100.0 * COALESCE(s.spent_yen, 0) / NULLIF(x.period_limit_yen, 0),
    1
  ) AS "消化率"
FROM explicit x
LEFT JOIN range_spent s ON s.category = x.category
ORDER BY "消化率" DESC NULLS LAST;

パネル 4b: 食費の今日目安（Stat）

（日割り。$period には依存しない）

WITH bounds AS (
  SELECT
    date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo')::date AS ym,
    date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo') AS month_start,
    date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '1 month' AS month_end
),
food AS (
  SELECT monthly_limit_yen AS limit_yen
  FROM budget_month_categories
  WHERE year_month = (SELECT ym FROM bounds) AND category = '食費'
),
spent AS (
  SELECT COALESCE(SUM(amount_yen), 0) AS spent_yen
  FROM expenses e, bounds b
  WHERE COALESCE(e.category, '') = '食費'
    AND (COALESCE(e.paid_at, e.created_at) AT TIME ZONE 'Asia/Tokyo') >= b.month_start
    AND (COALESCE(e.paid_at, e.created_at) AT TIME ZONE 'Asia/Tokyo') < b.month_end
),
days_left AS (
  SELECT GREATEST(
    (date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '1 month')::date
      - (NOW() AT TIME ZONE 'Asia/Tokyo')::date,
    1
  ) AS n
)
SELECT
  ROUND((f.limit_yen - s.spent_yen)::numeric / d.n, 0) AS food_daily_pace_yen
FROM food f CROSS JOIN spent s CROSS JOIN days_left d;

パネル 5: 今月予算ヘッダ（Table）

WITH bounds AS (
  SELECT date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo')::date AS ym
),
explicit_sum AS (
  SELECT COALESCE(SUM(monthly_limit_yen), 0) AS yen
  FROM budget_month_categories c, bounds b
  WHERE c.year_month = b.ym
)
SELECT
  m.year_month AS "月",
  m.status AS "状態",
  m.ceiling_yen AS "上限(前月給与)",
  m.savings_yen AS "固定貯金",
  m.fixed_costs_yen AS "固定費",
  m.variable_pool_yen AS "変動枠",
  e.yen AS "変動カテゴリ割当合計",
  m.unallocated_to_surplus_yen AS "未割当→別枠",
  m.month_end_surplus_yen AS "月末実残→別枠"
FROM budget_months m
CROSS JOIN bounds b
CROSS JOIN explicit_sum e
WHERE m.year_month = b.ym;

パネル 5b: 固定費マスタ（Table）

SELECT name AS "固定費", monthly_yen AS "月額", expense_category AS "実績カテゴリ", active AS "有効"
FROM fixed_cost_items
ORDER BY name;

パネル 5c: 別枠貯金残高（Stat）

SELECT COALESCE(SUM(amount_yen), 0) AS surplus_balance_yen
FROM surplus_ledger;

パネル 5d: 別枠貯金の月次入金（Table）

SELECT
  year_month AS "月",
  reason AS "理由",
  amount_yen AS "金額",
  note AS "メモ",
  created_at AT TIME ZONE 'Asia/Tokyo' AS "計上日時"
FROM surplus_ledger
ORDER BY created_at DESC
LIMIT 50;

パネル 6〜: 収入まわり（incomes）

収入テーブルが空だと 0 になる（その場合パネル1/3は計画月収フォールバック）。
Pie なら Value options を All values に。

6a. 今月の記録収入合計（Stat）

SELECT COALESCE(SUM(amount_yen), 0) AS recorded_income_yen
FROM incomes
WHERE (COALESCE(received_at, created_at) AT TIME ZONE 'Asia/Tokyo')
      >= date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo')
  AND (COALESCE(received_at, created_at) AT TIME ZONE 'Asia/Tokyo')
      <  date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '1 month';

6b. 計画月収 vs 記録収入（Table）

WITH recorded AS (
  SELECT COALESCE(SUM(amount_yen), 0) AS recorded_income_yen
  FROM incomes
  WHERE (COALESCE(received_at, created_at) AT TIME ZONE 'Asia/Tokyo')
        >= date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo')
    AND (COALESCE(received_at, created_at) AT TIME ZONE 'Asia/Tokyo')
        <  date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '1 month'
)
SELECT
  COALESCE((SELECT monthly_income_yen FROM budget_settings WHERE id = 1), 0) AS "計画月収",
  r.recorded_income_yen AS "記録収入",
  r.recorded_income_yen
    - COALESCE((SELECT monthly_income_yen FROM budget_settings WHERE id = 1), 0) AS "差分(記録-計画)"
FROM recorded r;

6c. （廃止）全体の残り

パネル 3 と同じ。新規ダッシュボードではパネル 3 を使う。

6d. 収入の分類内訳（Pie / Table）

SELECT
  COALESCE(category, 'その他') AS category,
  SUM(amount_yen) AS amount
FROM incomes
WHERE (COALESCE(received_at, created_at) AT TIME ZONE 'Asia/Tokyo')
      >= date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo')
  AND (COALESCE(received_at, created_at) AT TIME ZONE 'Asia/Tokyo')
      <  date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '1 month'
GROUP BY 1
ORDER BY amount DESC;

Pie のときは Show = All values。

6e. 今月の収支サマリ（Table）

WITH bounds AS (
  SELECT
    date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo') AS month_start,
    date_trunc('month', NOW() AT TIME ZONE 'Asia/Tokyo') + INTERVAL '1 month' AS month_end
),
inc AS (
  SELECT COALESCE(SUM(i.amount_yen), 0) AS income_yen
  FROM incomes i, bounds b
  WHERE (COALESCE(i.received_at, i.created_at) AT TIME ZONE 'Asia/Tokyo') >= b.month_start
    AND (COALESCE(i.received_at, i.created_at) AT TIME ZONE 'Asia/Tokyo') < b.month_end
),
exp AS (
  SELECT COALESCE(SUM(e.amount_yen), 0) AS expense_yen
  FROM expenses e, bounds b
  WHERE (COALESCE(e.paid_at, e.created_at) AT TIME ZONE 'Asia/Tokyo') >= b.month_start
    AND (COALESCE(e.paid_at, e.created_at) AT TIME ZONE 'Asia/Tokyo') < b.month_end
)
SELECT
  i.income_yen AS "収入",
  e.expense_yen AS "支出",
  i.income_yen - e.expense_yen AS "収支",
  (SELECT monthly_savings_yen FROM budget_settings WHERE id = 1) AS "予定貯金",
  i.income_yen - e.expense_yen
    - (SELECT monthly_savings_yen FROM budget_settings WHERE id = 1) AS "貯金後の残り感"
FROM inc i
CROSS JOIN exp e;

6f. 最近の収入明細（Table）

SELECT
  id AS income_id,
  COALESCE(received_at, created_at) AS when_ts,
  amount_yen,
  category,
  counterparty,
  memo,
  source
FROM incomes
ORDER BY id DESC
LIMIT 30;

参考: 分析用（サブダッシュボード向け）

SELECT COALESCE(SUM(amount_yen), 0) AS today_total
FROM expenses
WHERE COALESCE(paid_at, created_at)::date
    = (NOW() AT TIME ZONE 'Asia/Tokyo')::date;

右上の時間ピッカーと連動させたい分析パネルでは $__timeFrom() / $__timeTo() を WHERE に書く。
予算パネルは「今月」固定なので、通常はマクロ不要。

学習チェック

  1. なぜ Grafana に finance_app を渡さない？
  2. 「変動枠」の式は？（給与 − 固定貯金 − 固定費）
  3. ジャンル別残りに、変動枠のほか何が要る？
  4. paid_at が null の行をどう扱う？
  5. 別枠貯金はどのテーブル／どのタイミングで増える？

うまくいった状態

  • 「変動枠の残り」「ジャンル別の残り」「別枠貯金」が見える
  • 固定費マスタと変動カテゴリが分かれている
  • Bot が動いている

次

予算ダッシュボードが安定したら Tasker + Tunnel（PayPay 自動） へ。