← back to Cli Printing Press
feat(generator): add schema builder with data gravity scoring and insight scorecard dimension
63b89f6d34e1d22b3abb3be53986009a32275d37 · 2026-03-26 10:03:25 -0700 · Matt Van Horn
Phases 5-6 of the Creative Vision Engine:
- Schema Builder: generates domain-specific SQLite tables from API specs using
data gravity scoring (0-12). High-gravity entities get extracted scalar columns,
FK indexes, and FTS5 virtual tables instead of generic JSON blob tables.
- Scorecard: adds 12th dimension "Insight" (0-10) measuring behavioral insight
commands (health, similar, bottleneck, trends, patterns, forecast). Updates
workflow detection to find commands by prefix (stale, orphans, triage, load)
not just the workflow_*.go naming pattern. Max score now 120.
Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>
Files touched
A internal/generator/schema_builder.goM internal/generator/templates/store.go.tmplM internal/pipeline/scorecard.go
Diff
commit 63b89f6d34e1d22b3abb3be53986009a32275d37
Author: Matt Van Horn <455140+mvanhorn@users.noreply.github.com>
Date: Thu Mar 26 10:03:25 2026 -0700
feat(generator): add schema builder with data gravity scoring and insight scorecard dimension
Phases 5-6 of the Creative Vision Engine:
- Schema Builder: generates domain-specific SQLite tables from API specs using
data gravity scoring (0-12). High-gravity entities get extracted scalar columns,
FK indexes, and FTS5 virtual tables instead of generic JSON blob tables.
- Scorecard: adds 12th dimension "Insight" (0-10) measuring behavioral insight
commands (health, similar, bottleneck, trends, patterns, forecast). Updates
workflow detection to find commands by prefix (stale, orphans, triage, load)
not just the workflow_*.go naming pattern. Max score now 120.
Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>
---
internal/generator/schema_builder.go | 320 +++++++++++++++++++++++++++++
internal/generator/templates/store.go.tmpl | 6 +
internal/pipeline/scorecard.go | 70 ++++++-
3 files changed, 392 insertions(+), 4 deletions(-)
diff --git a/internal/generator/schema_builder.go b/internal/generator/schema_builder.go
new file mode 100644
index 00000000..51b63f07
--- /dev/null
+++ b/internal/generator/schema_builder.go
@@ -0,0 +1,320 @@
+package generator
+
+import (
+ "strings"
+ "unicode"
+
+ "github.com/mvanhorn/cli-printing-press/internal/spec"
+)
+
+type TableDef struct {
+ Name string
+ Columns []ColumnDef
+ Indexes []IndexDef
+ FTS5 bool
+ FTS5Fields []string
+ FTS5Triggers bool
+}
+
+type ColumnDef struct {
+ Name string
+ Type string
+ PrimaryKey bool
+ NotNull bool
+}
+
+type IndexDef struct {
+ Name string
+ TableName string
+ Columns string
+ Unique bool
+}
+
+// BuildSchema generates domain-specific table definitions from the API spec.
+// High-gravity entities (many endpoints, text fields, temporal fields) get
+// full column extraction. Low-gravity entities get simple id+data tables.
+func BuildSchema(s *spec.APISpec) []TableDef {
+ var tables []TableDef
+
+ for name, resource := range s.Resources {
+ gravity := computeDataGravity(name, resource)
+ tableName := toSnakeCase(name)
+
+ table := TableDef{
+ Name: tableName,
+ Columns: []ColumnDef{
+ {Name: "id", Type: "TEXT", PrimaryKey: true},
+ {Name: "data", Type: "JSON", NotNull: true},
+ {Name: "synced_at", Type: "DATETIME DEFAULT CURRENT_TIMESTAMP"},
+ },
+ }
+
+ if gravity >= 8 {
+ fields := collectResponseFields(resource)
+ for _, f := range fields {
+ if isScalarField(f) && f.Name != "id" {
+ col := ColumnDef{
+ Name: toSnakeCase(f.Name),
+ Type: sqliteType(f.Type, f.Format),
+ }
+ table.Columns = append(table.Columns, col)
+ }
+ if strings.HasSuffix(strings.ToLower(f.Name), "_id") {
+ table.Indexes = append(table.Indexes, IndexDef{
+ Name: "idx_" + tableName + "_" + toSnakeCase(f.Name),
+ TableName: tableName,
+ Columns: toSnakeCase(f.Name),
+ })
+ }
+ }
+ for _, temporal := range []string{"created_at", "updated_at"} {
+ if hasField(fields, temporal) {
+ table.Indexes = append(table.Indexes, IndexDef{
+ Name: "idx_" + tableName + "_" + temporal,
+ TableName: tableName,
+ Columns: temporal,
+ })
+ }
+ }
+ }
+
+ textFields := collectTextFieldNames(resource)
+ if len(textFields) >= 2 && gravity >= 6 {
+ table.FTS5 = true
+ table.FTS5Fields = textFields
+ table.FTS5Triggers = true
+ }
+
+ tables = append(tables, table)
+
+ for subName, subResource := range resource.SubResources {
+ subTable := buildSubResourceTable(subName, subResource, tableName)
+ tables = append(tables, subTable)
+ }
+ }
+
+ tables = append(tables, TableDef{
+ Name: "sync_state",
+ Columns: []ColumnDef{
+ {Name: "resource_type", Type: "TEXT", PrimaryKey: true},
+ {Name: "last_cursor", Type: "TEXT"},
+ {Name: "last_synced_at", Type: "DATETIME"},
+ {Name: "total_count", Type: "INTEGER DEFAULT 0"},
+ },
+ })
+
+ return tables
+}
+
+// computeDataGravity scores 0-12 based on endpoint count, field count,
+// text fields, temporal fields, and FK references.
+func computeDataGravity(name string, r spec.Resource) int {
+ score := 0
+
+ // Endpoint count: 1pt per endpoint, max 4
+ epCount := len(r.Endpoints)
+ if epCount >= 4 {
+ score += 4
+ } else {
+ score += epCount
+ }
+
+ // Field count from all params/body across endpoints
+ totalFields := 0
+ for _, ep := range r.Endpoints {
+ totalFields += len(ep.Params) + len(ep.Body)
+ }
+ if totalFields >= 10 {
+ score += 2
+ } else if totalFields >= 5 {
+ score += 1
+ }
+
+ // Text fields bonus
+ textFields := collectTextFieldNames(r)
+ if len(textFields) >= 3 {
+ score += 2
+ } else if len(textFields) >= 1 {
+ score += 1
+ }
+
+ // Temporal fields bonus
+ allFields := collectResponseFields(r)
+ temporalCount := 0
+ for _, f := range allFields {
+ lower := strings.ToLower(f.Name)
+ if strings.HasSuffix(lower, "_at") || strings.Contains(lower, "date") || f.Format == "date-time" {
+ temporalCount++
+ }
+ }
+ if temporalCount >= 2 {
+ score += 2
+ } else if temporalCount >= 1 {
+ score += 1
+ }
+
+ // FK references bonus
+ fkCount := 0
+ for _, f := range allFields {
+ if strings.HasSuffix(strings.ToLower(f.Name), "_id") {
+ fkCount++
+ }
+ }
+ if fkCount >= 2 {
+ score += 2
+ } else if fkCount >= 1 {
+ score += 1
+ }
+
+ if score > 12 {
+ score = 12
+ }
+ return score
+}
+
+// collectResponseFields gathers all field specs from GET endpoints.
+func collectResponseFields(r spec.Resource) []spec.Param {
+ seen := make(map[string]bool)
+ var fields []spec.Param
+
+ for _, ep := range r.Endpoints {
+ if ep.Method != "GET" {
+ continue
+ }
+ for _, p := range ep.Params {
+ if !seen[p.Name] {
+ seen[p.Name] = true
+ fields = append(fields, p)
+ }
+ }
+ for _, p := range ep.Body {
+ if !seen[p.Name] {
+ seen[p.Name] = true
+ fields = append(fields, p)
+ }
+ }
+ }
+
+ // Also include body fields from POST/PUT as they often mirror response shape
+ for _, ep := range r.Endpoints {
+ if ep.Method == "GET" {
+ continue
+ }
+ for _, p := range ep.Body {
+ if !seen[p.Name] {
+ seen[p.Name] = true
+ fields = append(fields, p)
+ }
+ }
+ }
+
+ return fields
+}
+
+// isScalarField returns true for string/int/bool/number fields (not objects/arrays).
+func isScalarField(p spec.Param) bool {
+ switch strings.ToLower(p.Type) {
+ case "string", "integer", "int", "boolean", "bool", "number", "float":
+ return true
+ default:
+ return false
+ }
+}
+
+// sqliteType maps spec types to SQLite column types.
+func sqliteType(goType, format string) string {
+ switch strings.ToLower(goType) {
+ case "integer", "int":
+ return "INTEGER"
+ case "number", "float":
+ return "REAL"
+ case "boolean", "bool":
+ return "INTEGER"
+ case "string":
+ if format == "date-time" || format == "date" {
+ return "DATETIME"
+ }
+ return "TEXT"
+ default:
+ return "TEXT"
+ }
+}
+
+// collectTextFieldNames finds fields likely to contain searchable text.
+func collectTextFieldNames(r spec.Resource) []string {
+ textKeywords := map[string]bool{
+ "title": true, "name": true, "description": true,
+ "body": true, "content": true, "summary": true, "subject": true,
+ "text": true, "message": true, "comment": true, "note": true,
+ }
+
+ seen := make(map[string]bool)
+ var result []string
+
+ for _, ep := range r.Endpoints {
+ allParams := append(ep.Params, ep.Body...)
+ for _, p := range allParams {
+ lower := strings.ToLower(p.Name)
+ if textKeywords[lower] && !seen[lower] && isScalarField(p) {
+ seen[lower] = true
+ result = append(result, toSnakeCase(p.Name))
+ }
+ }
+ }
+
+ return result
+}
+
+// hasField checks if a field with the given name exists in the list.
+func hasField(fields []spec.Param, name string) bool {
+ for _, f := range fields {
+ if toSnakeCase(f.Name) == name || strings.ToLower(f.Name) == name {
+ return true
+ }
+ }
+ return false
+}
+
+// buildSubResourceTable creates a table definition for a sub-resource with
+// a foreign key column referencing the parent table.
+func buildSubResourceTable(name string, r spec.Resource, parentTable string) TableDef {
+ tableName := toSnakeCase(name)
+
+ table := TableDef{
+ Name: tableName,
+ Columns: []ColumnDef{
+ {Name: "id", Type: "TEXT", PrimaryKey: true},
+ {Name: parentTable + "_id", Type: "TEXT", NotNull: true},
+ {Name: "data", Type: "JSON", NotNull: true},
+ {Name: "synced_at", Type: "DATETIME DEFAULT CURRENT_TIMESTAMP"},
+ },
+ Indexes: []IndexDef{
+ {
+ Name: "idx_" + tableName + "_" + parentTable + "_id",
+ TableName: tableName,
+ Columns: parentTable + "_id",
+ },
+ },
+ }
+
+ return table
+}
+
+// toSnakeCase converts camelCase, PascalCase, or kebab-case to snake_case.
+func toSnakeCase(s string) string {
+ s = strings.ReplaceAll(s, "-", "_")
+
+ var result strings.Builder
+ for i, r := range s {
+ if unicode.IsUpper(r) && i > 0 {
+ prev := rune(s[i-1])
+ if unicode.IsLower(prev) || unicode.IsDigit(prev) {
+ result.WriteRune('_')
+ } else if unicode.IsUpper(prev) && i+1 < len(s) && unicode.IsLower(rune(s[i+1])) {
+ result.WriteRune('_')
+ }
+ }
+ result.WriteRune(unicode.ToLower(r))
+ }
+ return result.String()
+}
diff --git a/internal/generator/templates/store.go.tmpl b/internal/generator/templates/store.go.tmpl
index 31f91933..95fa3c04 100644
--- a/internal/generator/templates/store.go.tmpl
+++ b/internal/generator/templates/store.go.tmpl
@@ -203,6 +203,12 @@ func (s *Store) GetSyncState(resourceType string) (cursor string, lastSynced tim
return
}
+// Query executes a raw SQL query and returns the rows.
+// Used by workflow commands that need custom queries against the local store.
+func (s *Store) Query(query string, args ...any) (*sql.Rows, error) {
+ return s.db.Query(query, args...)
+}
+
func (s *Store) Count(resourceType string) (int, error) {
var count int
err := s.db.QueryRow(
diff --git a/internal/pipeline/scorecard.go b/internal/pipeline/scorecard.go
index 7795268b..3d65d3a7 100644
--- a/internal/pipeline/scorecard.go
+++ b/internal/pipeline/scorecard.go
@@ -30,7 +30,8 @@ type SteinerScore struct {
Breadth int `json:"breadth"` // 0-10: how many commands (penalizes empty CLIs)
Vision int `json:"vision"` // 0-10
Workflows int `json:"workflows"` // 0-10
- Total int `json:"total"` // 0-110
+ Insight int `json:"insight"` // 0-10
+ Total int `json:"total"` // 0-120
Percentage int `json:"percentage"` // 0-100
}
@@ -61,6 +62,7 @@ func RunScorecard(outputDir, pipelineDir string) (*Scorecard, error) {
sc.Steinberger.Breadth = scoreBreadth(outputDir)
sc.Steinberger.Vision = scoreVision(outputDir)
sc.Steinberger.Workflows = scoreWorkflows(outputDir)
+ sc.Steinberger.Insight = scoreInsight(outputDir)
sc.Steinberger.Total = sc.Steinberger.OutputModes +
sc.Steinberger.Auth +
@@ -72,10 +74,11 @@ func RunScorecard(outputDir, pipelineDir string) (*Scorecard, error) {
sc.Steinberger.LocalCache +
sc.Steinberger.Breadth +
sc.Steinberger.Vision +
- sc.Steinberger.Workflows
+ sc.Steinberger.Workflows +
+ sc.Steinberger.Insight
if sc.Steinberger.Total > 0 {
- sc.Steinberger.Percentage = (sc.Steinberger.Total * 100) / 110
+ sc.Steinberger.Percentage = (sc.Steinberger.Total * 100) / 120
}
// Grade
@@ -608,12 +611,29 @@ func scoreWorkflows(dir string) int {
return 0
}
+ workflowPrefixes := []string{"stale", "orphan", "triage", "load", "overdue", "standup", "deps", "workflow"}
+
compoundCommands := 0
for _, e := range entries {
if e.IsDir() || !strings.HasSuffix(e.Name(), ".go") {
continue
}
+ name := strings.ToLower(e.Name())
+
+ // Detect workflow commands by filename pattern
+ isWorkflowFile := false
+ for _, prefix := range workflowPrefixes {
+ if strings.HasPrefix(name, prefix) {
+ isWorkflowFile = true
+ break
+ }
+ }
+ if isWorkflowFile {
+ compoundCommands++
+ continue
+ }
+
content := readFileContent(filepath.Join(cliDir, e.Name()))
// Count files that make 2+ different API calls in a single RunE.
@@ -655,6 +675,46 @@ func scoreWorkflows(dir string) int {
}
}
+func scoreInsight(dir string) int {
+ cliDir := filepath.Join(dir, "internal", "cli")
+ entries, err := os.ReadDir(cliDir)
+ if err != nil {
+ return 0
+ }
+
+ insightPrefixes := []string{"health", "similar", "bottleneck", "trends", "patterns", "forecast"}
+ found := 0
+ for _, e := range entries {
+ if e.IsDir() || !strings.HasSuffix(e.Name(), ".go") {
+ continue
+ }
+ name := strings.ToLower(e.Name())
+ for _, prefix := range insightPrefixes {
+ if strings.HasPrefix(name, prefix) {
+ found++
+ break
+ }
+ }
+ }
+
+ switch {
+ case found >= 6:
+ return 10
+ case found >= 5:
+ return 9
+ case found >= 4:
+ return 8
+ case found >= 3:
+ return 6
+ case found >= 2:
+ return 4
+ case found >= 1:
+ return 2
+ default:
+ return 0
+ }
+}
+
// sampleCommandFiles reads up to n command files from internal/cli/.
// If n <= 0, reads all command files.
func sampleCommandFiles(dir string, n int) []string {
@@ -786,6 +846,7 @@ func buildGapReport(s SteinerScore) []string {
{"breadth", s.Breadth},
{"vision", s.Vision},
{"workflows", s.Workflows},
+ {"insight", s.Insight},
}
for _, d := range dimensions {
if d.score < 5 {
@@ -863,12 +924,13 @@ func writeScorecardMD(sc *Scorecard, pipelineDir string) error {
{"Breadth", s.Breadth},
{"Vision", s.Vision},
{"Workflows", s.Workflows},
+ {"Insight", s.Insight},
}
for _, d := range dimensions {
bar := strings.Repeat("#", d.score) + strings.Repeat(".", 10-d.score)
b.WriteString(fmt.Sprintf("| %s | %d/10 %s |\n", d.name, d.score, bar))
}
- b.WriteString(fmt.Sprintf("| **Total** | **%d/110** |\n\n", s.Total))
+ b.WriteString(fmt.Sprintf("| **Total** | **%d/120** |\n\n", s.Total))
// Competitor comparison
if len(sc.CompetitorScores) > 0 {
← f55405b8 feat(generator): add PM workflow and behavioral insight temp
·
back to Cli Printing Press
·
docs: update README with Creative Vision Engine, NOI system, 0c1316fb →