# Social media module: database proposal (slice 1)

For db-admin. Additive only: four new tables, no change to any existing table. Nothing here has been executed; datahandler-dev does not run DDL. The matching Eloquent models live in `src/DataHandler/Model/Object/Social/` and the repositories in `src/DataHandler/Model/Social/Eloquent/`.

Conventions follow `oauth_grant` / `oauth_upstream`: `id` auto-increment, `uuid` char(36) unique set by the UniqueModel observer (uuid v7), `created_at` / `updated_at` maintained by Eloquent, InnoDB, utf8mb4. Timestamps are DATETIME in UTC.

## social_member

One row per person the connector has seen. `principal` is the identity string the MCP endpoint derives from the bearer token (`key:<api_key_id>` for an OAuth grant, `user:<id>` for a first-party key). Nothing in this table comes from agent input.

```sql
CREATE TABLE `social_member` (
  `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid`         CHAR(36)        NOT NULL,
  `principal`    VARCHAR(64)     NOT NULL,
  `display_name` VARCHAR(120)    NULL,
  `last_seen_at` DATETIME        NULL,
  `created_at`   DATETIME        NULL,
  `updated_at`   DATETIME        NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_social_member_uuid` (`uuid`),
  UNIQUE KEY `uq_social_member_principal` (`principal`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

## social_account

A connected platform account. `credential_envelope` is the sealed credential (`Model\Social\SocialCipher`, HKDF-separated from the Heat Applied cipher); it is set to NULL on disconnect. `owner_member_id` never changes. The unique key on (`platform`, `external_id`) is deliberately absent: a disconnected row stays for history and the same platform account may be reconnected later, so uniqueness among live rows is enforced in code (`findByExternal` filters `disconnected_at IS NULL`). If db-admin prefers a DB guarantee, a generated column `live_key` = IF(disconnected_at IS NULL, CONCAT(platform,':',external_id), NULL) with a unique index achieves it (MySQL 5.7+).

```sql
CREATE TABLE `social_account` (
  `id`                    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid`                  CHAR(36)        NOT NULL,
  `platform`              VARCHAR(32)     NOT NULL,
  `external_id`           VARCHAR(191)    NOT NULL,
  `handle`                VARCHAR(191)    NOT NULL,
  `display_name`          VARCHAR(191)    NULL,
  `label`                 VARCHAR(80)     NULL,
  `owner_member_id`       BIGINT UNSIGNED NOT NULL,
  `credential_envelope`   TEXT            NULL,
  `credential_expires_at` DATETIME        NULL,
  `scope`                 TEXT            NULL,
  `health`                VARCHAR(32)     NOT NULL DEFAULT 'ok',
  `health_detail`         VARCHAR(255)    NULL,
  `connected_at`          DATETIME        NULL,
  `reconnected_at`        DATETIME        NULL,
  `disconnected_at`       DATETIME        NULL,
  `created_at`            DATETIME        NULL,
  `updated_at`            DATETIME        NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_social_account_uuid` (`uuid`),
  KEY `ix_social_account_owner` (`owner_member_id`, `disconnected_at`),
  KEY `ix_social_account_external` (`platform`, `external_id`, `disconnected_at`),
  CONSTRAINT `fk_social_account_owner` FOREIGN KEY (`owner_member_id`) REFERENCES `social_member` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

`scope` is a JSON array stored as TEXT (same treatment as `oauth_upstream.scope`).

## social_account_access

Editor / viewer access held by a member other than the owner. Rows are never deleted; `revoked_at` closes them. The code reads revoked rows to answer `ACCESS_REVOKED` instead of `ACCOUNT_NOT_FOUND` for a member who once had access.

```sql
CREATE TABLE `social_account_access` (
  `id`                   BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid`                 CHAR(36)        NOT NULL,
  `account_id`           BIGINT UNSIGNED NOT NULL,
  `member_id`            BIGINT UNSIGNED NOT NULL,
  `role`                 ENUM('editor','viewer') NOT NULL,
  `granted_by_member_id` BIGINT UNSIGNED NOT NULL,
  `invitation_id`        BIGINT UNSIGNED NULL,
  `revoked_at`           DATETIME        NULL,
  `revoked_by_member_id` BIGINT UNSIGNED NULL,
  `revoke_reason`        VARCHAR(500)    NULL,
  `created_at`           DATETIME        NULL,
  `updated_at`           DATETIME        NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_social_account_access_uuid` (`uuid`),
  KEY `ix_social_account_access_account` (`account_id`, `revoked_at`),
  KEY `ix_social_account_access_member` (`member_id`, `revoked_at`),
  CONSTRAINT `fk_social_account_access_account` FOREIGN KEY (`account_id`) REFERENCES `social_account` (`id`),
  CONSTRAINT `fk_social_account_access_member` FOREIGN KEY (`member_id`) REFERENCES `social_member` (`id`),
  CONSTRAINT `fk_social_account_access_granter` FOREIGN KEY (`granted_by_member_id`) REFERENCES `social_member` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

## social_invitation

One-time claim tokens. Only `token_hash` (sha256 hex of the token) is stored; `token_hint` is the first 10 characters so the owner can tell open invitations apart. The claim path locks the row (`SELECT ... FOR UPDATE`) inside a transaction and re-checks `claimed_at` / `revoked_at` before writing, so two concurrent claims cannot both succeed.

```sql
CREATE TABLE `social_invitation` (
  `id`                   BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid`                 CHAR(36)        NOT NULL,
  `account_id`           BIGINT UNSIGNED NOT NULL,
  `invited_by_member_id` BIGINT UNSIGNED NOT NULL,
  `role`                 ENUM('editor','viewer') NOT NULL,
  `note`                 VARCHAR(500)    NULL,
  `token_hash`           CHAR(64)        NOT NULL,
  `token_hint`           VARCHAR(16)     NOT NULL,
  `expires_at`           DATETIME        NOT NULL,
  `claimed_at`           DATETIME        NULL,
  `claimed_by_member_id` BIGINT UNSIGNED NULL,
  `revoked_at`           DATETIME        NULL,
  `created_at`           DATETIME        NULL,
  `updated_at`           DATETIME        NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_social_invitation_uuid` (`uuid`),
  UNIQUE KEY `uq_social_invitation_token` (`token_hash`),
  KEY `ix_social_invitation_account` (`account_id`, `claimed_at`, `revoked_at`, `expires_at`),
  CONSTRAINT `fk_social_invitation_account` FOREIGN KEY (`account_id`) REFERENCES `social_account` (`id`),
  CONSTRAINT `fk_social_invitation_inviter` FOREIGN KEY (`invited_by_member_id`) REFERENCES `social_member` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

## Order and rollback

Create in the order above (member, account, account_access, invitation). Rollback is `DROP TABLE` in reverse order; nothing else references these tables.

## Later slices (not requested yet)

Drafts, revisions, media, authorizations, schedules, occurrences, publications and attempts come with milestones 3 to 7 of the plan and will be proposed separately, each additive. No existing table will be altered by this module.