add-minutes-query

name	add-minutes-query
description	Add a new query of the minutes data to the `odem` system. Given a description or name of the operation, implement, test, and integrate it.

You will generate a query to satisfy the request and then connect the query with various interfaces a fuzzy CLI (pkg/fzfui) for interactive use, a static report renderer (pkg/reportui), and an MCP tool handler (pkg/mcpsrv). Once everything is implemented you will register the operations in the dispatcher (pkg/dispatch), test it thoroughly.

Step 1 — Understand the schema

Read pkg/db/fasoladb/minutes_schema.sql, pkg/db/views.sql, and existing queries in pkg/db/db.go before writing SQL. Prefer views over raw tables.

Key tables/views:

Name	Alias	Key columns
song_leader_joins	slj	song_id, leader_id, minutes_id, lesson_id
leader_song_stats	lss	leader_id, song_id, lesson_count, lesson_rank
song_stats	ss	song_id, year, lesson_count, rank
book_song_joins	bsj	song_id, book_id, page_num, keys — use book_id = 2 for Denson
leaders		id, name, lesson_count
leader_name_invalid	inv	exclude with LEFT JOIN … WHERE inv.name IS NULL
minutes		id, Name, Year, Date, Location
locations		id, state_province, country, city
minutes_location_joins	mlj	minutes_id → location_id
leader_coattendance	lca	leader_a_id, leader_b_id, shared_singings (precomputed)
song_details, lesson_details, song_leader_stats		precomputed views — check columns before use

Step 2 — Validate the SQL

Run the query against /tmp/minutes.db using the sqlite3 CLI before writing any Go. Use "Bud Oliver", "Sam Kleinman", "Rose Altha Taylor" for leader-scoped queries; "82t", "475", and "89" for song-scoped queries.

Ask the user to confirm the results look correct before continuing.

Step 3 — Add the method to pkg/db/db.go

Fixed query:

func (conn *Connection) MyQuery(ctx context.Context, name string, limit int) iter.Seq2[models.LeaderSongRank, error] {
    const query = `SELECT …`
    cur, err := conn.db.QueryContext(ctx, query, name, cmp.Or(limit, 40))
    if err != nil {
        return irt.Two(models.LeaderSongRank{}, err)
    }
    return dbx.Cursor[models.LeaderSongRank](cur)
}

Dynamic query — use dbx.Builder, never fmt.Sprintf + strings.Repeat:

func (conn *Connection) MyQuery(ctx context.Context, limit int, items ...models.SingingLocality) iter.Seq2[models.LeaderSongRank, error] {
    strs := make([]string, len(items)) // named string types must be converted to []string
    for i, s := range items { strs[i] = string(s) }

    var qb dbx.Builder
    qb.WithSQL(`SELECT …`)
    if len(strs) > 0 {
        qb.With(" WHERE col IN (%+?)", strs) // %+? expands slice to ?, ?, ?
    }
    qb.WithSQL(` ORDER BY count DESC`)
    if limit > 0 {
        qb.With(" LIMIT %?", limit)
    }
    query, args := qb.Build()
    cur, err := conn.db.QueryContext(ctx, query, args...)
    if err != nil {
        return irt.Two(models.LeaderSongRank{}, err)
    }
    return dbx.Cursor[models.LeaderSongRank](cur)
}

dbx.Builder placeholders (SQLite): %? scalar, %+? slice ([]string, []int, []any — not named types), %s raw SQL fragment.

dbx.Cursor maps columns to struct fields by db: tag; unmatched fields are zero. models.LeaderSongRank tags: rank (int), name, count, song_page, song_title, song_keys (strings), ratio (float64).

Step 4 — Write a smoke test in pkg/db/db_test.go

func TestMyQuery(t *testing.T) {
    conn, ctx := testConn(t)
    count := 0
    for _, err := range conn.MyQuery(ctx, testLeader, 5) {
        if err != nil { t.Fatal(err) }
        count++
    }
    if count == 0 {
        t.Errorf("MyQuery(%q): expected at least one result", testLeader)
    }
}

Add break after the first iteration for unbounded queries. Assert domain invariants where meaningful (e.g. ratios in (0,1]). Run go test ./pkg/db/ and ask the user to confirm the output looks correct before continuing.

Step 6 — Add a Static Report rendering to pkg/reportui/reports.go

Add an report generation function following the pattern of the other reporting functions in this file. Use the func(ctx context.Context, conn *db.Connection, p Params) error signature.

