# Social media module: database proposal 2 (slice 2)

For db-admin. Additive only: eleven new tables, no change to any existing table, including the four slice-1 tables (`social_member`, `social_account`, `social_account_access`, `social_invitation`, created 2026-10-13). Nothing here has been executed; datahandler-dev does not run DDL. Eloquent models are in `src/DataHandler/Model/Object/Social/`, each with a `$table`, `$fillable`, `$dates` and `JSON_COLUMNS` that match the columns below; the generic repository is `Model\Social\Eloquent\Table`.

Same conventions as proposal 1: `id` auto-increment, `uuid` char(36) unique set by the UniqueModel observer (uuid v7), `created_at` / `updated_at` maintained by Eloquent, InnoDB, utf8mb4. DATETIME columns are UTC; the code converts to and from unix timestamps. JSON columns are TEXT holding a JSON document (same as `oauth_upstream.scope`). Foreign keys are listed so the relationships are explicit; if db-admin prefers to omit them for operational reasons the code does not depend on them (every delete is a soft delete, and the cross-table checks are done in code).

Suggested creation order (dependencies): grant_binding, media, post, post_revision, authorization, schedule, occurrence, publication, publication_attempt, connection_intent, recovery_code. `social_post.last_publication_id` and `social_occurrence.publication_id` reference tables created later, so those two FKs are added at the end with ALTERs, or left off.

## social_grant_binding

Maps a connector principal (`key:<api_key_id>` / `user:<id>`) to a member. Normal sessions still resolve through `social_member.principal`; a binding is consulted first so that a recovery code can re-point a fresh grant at an existing member. Revoked bindings stay for audit.

```sql
CREATE TABLE `social_grant_binding` (
  `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid`       CHAR(36)        NOT NULL,
  `principal`  VARCHAR(64)     NOT NULL,
  `member_id`  BIGINT UNSIGNED NOT NULL,
  `revoked_at` DATETIME        NULL,
  `created_at` DATETIME        NULL,
  `updated_at` DATETIME        NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_social_grant_binding_uuid` (`uuid`),
  KEY `ix_social_grant_binding_principal` (`principal`, `revoked_at`),
  CONSTRAINT `fk_social_grant_binding_member` FOREIGN KEY (`member_id`) REFERENCES `social_member` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

## social_media

An uploaded image or video. Bytes live in S3 (`storage_key`, `public_url`), never in the database. Soft delete.

