sa-orchestration/includes/class-sa-migrations.php

<?php
defined( 'ABSPATH' ) || exit;

class SA_Orch_Migrations {

    public static function install() {
        global $wpdb;

        require_once ABSPATH . 'wp-admin/includes/upgrade.php';

        $charset = $wpdb->get_charset_collate();
        $p       = $wpdb->prefix . SA_ORCH_DB_PREFIX;

        // wp_sa_arbitration
        $sql_arb = "CREATE TABLE {$p}arbitration (
            id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            hash            VARCHAR(64) NOT NULL,
            demand_surface  VARCHAR(64) NOT NULL,
            demand_ref      VARCHAR(255) NOT NULL,
            rationale       TEXT NOT NULL,
            projection_type VARCHAR(64) NOT NULL,
            actor_user_id   BIGINT UNSIGNED DEFAULT NULL,
            correlation_id  VARCHAR(64) NOT NULL,
            causation_id    VARCHAR(64) DEFAULT NULL,
            truth_class     VARCHAR(32) NOT NULL DEFAULT 'canonical',
            asterion_path   VARCHAR(255) DEFAULT NULL,
            created_at      DATETIME NOT NULL,
            PRIMARY KEY  (id),
            UNIQUE KEY uniq_hash (hash),
            KEY idx_demand   (demand_surface, demand_ref),
            KEY idx_corr     (correlation_id),
            KEY idx_created  (created_at)
        ) $charset;";

        // wp_sa_arbitration_plan_link
        $sql_link = "CREATE TABLE {$p}arbitration_plan_link (
            id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            arbitration_id  BIGINT UNSIGNED NOT NULL,
            plan_surface    VARCHAR(64) NOT NULL,
            plan_ref        VARCHAR(255) NOT NULL,
            link_kind       VARCHAR(32) NOT NULL DEFAULT 'derived',
            created_at      DATETIME NOT NULL,
            PRIMARY KEY  (id),
            KEY idx_arb     (arbitration_id),
            KEY idx_plan    (plan_surface, plan_ref)
        ) $charset;";

        // wp_sa_acceptance_event
        $sql_acc = "CREATE TABLE {$p}acceptance_event (
            id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            hash            VARCHAR(64) NOT NULL,
            demand_surface  VARCHAR(64) NOT NULL,
            demand_ref      VARCHAR(255) NOT NULL,
            event_type      VARCHAR(32) NOT NULL,
            prior_event_id  BIGINT UNSIGNED DEFAULT NULL,
            actor_user_id   BIGINT UNSIGNED DEFAULT NULL,
            actor_role      VARCHAR(32) NOT NULL,
            details         TEXT DEFAULT NULL,
            truth_class     VARCHAR(32) NOT NULL DEFAULT 'canonical',
            asterion_path   VARCHAR(255) DEFAULT NULL,
            observed_at     DATETIME NOT NULL,
            recorded_at     DATETIME NOT NULL,
            PRIMARY KEY  (id),
            UNIQUE KEY uniq_hash (hash),
            KEY idx_demand_time (demand_surface, demand_ref, observed_at),
            KEY idx_event_type  (event_type)
        ) $charset;";

        // wp_sa_attention_log — Board #21 (Attention Bridge REST).
        //
        // SEMANTICS: this is an ATTEMPTED-EMISSION log, NOT a successful-receipt log.
        // A row records that an emit was attempted; the 'status' column tells you
        // whether the receiver accepted it.
        //
        // status enum (Board #21 rev2):
        //   'success'        — outbound POST returned HTTP 2xx; receiver accepted the packet
        //   'failed'         — transport error (connection refused, DNS, timeout) OR non-2xx HTTP response
        //   'blocked_config' — RESERVED. Currently never written. If config validation fails,
        //                      no log row is created at all (validation precedes any I/O). The value
        //                      is reserved for future use cases (e.g. partial-config emission attempts).
        //
        // NOT a query/analytics store. Captures:
        //   - what was attempted (full payload_json)
        //   - whether it arrived (status + http_status + response_excerpt OR error_message)
        // Per stakeholder spec: "Did packet arrive? What did it contain?"
        $sql_attn = "CREATE TABLE {$p}attention_log (
            id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            site_id         VARCHAR(64) NOT NULL,
            surface         VARCHAR(64) DEFAULT NULL,
            ref             VARCHAR(255) DEFAULT NULL,
            target_url      VARCHAR(512) NOT NULL,
            payload_json    TEXT NOT NULL,
            status          VARCHAR(16) DEFAULT NULL,
            http_status     INT DEFAULT NULL,
            response_excerpt TEXT DEFAULT NULL,
            error_message   TEXT DEFAULT NULL,
            recorded_at     DATETIME NOT NULL,
            PRIMARY KEY  (id),
            KEY idx_site_time (site_id, recorded_at),
            KEY idx_status    (status)
        ) $charset;";

        // wp_sa_token_log — Board #16 (Token + Usage Visibility).
        // Append-only audit of LLM invocations. Each call writes one row.
        // (comment_kind, comment_id) is filled in later by the future
        // invocation surface; until then, all rows have nulls there and
        // the Board #23 filter callback returns false for all comments.
        $sql_token = "CREATE TABLE {$p}token_log (
            id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            user_id         BIGINT UNSIGNED DEFAULT NULL,
            provider        VARCHAR(64) NOT NULL,
            model           VARCHAR(128) DEFAULT NULL,
            prompt_tokens   INT UNSIGNED DEFAULT 0,
            completion_tokens INT UNSIGNED DEFAULT 0,
            total_tokens    INT UNSIGNED DEFAULT 0,
            comment_kind    VARCHAR(32) DEFAULT NULL,
            comment_id      BIGINT UNSIGNED DEFAULT NULL,
            invoked_at      DATETIME NOT NULL,
            PRIMARY KEY  (id),
            KEY idx_user_time (user_id, invoked_at),
            KEY idx_comment   (comment_kind, comment_id)
        ) $charset;";

        // wp_sa_actor_handoff — Board #23 (Actor Handoff Log).
        // Two-axis attribution log. Records who "pressed send" for an
        // actor-authored comment. Strictly additive — does NOT touch
        // fbs_comments / fs_conversations.
        $sql_handoff = "CREATE TABLE {$p}actor_handoff (
            id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            comment_kind    VARCHAR(32) NOT NULL,
            comment_id      BIGINT UNSIGNED NOT NULL,
            committed_by_user_id BIGINT UNSIGNED NOT NULL,
            recorded_at     DATETIME NOT NULL,
            PRIMARY KEY  (id),
            KEY idx_comment   (comment_kind, comment_id),
            KEY idx_committer (committed_by_user_id, recorded_at)
        ) $charset;";

        dbDelta( $sql_arb );
        dbDelta( $sql_link );
        dbDelta( $sql_acc );
        dbDelta( $sql_attn );
        dbDelta( $sql_handoff );
        dbDelta( $sql_token );

        // Asterion ledger root
        $root = SA_Orch_Asterion::root();
        if ( ! is_dir( $root ) ) {
            wp_mkdir_p( $root );
        }
        $marker = $root . '/.sa-asterion';
        if ( ! file_exists( $marker ) ) {
            file_put_contents(
                $marker,
                "Asterion ledger root for SA-Orchestration plugin.\n"
                . "Canonical-at-write store. Runtime DB at {$wpdb->prefix}{$p}* is a cache.\n"
                . "If runtime != Asterion, runtime is the lie.\n"
            );
        }
    }
}