database

package
v0.1.0 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Apr 7, 2026 License: MIT Imports: 18 Imported by: 0

Documentation

Overview

Package database provides SQLite database access for the wherehouse inventory system.

Index

Constants

View Source 
const (
	// DefaultBusyTimeout is the default timeout in milliseconds for database locks.
	DefaultBusyTimeout = 30000 // 30 seconds

	// DefaultBaseRetryDelay is the base delay for exponential backoff retries.
	DefaultBaseRetryDelay = 100 * time.Millisecond
)

Variables

View Source 
var (
	// ErrDatabasePathRequired is returned when a database path is not provided.
	ErrDatabasePathRequired = errors.New("database path is required")

	// ErrEventNotFound is returned when an event is not found.
	ErrEventNotFound = errors.New("event not found")

	// ErrLocationNotFound is returned when a location is not found.
	ErrLocationNotFound = errors.New("location not found")

	// ErrItemNotFound is returned when an item is not found.
	ErrItemNotFound = errors.New("item not found")
)

Common database errors.

Functions

func CanonicalizeString 

func CanonicalizeString(s string) string

CanonicalizeString converts a display name to canonical form. Rules: lowercase, trim whitespace, collapse runs to '_', normalize separators to '_'.

func ValidateNoColonInName 

func ValidateNoColonInName(name string) error

ValidateNoColonInName checks if a name contains a colon character. Colons are reserved for the selector syntax (LOCATION:ITEM). Returns an error if the name contains a colon.

Types

type AmbiguousLocationError 

type AmbiguousLocationError struct {
	CanonicalName string
	MatchingIDs   []string
}

AmbiguousLocationError is returned when multiple locations match a canonical name.

func (*AmbiguousLocationError) Error 

func (e *AmbiguousLocationError) Error() string

type Config 

type Config struct {
	// Path to the SQLite database file
	Path string
	// BusyTimeout in milliseconds for locked database retries
	BusyTimeout int
	// AutoMigrate runs migrations on Open if true
	AutoMigrate bool
}

Config holds database configuration options.

func DefaultConfig 

func DefaultConfig() Config

DefaultConfig returns a Config with sensible defaults.

type Database 

type Database struct {
	// contains filtered or unexported fields
}

Database wraps the SQLite database connection and provides methods for database operations.

func Open 

func Open(cfg Config) (*Database, error)

Open opens a connection to the SQLite database and configures it for use. If AutoMigrate is enabled in the config, it runs pending migrations.

func (*Database) AppendEvent 

func (d *Database) AppendEvent(
	ctx context.Context,
	eventType EventType,
	actorUserID string,
	payload any,
	note string,
) (int64, error)

AppendEvent creates a new event in the event log and immediately applies it to the projections within a single atomic transaction.

This is the primary method for recording new domain events from command code. Use insertEvent directly only for replay/seed scenarios where you want to batch-insert events before processing them with ProcessEvent.

func (*Database) Close 

func (d *Database) Close() error

Close closes the database connection.

func (*Database) CreateItem 

func (d *Database) CreateItem(
	ctx context.Context,
	itemID, displayName, locationID string,
	eventID int64,
	timestamp string,
) error

CreateItem creates a new item projection entry.

func (*Database) CreateLocation 

func (d *Database) CreateLocation(
	ctx context.Context,
	locationID, displayName string,
	parentID *string,
	isSystem bool,
	_ int64,
	timestamp string,
) error

CreateLocation creates a new location projection entry.

func (*Database) DB 

func (d *Database) DB() *sql.DB

DB returns the underlying sql.DB for direct access if needed. Use with caution - prefer using the Database methods when possible.

func (*Database) DeleteLocation 

func (d *Database) DeleteLocation(ctx context.Context, locationID string) error

DeleteLocation removes a location from the projection.

func (*Database) DetectLocationCycle 

func (d *Database) DetectLocationCycle(ctx context.Context, locationID string, newParentID *string) error

DetectLocationCycle checks if setting a location's parent to newParentID would create a cycle. A cycle occurs when a location would become its own ancestor. Returns ErrLocationCycle if a cycle would be created.

func (*Database) ExecInTransaction 

func (d *Database) ExecInTransaction(ctx context.Context, fn func(*sql.Tx) error) error

