roryqi commented on code in PR #13386:
URL: https://github.com/apache/gravitino/pull/13386#discussion_r4219217186


##########
design-docs/table-maintenance-service-design.md:
##########
@@ -0,0 +1,663 @@
+<!--
+  Licensed to the Apache Software Foundation (ASF) under one
+  or more contributor license agreements.  See the NOTICE file
+  distributed with this work for additional information
+  regarding copyright ownership.  The ASF licenses this file
+  to you under the Apache License, Version 2.0 (the
+  "License"); you may not use this file except in compliance
+  with the License.  You may obtain a copy of the License at
+
+   http://www.apache.org/licenses/LICENSE-2.0
+
+  Unless required by applicable law or agreed to in writing,
+  software distributed under the License is distributed on an
+  "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+  KIND, either express or implied.  See the License for the
+  specific language governing permissions and limitations
+  under the License.
+-->
+
+# Design of Table Maintenance Service in Gravitino
+
+## 1. Background
+
+The Table Maintenance Service (TMS) is an alpha feature. The 
`maintenance/optimizer` package already
+has statistics, rule evaluation, strategy recommendation, and job submission 
(`Updater`,
+`Recommender`, providers, `JobSubmitter`), but that core is not hosted as a 
long-running Gravitino
+service. Gaps today:
+
+1. No stable **server-side commit event** after Iceberg commits through IRC.
+2. Hard to centralize configuration, audit, and service metrics for ad hoc 
runs.
+3. No **embedded scheduler** for multi-node safe expand / submit.
+
+This design loads TMS as a **main-server REST plugin** on port **8090**
+(`gravitino.server.rest.extensionPackages`, same pattern as IdP) so colocated 
IRC can wake
+maintenance after commits.
+
+---
+
+## 2. Goals
+
+1. **In-process plugin** on the main server (Jersey 2 `Feature`). Commit path 
does **not** use HTTP.
+2. **IRC in-process commit event**: after successful Iceberg commits, enqueue 
commit-triggered
+   maintenance (`task_instance = {table_id}`); do **not** `runJob` on the IRC 
thread (§5.2).
+3. **Reuse optimizer core** in-process (`Updater` / `Recommender` / job 
submit).
+4. **Job framework**: Spark work stays in Gravitino Jobs; TMS records runs in
+   `table_maintenance_job` (Validation + submit gates).
+5. **Govern Policy reuse**: existing `policy_meta` / Policy APIs; no parallel 
policy store.
+6. **Multi-node safe**: db-scheduler pick + heartbeat; in-flight gated by
+   `table_maintenance_job.finished_at IS NULL`. Expand / cron / commit task 
kinds and pools are
+   defined in §5.2 — not restated here.
+7. **Policy triggers + jobOptions** in `policy_version_info.content` 
(`schedule`, `jobOptions` /
+   `rewriteOptions`); nearest attachment wins (§5.1). Non-sensitive IRC/Spark 
client props (e.g.
+   `uri`, `type`, `io-impl`) live in **`jobOptions`**.
+8. **Cluster-static client auth** in **`gravitino.conf`** 
(`gravitino.maintenance.gravitinoAuth.*`
+   / `gravitino.maintenance.ircAuth.*`, §7.4): one Gravitino identity and one 
IRC client auth for
+   the service — not per-policy and not a DB table.
+
+---
+
+## 3. Non-Goals
+
+1. Standalone maintenance daemon or dedicated aux Jetty port (e.g. **9301**).
+2. Rewrite of optimizer provider / submitter SPIs.
+3. Engine-side commit report when engines bypass Gravitino IRC.
+4. Commit-path HTTP or Kafka (`POST …/events/iceberg-commit`, health resource).
+5. Auto-create a built-in TMS user / role. Operators grant privileges when 
authorization is on (§6).
+
+---
+
+## 4. Solution Investigations
+
+### 4.1 Deployment shape
+
+| Option                        | Decision                                     
          |
