LoginSignup
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?

@engineerkw001

美容クリニック向けの在庫管理システムのDB設計について真剣に考える

Posted at

はじめに：なぜ美容クリニックの在庫管理は特殊なのか？

美容クリニックの在庫管理システムには、一般的な小売とは異なる論点が複数あります。

  • 薬剤や医療材料は有効期限切れが重大インシデントにつながる
  • 複数院を跨ぐ運用で、施設間のデータ混在を避ける必要がある
  • 役職に応じて操作権限を厳密に制御しなければならない
  • 監査対応に備え「いつ・誰が・何をしたか」を証明する仕組みが必須

以下では、これらの課題をデータベース設計段階で解決するための実践的なパターンをまとめます。

1. 施設（院）ごとにデータを完全分離する

facility_id を全テーブルに通す

複数院を運営する場合、院を跨いだデータ参照はインシデントにつながる可能性があります。以下は在庫残高テーブルを例に施設スコープを明示的に持たせたサンプルです。

create table inventory_balances_example (
  id uuid primary key default gen_random_uuid(),
  facility_id uuid not null references facilities(id) on delete cascade,
  item_id uuid not null references items(id) on delete cascade,
  quantity_units integer not null check (quantity_units >= 0),
  updated_at timestamptz not null default now(),
  updated_by uuid references auth.users(id),
  constraint inventory_balances_example_facility_item_unique unique (facility_id, item_id)
);

中間テーブルにも facility_id を冗長に保持しておくと、RLS・監査・検索インデックスの設計が楽になります。

can_access_facility を組み込んだ RLS

施設アクセスを判定する補助関数（例：can_access_facility）を用意しておくと、RLS ポリシーを以下のようにシンプルに記述できます。

create policy inventory_balances_read_access on inventory_balances_example
  for select using (
    has_permission(auth.jwt(), 'inventory.read')
    and can_access_facility(facility_id)
  );

can_access_facility は user_facilities テーブルを参照しつつ、権限に応じて本部スタッフへ広い閲覧範囲を付与します。施設分離を厳格にしたいテーブルはすべて同じスタイルでポリシーを用意しましょう。

2. 「誰が何をできるか」は SSOT と DB で強制する

権限定義を SSOT にする

ロールと権限の対応は単一の TypeScript 定義に集約し、アプリケーションと DB で同じ定義を参照します。以下はサンプルです。

type Permission =
  | 'inventory.read'
  | 'inventory.adjust'
  | 'facility.manage'
  | 'audit.view'
  | 'user.manage';

type UserRole = 'clinic_admin' | 'facility_manager' | 'staff';

const rolePermissions: Record<UserRole, Permission[]> = {
  clinic_admin: ['inventory.read', 'inventory.adjust', 'facility.manage', 'audit.view', 'user.manage'],
  facility_manager: ['inventory.read', 'inventory.adjust', 'audit.view'],
  staff: ['inventory.read'],
};

JWT の app_metadata.role を使った権限判定

Supabase ではカスタムクレームとして app_metadata.role を JWT に含められます。DB 側では has_permission のような関数を用意し、ロールに対応する権限を返します。

create or replace function has_permission(jwt jsonb, required_permission text)
returns boolean
language plpgsql
stable
as $$
declare
  role text;
  permissions text[];
begin
  role := coalesce(jwt -> 'app_metadata' ->> 'role', '');
  if role = '' then
    return false;
  end if;

  case role
    when 'clinic_admin' then
      permissions := array['inventory.read', 'inventory.adjust', 'facility.manage', 'audit.view', 'user.manage'];
    when 'facility_manager' then
      permissions := array['inventory.read', 'inventory.adjust', 'audit.view'];
    when 'staff' then
      permissions := array['inventory.read'];
    else
      permissions := array[]::text[];
  end case;

  return required_permission = any(permissions);
end;
$$;

RLS ポリシーはこの関数を介して記述します。

create policy stock_transfers_select_access on stock_transfers_example
  for select using (
    (
      has_permission(auth.jwt(), 'inventory.read')
      and (
        can_access_facility(from_facility_id)
        or can_access_facility(to_facility_id)
      )
    )
    or has_permission(auth.jwt(), 'audit.view')
  );

アプリケーション側でもサーバー専用の権限チェックヘルパーを経由して同じ判定を行うことで、UI と DB の整合性を維持できます。

3. CHECK 制約で「変なデータ」を早期に遮断する

量や整合性のチェック

人的ミスによる誤登録を防ぐには、テーブル定義に最低限の制約を仕込むのが効果的です。以下はロット在庫テーブルのサンプルです。