func MyQuery(ctx context.Context, conn *db.Connection, in Params) (err error) {
    info, err := selector.Leader(ctx, conn, in.Search()) // or other selector
	if err != nil {
		return err
	}

	w, err := in.getWriter(info.Name, "<my-query>")
	if err != nil {
		return err
	}
	defer func() { err = erc.Join(w.Close()) }()
	// ---------------- THE FOLD ----------------
	var ec erc.Collector
	var mb mdwn.Builder

	mb.H1(singer.Name, "--", "<title>")
	models.WriteSongTable(&mb, erc.HandleAll(conn.DatabasQuery(ctx, info.Name, 40), ec.Push))
	ec.Push(flush(w, &mb))
	return ec.Resolve()
}

For custom types add a table helper longside models.WriteSongTable. mdwn.Builder tables take iter.Seq — use erc.HandleAll to strip errors.

The comments with THE FOLD split the function between setup and configuration, and rendering of results.

Step 6a — For Leader-scoped queries, add a section to the LeaderOverview report in pkg/reportui/overview.go

mb.H2("My New Section")
mb.Paragraph("One-line description.")
// render — choose one:
writeSongTable(&mb, erc.HandleAll(conn.MyQuery(ctx, singer, 25), ec.Push))                          // iter.Seq2[models.LeaderSongRank, error]
mb.KVTable(irt.MakeKV("Name", "Count"),                                                             // iter.Seq2[irt.KV[string, int], error]
    irt.Convert2(irt.KVsplit(erc.HandleAll(conn.MyQuery(ctx, singer, 25), ec.Push)), intValToStr))
writeMyTable(&mb, erc.HandleAll(conn.MyQuery(ctx, singer, 25), ec.Push))                            // custom type — add helper at bottom of file

For custom types add a table helper longside models.WriteSongTable. mdwn.Builder tables take iter.Seq — use erc.HandleAll to strip errors.

Step 7 — Add an MCP handler to pkg/mcpsrv/handlers.go

Follow the pattern of existing handlers. Each handler takes (ctx context.Context, conn *db.Connection, p models.Params) and returns (*ContextualSequence[C, T], error) where C is the context type (usually string for a leader name) and T is the result row type.

func MyQuery(ctx context.Context, conn *db.Connection, p models.Params) (*ContextualSequence[string, T], error) {
    leader, err := reportui.SelectLeader(ctx, conn, p.Name)
    if err != nil {
        return nil, err
    }
    results, err := erc.FromIteratorUntil(conn.MyQuery(ctx, leader.Name, cmp.Or(p.Limit, 20)))
    if err != nil {
        return nil, err
    }
    return &ContextualSequence[string, T]{
        Results: results,
        Context: leader.Name,
    }, nil
}

Use irt.KV[string, int] for key-value count results, models.LeaderSongRank for song rank results, or a custom model as needed.

Step 8 — Create an interactive Messenger handler in pkg/msgui/msgui.go

Follow the pattern of the existing Messenger implementations, which have the func(context.Context, *db.Connection, models.Params) iter.Seq2[*mdwn.Builder, error] signature.

The handlers mostly follow the same structure and content as the reportui implementations, but render more compact outputs, without headings, and have smaller result sets.

func MessengerOp(ctx context.Context, conn *db.Connection, p models.Params) iter.Seq2[*mdwn.Builder, error] {
	return func(yield func(*mdwn.Builder, error) bool) {

		md := mdwn.MakeBuilder(len(p.Name) + 20)
		md.Concat("Title **", p.Name, "**:")
		if !yield(md, nil) {
			return
		}

		mdtb := mdwn.MakeBuilder(4096)
	    // collect and generate data, potentially in several messages
	    yield(mdtb, nil)
	}

Each mdwn.Builder represents a single message sent to the user. Include all tables in three back-ticks to prevent formatting problems for wider output. Use the models.Write<> helpers to generate well-formed output.

Step 9 — Register in pkg/dispatch/dispatcher.go

All in dispatch.go:

1. iota block — add before MinutesAppOpInvalid:

   MinutesAppOpMyOp
   MinutesAppOpInvalid
   

2. Registry() — build a MinutesAppRegistration with Reporter, Fuzz, Messenger, and MCP all wired:

   MinutesAppRegistration{
       ID:          mao,
       Command:     "my-op",
       Description: "description to provide context and for help text",
       Aliases:     []string{"alternate", "shorthand"},
       Reporter:    reportui.MyQuery,
       MCP:         mcpsrv.NewTool(mcpsrv.MyQuery).Register,
       Requires:    dt.MakeSet(irt.Args(MinuitesAppQueryType))
   }
   

The Requires set identifies the parameter values needed for this operation: not all queries require all arguments.

All wiring and necessary functionality is derived from this registration information.

Step 10 — Build and verify

Run go build ./... and fix any errors. Use go test ./... -timeout=1m to verify that all tests pass. Confirm the operation appears in the fzf menu and returns sensible output.