+| ----------------------------- | 
------------------------------------------------------ |
+| Process-local only            | Rejected — no IRC wake / multi-node 
coordination       |
+| **In-process plugin on 8090** | **Chosen** — same JVM as IRC; 
`extensionPackages`      |
+| Separate long-running process | Rejected — extra deployable                  
          |
+| Aux Jetty listener (`:9301`)  | Rejected — extra port; diverges from 
IdP-style plugins |
+
+### 4.2 Scheduler
+
+TMS needs cluster-safe scheduling for **three** kinds of work (policy→Spark / 
cron Spark /
+commit-triggered Spark) plus durable **`table_maintenance_job`** for 
Validation and in-flight
+occupancy.
+
+|                                  | **db-scheduler** (Chosen) | JobRunr | 
ShedLock   | Quartz JDBC |
+| -------------------------------- | ------------------------- | ------- | 
---------- | ----------- |
+| License                          | Apache 2.0                | LGPL v3 | 
Apache 2.0 | Apache 2.0  |
+| Cluster CAS + heartbeat          | Yes                       | Yes     | 
Lock only  | Yes (heavy) |
+| Fits long-lived 1 + one-shot 2/3 | Yes                       | Yes     | No  
       | Yes         |
+
+**Chosen:** db-scheduler (Apache 2.0, one `scheduled_tasks` table, built-in 
heartbeat). Three pools:
+expand / cron / commit. Reject JobRunr (license), ShedLock (not a scheduler), 
Quartz (heavy).
+
+### 4.3 Where Job configuration lives
+
+| Concern                     | Rejected                                       
   | **Chosen**                                            |
+| --------------------------- | 
------------------------------------------------- | 
----------------------------------------------------- |
+| Non-auth Spark / IRC params | Extra options table keyed by 
catalog/schema/table | Policy content `jobOptions` / `rewriteOptions` (§5.1) |
+| Client auth binding         | Per-policy named auth configs in policy 
content   | Cluster-static keys in `gravitino.conf` (§7.4)        |
+
+---
+
+## 5. Proposal
+
+TMS is an in-process plugin. Policy content defines **what** and **when**; 
db-scheduler defines
+**who runs which unit** under multi-node pick. Writing `scheduled_tasks` does 
**not** mean every
+node executes — pools poll; **N compete, one pick wins**.
+
+| Table / store                                                  | Role        
                                                        |
