indexer/store/store.go

// Package store provides durable persistence of scraped projects in SQLite.
//
// The database is the system of record: scrapers write discovered projects
// here, and a search index (e.g. Typesense) can be built or rebuilt from it
// without re-scraping the forges. Projects are stored as one row each, with
// the URL as the natural key so re-running the scrape upserts in place.
package store

import (
	"database/sql"
	"encoding/json"
	"fmt"
	"time"

	_ "modernc.org/sqlite"

	"git.xengi.de/ffd/indexer/scraper"
)

const schema = `
CREATE TABLE IF NOT EXISTS projects (
	url                TEXT PRIMARY KEY,
	name               TEXT NOT NULL,
	description        TEXT NOT NULL DEFAULT '',
	topics             TEXT NOT NULL DEFAULT '[]',   -- JSON array
	languages          TEXT NOT NULL DEFAULT '[]',   -- JSON array
	licenses           TEXT NOT NULL DEFAULT '[]',   -- JSON array
	open_issues         INTEGER NOT NULL DEFAULT 0,
	open_prs            INTEGER NOT NULL DEFAULT 0,
	latest_commit       INTEGER NOT NULL DEFAULT 0,   -- unix epoch seconds (UTC)
	latest_release_name  TEXT   NOT NULL DEFAULT '',
	latest_release_date INTEGER NOT NULL DEFAULT 0,   -- unix epoch seconds (UTC)
	readme              TEXT   NOT NULL DEFAULT '',
	uses_ai             INTEGER NOT NULL DEFAULT 0,
	scraped_at          INTEGER NOT NULL DEFAULT 0    -- unix epoch seconds (UTC)
);
`

// Store wraps a SQLite database holding scraped projects.
type Store struct {
	db *sql.DB
}

// Open opens (creating if needed) the SQLite database at path.
func Open(path string) (*Store, error) {
	db, err := sql.Open("sqlite", path)
	if err != nil {
		return nil, fmt.Errorf("open sqlite db %q: %w", path, err)
	}
	if _, err := db.Exec(schema); err != nil {
		_ = db.Close()
		return nil, fmt.Errorf("create schema: %w", err)
	}
	return &Store{db: db}, nil
}

// Close closes the underlying database.
func (s *Store) Close() error {
	return s.db.Close()
}

const upsertSQL = `
INSERT INTO projects (
	url, name, description, topics, languages, licenses,
	open_issues, open_prs, latest_commit,
	latest_release_name, latest_release_date,
	readme, uses_ai, scraped_at
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
ON CONFLICT(url) DO UPDATE SET
	name = excluded.name,
	description = excluded.description,
	topics = excluded.topics,
	languages = excluded.languages,
	licenses = excluded.licenses,
	open_issues = excluded.open_issues,
	open_prs = excluded.open_prs,
	latest_commit = excluded.latest_commit,
	latest_release_name = excluded.latest_release_name,
	latest_release_date = excluded.latest_release_date,
	readme = excluded.readme,
	uses_ai = excluded.uses_ai,
	scraped_at = excluded.scraped_at;
`

// Upsert inserts or updates a project, keyed on its URL.
func (s *Store) Upsert(p scraper.Project) error {
	topics, err := json.Marshal(p.Topics)
	if err != nil {
		return fmt.Errorf("marshal topics: %w", err)
	}
	langs, err := json.Marshal(p.Languages)
	if err != nil {
		return fmt.Errorf("marshal languages: %w", err)
	}
	licenses, err := json.Marshal(p.Licenses)
	if err != nil {
		return fmt.Errorf("marshal licenses: %w", err)
	}

	releaseDate := timeToUnix(p.LatestRelease.Date)
	latestCommit := timeToUnix(p.LatestCommit)

	_, err = s.db.Exec(upsertSQL,
		p.Url,
		p.Name,
		p.Description,
		string(topics),
		string(langs),
		string(licenses),
		p.OpenIssues,
		p.OpenPRs,
		latestCommit,
		p.LatestRelease.Name,
		releaseDate,
		p.Readme,
		boolToInt(p.UsesAI),
		time.Now().UTC().Unix(),
	)
	return err
}

// All returns every stored project.
func (s *Store) All() ([]scraper.Project, error) {
	rows, err := s.db.Query(`SELECT
		url, name, description, topics, languages, licenses,
		open_issues, open_prs, latest_commit,
		latest_release_name, latest_release_date,
		readme, uses_ai
		FROM projects`)
	if err != nil {
		return nil, err
	}
	defer func() { _ = rows.Close() }()

	var projects []scraper.Project
	for rows.Next() {
		var (
			p            scraper.Project
			topicsJSON   string
			langsJSON    string
			licensesJSON string
			latestCommit int64
			relName      string
			relDate      int64
			usesAI       int
		)
		if err := rows.Scan(
			&p.Url, &p.Name, &p.Description,
			&topicsJSON, &langsJSON, &licensesJSON,
			&p.OpenIssues, &p.OpenPRs, &latestCommit,
			&relName, &relDate,
			&p.Readme, &usesAI,
		); err != nil {
			return nil, err
		}

		p.UsesAI = usesAI != 0
		if err := json.Unmarshal([]byte(topicsJSON), &p.Topics); err != nil {
			return nil, err
		}
		if err := json.Unmarshal([]byte(langsJSON), &p.Languages); err != nil {
			return nil, err
		}
		if err := json.Unmarshal([]byte(licensesJSON), &p.Licenses); err != nil {
			return nil, err
		}
		p.LatestCommit = time.Unix(latestCommit, 0).UTC()
		p.LatestRelease.Name = relName
		p.LatestRelease.Date = time.Unix(relDate, 0).UTC()

		projects = append(projects, p)
	}
	return projects, rows.Err()
}

// timeToUnix returns the Unix epoch seconds for a time, or 0 for the zero
// time (which is stored as a 0/absent value).
func timeToUnix(t time.Time) int64 {
	if t.IsZero() {
		return 0
	}
	return t.Unix()
}

func boolToInt(b bool) int {
	if b {
		return 1
	}
	return 0
}