ExecInTransaction executes a function within a transaction. If the function returns an error, the transaction is rolled back. Otherwise, the transaction is committed.

func (*Database) GetAllEvents 

func (d *Database) GetAllEvents(ctx context.Context) ([]*Event, error)

GetAllEvents retrieves all events ordered by event_id (for replay).

func (*Database) GetAllItems 

func (d *Database) GetAllItems(ctx context.Context) ([]*Item, error)

GetAllItems retrieves all items from the projection table. Used by migration operations that need to enumerate all entity IDs.

func (*Database) GetAllLocations 

func (d *Database) GetAllLocations(ctx context.Context) ([]*Location, error)

GetAllLocations retrieves all locations from the projection table. Used by migration operations that need to enumerate all entity IDs.

func (*Database) GetEventByID 

func (d *Database) GetEventByID(ctx context.Context, eventID int64) (*Event, error)

GetEventByID retrieves a single event by its event_id.

func (*Database) GetEventsAfter 

func (d *Database) GetEventsAfter(ctx context.Context, afterEventID int64) ([]*Event, error)

GetEventsAfter retrieves all events after a specific event_id (for incremental replay).

func (*Database) GetEventsByEntity 

func (d *Database) GetEventsByEntity(ctx context.Context, itemID, locationID *string) ([]*Event, error)

GetEventsByEntity retrieves all events for a specific entity (item or location). Exactly one of itemID or locationID must be non-nil.

func (*Database) GetEventsByType 

func (d *Database) GetEventsByType(ctx context.Context, eventType EventType) ([]*Event, error)

GetEventsByType retrieves all events of a specific type, ordered by event_id.

func (*Database) GetItem 

func (d *Database) GetItem(ctx context.Context, itemID string) (*Item, error)

GetItem retrieves an item by its ID.

func (*Database) GetItemLoanedInfo 

func (d *Database) GetItemLoanedInfo(ctx context.Context, itemID string) (*LoanedInfo, error)

GetItemLoanedInfo retrieves the loaned_to value and timestamp from the latest item.loaned event. This is used by the find command to display loan information for items in the Loaned location. Returns ErrEventNotFound if no item.loaned event exists for this item.

func (*Database) GetItemsByCanonicalName 

func (d *Database) GetItemsByCanonicalName(ctx context.Context, canonicalName string) ([]*Item, error)

GetItemsByCanonicalName retrieves all non-removed items with a specific canonical name. Returns a slice because canonical names are not unique across locations. Items in the Removed system location are excluded.

func (*Database) GetItemsByLocation 

func (d *Database) GetItemsByLocation(ctx context.Context, locationID string) ([]*Item, error)

GetItemsByLocation retrieves all items in a specific location. Items in the Removed system location are excluded from all results.

func (*Database) GetLocation 

func (d *Database) GetLocation(ctx context.Context, locationID string) (*Location, error)

GetLocation retrieves a location by its ID.

func (*Database) GetLocationByCanonicalName 

func (d *Database) GetLocationByCanonicalName(ctx context.Context, canonicalName string) (*Location, error)

GetLocationByCanonicalName retrieves a location by its canonical name. Returns ErrLocationNotFound if no location matches. Returns AmbiguousLocationError if multiple locations match (violates global uniqueness).

func (*Database) GetLocationChildren 

func (d *Database) GetLocationChildren(ctx context.Context, parentID string) ([]*Location, error)

GetLocationChildren retrieves all child locations of a parent.

func (*Database) GetMetadata 

func (d *Database) GetMetadata(ctx context.Context, key string) (string, error)

GetMetadata retrieves a value from schema_metadata by key.

func (*Database) GetMigrationVersion 

func (d *Database) GetMigrationVersion() (uint, bool, error)

GetMigrationVersion returns the current migration version and dirty state.

func (*Database) GetRootLocations 

func (d *Database) GetRootLocations(ctx context.Context) ([]*Location, error)

GetRootLocations retrieves all locations with no parent (top-level), ordered by display_name. Includes system locations (Missing, Borrowed) but excludes the Removed location, which is a tombstone not shown in listings.

func (*Database) GetSystemLocationIDs 

func (d *Database) GetSystemLocationIDs(ctx context.Context) (string, string, string, string, error)