+| -------------------------------------------------------------- | 
------------------------------------------------------------------- |
+| `policy_meta` / `policy_relation_meta` / `policy_version_info` | Triggers + 
`jobOptions` / `rewriteOptions` (§5.1)                   |
+| `scheduled_tasks`                                              | ① 
`policy-expand` + ② `cron-job` + ③ `commit-job` (§5.2)            |
+| `table_maintenance_job`                                        | Per-run 
occupancy + before/after `snapshot_id`; submit gates (§5.2) |
+| `table_snapshot_statistic`                                     | Stats keyed 
by Iceberg `snapshot_id` (§5.2.6)                       |
+| `gravitino.conf`                                               | 
Cluster-static Gravitino / IRC client auth (§7.4)                   |
+| `job_run_meta`                                                 | Spark job 
run record / `runtime_job_template` snapshot              |
+
+```text
+Create / enable policy (policy_id = P)
+  → INSERT ① policy-expand / {policy_id}
+     execution_time = next crontab (or far-future if onCommit-only)
+
+crontab due → pick ① (expand pool)
+  → page candidates → gates → INSERT ② table:{table_id}:{policy_id}
+  → retain ①; set next crontab due
+
+IRC commit → upsert ③ commit-job / {table_id}  (IRC thread; not pools)
+
+pick ② (cron pool):
+  → gate → sample once at S0 → Recommender → one-type runJob if needed → 
DELETE ②
+pick ③ (commit pool):
+  → gate → resolve onCommit types (§5.1.2) → sample each at S0 → Recommender 
per type
+  → one Spark Job chains remaining types in order → DELETE ③
+  → Job terminal: sample S1 per policy in the chain → after_snapshot_id=S1, 
finished_at
+     (no re-upsert of ③ for “next type”; new IRC commits may upsert ③ again)
+```
+
+### 5.1 Policy
+
+Maintenance policies stay on existing metalake Policy APIs. TMS does **not** 
add
+`/api/maintenance/table/policies` CRUD. One policy row can enable both 
evaluate triggers; use
+**two policies** only when maintenance **types** differ.
+
+#### 5.1.1 Evaluate triggers (`onCommit` + `crontab`)
+
+Stored in **`policy_version_info.content.schedule`** (not `policy_meta` 
columns, not
+`scheduled_tasks`).
+
+| Trigger    | Meaning                                                       | 
Notes                                                |
+| ---------- | ------------------------------------------------------------- | 
---------------------------------------------------- |
+| `onCommit` | After IRC commit, one Spark Job chains ordered types (§5.1.2) | 
Compaction / manifest-rewrite / snapshot-expiry only |
+| `crontab`  | Periodic expand on a crontab expression                       | 
Required for orphan-cleanup                          |
+
+`crontab` drives **when ① expand runs**. `onCommit` goes through **③**, not ①.
+`minIntervalMs` (§7) caps how soon another enqueue/submit may proceed after
+`MAX(finished_at)` on `table_maintenance_job` for that `(table, policy)`.
+
+**`orphan-cleanup` must not use `onCommit`.** Policy create/alter rejects
+`schedule.onCommit = true` for orphan-cleanup. Orphan cleanup is 
**crontab-only**.
+
+Illustrative content:
+
+```json
+{
+  "rewriteOptions": { "target-file-size-bytes": "536870912" },
+  "jobOptions": {
+    "spark.executor.memory": "7g",
+    "uri": "http://host:9001/iceberg";,
+    "type": "rest"
+  },
+  "schedule": { "onCommit": true, "crontab": "0 2 * * *" }
+}
+```
+
+| Field               | TMS runtime                                            
                    |
+| ------------------- | 
-------------------------------------------------------------------------- |
+| `schedule.onCommit` | IRC upserts ③; commit pool submits one Job that chains 
§5.1.2 order in-Job |
+| `schedule.crontab`  | Sets / refreshes ① next 
`scheduled_tasks.execution_time`                   |
+| `jobOptions`        | Non-sensitive Spark / IRC client props (`uri`, `type`, 
memory, …)          |
+
+At least one of `onCommit` or `crontab` should be set for automated 
maintenance (except
+orphan-cleanup: **`crontab` only**).
+
+**Policy lifecycle → ①:** create/enable → INSERT ①; alter schedule → update ① 
`execution_time`;
+disable/drop → DELETE ① and outstanding ② for that `policy_id`.
+
+#### 5.1.2 Commit-driven type order
+
+**`onCommit` allows only** `compaction` → `manifest-rewrite` → 
`snapshot-expiry` (strict order).
+**`orphan-cleanup` is not allowed** on `onCommit`.
+
+On the commit path for a table, TMS takes the Active, attached, 
`onCommit`-enabled subset and
+submits **one** Spark Job that runs those types **in that fixed order inside 
the same Job**
+(skip missing types or types the Recommender skips; never reorder). Control 
plane does **not**
+submit one Job per type and does **not** re-upsert ③ after a step for the 
“next type”.
+
+That single Job’s Spark resources come from merging the chain’s policies’ 
`jobOptions`: conflicting
+numeric resource settings (e.g. `spark.executor.memory`) use the **maximum**. 
Per-type
+`rewriteOptions` / thresholds stay with each step. `minIntervalMs` is **not** 
part of this merge
+(§7.3).
+
+`commit-job` is one row per `{table_id}` (IRC bursts coalesce). One pick → at 
most one chained
+`runJob` → DELETE ③. A later IRC commit may upsert ③ again for a new round.
+
+Crontab remains per-policy expand (① per `policy_id`) and one type per ② Job — 
it does not use
+this in-Job chain.
+
+#### 5.1.3 Job template parameters (`jobOptions`)
+
+Non-sensitive Spark / strategy / IRC **client** options live in 
**`policy_version_info.content`**
+alongside `schedule` as an inline map. Examples: `spark.executor.memory`, 
`rewriteOptions`, Iceberg
+REST `uri` / `type` / `warehouse` / `prefix` / `io-impl`. They are **not** 
`policy_meta` columns and
+TMS does **not** add a separate options table.
+
+**Precedence** (first hit wins), matching policy attachment:
+
+```text
+table-attached > schema-attached > catalog-attached
+```
+
+Policy create/alter **rejects** credential-shaped keys in `jobOptions` / 
`rewriteOptions`
+(`password`, `secret`, `token`, `access-key`, …). Client auth is 
cluster-static in
+`gravitino.conf` (§7.4), not in policy content. Credential-vending props stay 
on the catalog /
+IRC server side.
+
+`job_run_meta.runtime_job_template` is a **run snapshot** only — not the 
default config store.
+
+#### 5.1.4 Client auth (cluster config)
+
+TMS uses **one** Gravitino client identity and **one** Iceberg REST client 
auth for the whole
+service. There is no per-policy auth switch — operators set
+`gravitino.maintenance.gravitinoAuth.*` and `gravitino.maintenance.ircAuth.*` 
in `gravitino.conf`
+(§7.4). Restart (or conf reload, if supported) applies changes.
+
+Same split as today’s maintenance / optimizer CLI (`--updater-options` vs 
Spark / IRC job conf).
+Each bag is **non-sensitive props ⊕ auth overlay** — auth alone does not fill 
the bag:
+
+| Bag                 | Non-sensitive (examples)                               
       | Auth overlay (from `gravitino.conf`)    |
