db

package
v0.3.4 Latest Latest
Warning

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

Go to latest
Published: Sep 6, 2026 License: MIT Imports: 4 Imported by: 0

README

Database package

internal/db defines the persistence contracts used by Syfon. The backend implementations remain separate because SQLite and PostgreSQL use different SQL, connection, locking, and parameter-limit behavior.

Package layout

  • interface.go defines capability interfaces, including ObjectStore, CredentialStore, UsageStore, and the aggregate DatabaseInterface.
  • sqlite/ implements the contracts with SQLite. sqlite.NewSqliteDB initializes and upgrades the runtime schema before it returns.
  • postgres/ implements the contracts with PostgreSQL. postgres.NewPostgresDB initializes and upgrades the runtime schema before it returns.
  • postgres/object_schema.sql contains the object tables and indexes embedded by the PostgreSQL implementation.
  • scripts/init_sqlite.sql and scripts/init_sqlite_db.sh provide a manual SQLite bootstrap helper.
  • At the 2026-09-04 audit baseline, testing.go provides db.NewInMemoryDB for tests by calling the SQLite implementation. The accepted architecture worklist moves this helper to internal/testutils so the contract package does not import a backend.

Production dependency direction is cmd/server and the API and core packages -> internal/db contracts -> sqlite or postgres. The server selects a backend from internal/config in cmd/server/server.go. The current test helper is the exception described above.

Object and access tables

Both runtime backends store the following object data. SQLite creates these tables in internal/db/sqlite/sqlite.go:initSchema. PostgreSQL creates the object tables from internal/db/postgres/object_schema.sql.

drs_object

One row stores the metadata for a canonical object record.

  • id is the text primary key.
  • size, created_time, updated_time, name, version, and description store object metadata.

Core uses the canonical SHA-256 checksum to group and resolve content. The record ID remains the persisted primary key. drs_object_alias maps an alias ID, such as an older UUID, to that canonical record.

drs_object_checksum

This table stores one row for each typed checksum value.

  • object_id references drs_object.id.
  • type stores values such as sha256 or md5.
  • checksum stores the corresponding value.

internal/common.CanonicalSHA256 normalizes SHA-256 values for identity and lookup. The runtime schema includes indexes for ordinary checksum lookup and the normalized SHA-256 identity expression.

drs_object_access_method

This table stores the provider locations for an object.

  • object_id references drs_object.id.
  • url stores a location such as s3://bucket/key.
  • type stores the provider type, such as s3.

Organization and project columns do not belong to this table. Scoped authorization uses drs_object_controlled_access.

drs_object_controlled_access

Each row associates an object with an Arborist-compatible resource path in resource. Core builds a path from an organization and project with common.ResourcePath. API and authz code use the stored resource values when they evaluate access.

drs_object_read_policy

This table stores the per-object public_read flag. The policy row is internal state and is not part of the DRS response.

drs_object_alias

Each alias_id points to one canonical object_id. Object lookup resolves this mapping before loading the canonical row. Deletes and updates apply the current alias rules in each backend.

drs_object_name_alias

This table stores prior or alternate object names. The (object_id, name_alias) pair is unique, and reads return normalized aliases with the object.

Credentials, scopes, and transfer records

s3_credential

The credential_id text column is the primary key. The row also stores bucket, provider, region, access_key, secret_key, and an optional endpoint.

Runtime initialization enforces one credential identity per physical bucket. SQLite uses triggers. PostgreSQL uses a trigger function. A bucket may still have multiple logical project scopes through bucket_scope.

bucket_scope

The (organization, project_id) pair is the primary key. Each row maps a project to a credential_id, physical bucket, and optional path_prefix.

lfs_pending_metadata

This table stores pending LFS metadata by oid, with creation and expiry timestamps. The LFS implementation consumes entries atomically when it verifies an upload.

object_usage and object_usage_event

object_usage stores upload and download counters and their last-event timestamps. object_usage_event stores the event history used by usage reporting.

transfer_attribution_event, access_grant, and provider_transfer_event