GetSystemLocationIDs retrieves the UUIDs of the Missing, Borrowed, Loaned, and Removed system locations. Returns (missingID, borrowedID, loanedID, removedID, error).

func (*Database) ProcessEvent 

func (d *Database) ProcessEvent(ctx context.Context, event *Event) error

ProcessEvent applies a single event to the projections. This is the primary entry point for event replay and is wrapped in a transaction.

func (*Database) RebuildProjections 

func (d *Database) RebuildProjections(ctx context.Context) error

RebuildProjections drops and rebuilds all projection tables from the event log. This is an atomic operation - either all projections are rebuilt successfully or none are. The rebuild happens within a single transaction to ensure consistency.

func (*Database) ReplayEventsFrom 

func (d *Database) ReplayEventsFrom(ctx context.Context, fromEventID int64) error

ReplayEventsFrom replays events starting from a specific event_id. This is used for incremental projection updates after a known checkpoint. The replay happens within a transaction to ensure consistency.

func (*Database) RollbackMigration 

func (d *Database) RollbackMigration() error

RollbackMigration rolls back to the previous migration version. Use with caution - this may result in data loss.

func (*Database) RunMigrations 

func (d *Database) RunMigrations() error

RunMigrations runs all pending database migrations. This is called automatically during Open() if AutoMigrate is enabled.

func (*Database) ScryItem 

func (d *Database) ScryItem(ctx context.Context, item *Item) (*ScryResult, error)

ScryItem returns ranked location suggestions for a missing item. item is the already-fetched Item record; callers should not fetch it again. Returns a ScryResult with all suggestion categories populated. The HomeLocation field is guaranteed non-nil if the item has any events (it always does).

func (*Database) SearchByName 

func (d *Database) SearchByName(
	ctx context.Context,
	searchTerm string,
	limit int,
) ([]*SearchResult, error)

SearchByName searches for items and locations by canonical name using substring matching. Results are ranked by Levenshtein distance (exact matches first) and limited to the specified count. A limit of 0 means unlimited results.

func (*Database) SetMetadata 

func (d *Database) SetMetadata(ctx context.Context, key, value string) error

SetMetadata sets a value in schema_metadata.

func (*Database) SetMigrationVersion 

func (d *Database) SetMigrationVersion(ctx context.Context, version uint, dirty bool) error

SetMigrationVersion manually sets the migration version. This is primarily for testing purposes.

func (*Database) UpdateItem 

func (d *Database) UpdateItem(
	ctx context.Context,
	itemID string,
	updates map[string]any,
	eventID int64,
	timestamp string,
) error

UpdateItem updates an item's fields.

func (*Database) UpdateLocation 

func (d *Database) UpdateLocation(
	ctx context.Context,
	locationID string,
	updates map[string]any,
	timestamp string,
) error

UpdateLocation updates a location's basic fields (not path-related).

func (*Database) ValidateFromLocation 

func (d *Database) ValidateFromLocation(ctx context.Context, itemID, expectedFromLocationID string) error

ValidateFromLocation verifies that an item or location is in the expected location before an event. This is critical for detecting projection corruption and concurrent modifications. Returns ErrInvalidFromLocation if the current location doesn't match the expected from_location_id.

func (*Database) ValidateFromParent 

func (d *Database) ValidateFromParent(ctx context.Context, locationID string, expectedFromParentID *string) error

ValidateFromParent verifies that a location's parent matches the expected from_parent_id. This is critical for location.reparented events to detect projection corruption. Returns an error if the current parent doesn't match the expected from_parent_id.

func (*Database) ValidateItemExists 

func (d *Database) ValidateItemExists(ctx context.Context, itemID string) error

ValidateItemExists checks if an item exists. Returns ErrItemNotFound if item doesn't exist.

func (*Database) ValidateItemLoaned 

func (d *Database) ValidateItemLoaned(ctx context.Context, itemID, fromLocationID, loanedTo string) error

ValidateItemLoaned validates that an item can be loaned. Checks: item exists, from_location matches projection, loaned_to is non-empty. Re-loaning is allowed (item can be loaned from Loaned location). Returns an error if validation fails.

func (*Database) ValidateLocationEmpty 

func (d *Database) ValidateLocationEmpty(ctx context.Context, locationID string) error

