Batch Item Table

Overview

Stores one batch request, its child job ID, and a durable SQS message. Child lifecycle rows and messages are inserted together in one transaction.

Table Definition

Logical Name	Physical Name	Column Name	Data Type	Primary Key	Relation	Unique	Nullable	Default Value	Remarks
Batch Item	batch_item	id	string	◯					Backend UUIDv7 (varchar(255))
		batch_id	string		batch:id				Owning batch / バッチ (varchar(255))
		position	number						Zero-based input order / 0 始まりの入力順序
		request	json						Validated API request / 検証済み API 入力 (jsonb)
		job_id	string				◯		Page Import or Code2Des ID / 子ジョブ ID (varchar(255))
		message	json				◯		Durable worker payload / 永続化したワーカー入力 (jsonb)
		dispatched_at	datetime				◯		SQS accepted timestamp / SQS 送信確認日時
		lease_until	datetime				◯		Dispatch claim expiry / ディスパッチ取得の有効期限
		dispatch_attempts	number					0	Dispatch claim count / ディスパッチ試行数
		error_message	string				◯		Terminal dispatch error / ディスパッチ失敗理由 (text)
		cancelled_at	datetime				◯		Cancellation timestamp / キャンセル日時
		created_at	datetime					CURRENT_TIMESTAMP	Shared audit field / 共通監査項目
		updated_at	datetime					CURRENT_TIMESTAMP	Shared audit field / 共通監査項目
		deleted_at	datetime				◯		Soft delete / 論理削除日時
		created_by	string						Cognito subject (varchar(50))
		updated_by	string						Cognito subject (varchar(50))
		deleted_by	string				◯		Cognito subject (varchar(50))

Relations

• batch_id → batch.id (delete cascade)
• job_id resolves to page_import.id or code2des.id according to batch type; no polymorphic foreign key. / バッチ種別に応じた子ジョブ ID。外部キー制約は設定しません。

Indexes

• PRIMARY KEY (id)
• INDEX batch_item_batch_idx (batch_id, position)
• INDEX batch_item_dispatch_idx (dispatched_at, lease_until)

Notes

• Aggregate status is derived from child jobs and dispatch metadata; counters are not stored separately.
• Dispatch claims expire after 90 seconds. Row locks prevent concurrent claims. Queue delivery is attempted up to three times using the same child ID and message.

• Workers do not connect to PostgreSQL. Existing webhooks update child job completion.

• API status is derived: "0" processing, "1" completed, "2" failed, "3" cancelled. Queued/running are separate counts, not stored statuses.