PathCollector

【元仕様書 12.2 PostgreSQL DDL 全文】以下を必ず参照のこと。リポジトリの /root/projects/pathcollector/docs/SPEC.md にも要点を載せてあるが、このテーブル定義自体はここが唱一の正とする。

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE organizations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(200) NOT NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'active',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE projects (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    name VARCHAR(200) NOT NULL,
    slug VARCHAR(100) NOT NULL,
    timezone VARCHAR(100) NOT NULL DEFAULT 'Asia/Tokyo',
    status VARCHAR(30) NOT NULL DEFAULT 'active',
    consent_mode VARCHAR(20) NOT NULL DEFAULT 'explicit',
    sample_rate NUMERIC(5,4) NOT NULL DEFAULT 1.0000,
    raw_retention_days INTEGER NOT NULL DEFAULT 30,
    action_retention_days INTEGER NOT NULL DEFAULT 90,
    aggregate_retention_days INTEGER NOT NULL DEFAULT 395,
    settings JSONB NOT NULL DEFAULT '{}'::jsonb,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (organization_id, slug),
    CHECK (sample_rate >= 0 AND sample_rate <= 1),
    CHECK (consent_mode IN ('explicit', 'implicit'))
);

CREATE TABLE project_domains (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    domain VARCHAR(255) NOT NULL,
    include_subdomains BOOLEAN NOT NULL DEFAULT FALSE,
    status VARCHAR(20) NOT NULL DEFAULT 'active',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (project_id, domain)
);

CREATE TABLE api_credentials (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    credential_type VARCHAR(30) NOT NULL,
    key_prefix VARCHAR(40) NOT NULL,
    key_hash VARCHAR(255) NOT NULL,
    scopes TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[],
    status VARCHAR(20) NOT NULL DEFAULT 'active',
    expires_at TIMESTAMPTZ,
    last_used_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    revoked_at TIMESTAMPTZ,
    CHECK (credential_type IN ('collector_write','server_read','server_admin','mcp'))
);
CREATE INDEX idx_api_credentials_prefix ON api_credentials(key_prefix);

CREATE TABLE privacy_rules (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    rule_type VARCHAR(30) NOT NULL,
    selector VARCHAR(1000),
    attribute_name VARCHAR(255),
    pattern VARCHAR(2000),
    mask_type VARCHAR(50),
    priority INTEGER NOT NULL DEFAULT 100,
    enabled BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CHECK (rule_type IN ('mask_selector','ignore_selector','mask_attribute','redact_query_parameter','regex'))
);
CREATE INDEX idx_privacy_rules_project_priority ON privacy_rules(project_id, priority);

CREATE TABLE anonymous_visitors (
    id UUID PRIMARY KEY,
    project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    first_seen_at TIMESTAMPTZ NOT NULL,
    last_seen_at TIMESTAMPTZ NOT NULL,
    first_referrer_domain VARCHAR(255),
    first_landing_path TEXT,
    device_type VARCHAR(30),
    browser_family VARCHAR(100),
    os_family VARCHAR(100),
    consent_state VARCHAR(30) NOT NULL DEFAULT 'unknown',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (project_id, id),
    CHECK (consent_state IN ('unknown','granted','denied','essential_only'))
);
CREATE INDEX idx_visitors_project_last_seen ON anonymous_visitors(project_id, last_seen_at DESC);