ValidateLocationEmpty checks if a location has no children and no items. This is required before deleting a location. Returns an error if the location has children or items.

func (*Database) ValidateLocationExists 

func (d *Database) ValidateLocationExists(ctx context.Context, locationID string) error

ValidateLocationExists checks if a location exists. Returns ErrLocationNotFound if location doesn't exist.

func (*Database) ValidateSystemLocation 

func (d *Database) ValidateSystemLocation(ctx context.Context, locationID string) error

ValidateSystemLocation checks if a location is a system location. System locations (Missing, Borrowed) cannot be modified or deleted. Returns an error if the location is a system location.

func (*Database) ValidateUniqueItemName 

func (d *Database) ValidateUniqueItemName(
	ctx context.Context,
	locationID, canonicalName string,
	excludeItemID *string,
) error

ValidateUniqueItemName checks if an item's canonical name is unique within its location. Item names are unique per location (not globally unique like locations). Returns ErrDuplicateItem if an item with this canonical name already exists in the location.

func (*Database) ValidateUniqueLocationName 

func (d *Database) ValidateUniqueLocationName(
	ctx context.Context,
	canonicalName string,
	excludeLocationID *string,
) error

ValidateUniqueLocationName checks if a location's canonical name is unique within its parent. Location names must be globally unique according to the schema's UNIQUE constraint. Returns ErrDuplicateLocation if a location with this canonical name already exists.

func (*Database) WithRetry 

func (d *Database) WithRetry(ctx context.Context, fn func() error) error

WithRetry executes a function with exponential backoff retry logic. This is useful for handling SQLite BUSY errors on write operations.

type DuplicateItemError 

type DuplicateItemError struct {
	CanonicalName string
	LocationID    string
	ExistingID    string
}

DuplicateItemError is returned when an item with the same canonical name already exists in a location.

func (*DuplicateItemError) Error 

func (e *DuplicateItemError) Error() string

type DuplicateLocationError 

type DuplicateLocationError struct {
	CanonicalName string
	ParentID      *string
	ExistingID    string
}

DuplicateLocationError is returned when a location with the same canonical name and parent already exists.

func (*DuplicateLocationError) Error 

func (e *DuplicateLocationError) Error() string

type Event 

type Event struct {
	EventID      int64
	EventType    EventType
	TimestampUTC string
	ActorUserID  string
	Payload      json.RawMessage
	Note         *string
	ItemID       *string
	LocationID   *string
}

Event represents a single event from the event log.

type EventType 

type EventType int

EventType is an enumeration of event types. 

const (
	// ItemCreatedEvent records a new item being added to the inventory at a location.
	ItemCreatedEvent EventType = iota + 1 // item.created
	// ItemMovedEvent records an item being relocated from one storage location to another.
	ItemMovedEvent // item.moved
	// ItemMissingEvent records that an item could not be found at its expected location.
	ItemMissingEvent // item.missing
	// ItemBorrowedEvent records an item being taken by someone with the intent to return it.
	ItemBorrowedEvent // item.borrowed
	// ItemLoanedEvent records an item being lent out to someone else.
	ItemLoanedEvent // item.loaned
	// ItemFoundEvent records a previously missing item being located again.
	ItemFoundEvent // item.found
	// ItemRemovedEvent records an item being moved to the Removed system location.
	ItemRemovedEvent // item.removed

	// LocationCreatedEvent records a new storage location being added.
	LocationCreatedEvent // location.created
	// LocationRenamedEvent records a location's display name being changed.
	LocationRenamedEvent // location.renamed
	// LocationMovedEvent records a location being reparented under a different location.
	LocationMovedEvent // location.reparented
	// LocationRemovedEvent records a location being moved to the Removed system location.
	LocationRemovedEvent // location.removed

)

func ParseEventType 

func ParseEventType(s string) (EventType, error)

ParseEventType converts a string representation to an EventType constant. Returns an error for unrecognized strings to fail loudly on mismatch.

func (*EventType) Scan 

func (e *EventType) Scan(src any) error

Scan implements sql.Scanner, reading a string from the database and converting back to the typed EventType constant.

func (EventType) String 

func (i EventType) String() string

func (EventType) Value 

func (e EventType) Value() (driver.Value, error)

