// Package api serves the ledger over HTTP as JSON. // // Everything the API returns is safe to publish: sole traders' names and // addresses are withheld, applicants are identified by public_id (a keyed // hash), and contacts appear only as keyed hashes. package api import ( "context" "database/sql" "encoding/json" "log/slog" "net/http" "strconv" "strings" "time" "github.com/go-chi/chi/v5" "github.com/go-chi/chi/v5/middleware" "eacledger/internal/version" ) // API is the HTTP handler set. type API struct { db *sql.DB log *slog.Logger } // New returns an API over db. func New(db *sql.DB, log *slog.Logger) *API { return &API{db: db, log: log} } // Router returns the HTTP routes. func (a *API) Router() http.Handler { r := chi.NewRouter() r.Use(middleware.RealIP, middleware.Recoverer, middleware.Timeout(60*time.Second)) r.Get("/healthz", func(w http.ResponseWriter, _ *http.Request) { w.Write([]byte("ok\n")) }) r.Get("/api/stats", a.stats) r.Get("/api/docs", a.docs) r.Get("/api/docs/{key}", a.doc) r.Get("/api/applicants/{id}", a.applicant) r.Get("/api/brands", a.brands) r.Get("/api/events", a.events) return r } // KV is one summary line. type KV struct { Key string `json:"key"` Value any `json:"value"` } // Summary returns headline counts. func Summary(ctx context.Context, db *sql.DB) ([]KV, error) { q := []struct{ k, sql string }{ {"documents", `SELECT COUNT(*) FROM docs`}, {"documents_active_status", `SELECT COUNT(*) FROM docs WHERE status_code IN ('01','04','05')`}, {"certificates", `SELECT COUNT(*) FROM docs WHERE kind='certificate'`}, {"declarations", `SELECT COUNT(*) FROM docs WHERE kind='declaration'`}, {"issued_RU", `SELECT COUNT(*) FROM docs WHERE issuing_country='RU'`}, {"issued_BY", `SELECT COUNT(*) FROM docs WHERE issuing_country='BY'`}, {"issued_KZ", `SELECT COUNT(*) FROM docs WHERE issuing_country='KZ'`}, {"issued_KG", `SELECT COUNT(*) FROM docs WHERE issuing_country='KG'`}, {"issued_AM", `SELECT COUNT(*) FROM docs WHERE issuing_country='AM'`}, {"coalition_manufacturer_docs", `SELECT COUNT(*) FROM docs WHERE coalition_mfr=1`}, {"coalition_manufacturer_docs_since_2022-02-24", `SELECT COUNT(*) FROM docs WHERE coalition_mfr=1 AND start_date>='2022-02-24'`}, {"chpl_docs", `SELECT COUNT(*) FROM docs WHERE chp_tier IS NOT NULL`}, {"chpl_coalition_docs_since_2022-02-24", `SELECT COUNT(*) FROM docs WHERE chp_tier IS NOT NULL AND coalition_mfr=1 AND start_date>='2022-02-24'`}, {"docs_with_brand_match", `SELECT COUNT(DISTINCT doc_key) FROM doc_brands`}, {"applicants", `SELECT COUNT(*) FROM applicants`}, {"manufacturers", `SELECT COUNT(*) FROM manufacturers`}, {"vanished_docs", `SELECT COUNT(*) FROM docs WHERE vanished_on IS NOT NULL`}, {"deleted_flag_docs", `SELECT COUNT(*) FROM docs WHERE deleted=1`}, {"events", `SELECT COUNT(*) FROM events`}, {"last_full_snapshot", `SELECT COALESCE((SELECT value FROM meta WHERE key='last_full_day'),'')`}, {"last_ingest_day", `SELECT COALESCE(MAX(day),'') FROM ingests WHERE finished_at IS NOT NULL`}, } out := make([]KV, 0, len(q)) for _, x := range q { var v any if err := db.QueryRowContext(ctx, x.sql).Scan(&v); err != nil { return nil, err } if b, ok := v.([]byte); ok { v = string(b) } out = append(out, KV{x.k, v}) } return out, nil } func (a *API) stats(w http.ResponseWriter, r *http.Request) { s, err := Summary(r.Context(), a.db) if err != nil { a.fail(w, err) return } m := map[string]any{"version": version.Version} for _, kv := range s { m[kv.Key] = kv.Value } writeJSON(w, m) } // DocRow is a document in list responses. type DocRow struct { Key string `json:"doc_key"` DocID string `json:"doc_id"` Issuing *string `json:"issuing_country"` Kind string `json:"kind"` Start *string `json:"start_date"` Validity *string `json:"validity_date"` Status *string `json:"status_code"` StatusStart *string `json:"status_start"` ApplicantID *string `json:"applicant_id"` ApplicantName *string `json:"applicant_name"` ApplicantCC *string `json:"applicant_country"` ApplicantReg *string `json:"applicant_region"` Authority *string `json:"authority_name"` AuthorityID *string `json:"authority_id"` CoalitionMfr bool `json:"coalition_manufacturer"` CHPTier *string `json:"chp_tier"` VanishedOn *string `json:"vanished_on"` Manufacturers []string `json:"manufacturers,omitempty"` Brands []string `json:"brands,omitempty"` } const docSelect = `SELECT d.doc_key, d.doc_id, d.issuing_country, d.kind, d.start_date, d.validity_date, d.status_code, d.status_start, ap.public_id, CASE WHEN ap.natural_person=1 THEN NULL ELSE ap.name END, ap.country, ap.region, d.authority_name, d.authority_id, d.coalition_mfr, d.chp_tier, d.vanished_on FROM docs d LEFT JOIN applicants ap ON ap.applicant_key=d.applicant_key` func scanDoc(rows interface{ Scan(...any) error }) (DocRow, error) { var d DocRow var coal int err := rows.Scan(&d.Key, &d.DocID, &d.Issuing, &d.Kind, &d.Start, &d.Validity, &d.Status, &d.StatusStart, &d.ApplicantID, &d.ApplicantName, &d.ApplicantCC, &d.ApplicantReg, &d.Authority, &d.AuthorityID, &coal, &d.CHPTier, &d.VanishedOn) d.CoalitionMfr = coal == 1 return d, err } func (a *API) docs(w http.ResponseWriter, r *http.Request) { q := r.URL.Query() var where []string var args []any add := func(cond string, v ...any) { where = append(where, cond); args = append(args, v...) } if v := q.Get("brand"); v != "" { add(`d.doc_key IN (SELECT doc_key FROM doc_brands WHERE brand=?)`, v) } if v := q.Get("hs6"); v != "" { add(`d.doc_key IN (SELECT doc_key FROM doc_hs WHERE hs6=?)`, v) } if v := q.Get("tier"); v != "" { add(`d.chp_tier=?`, v) } if q.Get("chpl") == "1" { add(`d.chp_tier IS NOT NULL`) } if v := q.Get("applicant"); v != "" { add(`ap.public_id=?`, v) } if v := q.Get("mfr_country"); v != "" { add(`d.doc_key IN (SELECT dm.doc_key FROM doc_manufacturers dm JOIN manufacturers m ON m.mfr_key=dm.mfr_key WHERE m.country=?)`, strings.ToUpper(v)) } if v := q.Get("issuing"); v != "" { add(`d.issuing_country=?`, strings.ToUpper(v)) } if v := q.Get("status"); v != "" { add(`d.status_code=?`, v) } if v := q.Get("from"); v != "" { add(`d.start_date>=?`, v) } if v := q.Get("to"); v != "" { add(`d.start_date<=?`, v) } if q.Get("coalition") == "1" { add(`d.coalition_mfr=1`) } if q.Get("vanished") == "1" { add(`d.vanished_on IS NOT NULL`) } if v := q.Get("doc_id"); v != "" { add(`d.doc_id=?`, v) } if v := q.Get("region"); v != "" { add(`ap.region=?`, v) } sqlq := docSelect if len(where) > 0 { sqlq += " WHERE " + strings.Join(where, " AND ") } limit := clamp(atoi(q.Get("limit"), 100), 1, 1000) offset := max(atoi(q.Get("offset"), 0), 0) sqlq += ` ORDER BY d.start_date DESC, d.doc_key LIMIT ? OFFSET ?` args = append(args, limit, offset) rows, err := a.db.QueryContext(r.Context(), sqlq, args...) if err != nil { a.fail(w, err) return } defer rows.Close() out := []DocRow{} for rows.Next() { d, err := scanDoc(rows) if err != nil { a.fail(w, err) return } out = append(out, d) } if err := rows.Err(); err != nil { a.fail(w, err) return } for i := range out { a.decorate(r.Context(), &out[i]) } writeJSON(w, map[string]any{"limit": limit, "offset": offset, "docs": out}) } func (a *API) decorate(ctx context.Context, d *DocRow) { if rows, err := a.db.QueryContext(ctx, `SELECT CASE WHEN m.natural_person=1 THEN '(natural person)' ELSE m.name END||' ('||COALESCE(m.country,'?')||')' FROM doc_manufacturers dm JOIN manufacturers m ON m.mfr_key=dm.mfr_key WHERE dm.doc_key=?`, d.Key); err == nil { for rows.Next() { var s string if rows.Scan(&s) == nil { d.Manufacturers = append(d.Manufacturers, s) } } rows.Close() } if rows, err := a.db.QueryContext(ctx, `SELECT DISTINCT brand FROM doc_brands WHERE doc_key=? ORDER BY brand`, d.Key); err == nil { for rows.Next() { var s string if rows.Scan(&s) == nil { d.Brands = append(d.Brands, s) } } rows.Close() } } func (a *API) doc(w http.ResponseWriter, r *http.Request) { ctx := r.Context() key := chi.URLParam(r, "key") d, err := scanDoc(a.db.QueryRowContext(ctx, docSelect+` WHERE d.doc_key=?`, key)) if err == sql.ErrNoRows { http.Error(w, "not found", http.StatusNotFound) return } if err != nil { a.fail(w, err) return } a.decorate(ctx, &d) out := map[string]any{"doc": d} out["products"] = a.rows(ctx, `SELECT idx, name, trade_names, text FROM doc_products WHERE doc_key=? ORDER BY idx`, key) out["hs"] = a.rows(ctx, `SELECT h.hs, h.hs6, c.tier AS chp_tier FROM doc_hs h LEFT JOIN chpl c ON c.hs6=h.hs6 WHERE h.doc_key=?`, key) out["evidence"] = a.rows(ctx, `SELECT lab_name, accreditation, evidence_id, evidence_date, evidence_name FROM doc_labs WHERE doc_key=? ORDER BY idx`, key) out["events"] = a.rows(ctx, `SELECT day, event, status_from, status_to, source FROM events WHERE doc_key=? ORDER BY id`, key) writeJSON(w, out) } func (a *API) applicant(w http.ResponseWriter, r *http.Request) { ctx := r.Context() id := chi.URLParam(r, "id") var key string if err := a.db.QueryRowContext(ctx, `SELECT applicant_key FROM applicants WHERE public_id=?`, id).Scan(&key); err != nil { http.Error(w, "not found", http.StatusNotFound) return } out := map[string]any{} if info := a.rows(ctx, `SELECT public_id, country, CASE WHEN natural_person=1 THEN NULL ELSE name END AS name, natural_person, id_kind, CASE WHEN natural_person=1 THEN NULL ELSE id_value END AS id_value, region, CASE WHEN natural_person=1 THEN NULL ELSE address END AS address FROM applicants WHERE applicant_key=?`, key); len(info) > 0 { out["applicant"] = info[0] } out["by_year"] = a.rows(ctx, `SELECT substr(start_date,1,4) AS year, COUNT(*) AS docs, SUM(coalition_mfr) AS coalition_docs, SUM(chp_tier IS NOT NULL) AS chpl_docs FROM docs WHERE applicant_key=? GROUP BY 1 ORDER BY 1`, key) out["brands"] = a.rows(ctx, `SELECT b.brand, COUNT(DISTINCT b.doc_key) AS docs, MIN(d.start_date) AS first, MAX(d.start_date) AS last FROM doc_brands b JOIN docs d ON d.doc_key=b.doc_key WHERE d.applicant_key=? GROUP BY 1 ORDER BY 2 DESC LIMIT 100`, key) out["manufacturer_countries"] = a.rows(ctx, `SELECT m.country, COUNT(DISTINCT d.doc_key) AS docs FROM docs d JOIN doc_manufacturers dm ON dm.doc_key=d.doc_key JOIN manufacturers m ON m.mfr_key=dm.mfr_key WHERE d.applicant_key=? GROUP BY 1 ORDER BY 2 DESC`, key) out["issuing_countries"] = a.rows(ctx, `SELECT issuing_country, COUNT(*) AS docs FROM docs WHERE applicant_key=? GROUP BY 1 ORDER BY 2 DESC`, key) // Applicants that share a phone, e-mail or website with this one. out["shared_contacts"] = a.rows(ctx, `SELECT ap.public_id, ap.country, CASE WHEN ap.natural_person=1 THEN NULL ELSE ap.name END AS name, COUNT(DISTINCT c2.hash) AS shared FROM contacts c1 JOIN contacts c2 ON c2.hash=c1.hash AND c2.owner_kind='applicant' AND c2.owner_key<>c1.owner_key JOIN applicants ap ON ap.applicant_key=c2.owner_key WHERE c1.owner_kind='applicant' AND c1.owner_key=? GROUP BY 1,2,3 ORDER BY 4 DESC LIMIT 100`, key) writeJSON(w, out) } func (a *API) brands(w http.ResponseWriter, r *http.Request) { from := r.URL.Query().Get("from") if from == "" { from = "2022-02-24" } writeJSON(w, a.rows(r.Context(), `SELECT b.brand, br.country, br.sector, COUNT(DISTINCT b.doc_key) AS docs, COUNT(DISTINCT d.applicant_key) AS applicants, SUM(d.issuing_country='RU') AS issued_ru, SUM(d.issuing_country IN ('KG','AM','KZ','BY')) AS issued_other_eaeu FROM doc_brands b JOIN docs d ON d.doc_key=b.doc_key LEFT JOIN brands br ON br.brand=b.brand WHERE d.start_date>=? GROUP BY 1,2,3 ORDER BY docs DESC`, from)) } func (a *API) events(w http.ResponseWriter, r *http.Request) { q := r.URL.Query() since := q.Get("since") if since == "" { since = time.Now().AddDate(0, 0, -30).Format("2006-01-02") } ev := q.Get("event") limit := clamp(atoi(q.Get("limit"), 500), 1, 5000) writeJSON(w, a.rows(r.Context(), `SELECT e.day, e.event, e.status_from, e.status_to, d.doc_id, d.doc_key, d.issuing_country, d.coalition_mfr, d.chp_tier FROM events e JOIN docs d ON d.doc_key=e.doc_key WHERE e.day>=? AND (?='' OR e.event=?) ORDER BY e.day DESC, e.id DESC LIMIT ?`, since, ev, ev, limit)) } func (a *API) rows(ctx context.Context, q string, args ...any) []map[string]any { rows, err := a.db.QueryContext(ctx, q, args...) if err != nil { a.log.Error("query", "err", err) return []map[string]any{} } defer rows.Close() cols, _ := rows.Columns() out := []map[string]any{} for rows.Next() { vals := make([]any, len(cols)) ptrs := make([]any, len(cols)) for i := range vals { ptrs[i] = &vals[i] } if err := rows.Scan(ptrs...); err != nil { a.log.Error("scan", "err", err) return out } m := make(map[string]any, len(cols)) for i, c := range cols { if b, ok := vals[i].([]byte); ok { vals[i] = string(b) } m[c] = vals[i] } out = append(out, m) } return out } func (a *API) fail(w http.ResponseWriter, err error) { a.log.Error("request", "err", err) http.Error(w, "internal error", http.StatusInternalServerError) } func writeJSON(w http.ResponseWriter, v any) { w.Header().Set("Content-Type", "application/json; charset=utf-8") enc := json.NewEncoder(w) enc.SetEscapeHTML(false) enc.Encode(v) } func atoi(s string, def int) int { if n, err := strconv.Atoi(s); err == nil { return n } return def } func clamp(n, lo, hi int) int { return max(lo, min(n, hi)) }