(残りのsessions/page_views/event_batches/raw_events/user_actions/privacy_findings/daily_metrics/deletion_requests/audit_logsはEpic #10236の本文参照)

【DDL全文 続き（sessions以降）】

CREATE TABLE sessions (
    id UUID PRIMARY KEY,
    project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    visitor_id UUID NOT NULL,
    started_at TIMESTAMPTZ NOT NULL,
    ended_at TIMESTAMPTZ,
    last_activity_at TIMESTAMPTZ NOT NULL,
    entry_url TEXT, entry_path TEXT, exit_url TEXT, exit_path TEXT,
    referrer_url TEXT, referrer_domain VARCHAR(255),
    page_view_count INTEGER NOT NULL DEFAULT 0,
    event_count INTEGER NOT NULL DEFAULT 0,
    error_count INTEGER NOT NULL DEFAULT 0,
    duration_ms BIGINT,
    converted BOOLEAN NOT NULL DEFAULT FALSE,
    device_type VARCHAR(30), browser_family VARCHAR(100), os_family VARCHAR(100),
    language VARCHAR(20), timezone VARCHAR(100),
    campaign JSONB NOT NULL DEFAULT '{}'::jsonb,
    attributes JSONB NOT NULL DEFAULT '{}'::jsonb,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    FOREIGN KEY (project_id, visitor_id) REFERENCES anonymous_visitors(project_id, id) ON DELETE CASCADE
);
CREATE INDEX idx_sessions_project_started ON sessions(project_id, started_at DESC);
CREATE INDEX idx_sessions_project_visitor ON sessions(project_id, visitor_id, started_at DESC);
CREATE INDEX idx_sessions_project_conversion ON sessions(project_id, converted, started_at DESC);
CREATE INDEX idx_sessions_project_error ON sessions(project_id, error_count, started_at DESC);

CREATE TABLE page_views (
    id UUID PRIMARY KEY,
    project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    session_id UUID NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
    sequence INTEGER NOT NULL,
    url TEXT NOT NULL, path TEXT NOT NULL,
    query_keys TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[],
    title TEXT, referrer_url TEXT, referrer_path TEXT,
    page_type VARCHAR(100), content_id VARCHAR(255),
    started_at TIMESTAMPTZ NOT NULL, ended_at TIMESTAMPTZ, duration_ms BIGINT,
    max_scroll_percent NUMERIC(5,2) DEFAULT 0,
    interaction_count INTEGER NOT NULL DEFAULT 0,
    error_count INTEGER NOT NULL DEFAULT 0,
    viewport_width INTEGER, viewport_height INTEGER,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (session_id, sequence)
);
CREATE INDEX idx_page_views_project_path_started ON page_views(project_id, path, started_at DESC);
CREATE INDEX idx_page_views_session_sequence ON page_views(session_id, sequence);

CREATE TABLE event_batches (
    id UUID PRIMARY KEY,
    project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    session_id UUID NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
    received_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), sent_at TIMESTAMPTZ,
    sdk_version VARCHAR(50), schema_version VARCHAR(20) NOT NULL,
    event_count INTEGER NOT NULL,
    compressed_bytes INTEGER, uncompressed_bytes INTEGER,
    privacy_finding_count INTEGER NOT NULL DEFAULT 0,
    processing_status VARCHAR(30) NOT NULL DEFAULT 'received',
    processing_error_code VARCHAR(100),
    CHECK (processing_status IN ('received','processing','completed','rejected','failed'))
);
CREATE INDEX idx_event_batches_project_received ON event_batches(project_id, received_at DESC);

CREATE TABLE raw_events (
    id UUID PRIMARY KEY,
    project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    session_id UUID NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
    page_view_id UUID REFERENCES page_views(id) ON DELETE CASCADE,
    batch_id UUID REFERENCES event_batches(id) ON DELETE SET NULL,
    sequence BIGINT NOT NULL, occurred_at TIMESTAMPTZ NOT NULL,
    event_type VARCHAR(50) NOT NULL, rrweb_type SMALLINT, rrweb_source SMALLINT,
    payload JSONB NOT NULL,
    masked BOOLEAN NOT NULL DEFAULT TRUE,
    privacy_finding_count INTEGER NOT NULL DEFAULT 0,
    expires_at TIMESTAMPTZ NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (session_id, sequence)
);
CREATE INDEX idx_raw_events_session_sequence ON raw_events(session_id, sequence);
CREATE INDEX idx_raw_events_project_occurred ON raw_events(project_id, occurred_at DESC);
CREATE INDEX idx_raw_events_expires ON raw_events(expires_at);