```sql
CREATE TABLE `social_media` (
  `id`              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid`            CHAR(36)        NOT NULL,
  `owner_member_id` BIGINT UNSIGNED NOT NULL,
  `name`            VARCHAR(120)    NOT NULL,
  `mime_type`       VARCHAR(64)     NOT NULL,
  `size_bytes`      INT UNSIGNED    NOT NULL,
  `sha256`          CHAR(64)        NOT NULL,
  `width`           INT UNSIGNED    NULL,
  `height`          INT UNSIGNED    NULL,
  `storage_key`     VARCHAR(255)    NOT NULL,
  `public_url`      VARCHAR(512)    NOT NULL,
  `deleted_at`      DATETIME        NULL,
  `created_at`      DATETIME        NULL,
  `updated_at`      DATETIME        NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_social_media_uuid` (`uuid`),
  KEY `ix_social_media_owner` (`owner_member_id`, `deleted_at`),
  CONSTRAINT `fk_social_media_owner` FOREIGN KEY (`owner_member_id`) REFERENCES `social_member` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

## social_post

A post and its destinations. `account_ids` is a JSON array of `social_account.id`. The content lives in revisions; `current_revision` points at the one in force. `status` is draft, scheduled, published or archived.

```sql
CREATE TABLE `social_post` (
  `id`                   BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid`                 CHAR(36)        NOT NULL,
  `created_by_member_id` BIGINT UNSIGNED NOT NULL,
  `account_ids`          TEXT            NOT NULL,
  `title`                VARCHAR(120)    NULL,
  `status`               VARCHAR(16)     NOT NULL DEFAULT 'draft',
  `current_revision`     INT UNSIGNED    NOT NULL DEFAULT 1,
  `last_publication_id`  BIGINT UNSIGNED NULL,
  `archived_at`          DATETIME        NULL,
  `created_at`           DATETIME        NULL,
  `updated_at`           DATETIME        NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_social_post_uuid` (`uuid`),
  KEY `ix_social_post_status` (`status`, `id`),
  CONSTRAINT `fk_social_post_creator` FOREIGN KEY (`created_by_member_id`) REFERENCES `social_member` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

## social_post_revision

Immutable. One row per edit; rows are never updated or deleted. `media_ids` is a JSON array of `social_media.id`, `options` a JSON object of platform options.

```sql
CREATE TABLE `social_post_revision` (
  `id`                   BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid`                 CHAR(36)        NOT NULL,
  `post_id`              BIGINT UNSIGNED NOT NULL,
  `revision`             INT UNSIGNED    NOT NULL,
  `text`                 TEXT            NOT NULL,
  `media_ids`            TEXT            NOT NULL,
  `link`                 VARCHAR(2000)   NULL,
  `options`              TEXT            NOT NULL,
  `note`                 VARCHAR(500)    NULL,
  `created_by_member_id` BIGINT UNSIGNED NOT NULL,
  `created_at`           DATETIME        NULL,
  `updated_at`           DATETIME        NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_social_post_revision_uuid` (`uuid`),
  UNIQUE KEY `uq_social_post_revision_n` (`post_id`, `revision`),
  CONSTRAINT `fk_social_post_revision_post` FOREIGN KEY (`post_id`) REFERENCES `social_post` (`id`),
  CONSTRAINT `fk_social_post_revision_member` FOREIGN KEY (`created_by_member_id`) REFERENCES `social_member` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

## social_authorization

An owner's permission to publish on an account without the owner present. `kind` is post (one post) or standing (any post). `post_id` is set for kind post. `subject_member_id` NULL means anyone with editor access. `evidence` is a JSON object: the tool, principal and request id of the granting call. Revocation keeps the row.

```sql
CREATE TABLE `social_authorization` (
  `id`                   BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid`                 CHAR(36)        NOT NULL,
  `account_id`           BIGINT UNSIGNED NOT NULL,
  `granted_by_member_id` BIGINT UNSIGNED NOT NULL,
  `kind`                 VARCHAR(16)     NOT NULL,
  `post_id`              BIGINT UNSIGNED NULL,
  `subject_member_id`    BIGINT UNSIGNED NULL,
  `valid_from`           DATETIME        NULL,
  `valid_until`          DATETIME        NULL,
  `max_uses`             INT UNSIGNED    NULL,
  `uses`                 INT UNSIGNED    NOT NULL DEFAULT 0,
  `evidence`             TEXT            NULL,
  `revoked_at`           DATETIME        NULL,
  `revoked_by_member_id` BIGINT UNSIGNED NULL,
  `created_at`           DATETIME        NULL,
  `updated_at`           DATETIME        NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_social_authorization_uuid` (`uuid`),
  KEY `ix_social_authorization_account` (`account_id`, `revoked_at`),
  CONSTRAINT `fk_social_authorization_account` FOREIGN KEY (`account_id`) REFERENCES `social_account` (`id`),
  CONSTRAINT `fk_social_authorization_granter` FOREIGN KEY (`granted_by_member_id`) REFERENCES `social_member` (`id`),
  CONSTRAINT `fk_social_authorization_post` FOREIGN KEY (`post_id`) REFERENCES `social_post` (`id`),
  CONSTRAINT `fk_social_authorization_subject` FOREIGN KEY (`subject_member_id`) REFERENCES `social_member` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

## social_schedule

A recurrence rule plus the authorizations it relies on. Local times are stored as the user typed them (`YYYY-MM-DD HH:MM` in `timezone`) and converted at materialization, so a DST change does not move the wall-clock time. `next_local` is the cursor for the next occurrence to generate; NULL once the rule is exhausted. JSON columns: `account_ids`, `series_post_ids`, `authorizations` (ids), `grants` (evidence per account), `weekdays`.

```sql
CREATE TABLE `social_schedule` (
  `id`                   BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid`                 CHAR(36)        NOT NULL,
  `created_by_member_id` BIGINT UNSIGNED NOT NULL,
  `account_ids`          TEXT            NOT NULL,
  `post_id`              BIGINT UNSIGNED NULL,
  `series_post_ids`      TEXT            NULL,
  `brief`                TEXT            NULL,
  `authorizations`       TEXT            NULL,
  `grants`               TEXT            NULL,
  `timezone`             VARCHAR(64)     NOT NULL DEFAULT 'UTC',
  `start_local`          VARCHAR(16)     NOT NULL,
  `frequency`            VARCHAR(8)      NOT NULL,
  `interval_n`           INT UNSIGNED    NOT NULL DEFAULT 1,
  `weekdays`             VARCHAR(32)     NULL,
  `until_local`          VARCHAR(16)     NULL,
  `max_occurrences`      INT UNSIGNED    NULL,
  `generated_count`      INT UNSIGNED    NOT NULL DEFAULT 0,
  `next_local`           VARCHAR(16)     NULL,
  `paused_at`            DATETIME        NULL,
  `cancelled_at`         DATETIME        NULL,
  `created_at`           DATETIME        NULL,
  `updated_at`           DATETIME        NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_social_schedule_uuid` (`uuid`),
  KEY `ix_social_schedule_tick` (`cancelled_at`, `paused_at`, `next_local`),
  CONSTRAINT `fk_social_schedule_creator` FOREIGN KEY (`created_by_member_id`) REFERENCES `social_member` (`id`),
  CONSTRAINT `fk_social_schedule_post` FOREIGN KEY (`post_id`) REFERENCES `social_post` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

## social_occurrence

One calendar slot. `status`: ready, needs_content, publishing, published, partial, failed, unknown, blocked, missed, skipped, cancelled. The cron tick selects `status IN ('ready','needs_content') AND scheduled_at <= now` every minute; the calendar tool selects by `scheduled_at` range.

```sql
CREATE TABLE `social_occurrence` (
  `id`             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid`           CHAR(36)        NOT NULL,
  `schedule_id`    BIGINT UNSIGNED NOT NULL,
  `sequence`       INT UNSIGNED    NOT NULL,
  `scheduled_at`   DATETIME        NOT NULL,
  `post_id`        BIGINT UNSIGNED NULL,
  `status`         VARCHAR(16)     NOT NULL,
  `publication_id` BIGINT UNSIGNED NULL,
  `note`           VARCHAR(255)    NULL,
  `created_at`     DATETIME        NULL,
  `updated_at`     DATETIME        NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_social_occurrence_uuid` (`uuid`),
  UNIQUE KEY `uq_social_occurrence_seq` (`schedule_id`, `sequence`),
  KEY `ix_social_occurrence_due` (`status`, `scheduled_at`),
  KEY `ix_social_occurrence_post` (`post_id`, `status`),
  CONSTRAINT `fk_social_occurrence_schedule` FOREIGN KEY (`schedule_id`) REFERENCES `social_schedule` (`id`),
  CONSTRAINT `fk_social_occurrence_post` FOREIGN KEY (`post_id`) REFERENCES `social_post` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

## social_publication

One publish instruction for one revision. `trigger_kind` manual or schedule. `status`: pending, in_progress, published, partial, failed, unknown. `evidence` JSON: the basis per account (owner instruction or authorization id), the tool and request id, and later edits or deletes of the live post.

```sql
CREATE TABLE `social_publication` (
  `id`                     BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid`                   CHAR(36)        NOT NULL,
  `post_id`                BIGINT UNSIGNED NOT NULL,
  `revision`               INT UNSIGNED    NOT NULL,
  `occurrence_id`          BIGINT UNSIGNED NULL,
  `requested_by_member_id` BIGINT UNSIGNED NOT NULL,
  `trigger_kind`           VARCHAR(16)     NOT NULL,
  `status`                 VARCHAR(16)     NOT NULL DEFAULT 'pending',
  `evidence`               TEXT            NULL,
  `completed_at`           DATETIME        NULL,
  `created_at`             DATETIME        NULL,
  `updated_at`             DATETIME        NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_social_publication_uuid` (`uuid`),
  KEY `ix_social_publication_post` (`post_id`, `id`),
  CONSTRAINT `fk_social_publication_post` FOREIGN KEY (`post_id`) REFERENCES `social_post` (`id`),
  CONSTRAINT `fk_social_publication_occurrence` FOREIGN KEY (`occurrence_id`) REFERENCES `social_occurrence` (`id`),
  CONSTRAINT `fk_social_publication_member` FOREIGN KEY (`requested_by_member_id`) REFERENCES `social_member` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

## social_publication_attempt

One row per delivery try per destination account. A retry inserts a new row with `attempt + 1`; the latest row per account is the current state. `status`: pending, running, succeeded, retry, failed, unknown, deleted. `external_id` / `external_url` are the platform's post id and link.

```sql
CREATE TABLE `social_publication_attempt` (
  `id`              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid`            CHAR(36)        NOT NULL,
  `publication_id`  BIGINT UNSIGNED NOT NULL,
  `account_id`      BIGINT UNSIGNED NOT NULL,
  `attempt`         INT UNSIGNED    NOT NULL DEFAULT 1,
  `status`          VARCHAR(16)     NOT NULL DEFAULT 'pending',
  `external_id`     VARCHAR(191)    NULL,
  `external_url`    VARCHAR(512)    NULL,
  `error_code`      VARCHAR(48)     NULL,
  `error_message`   VARCHAR(1000)   NULL,
  `started_at`      DATETIME        NULL,
  `finished_at`     DATETIME        NULL,
  `next_attempt_at` DATETIME        NULL,
  `created_at`      DATETIME        NULL,
  `updated_at`      DATETIME        NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_social_publication_attempt_uuid` (`uuid`),
  UNIQUE KEY `uq_social_publication_attempt_n` (`publication_id`, `account_id`, `attempt`),
  KEY `ix_social_publication_attempt_status` (`publication_id`, `status`),
  CONSTRAINT `fk_social_publication_attempt_pub` FOREIGN KEY (`publication_id`) REFERENCES `social_publication` (`id`),
  CONSTRAINT `fk_social_publication_attempt_account` FOREIGN KEY (`account_id`) REFERENCES `social_account` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

## social_connection_intent

A browser OAuth connection in flight. `state_hash` is sha256 of the state parameter; `pkce_envelope` is the sealed PKCE verifier (and label); `credential_envelope` holds the sealed platform tokens between the browser callback and `social_complete_connect`, then is set to NULL. `accounts` is the public list the platform offered (external_id, handle, display name). `status`: pending, authorized, completed, failed, expired. Rows are short-lived (30 minutes to complete) and can be purged after a week.

```sql
CREATE TABLE `social_connection_intent` (
  `id`                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid`                CHAR(36)        NOT NULL,
  `platform`            VARCHAR(32)     NOT NULL,
  `member_id`           BIGINT UNSIGNED NOT NULL,
  `state_hash`          CHAR(64)        NOT NULL,
  `pkce_envelope`       TEXT            NOT NULL,
  `status`              VARCHAR(16)     NOT NULL DEFAULT 'pending',
  `credential_envelope` TEXT            NULL,
  `accounts`            TEXT            NULL,
  `error`               VARCHAR(500)    NULL,
  `expires_at`          DATETIME        NOT NULL,
  `completed_at`        DATETIME        NULL,
  `created_at`          DATETIME        NULL,
  `updated_at`          DATETIME        NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_social_connection_intent_uuid` (`uuid`),
  UNIQUE KEY `uq_social_connection_intent_state` (`state_hash`),
  KEY `ix_social_connection_intent_member` (`member_id`, `status`, `id`),
  CONSTRAINT `fk_social_connection_intent_member` FOREIGN KEY (`member_id`) REFERENCES `social_member` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

## social_recovery_code

A one-time code that lets a fresh connector session bind to an existing member. Only the hash is stored; `token_hint` is the first few characters for support. Valid 72 hours, single use.

```sql
CREATE TABLE `social_recovery_code` (
  `id`                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid`              CHAR(36)        NOT NULL,
  `member_id`         BIGINT UNSIGNED NOT NULL,
  `token_hash`        CHAR(64)        NOT NULL,
  `token_hint`        VARCHAR(16)     NOT NULL,
  `expires_at`        DATETIME        NOT NULL,
  `claimed_at`        DATETIME        NULL,
  `claimed_principal` VARCHAR(64)     NULL,
  `created_at`        DATETIME        NULL,
  `updated_at`        DATETIME        NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_social_recovery_code_uuid` (`uuid`),
  UNIQUE KEY `uq_social_recovery_code_hash` (`token_hash`),
  CONSTRAINT `fk_social_recovery_code_member` FOREIGN KEY (`member_id`) REFERENCES `social_member` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

## Deferred foreign keys

```sql
ALTER TABLE `social_post`
  ADD CONSTRAINT `fk_social_post_last_publication` FOREIGN KEY (`last_publication_id`) REFERENCES `social_publication` (`id`);
ALTER TABLE `social_occurrence`
  ADD CONSTRAINT `fk_social_occurrence_publication` FOREIGN KEY (`publication_id`) REFERENCES `social_publication` (`id`);
```

## Growth and retention

- `social_post_revision` and `social_publication_attempt` are append-only and grow with use; both are small rows. No purge is planned; a yearly archive of attempts older than 12 months would be the first thing to add.
- `social_connection_intent` rows can be deleted once `completed_at` (or `expires_at`) is more than 7 days old. There is no cron for that yet; it is a candidate for a later housekeeping tick.
- Nothing in these tables comes from agent input unescaped: text and options are stored as given but only ever rendered through the platform providers and the JSON tool results.

## What the code does until the tables exist

`social.enabled` stays false on live, so none of these tables is read. The test suite (`php index.php --test=McpSocialTest -V`, 502 checks) runs over in-memory tables and never touches the database.