"OKR KR progress_source DSL — declarative query against PROJ / INV / HR / LEARN modules for auto-progress feed"

§1 — Description (BCP-14 normative)

The OKR service MUST ship progress_source DSL at services/okr/src/dsl/ parsing JSONB queries against 5 modules with metric whitelist + custom_sql dual-sign gate, 4 memory audit kinds.

1. MUST validate dsl_module against closed enum per DEC-1981, dsl_agg per DEC-1982.

1. MUST define schema at migration 0003: ```sql ALTER TABLE okr_krs ADD COLUMN progress_source_jsonb JSONB; ALTER TABLE okr_krs ADD COLUMN progress_source_last_resolved_at TIMESTAMPTZ;

CREATE TABLE okr_custom_sql_approvals ( kr_id UUID PRIMARY KEY, tenant_id UUID NOT NULL, sql_text TEXT NOT NULL, cfo_signed_by UUID NOT NULL, cfo_signed_at TIMESTAMPTZ NOT NULL, ceo_signed_by UUID NOT NULL, ceo_signed_at TIMESTAMPTZ NOT NULL, approved_at TIMESTAMPTZ NOT NULL DEFAULT now() ); ALTER TABLE okr_custom_sql_approvals ENABLE ROW LEVEL SECURITY; CREATE POLICY custom_sql_rls ON okr_custom_sql_approvals USING (tenant_id = current_setting('auth.tenant_id')::uuid) WITH CHECK (tenant_id = current_setting('auth.tenant_id')::uuid); REVOKE UPDATE, DELETE ON okr_custom_sql_approvals FROM cyberos_app; ```

1. MUST parse DSL at parser.rs::parse(jsonb) → DslQuery:

• Required fields: module, metric, agg
• Optional: filter (key-value pairs), date_range
• Reject unknown enum values.

1. MUST dispatch per module at resolvers/mod.rs::resolve(dsl, tenant):

• proj → proj_resolver (e.g. metric=issues_closed, agg=count, filter={status:done})
• inv → inv_resolver (e.g. metric=invoice_amount_paid, agg=sum)
• hr → hr_resolver (e.g. metric=member_count, agg=count, filter={status:active})
• learn → learn_resolver (e.g. metric=courses_completed, agg=sum)
• custom_sql → custom_sql_resolver (gated)

1. MUST enforce metric whitelist per DEC-1983 at metric_whitelist.rs::is_allowed(module, metric) — only pre-approved metrics; reject all others.

1. MUST gate custom_sql per DEC-1983 — execution requires okr_custom_sql_approvals row with both CFO + CEO signatures; same person can't sign both.

1. MUST emit 4 memory audit kinds per DEC-1985. PII per TASK-MEMORY-111: resolved value SHA-256 hashed; module/metric ok.

1. MUST thread trace_id from set → resolve → audit.

1. MUST NOT bypass whitelist per DEC-1983.

1. MUST NOT execute custom_sql without dual-sign per DEC-1983.

§2 — Why this design

Why DSL not raw SQL (DEC-1980)? Safety + portability — module-aware resolvers respect RLS automatically.

Why whitelist (DEC-1983)? Without whitelist, KR DSL becomes a side-channel for reading any tenant data (PII risk).

Why custom_sql gate (DEC-1983)? Escape hatch for edge cases but requires governance — dual sign is the discipline.

Why JSONB storage (DEC-1984)? Flexible schema; easy to extend with new agg / filter shapes.

§3 — API contract

Sample DSL:

{
  "module": "proj",
  "metric": "issues_closed",
  "agg": "count",
  "filter": {"status": "done", "label": "p0-customer"},
  "date_range": {"from": "2026-01-01", "to": "2026-03-31"}
}

Resolver returns: {value: 47, resolved_at: "2026-05-17T02:00:00Z", source: "proj.issues_closed"}.

Custom SQL approval flow:

POST /v1/okr/krs/{id}/custom-sql/request   body: {sql_text}
POST /v1/okr/krs/{id}/custom-sql/cfo-sign
POST /v1/okr/krs/{id}/custom-sql/ceo-sign

§4 — Acceptance criteria

1. dsl_module enum cardinality 5. 2. dsl_agg enum cardinality 6. 3. Parser rejects unknown enums. 4. proj resolver works. 5. inv resolver works. 6. hr resolver works. 7. learn resolver works. 8. Metric whitelist enforced. 9. custom_sql requires dual-sign. 10. Same-person dual-sign rejected. 11. 4 memory audit kinds emitted. 12. PII scrubbed (resolved value SHA256). 13. RLS denies cross-tenant. 14. Trace_id preserved. 15. Resolvers respect module RLS. 16. Append-only approvals via REVOKE. 17. Filter key-value validated per module schema. 18. date_range respects timezone. 19. Resolution failure → sev-2 + null value. 20. last_resolved_at updated on each compute.

§5 — Verification

#[tokio::test]
async fn proj_resolver_counts_closed_issues() {
    let ctx = TestContext::with_5_closed_issues().await;
    let dsl = json!({"module":"proj","metric":"issues_closed","agg":"count","filter":{"status":"done"}});
    let val = resolve(dsl, ctx.tenant).await;
    assert_eq!(val, 5);
}

#[tokio::test]
async fn whitelist_rejects_unknown_metric() {
    let dsl = json!({"module":"proj","metric":"private_field","agg":"count"});
    let r = resolve(dsl, ctx.tenant).await;
    assert!(r.is_err());
}

#[tokio::test]
async fn custom_sql_requires_dual_sign() {
    let dsl = json!({"module":"custom_sql","sql":"SELECT count(*) FROM users"});
    let r = try_resolve(dsl, ctx.tenant).await;
    assert!(r.is_err());  // not approved
    ctx.approve_cfo(ctx.kr_id).await;
    let r2 = try_resolve(dsl, ctx.tenant).await;
    assert!(r2.is_err());  // CEO missing
    ctx.approve_ceo(ctx.kr_id).await;
    let r3 = try_resolve(dsl, ctx.tenant).await;
    assert!(r3.is_ok());
}

// 5.4..5.10

§7 — Dependencies

Upstream: TASK-OKR-001. Downstream: TASK-OKR-004 (auto-progress cron uses resolver). Cross-module: TASK-PROJ-013, TASK-INV-009, TASK-HR-008, TASK-LEARN-001 (data sources), TASK-AUTH-101 (CFO/CEO roles), TASK-MEMORY-111 (PII).

§10 — Failure modes

§11 — Implementation notes

• §11.1 Each resolver implements trait Resolver { async fn resolve(&self, dsl) -> Result<Value> }.
• §11.2 Metric whitelist per-module is hardcoded constants; reviewed in PR.
• §11.3 Custom SQL approved sql_text is immutable (REVOKE UPDATE); change = new approval.
• §11.4 memory audit body: kr_id, module, metric, agg; resolved_value SHA256.
• §11.5 Auto-progress cron (TASK-OKR-004) calls resolve() and writes to current_value.

End of TASK-OKR-003 spec.