+| ------------------- | 
------------------------------------------------------------- | 
--------------------------------------- |
+| **`updateOptions`** | `gravitino_uri`, `metalake`, updater impl names, …     
       | `gravitino.maintenance.gravitinoAuth.*` |
+| **`jobOptions`**    | nearest policy `jobOptions` (`uri`, `type`, Spark 
resources…) | `gravitino.maintenance.ircAuth.*`       |
+
+TMS control plane also uses `gravitinoAuth.*` directly (expand / submit / 
RBAC), not only via
+`updateOptions`.
+
+**Submit overlay:**
+
+```text
+control plane: gravitinoAuth.* → authenticate Gravitino client → RBAC from 
token → runJob
+runJob payload:
+  updateOptions ← cluster non-sensitive updater props (gravitino_uri, 
metalake, …)
+                  ⊕ gravitino.maintenance.gravitinoAuth.*
+  jobOptions    ← nearest policy jobOptions (uri, type, Spark resources, …)
+                  ⊕ gravitino.maintenance.ircAuth.*
+```
+
+### 5.2 Scheduling and commit
+
+#### 5.2.1 Three tasks and three pools
+
+| Kind                     | `task_name`     | When created           | 
Instance key                                                     | After pick   
                       |
+| ------------------------ | --------------- | ---------------------- | 
---------------------------------------------------------------- | 
----------------------------------- |
+| ① Policy → Spark         | `policy-expand` | Policy create / enable | 
`{policy_id}`                                                    | Page → 
INSERT ②; **keep** ①         |
+| ② Cron Spark             | `cron-job`      | Written by ①           | 
`table:{table_id}:{policy_id}` or `batch:{batch_id}:{policy_id}` | Short submit 
→ job row → **DELETE** |
+| ③ Commit-triggered Spark | `commit-job`    | IRC upsert             | 
`{table_id}`                                                     | One chained 
submit → **DELETE**     |
+
+| Pool   | Registers | Default threads | conf key (`gravitino.maintenance.`) |
+| ------ | --------- | --------------- | ----------------------------------- |
+| Expand | ① only    | 4               | `scheduler.expand.threads`          |
+| Cron   | ② only    | 8               | `scheduler.cron.threads`            |
+| Commit | ③ only    | 4               | `scheduler.commit.threads`          |
+
+**Uniqueness:** PK `(task_name, task_instance)`. Commit bursts coalesce on
+`(commit-job, {table_id})`. Prefixes `table:` / `batch:` avoid id ambiguity.
+
+**Expand paging:** each ① pick inserts at most `expand.enqueueBatchSize` 
(default **100**) ② rows;
+cursor in ① `task_data` while paging, then cleared. Cron ②: compaction / 
manifest-rewrite /
+orphan-cleanup stay one table per job; **snapshot-expiry** may batch
+(`batch:{batch_id}:{policy_id}`). Commit ③: one table, one Job, up to three 
types chained in-Job
+(§5.1.2).
+
+```text
+Spark / Flink / Trino → IRC (:9001) commit
+        → EventListener (IcebergUpdateTableEvent) → upsert ③ {table_id}
+
+Node A / B / C — three pools poll; N compete, one pick
+        ├─ expand: pick ① → INSERT ② → retain ①
+        ├─ cron:   pick ② → one-type runJob → INSERT job → DELETE ②
+        └─ commit: pick ③ → resolve chain → one chained runJob → DELETE ③
+```
+
+#### 5.2.2 In-process commit event
+
+| Requirement | Detail                                                         
                               |