These tables store issued-access records, access grant aggregates, and provider transfer records. They include object, checksum, scope, actor, request, range, and reconciliation fields used by the metrics API.

Runtime schema initialization

The application initializes its schema when it creates a database:

  • sqlite.NewSqliteDB opens the configured SQLite file, enables its connection settings, calls initSchema, and runs compatibility upgrades before returning.
  • postgres.NewPostgresDB opens and pings PostgreSQL, loads object_schema.sql, then runs the credential, bucket-scope, LFS, usage, and transfer schema initializers.

The SQLite runtime schema includes drs_object, the access and policy tables, aliases, credentials and scopes, LFS pending metadata, usage tables, transfer attribution tables, access grants, provider transfer events, indexes, and credential uniqueness triggers. Runtime initialization also handles older databases. It adds missing columns, migrates old credential identity shape, removes retired object columns, removes the retired browse index, and backfills access grants.

The PostgreSQL runtime schema has the same logical table groups. Its object DDL lives in postgres/object_schema.sql. The remaining DDL and compatibility statements live in postgres/postgres.go.

Standalone SQLite script

internal/db/scripts/init_sqlite.sql is a manual bootstrap helper used by init_sqlite_db.sh. The application does not read this file during normal startup. The script creates a basic object, access, checksum, credential, bucket-scope, and access-grant schema.

The script is intentionally smaller than the runtime schema. It omits drs_object_read_policy, drs_object_alias, drs_object_name_alias, lfs_pending_metadata, object_usage, object_usage_event, transfer_attribution_event, and provider_transfer_event, along with their runtime indexes and the SQLite credential uniqueness triggers. It also does not run the runtime compatibility upgrades or the access-grant backfill.

Starting Syfon against a database created by the script still runs sqlite.NewSqliteDB, which creates the missing runtime tables and applies its upgrades. The difference between the script and runtime DDL does not show that normal server startup fails. Do not treat the standalone script as the complete runtime schema or as a migration engine. Keep its behavior unchanged when reorganizing files.

Add a database operation

  1. Add the smallest suitable capability method to internal/db/interface.go.
  2. Implement the method in both backend packages.
  3. Add backend-specific tests for SQL, transactions, missing rows, and error mapping.
  4. Update a test stub only when the test needs the new capability.

Keep SQLite and PostgreSQL SQL separate. Use the existing backend tests and the in-memory SQLite constructor for behavior that must match. Run a live PostgreSQL test when a change affects PostgreSQL persistence.

For resource paths, use common.ResourcePath for an organization and project and internal/common.ParseResourcePath when parsing stored paths. Do not add organization or project columns to drs_object_access_method.

Documentation

Index

Constants

This section is empty.

Variables

This section is empty.

Functions

This section is empty.

Types

type BucketVisibilityLister added in v0.2.8

type BucketVisibilityLister interface {
	ListBucketVisibilityRows(ctx context.Context, resources []string, includeUnscoped, restrictToResources bool) ([]models.BucketVisibilityRow, error)
}

type CredentialStore

type CredentialStore interface {
	GetS3Credential(ctx context.Context, bucket string) (*models.S3Credential, error)
	ListS3Credentials(ctx context.Context) ([]models.S3Credential, error)
	SaveS3Credential(ctx context.Context, cred *models.S3Credential) error
	DeleteS3Credential(ctx context.Context, bucket string) error
	CreateBucketScope(ctx context.Context, scope *models.BucketScope) error
	DeleteBucketScope(ctx context.Context, organization, projectID, credentialID, pathPrefix string) error
	GetBucketScope(ctx context.Context, organization, projectID string) (*models.BucketScope, error)
	ListBucketScopes(ctx context.Context) ([]models.BucketScope, error)
}

CredentialStore groups bucket credential and scope management.

type DatabaseInterface

DatabaseInterface defines the full database backend contract.

type FileUsageScopedLister added in v0.2.8