Value implements driver.Valuer, persisting EventType as its string representation.

type InvalidFromLocationError 

type InvalidFromLocationError struct {
	ItemID           string
	ExpectedLocation string
	ActualLocation   string
}

InvalidFromLocationError is returned when the from_location_id doesn't match the current location.

func (*InvalidFromLocationError) Error 

func (e *InvalidFromLocationError) Error() string

type Item 

type Item struct {
	ItemID               string
	DisplayName          string
	CanonicalName        string
	LocationID           string
	InTemporaryUse       bool
	TempOriginLocationID *string
	LastEventID          int64
	UpdatedAt            string
}

Item represents an item in the projection.

type LoanedInfo 

type LoanedInfo struct {
	LoanedTo    string
	LoanedAt    time.Time
	EventID     int64
	ActorUserID string
}

LoanedInfo represents information about a loaned item.

type Location 

type Location struct {
	LocationID        string
	DisplayName       string
	CanonicalName     string
	ParentID          *string
	FullPathDisplay   string
	FullPathCanonical string
	Depth             int
	IsSystem          bool
	UpdatedAt         string
}

Location represents a location in the projection.

type LocationCycleError 

type LocationCycleError struct {
	LocationID string
	ParentID   string
	Cycle      []string // The cycle path for debugging
}

LocationCycleError is returned when a location parent change would create a cycle.

func (*LocationCycleError) Error 

func (e *LocationCycleError) Error() string

type LocationInfo 

type LocationInfo struct {
	LocationID      string
	DisplayName     string
	FullPathDisplay string
	IsSystem        bool
}

LocationInfo contains basic information about a location.

type ScoredLocation 

type ScoredLocation struct {
	Location *LocationInfo

	// Occurrences is the count of times this location appeared in history (categories 2 and 3).
	// For similar-item results, this is 0.
	// This field is used for ranking; displayed only in --verbose mode.
	Occurrences int

	// SimilarItemName is the canonical name of the similar item (category 4 only).
	// Empty for history-based results.
	SimilarItemName string

	// SimilarItemDisplayName is the display name of the similar item (category 4 only).
	// Empty for history-based results.
	SimilarItemDisplayName string

	// LevenshteinDistance is the distance from the target canonical name (category 4 only).
	// 0 for history-based results.
	LevenshteinDistance int
}

ScoredLocation is a location with ranking metadata.

type ScryResult 

type ScryResult struct {
	ItemID        string
	DisplayName   string
	CanonicalName string

	// HomeLocation is the most authoritative suggestion.
	// Derived from temp_origin_location_id (projection) or item.created event location_id.
	// This is NEVER nil because every item was created in some location.
	HomeLocation *LocationInfo

	// FoundLocations are locations where the item was physically found before (item.found events).
	// Ordered by occurrence count descending, then most recent event_id descending.
	// Occurrences field is populated; displayed only in verbose mode.
	FoundLocations []*ScoredLocation

	// TempUseLocations are locations the item was taken to temporarily (item.moved, move_type=temporary_use).
	// Ordered by occurrence count descending, then most recent event_id descending.
	// Occurrences field is populated; displayed only in verbose mode.
	TempUseLocations []*ScoredLocation

	// SimilarItemLocations are current locations of similarly-named items (Levenshtein distance <= 3).
	// Ordered by distance ascending.
	SimilarItemLocations []*ScoredLocation
}

ScryResult contains ranked location suggestions for a missing item.

type SearchResult 

type SearchResult struct {
	Type              string // "item" or "location"
	ItemID            *string
	LocationID        *string
	DisplayName       string
	CanonicalName     string
	CurrentLocation   *LocationInfo // For items only
	FullPath          string        // Display path
	FullPathCanonical string
	IsSystem          bool // For locations
	InTemporaryUse    bool // For items
	IsMissing         bool // Derived from location_id
	IsBorrowed        bool // Derived from location_id
	IsLoaned          bool // Derived from location_id
	IsRemoved         bool // Derived from location_id
	// LastNonSystemLocation is populated for missing/borrowed items
	LastNonSystemLocation *LocationInfo
	LevenshteinDistance   int // For result sorting
}

SearchResult represents a single search result (item or location).

Source Files

View all Source files

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.