+| ----------- | 
---------------------------------------------------------------------------------------------
 |
+| Deployment  | IRC and main server share **one JVM**                          
                               |
+| Transport   | IRC `EventListenerPlugin` on successful 
`IcebergUpdateTableEvent` — **no** HTTP, **no** Kafka |
+| Enqueue     | Listener upserts `(commit-job, {table_id})` (must stay short)  
                               |
+| Execute     | **`commit.threads`** picks ③; resolve + gate + short submit    
                               |
+
+Register the listener with `gravitino.eventListener.names` / `{name}.class` 
(§7.1). The handler
+must only enqueue ③ — it does **not** call `runJob`, does **not** use 
expand/cron/commit pools, and
+does **not** build the onCommit type chain (that happens when the commit pool 
picks ③).
+
+#### 5.2.3 Execute path
+
+```text
+① due (expand.threads):
+   load policy → page candidates → expand gates → INSERT ② → retain ① / next 
crontab
+
+② due (cron.threads):
+   in-flight re-check
+   → S0 = currentSnapshotId; sample once → UPSERT table_snapshot_statistic (…, 
policy_id, S0)
+   → Recommender (S0 + that policy)
+   → if go: updateOptions←updater props⊕gravitinoAuth; 
jobOptions←policy⊕ircAuth (§7.4) → runJob
+            → INSERT table_maintenance_job (policy_id, before_snapshot_id=S0, 
finished_at=NULL)
+   → else: no runJob
+   → DELETE ② → return
+
+③ due (commit.threads):
+   in-flight re-check (any policy in the prospective chain)
+   → resolve Active onCommit types in order (§5.1.2)
+   → S0 = currentSnapshotId; sample once per policy in the chain → UPSERT stats
+   → Recommender per type → drop skipped types; if chain empty → DELETE ③ → 
return
+   → updateOptions←updater props⊕gravitinoAuth; merge jobOptions (Spark max) ⊕ 
ircAuth (§7.4) → one runJob
+        (Spark runs remaining types in order inside this Job)
+   → INSERT table_maintenance_job (policy_id = first step; full chain in 
runtime template;
+        before_snapshot_id=S0, finished_at=NULL)
+   → DELETE ③ → return
+
+Job terminal listener:
+   S1 = currentSnapshotId; sample per policy touched by the Job → UPSERT stats
+   → UPDATE job SET after_snapshot_id=S1, finished_at=…
+   # no re-upsert of ③ for next type
+```
+
+**Why DELETE ②/③; keep ①:** ① is the durable per-policy expand lease. ②/③ are 
one-shot submit
+units; Spark lifetime and anti-double-submit live on `table_maintenance_job` + 
Job listener /
+reconcile.
+
+**Metrics / snapshot drift (Amoro-style, one evaluate per pick):**
+
+Control-plane sampling is **once per ②/③ claim**. Persist that sample under 
Iceberg
+`snapshot_id` in `table_snapshot_statistic`, then decide. Do **not** re-sample 
in a loop when HEAD
+moves (same pattern as Amoro / Floe / OpenHouse). Stats for `S0` stay valid as 
history even if HEAD
+advances; they may be stale for “current” maintenance need — next `cron-job` / 
`commit-job` will
+sample a newer snapshot.
+
+| Outcome after one sample              | Action                               
                                  |