type FileUsageScopedLister interface {
	ListFileUsagePageByScope(ctx context.Context, organization, project string, limit, offset int, inactiveSince *time.Time) ([]models.FileUsage, error)
	ListFileUsagePageByResources(ctx context.Context, resources []string, includeUnscoped bool, limit, offset int, inactiveSince *time.Time) ([]models.FileUsage, error)
	GetFileUsageSummaryByScope(ctx context.Context, organization, project string, inactiveSince *time.Time) (models.FileUsageSummary, error)
	GetFileUsageSummaryByResources(ctx context.Context, resources []string, includeUnscoped bool, inactiveSince *time.Time) (models.FileUsageSummary, error)
	GetProjectRecordSummaryByScope(ctx context.Context, organization, project string) (models.FileUsageSummary, error)
}

type LFSStore

LFSStore is the minimum storage surface needed by the LFS API.

type MetricsStore

type MetricsStore interface {
	ListObjectIDsByScope(ctx context.Context, organization, project string) ([]string, error)
	GetObject(ctx context.Context, id string) (*models.InternalObject, error)
	RecordTransferAttributionEvents(ctx context.Context, events []models.TransferAttributionEvent) error
	RecordProviderTransferEvents(ctx context.Context, events []models.ProviderTransferEvent) error
	ListS3Credentials(ctx context.Context) ([]models.S3Credential, error)
	GetTransferAttributionSummary(ctx context.Context, filter models.TransferAttributionFilter) (models.TransferAttributionSummary, error)
	GetTransferAttributionBreakdown(ctx context.Context, filter models.TransferAttributionFilter, groupBy string) ([]models.TransferAttributionBreakdown, error)
	GetFileUsage(ctx context.Context, objectID string) (*models.FileUsage, error)
	ListFileUsageByObjectIDs(ctx context.Context, ids []string) ([]models.FileUsage, error)
	ListFileUsage(ctx context.Context, limit, offset int, inactiveSince *time.Time) ([]models.FileUsage, error)
	GetFileUsageSummary(ctx context.Context, inactiveSince *time.Time) (models.FileUsageSummary, error)
}

MetricsStore is the minimum storage surface needed by the metrics API.

type ObjectAuthorizedLister added in v0.2.8

type ObjectAuthorizedLister interface {
	ListObjectIDsByScopeAndResources(ctx context.Context, organization, project string, resources []string, restrictToResources bool) ([]string, error)
	ListObjectIDsByChecksumsAndResources(ctx context.Context, checksums []string, resources []string, includeUnscoped, restrictToResources bool) (map[string][]string, error)
}

type ObjectChecksumPageLister added in v0.2.8

type ObjectChecksumPageLister interface {
	ListObjectIDsPageByChecksum(ctx context.Context, checksum, checksumType, organization, project, startAfter string, limit, offset int, resources []string, includeUnscoped, restrictToResources bool) ([]string, error)
}

type ObjectIDPageLister added in v0.2.8

type ObjectIDPageLister interface {
	ListObjectIDsPageByScope(ctx context.Context, organization, project, startAfter string, limit, offset int) ([]string, error)
	ListObjectIDsPageByResources(ctx context.Context, resources []string, includeUnscoped bool, startAfter string, limit, offset int) ([]string, error)
}

type ObjectIDResourceLister added in v0.2.8

type ObjectIDResourceLister interface {
	ListObjectIDsByResources(ctx context.Context, resources []string, includeUnscoped bool) ([]string, error)
}

type ObjectStore

