# Social media module: database proposal 3 (usage ledger)

For db-admin. Additive only: one new table, no change to any existing table. Nothing here has been executed; datahandler-dev does not run DDL. Model: `Model\Object\Social\ApiUsage`; written through `Model\Social\Usage\Meter`, read by `Model\Social\Service\UsageService`.

Same conventions as proposals 1 and 2: `id` auto-increment, `uuid` char(36) unique set by the UniqueModel observer, `created_at` / `updated_at` maintained by Eloquent, InnoDB, utf8mb4, DATETIME in UTC.

## social_api_usage

One row per outbound HTTP request the module sends to a platform (Facebook, Instagram, X): who it was for (`owner_member_id`, the billable party), who caused it (`actor_member_id`), which account and publication, how it was triggered (`origin`), what was called (`operation`, a path pattern with ids collapsed and no query string) and the outcome. Nothing in a row is a credential or user content. `billable` is 1 for platforms listed in `social.billablePlatforms` (X today).

Foreign keys are listed for clarity; omit them if preferred. The member and account references are left as plain columns on purpose: a ledger row must survive the account being disconnected, so no cascade.

```sql
CREATE TABLE `social_api_usage` (
  `id`              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid`            CHAR(36)        NOT NULL,
  `owner_member_id` BIGINT UNSIGNED NULL,
  `actor_member_id` BIGINT UNSIGNED NULL,
  `account_id`      BIGINT UNSIGNED NULL,
  `platform`        VARCHAR(32)     NULL,
  `origin`          VARCHAR(64)     NULL,
  `principal`       VARCHAR(64)     NULL,
  `request_id`      VARCHAR(64)     NULL,
  `publication_id`  BIGINT UNSIGNED NULL,
  `method`          VARCHAR(8)      NOT NULL,
  `operation`       VARCHAR(160)    NOT NULL,
  `http_status`     SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  `duration_ms`     INT UNSIGNED    NOT NULL DEFAULT 0,
  `bytes_out`       INT UNSIGNED    NOT NULL DEFAULT 0,
  `bytes_in`        INT UNSIGNED    NOT NULL DEFAULT 0,
  `error`           VARCHAR(255)    NULL,
  `billable`        TINYINT(1)      NOT NULL DEFAULT 0,
  `created_at`      DATETIME        NULL,
  `updated_at`      DATETIME        NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_social_api_usage_uuid` (`uuid`),
  KEY `ix_social_api_usage_owner_time` (`owner_member_id`, `created_at`),
  KEY `ix_social_api_usage_account_time` (`account_id`, `created_at`),
  KEY `ix_social_api_usage_time` (`created_at`),
  CONSTRAINT `fk_social_api_usage_owner` FOREIGN KEY (`owner_member_id`) REFERENCES `social_member` (`id`),
  CONSTRAINT `fk_social_api_usage_actor` FOREIGN KEY (`actor_member_id`) REFERENCES `social_member` (`id`),
  CONSTRAINT `fk_social_api_usage_account` FOREIGN KEY (`account_id`) REFERENCES `social_account` (`id`),
  CONSTRAINT `fk_social_api_usage_publication` FOREIGN KEY (`publication_id`) REFERENCES `social_publication` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

## Growth and retention

Append-only. Volume is one row per platform call: a publish to one account is one to three rows (media upload, create, occasionally a token refresh), a read-back is one. At a few hundred posts a day this is well under a million rows a year. Billing needs the current and previous month; a yearly archive of rows older than 13 months is the first housekeeping to add.

## Reads

`UsageService` selects by `owner_member_id` (+ `account_id` when filtered) + `created_at BETWEEN from AND to` (owner summary) or `created_at BETWEEN from AND to` alone (operator report) and aggregates in PHP. Both are covered by the indexes above. If the operator report gets slow, the next step is a `GROUP BY owner_member_id, platform` query in the Eloquent table, not a schema change.

## Until the table exists

With `social.enabled` on and this table missing, every platform call still goes through: the ledger insert throws, `Meter::record` catches it and writes one line to the PHP error log per call. So the only cost of deploying the code before the DDL is log noise, not lost posts. Usage reads (`social_get_usage`, the report) fail with a database error until the table exists.