+| ------------------------------------- | 
---------------------------------------------------------------------- |
+| Recommender says maintain             | `runJob`; Job / Iceberg OCC handles 
concurrent commits                 |
+| Recommender says skip, or gates block | No `runJob`; still **DELETE** ②/③ — 
this round is **complete**         |
+| Snapshot moves during/after sample    | Do **not** re-sample; end the round; 
fresher state on the next wake-up |
+
+**Expand gates** (on ①, before INSERT ②):
+
+1. In-flight for `(table_id, policy_id)` (`finished_at IS NULL`) → skip.
+2. `MAX(finished_at)` within resolved `minIntervalMs` → skip.
+
+**Submit path** re-checks in-flight; if occupied, **DELETE** the scheduled row 
and return (no second
+`runJob`). Sample **once** at `S0`, upsert history, then recommend.
+
+**Crontab timeline** (`0 2 * * *`): create → INSERT ① due next 02:00 → pick ① 
→ INSERT many ② due
+now → retain ① with next crontab → pick each ② → short submit → DELETE ②. 
Expand due time ≠ Spark
+wall-clock stamp on ②.
+
+#### 5.2.4 Multi-node coordination
+
+Every node runs db-scheduler. IRC on the commit node upserts ③; when due, 
nodes compete and
+**exactly one** claim runs (CAS + heartbeat). Missed heartbeats free a hung 
**short callback** only.
+
+```text
+Policy create → INSERT ①
+crontab due → pick ① → INSERT many ② → retain ①
+IRC commit → upsert ③ {table_id}
+Node A / B / C
+        ├─ expand: pick ① → INSERT ② → retain ①
+        ├─ cron:   pick ② → short submit → DELETE ②
+        ├─ commit: pick ③ → one chained submit → DELETE ③
+        └─ picker dies → missed heartbeats → instance runnable again
+```
+
+**Not** “write `scheduled_tasks` ⇒ every node executes.” **Yes** “when due ⇒ 
every node may try;
+exactly one claim runs that instance.”
+
+`scheduled_tasks` is owned by db-scheduler (upstream DDL). Illustrative usage:
+
+```text
+PK (task_name, task_instance)
+① policy-expand / {policy_id}     task_data empty or cursor
+② cron-job / table:…|batch:…   task_data = { tableIds, policyIds }
+③ commit-job / {table_id}       task_data = { tableIds, policyIds }  # chain 
built at pick
+②/③ DELETE after short submit; ① deleted only on policy disable/drop/replace
+```
+
+#### 5.2.5 Per-run table `table_maintenance_job`
+
+One row per Gravitino Job (`job_id`): in-flight / `minIntervalMs` gates, plus 
Iceberg
+**`before_snapshot_id` / `after_snapshot_id`** for Jobs Validation. Metric 
**values** live in
+`table_snapshot_statistic` (§5.2.6), not as JSON on this row.
+`minIntervalMs` uses this row’s `finished_at`, not 
`job_run_meta.job_finished_at`.
+Column name is **`job_id`** (Jobs API / `JobHandle` wording); it stores the 
same numeric id as
+`job_run_meta.job_run_id`. Surrogate **`id`** is the table PK.
+
+| Column               | Type                       | Notes                    
                                                                       |
+| -------------------- | -------------------------- | 
-----------------------------------------------------------------------------------------------
 |
+| `id`                 | `BIGINT UNSIGNED` PK       | Auto increment           
                                                                       |
+| `job_id`             | `BIGINT UNSIGNED NOT NULL` | Unique; maps to 
`job_run_meta.job_run_id`                                                       
|
+| `table_id`           | `BIGINT UNSIGNED NOT NULL` | `table_meta` PK          
                                                                       |