type ObjectStore interface {
	GetObject(ctx context.Context, id string) (*models.InternalObject, error)
	DeleteObject(ctx context.Context, id string) error
	DeleteObjectAlias(ctx context.Context, aliasID string) error
	CreateObject(ctx context.Context, obj *models.InternalObject) error
	GetObjectsByChecksum(ctx context.Context, checksum string) ([]models.InternalObject, error)
	GetObjectsByChecksums(ctx context.Context, checksums []string) (map[string][]models.InternalObject, error)
	ListScopedObjectIDsByChecksums(ctx context.Context, organization, project string, checksums []string) (map[string][]string, error)
	ListObjectIDsByScope(ctx context.Context, organization, project string) ([]string, error)
	CreateObjectAlias(ctx context.Context, aliasID, canonicalObjectID string) error
	ResolveObjectAlias(ctx context.Context, aliasID string) (string, error)
	GetBulkObjects(ctx context.Context, ids []string) ([]models.InternalObject, error)
	BulkDeleteObjects(ctx context.Context, ids []string) error
	RegisterObjects(ctx context.Context, objects []models.InternalObject) error
	ReplaceObjects(ctx context.Context, objects []models.InternalObject) error
	UpdateObjectAccessMethods(ctx context.Context, objectID string, accessMethods []drs.AccessMethod) error
	BulkUpdateAccessMethods(ctx context.Context, updates map[string][]drs.AccessMethod) error
	RemoveObjectControlledAccess(ctx context.Context, objectID, resource string) error
	RemoveObjectControlledAccessBulk(ctx context.Context, objectIDs []string, resource string) (int, error)
}

ObjectStore groups the object lifecycle and lookup capabilities used by the API layers.

type ObjectURLPageLister added in v0.2.8

type ObjectURLPageLister interface {
	ListObjectIDsPageByURL(ctx context.Context, objectURL, organization, project, startAfter string, limit, offset int, resources []string, includeUnscoped, restrictToResources bool) ([]string, error)
}

type ObjectsAPIServiceDatabase

type ObjectsAPIServiceDatabase interface {
	ServiceInfoStore
	ObjectStore
	CredentialStore
	UsageStore
}

ObjectsAPIServiceDatabase is the storage surface used by the object service package.

type PendingLFSMetaStore

type PendingLFSMetaStore interface {
	SavePendingLFSMeta(ctx context.Context, entries []models.PendingLFSMeta) error
	GetPendingLFSMeta(ctx context.Context, oid string) (*models.PendingLFSMeta, error)
	PopPendingLFSMeta(ctx context.Context, oid string) (*models.PendingLFSMeta, error)
}

PendingLFSMetaStore manages pending LFS metadata.

type SHA256ValidityStore

type SHA256ValidityStore interface {
	GetObjectsByChecksums(ctx context.Context, checksums []string) (map[string][]models.InternalObject, error)
	ListS3Credentials(ctx context.Context) ([]models.S3Credential, error)
}

SHA256ValidityStore is the minimum storage surface needed by the SHA256 validity endpoint.

type ServiceInfoStore

type ServiceInfoStore interface {
	GetServiceInfo(ctx context.Context) (*drs.Service, error)
}

ServiceInfoStore exposes service metadata reads.

type TransferAttributionScopedStore added in v0.2.8

type TransferAttributionScopedStore interface {
	GetTransferAttributionSummaryByResources(ctx context.Context, filter models.TransferAttributionFilter, resources []string) (models.TransferAttributionSummary, error)
	GetTransferAttributionBreakdownByResources(ctx context.Context, filter models.TransferAttributionFilter, groupBy string, resources []string) ([]models.TransferAttributionBreakdown, error)
}

type UsageStore

type UsageStore interface {
	RecordFileUpload(ctx context.Context, objectID string) error
	RecordFileDownload(ctx context.Context, objectID string) error
	RecordTransferAttributionEvents(ctx context.Context, events []models.TransferAttributionEvent) error
	RecordProviderTransferEvents(ctx context.Context, events []models.ProviderTransferEvent) error
	GetTransferAttributionSummary(ctx context.Context, filter models.TransferAttributionFilter) (models.TransferAttributionSummary, error)
	GetTransferAttributionBreakdown(ctx context.Context, filter models.TransferAttributionFilter, groupBy string) ([]models.TransferAttributionBreakdown, error)
	GetFileUsage(ctx context.Context, objectID string) (*models.FileUsage, error)
	ListFileUsageByObjectIDs(ctx context.Context, ids []string) ([]models.FileUsage, error)
	ListFileUsage(ctx context.Context, limit, offset int, inactiveSince *time.Time) ([]models.FileUsage, error)
	GetFileUsageSummary(ctx context.Context, inactiveSince *time.Time) (models.FileUsageSummary, error)
}

UsageStore manages file usage counters and summaries.

Directories

Path Synopsis

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL