0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@hideszin株式会社Tech Lab

SwitchBot + FastAPI + Flutter で家庭の電力消費を可視化するWebアプリ構築（その3）

Last updated at Posted at 2026-01-19

Supabase で IoT 時系列データを管理する - スキーマ設計から RLS まで

📝 はじめに

この記事は
SwitchBot + FastAPI + Flutter で家庭の電力消費を可視化するWebアプリ構築（その2） #Python - Qiita
の続きになります。

:white_check_mark: この記事で書くこと

  • Supabase（PostgreSQL）でのテーブル設計
  • Python クライアントでの CRUD 操作
  • Row Level Security（RLS）によるセキュリティ設定

:white_check_mark: 対象読者

  • Supabase を使ってみたい方
  • IoT データのデータベース設計に興味がある方
  • PostgreSQL の RLS を学びたい方

:white_check_mark: 前提

  • SQL の基本知識
  • REST API の基本概念

🎯 背景・動機

なぜ Supabase を選んだのか

:thinking: 課題
5分ごとに収集される電力データを効率的に保存・検索したい。

:bulb: Firebase ではなく Supabase を選んだ理由

項目 Supabase Firebase
データベース PostgreSQL（RDB） Firestore（NoSQL）
クエリ SQL 独自クエリ
集計関数 AVG, MAX, MIN 等 限定的
JOIN サポート 非サポート
無料枠 500MB / 50,000行 1GB

:point_right: 時系列データの集計には SQL の集計関数が便利です。

🛠️ 手順/解説

テーブル設計

CREATE TABLE power_monitor_data (
    id BIGSERIAL PRIMARY KEY,
    device_id VARCHAR NOT NULL,
    device_name VARCHAR NOT NULL,
    power_consumption DECIMAL(10, 2),
    voltage DECIMAL(10, 2),
    ampere DECIMAL(10, 2),
    electricity_of_day DECIMAL(10, 2),
    power_state VARCHAR(10),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc', NOW()),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc', NOW())
);

各カラムの設計意図

カラム 型 説明
id BIGSERIAL 自動採番 ID
device_id VARCHAR SwitchBot のデバイス ID
device_name VARCHAR 人間が識別しやすい名前
power_consumption DECIMAL(10,2) 消費電力（W）
voltage DECIMAL(10,2) 電圧（V）
ampere DECIMAL(10,2) 電流（A）
electricity_of_day DECIMAL(10,2) 当日累積消費量（Wh）
power_state VARCHAR(10) "on" / "off" / "unknown"
created_at TIMESTAMP WITH TIME ZONE データ収集時刻

なぜ DECIMAL を使うのか

power_consumption DECIMAL(10, 2)  -- 小数点以下2桁

:white_check_mark: DECIMAL vs FLOAT

型 精度 用途
FLOAT 近似値 科学計算
DECIMAL 正確 金額・測定値

電力データはグラフ表示で誤差が出ると困るため、DECIMAL を選択しました。

インデックス設計

-- デバイス ID での検索を高速化
CREATE INDEX idx_power_monitor_device_id
ON power_monitor_data(device_id);

-- 時系列データの取得を高速化
CREATE INDEX idx_power_monitor_created_at
ON power_monitor_data(created_at);

-- 複合インデックス（デバイス別の時系列取得）
CREATE INDEX idx_power_monitor_device_created
ON power_monitor_data(device_id, created_at DESC);

:white_check_mark: インデックス戦略

クエリパターン 必要なインデックス
WHERE device_id = 'xxx' device_id
ORDER BY created_at DESC created_at
両方の組み合わせ 複合インデックス

Python クライアントの実装

基本設定

from supabase import create_client, Client

class SupabaseClient:
    def __init__(self, url: str, key: str):
        self.client: Client = create_client(url, key)

データ取得（SELECT）

def fetch_table_data(
    self,
    table_name: str,
    limit: int = None,
    order_by: str = None,
    ascending: bool = True
) -> list:
    """テーブルからデータを取得"""
    query = self.client.table(table_name).select("*")

    if order_by:
        query = query.order(order_by, desc=not ascending)

    if limit:
        query = query.limit(limit)

    response = query.execute()
    return response.data

:white_check_mark: クエリビルダーパターン

メソッドチェーンでクエリを構築できます。

result = (
    self.client
    .table("power_monitor_data")
    .select("*")
    .eq("device_id", "XXX")
    .gte("created_at", "2024-01-01")
    .order("created_at", desc=True)
    .limit(100)
    .execute()
)

フィルターメソッド一覧