create table stock_lots_example (
  id uuid primary key default gen_random_uuid(),
  facility_id uuid not null references facilities(id) on delete cascade,
  item_id uuid not null references items(id) on delete cascade,
  lot_number text not null,
  expiration_date date,
  received_at date,
  initial_quantity_units integer not null check (initial_quantity_units >= 0),
  remaining_quantity_units integer not null check (remaining_quantity_units >= 0),
  created_at timestamptz not null default now(),
  created_by uuid references auth.users(id),
  constraint stock_lots_example_remaining_check check (remaining_quantity_units <= initial_quantity_units),
  constraint stock_lots_example_expiration_consistency
    check (
      expiration_date is null
      or received_at is null
      or expiration_date >= received_at
    )
);
  • ロット単位で数量が負になる更新を防止
  • expiration_date が received_at より前になる誤入力を排除
  • 過去データをインポートしたい場合は received_at を明示しておけば制約に引っかかりません

加えて、reason やステータスは列挙を CHECK (reason in (...)) で縛ると、アプリ側の選択肢と不整合を起こしません。

4. 「いつ・誰が・何をしたか」を確実に残す

監査ログテーブルを基盤にする

医療機関では後追い調査が頻繁に発生するため、監査ログは最初から設計に組み込みます。以下はテーブル定義の一例です。

create table audit_logs_example (
  id uuid primary key default gen_random_uuid(),
  occurred_at timestamptz not null default now(),
  user_id uuid not null references auth.users(id),
  facility_id uuid references facilities(id),
  action text not null,
  table_name text not null,
  record_id uuid,
  old_data jsonb,
  new_data jsonb,
  ip_address inet,
  user_agent text
);

alter table audit_logs_example enable row level security;
create policy audit_logs_read_access on audit_logs_example
  for select using (
    has_permission(auth.jwt(), 'audit.view')
    and (
      facility_id is null
      or can_access_facility(facility_id)
    )
  );

書き込みはサーバーアクション経由で一元化

Service Role キーは RLS をバイパスできる強力な権限を持つため、直接テーブルへ書き込むのではなくストアドファンクション経由での最小権限実行にまとめます。

create or replace function log_audit_event_example(
  p_user_id uuid,
  p_facility_id uuid,
  p_action text,
  p_table_name text,
  p_record_id uuid,
  p_old_data jsonb,
  p_new_data jsonb,
  p_ip_address inet,
  p_user_agent text
) returns void
language plpgsql
security definer
as $$
begin
  insert into audit_logs_example (
    user_id,
    facility_id,
    action,
    table_name,
    record_id,
    old_data,
    new_data,
    ip_address,
    user_agent
  ) values (
    p_user_id,
    p_facility_id,
    p_action,
    p_table_name,
    p_record_id,
    p_old_data,
    p_new_data,
    p_ip_address,
    p_user_agent
  );
end;
$$;
  • security definer とすることで、Service Role 経由でもこの関数が許可する挿入のみに限定できます
  • アプリケーション側では専用の Server Action/Route から supabase.rpc('log_audit_event_example', ...) を呼び出し、引数をサニタイズします
  • 監査ログ自身への INSERT ポリシーは auth.jwt() を使わず、ファンクション経由のみで行う方針を明文化しておきます

5. Supabase MCP を安全に活用する

MCP（Model Context Protocol）を使うと、AI アシスタントがスキーマを理解した上でレビューやドキュメント化を支援してくれます。ただし、扱いに注意が必要です。

// サンプル: 開発環境のメタデータだけを公開し、読み取り専用で接続する
const mcpConfig = {
  environment: 'development',
  readOnly: true,
  allowedOperations: ['explain', 'review', 'test'],
};
  • 本番環境の接続情報は共有しない
  • 読み取り専用キーに限定し、更新操作はすべて Server Action/Route を経由
  • スキーマや RBAC に変更が入ったら社内のガイドライン／仕様書を即時更新し、MCP の参照元を最新に保つ

まとめ：押さえるべき 5 つのポイント

  1. 施設ごとのデータ分離 —— facility_id と RLS (can_access_facility) で完全分離
  2. 権限管理の一元化 —— 権限定義を単一ソース化し、has_permission でサーバー強制
  3. データ品質の保証 —— CHECK 制約で数量・日付の整合性を担保
  4. 監査ログの整備 —— 専用テーブル + log_audit_event_example で操作履歴を完全記録
  5. 開発効率の両立 —— Supabase MCP は開発環境・読み取り専用で安全に活用

データベース設計は建物の基礎工事と同じです。最初に堅牢な土台を作っておけば、後続のアプリケーション開発や監査対応が格段にスムーズになります。ぜひ参考にしてみてください。

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?