CREATE TABLE user_actions (
    id UUID PRIMARY KEY,
    project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    session_id UUID NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
    page_view_id UUID REFERENCES page_views(id) ON DELETE CASCADE,
    raw_event_id UUID REFERENCES raw_events(id) ON DELETE SET NULL,
    sequence BIGINT NOT NULL, occurred_at TIMESTAMPTZ NOT NULL,
    action_type VARCHAR(50) NOT NULL, action_name VARCHAR(200),
    element_tag VARCHAR(50), element_role VARCHAR(100), element_type VARCHAR(100),
    element_selector TEXT, element_track_id VARCHAR(255), element_text TEXT,
    page_path TEXT NOT NULL, destination_path TEXT,
    scroll_percent NUMERIC(5,2),
    properties JSONB NOT NULL DEFAULT '{}'::jsonb,
    expires_at TIMESTAMPTZ NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX

レビューで仕様逸脱を発見したため再オープン。修正項目（項目名：現状 → 修正内容）:

1. organizations: 仕様にないslug/unique制約を削除(仕様通り5列のみ)
2. projects: consent_mode(explicit/implicit,CHECK付き)、sample_rate(NUMERIC(5,4),CHECK 0-1)、raw_retention_days/action_retention_days/aggregate_retention_days(別々の3列、現状のdata_retention_days 1列を廃止)、settings JSONBを追加。timezoneの既定値を'Asia/Tokyo'に
3. api_credentials: 現状のscope enum(ingest/mcp_read/mcp_admin)を廃止し、credential_type(collector_write/server_read/server_admin/mcpの4値CHECK)とscopes TEXT[]の2列に分離。status VARCHAR(20)列も追加
4. privacy_rules: 現状のrule_type enum(mask_selector/exclude_selector/consent_mode/ip_anonymize/retention_override)を廃止し、仕様通り(mask_selector/ignore_selector/mask_attribute/redact_query_parameter/regex)の5値CHECKに変更。attribute_name/pattern/mask_type(9.2の11値)/priority列を追加（現状のaction/value列は廃止）
5. 上記以外のテーブル(anonymous_visitors以降)も本チケットのコメント欄に追記済みの完全なDDLと一字一句照合し、カラム名・型・制約・CHECK・FKを完全一致させること
6. drizzle-kitでマイグレーションを再生成し、以前のマイグレーションは破棄(まだ本番データなしのため安全)
   ※ 今回はdocs/SPEC.mdを元仕様書全文に差し替え済み、このチケットのコメント欄にDDL全文も追記済みなので推測不要。

【DDL全文 続き(3/3、user_actionsの残りインデックス〜audit_logs)】前コメントが5000文字付近で切れていたため分割投稿。

CREATE INDEX idx_user_actions_session_sequence ON user_actions(session_id, sequence);
CREATE INDEX idx_user_actions_project_type_time ON user_actions(project_id, action_type, occurred_at DESC);
CREATE INDEX idx_user_actions_project_name_time ON user_actions(project_id, action_name, occurred_at DESC);
CREATE INDEX idx_user_actions_project_page ON user_actions(project_id, page_path, occurred_at DESC);
CREATE INDEX idx_user_actions_expires ON user_actions(expires_at);

CREATE TABLE privacy_findings (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    batch_id UUID REFERENCES event_batches(id) ON DELETE CASCADE,
    detected_type VARCHAR(50) NOT NULL,
    location_type VARCHAR(50) NOT NULL,
    event_count INTEGER NOT NULL DEFAULT 1,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_privacy_findings_project_created ON privacy_findings(project_id, created_at DESC);

CREATE TABLE daily_metrics (
    project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    metric_date DATE NOT NULL,
    dimension_type VARCHAR(50) NOT NULL,
    dimension_value VARCHAR(1000) NOT NULL,
    sessions BIGINT NOT NULL DEFAULT 0,
    visitors BIGINT NOT NULL DEFAULT 0,
    page_views BIGINT NOT NULL DEFAULT 0,
    entrances BIGINT NOT NULL DEFAULT 0,
    exits BIGINT NOT NULL DEFAULT 0,
    clicks BIGINT NOT NULL DEFAULT 0,
    form_starts BIGINT NOT NULL DEFAULT 0,
    form_submits BIGINT NOT NULL DEFAULT 0,
    conversions BIGINT NOT NULL DEFAULT 0,
    errors BIGINT NOT NULL DEFAULT 0,
    total_duration_ms BIGINT NOT NULL DEFAULT 0,
    metrics JSONB NOT NULL DEFAULT '{}'::jsonb,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (project_id, metric_date, dimension_type, dimension_value)
);
CREATE INDEX idx_daily_metrics_project_date ON daily_metrics(project_id, metric_date DESC);

CREATE TABLE deletion_requests (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    target_type VARCHAR(30) NOT NULL,
    target_identifier_hash VARCHAR(255) NOT NULL,
    reason VARCHAR(500),
    requested_by VARCHAR(255),
    status VARCHAR(30) NOT NULL DEFAULT 'pending',
    requested_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    started_at TIMESTAMPTZ,
    completed_at TIMESTAMPTZ,
    failed_at TIMESTAMPTZ,
    result JSONB NOT NULL DEFAULT '{}'::jsonb,
    CHECK (target_type IN ('session','visitor','project_range')),
    CHECK (status IN ('pending','processing','completed','failed'))
);

CREATE TABLE audit_logs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID REFERENCES organizations(id) ON DELETE SET NULL,
    project_id UUID REFERENCES projects(id) ON DELETE SET NULL,
    actor_type VARCHAR(30) NOT NULL,
    actor_id VARCHAR(255),
    action VARCHAR(100) NOT NULL,
    resource_type VARCHAR(100),
    resource_id VARCHAR(255),
    request_id UUID,
    source VARCHAR(30) NOT NULL,
    result VARCHAR(30) NOT NULL,
    metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CHECK (actor_type IN ('user','api_key','mcp_client','system')),
    CHECK (source IN ('api','collector','mcp','worker'))
);
CREATE INDEX idx_audit_logs_project_created ON audit_logs(project_id, created_at DESC);

これで全テーブルのDDLが揃いました。作業を再開してください。

[Producer監査] 解決済みだが終了未遷移だったため終了へクローズ