メソッド SQL 相当 例
.eq(col, val) = .eq("device_id", "XXX")
.neq(col, val) != .neq("power_state", "off")
.gt(col, val) > .gt("power_consumption", 50)
.gte(col, val) >= .gte("created_at", "2024-01-01")
.lt(col, val) < .lt("power_consumption", 100)

データ挿入（INSERT）

def insert_data(self, table_name: str, data: dict) -> bool:
    """データを挿入"""
    try:
        self.client.table(table_name).insert(data).execute()
        return True
    except Exception as e:
        print(f"Insert error: {e}")
        return False

使用例:

record = {
    'device_id': 'XXXXXXXXXXXX',
    'device_name': 'SwitchBot サーバー',
    'power_consumption': 45.2,
    'voltage': 100.5,
    'created_at': '2024-01-08T10:30:00+09:00',
}

client.insert_data("power_monitor_data", record)

データ更新（UPDATE）

def update_data(self, table_name: str, data: dict, condition: dict) -> bool:
    """条件に一致するレコードを更新"""
    try:
        query = self.client.table(table_name).update(data)

        for key, value in condition.items():
            query = query.eq(key, value)

        query.execute()
        return True
    except Exception as e:
        print(f"Update error: {e}")
        return False

Row Level Security（RLS）

RLS とは

行レベルでアクセス制御を行う PostgreSQL の機能です。

-- RLS を有効化
ALTER TABLE power_monitor_data ENABLE ROW LEVEL SECURITY;

なぜ RLS が必要か

:warning: Supabase ではクライアントが直接 DB にアクセスできるため、RLS がないと：

  • 誰でもすべてのデータを読み取れる
  • 誰でもデータを削除・変更できる

ポリシー設定例

読み取り許可（全員）:

CREATE POLICY "Allow public read access"
ON power_monitor_data
FOR SELECT
TO public
USING (true);

挿入許可（サービスロールのみ）:

CREATE POLICY "Service role only insert"
ON power_monitor_data
FOR INSERT
TO service_role
WITH CHECK (true);

:warning: 開発時の注意

開発中は RLS を無効化することがありますが、本番環境では必ず有効化してください。

-- 開発時
ALTER TABLE power_monitor_data DISABLE ROW LEVEL SECURITY;

-- 本番環境
ALTER TABLE power_monitor_data ENABLE ROW LEVEL SECURITY;

時系列データのクエリ例

最新データ取得

SELECT *
FROM power_monitor_data
ORDER BY created_at DESC
LIMIT 100;

デバイス別の平均電力

SELECT
    device_name,
    AVG(power_consumption) as avg_power,
    MAX(power_consumption) as max_power,
    MIN(power_consumption) as min_power
FROM power_monitor_data
WHERE created_at > NOW() - INTERVAL '24 hours'
GROUP BY device_name;

日別集計

SELECT
    DATE(created_at) as date,
    device_id,
    AVG(power_consumption) as avg_power
FROM power_monitor_data
GROUP BY DATE(created_at), device_id
ORDER BY date DESC;

📊 実行結果 & コツ

Supabase ダッシュボードの活用

:white_check_mark: テーブルエディタ

  • GUI でデータの確認・編集
  • カラムの追加・変更

:white_check_mark: SQL エディタ

  • 直接 SQL を実行
  • 集計クエリのテスト

:white_check_mark: ログ

  • API 呼び出しの履歴
  • エラーの確認

つまずきポイントと回避策

:x: つまずき1: RLS を有効化したらデータが取得できない

:white_check_mark: 回避策: 適切なポリシーを設定

CREATE POLICY "Allow read" ON table FOR SELECT USING (true);

:x: つまずき2: タイムゾーンがずれる

:white_check_mark: 回避策: TIMESTAMP WITH TIME ZONE を使用

created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc', NOW())

:x: つまずき3: データ量が増えてクエリが遅い

:white_check_mark: 回避策: 適切なインデックスを追加

CREATE INDEX idx_created_at ON power_monitor_data(created_at);

環境変数の設定

SUPABASE_URL=https://xxxxx.supabase.co
SUPABASE_KEY=eyJhbGciOiJI...（anon key）

:warning: API キーの種類

キー 用途 RLS
anon key クライアント用（公開可） 適用される
service_role key バックエンド用（非公開） バイパス

service_role key は絶対に公開しないでください。

📝 まとめ

学んだこと

:white_check_mark: Supabase の利点

  • PostgreSQL なので SQL の知識がそのまま使える
  • RLS でセキュリティを担保
  • 無料枠で十分に開発・検証できる

:white_check_mark: 時系列データの設計

  • DECIMAL 型で精度を確保
  • created_at にインデックスを設定
  • 複合インデックスでデバイス別検索を高速化
0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?