+| `policy_id`          | `BIGINT UNSIGNED NOT NULL` | Cron: that Job’s policy. 
Commit chain: first step’s `policy_id`; full chain in runtime template |
+| `before_snapshot_id` | `BIGINT NOT NULL`          | Iceberg snapshot at 
sample / submit                                                             |
+| `after_snapshot_id`  | `BIGINT NULL`              | Iceberg snapshot after 
job terminal; null while pending                                         |
+| `finished_at`        | `BIGINT UNSIGNED NULL`     | Epoch millis; **null = 
in-flight**                                                              |
+
+```sql
+CREATE TABLE IF NOT EXISTS `table_maintenance_job` (
+    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'auto increment 
id',
+    `job_id` BIGINT(20) UNSIGNED NOT NULL COMMENT 'job id; maps to 
job_run_meta.job_run_id',
+    `table_id` BIGINT(20) UNSIGNED NOT NULL COMMENT 'table id from table_meta',
+    `policy_id` BIGINT(20) UNSIGNED NOT NULL COMMENT 'policy id from 
policy_meta',
+    `before_snapshot_id` BIGINT(20) NOT NULL COMMENT 'Iceberg snapshot id at 
sample/submit',
+    `after_snapshot_id` BIGINT(20) NULL COMMENT 'Iceberg snapshot id after 
job; null while pending',
+    `finished_at` BIGINT(20) UNSIGNED NULL COMMENT 'end time; null = 
in-flight',
+    PRIMARY KEY (`id`),
+    UNIQUE KEY `uk_job_id` (`job_id`),
+    KEY `idx_tmj_table_policy_finished` (`table_id`, `policy_id`, 
`finished_at`),
+    KEY `idx_tmj_before_sid` (`table_id`, `before_snapshot_id`),
+    KEY `idx_tmj_after_sid` (`table_id`, `after_snapshot_id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
+  COMMENT 'per-run TMS job: snapshot pointers + submit gates';
+```
+
+**Validation:** load stats from `table_snapshot_statistic` where
+`(table_id, policy_id, snapshot_id)` matches the job’s `table_id` / 
`policy_id` /
+`before_snapshot_id` or `after_snapshot_id`. Do not store metric JSON on the 
job row.
+
+#### 5.2.6 Table snapshot statistic (`table_snapshot_statistic`)
+
+Per-policy Iceberg table statistics keyed by **`snapshot_id`**. Used by TMS 
sample, Recommender,
+and Jobs Validation. **One row = one `(table_id, policy_id, snapshot_id)`** — 
all metrics in
+`statistic_value` (JSON).
+
+**Why `policy_id`:** compaction, manifest-rewrite, snapshot-expiry, 
orphan-cleanup may sample the
+same table snapshot with **different** metric payloads / Validation needs. 
Without `policy_id`,
+UPSERT on `(table_id, snapshot_id)` would overwrite across types/policies.
+
+| Column            | Type                       | Notes                       
                |
+| ----------------- | -------------------------- | 
------------------------------------------- |
+| `id`              | `BIGINT UNSIGNED` PK       | Auto increment              
                |
+| `table_id`        | `BIGINT UNSIGNED NOT NULL` | `table_meta` PK             
                |
+| `policy_id`       | `BIGINT UNSIGNED NOT NULL` | `policy_meta.policy_id`     
                |
+| `snapshot_id`     | `BIGINT NOT NULL`          | Iceberg snapshot id         
                |
+| `statistic_value` | `MEDIUMTEXT NOT NULL`      | JSON payload of all metrics 
for this sample |
+
+**Unique key:** (`table_id`, `policy_id`, `snapshot_id`).  
+**Indexes:** (`table_id`); (`table_id`, `policy_id`).
+
+```sql
+CREATE TABLE IF NOT EXISTS `table_snapshot_statistic` (

Review Comment:
   Why does table's name is stats? It should be `metrics`.



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: [email protected]

For queries about this service, please contact Infrastructure at:
[email protected]

Reply via email to