Models

models

SQLAlchemy ORM models for Shoreguard.

Base

Bases: DeclarativeBase

Base class for all Shoreguard models.

Gateway

Bases: Base

A registered OpenShell gateway.

Attributes:

Name	Type	Description
id	Mapped[int]	Auto-incremented primary key.
name	Mapped[str]	Unique gateway name (max 253 chars).
endpoint	Mapped[str]	Host:port of the gateway gRPC endpoint.
scheme	Mapped[str]	Connection scheme (https by default).
auth_mode	Mapped[str | None]	Optional authentication mode identifier.
ca_cert	Mapped[bytes | None]	Optional CA certificate bytes for mTLS.
client_cert	Mapped[bytes | None]	Optional client certificate bytes for mTLS.
client_key	Mapped[bytes | None]	Optional client private key bytes for mTLS.
metadata_json	Mapped[str | None]	Optional JSON-encoded metadata blob.
description	Mapped[str | None]	Optional free-text description of the gateway's purpose.
labels_json	Mapped[str | None]	Optional JSON-encoded key-value labels for filtering.
registered_at	Mapped[datetime]	Timestamp when the gateway was registered.
last_seen	Mapped[datetime | None]	Timestamp of the most recent health check.
last_status	Mapped[str]	Last known health status string.

User

Bases: Base

A user account with email/password authentication.

Attributes:

Name	Type	Description
id	Mapped[int]	Auto-incremented primary key.
email	Mapped[str]	Unique email address (max 254 chars).
hashed_password	Mapped[str | None]	Bcrypt-hashed password, or None for invite-only.
role	Mapped[str]	Global role (admin, operator, viewer).
is_active	Mapped[bool]	Whether the account is enabled.
invite_token_hash	Mapped[str | None]	SHA-256 hash of the invite token, if pending.
created_at	Mapped[datetime]	Timestamp when the user was created.
oidc_provider	Mapped[str | None]	Name of the OIDC provider, or None for local accounts.
oidc_sub	Mapped[str | None]	OIDC subject identifier, or None for local accounts.

ServicePrincipal

Bases: Base

A service principal (API key) for programmatic access.

Attributes:

Name	Type	Description
id	Mapped[int]	Auto-incremented primary key.
name	Mapped[str]	Unique human-readable name (max 100 chars).
key_hash	Mapped[str]	SHA-256 hash of the API key.
key_prefix	Mapped[str | None]	First 12 characters of the key for identification.
role	Mapped[str]	Global role (admin, operator, viewer).
created_by	Mapped[int | None]	FK to the user who created this principal, or None.
created_at	Mapped[datetime]	Timestamp when the principal was created.
last_used	Mapped[datetime | None]	Timestamp of the most recent API call, or None.
expires_at	Mapped[datetime | None]	Optional expiry timestamp; None means never expires.

UserGatewayRole

Bases: Base

A per-gateway role override for a user.

Attributes:

Name	Type	Description
id	Mapped[int]	Auto-incremented primary key.
user_id	Mapped[int]	FK to the user.
gateway_id	Mapped[int]	FK to the gateway.
role	Mapped[str]	Scoped role for this user on this gateway.

SPGatewayRole

Bases: Base

A per-gateway role override for a service principal.

Attributes:

Name	Type	Description
id	Mapped[int]	Auto-incremented primary key.
sp_id	Mapped[int]	FK to the service principal.
gateway_id	Mapped[int]	FK to the gateway.
role	Mapped[str]	Scoped role for this principal on this gateway.

Group

Bases: Base

A named collection of users for group-based RBAC.

Attributes:

Name	Type	Description
id	Mapped[int]	Auto-incremented primary key.
name	Mapped[str]	Unique group name (max 100 chars).
description	Mapped[str | None]	Optional human-readable description.
role	Mapped[str]	Global group role (admin, operator, viewer).
created_at	Mapped[datetime]	Timestamp when the group was created.

GroupMember

Bases: Base

Junction table linking users to groups.

Attributes:

Name	Type	Description
id	Mapped[int]	Auto-incremented primary key.
group_id	Mapped[int]	FK to the group.
user_id	Mapped[int]	FK to the user.

GroupGatewayRole

Bases: Base

A per-gateway role override for a group.

Attributes:

Name	Type	Description
id	Mapped[int]	Auto-incremented primary key.
group_id	Mapped[int]	FK to the group.
gateway_id	Mapped[int]	FK to the gateway.
role	Mapped[str]	Scoped role for this group on this gateway.

AuditEntry

Bases: Base

A persistent audit log entry for state-changing operations.

Attributes:

Name	Type	Description
id	Mapped[int]	Auto-incremented primary key.
timestamp	Mapped[datetime]	When the action occurred.
actor	Mapped[str]	Email or service principal name of the acting identity.
actor_role	Mapped[str]	Effective role at time of action.
action	Mapped[str]	Machine-readable action identifier.
resource_type	Mapped[str]	Type of resource affected (e.g. sandbox).
resource_id	Mapped[str]	Identifier of the affected resource.
gateway_name	Mapped[str | None]	Human-readable gateway name, if applicable.
gateway_id	Mapped[int | None]	FK to the gateway, or None if deleted.
detail	Mapped[str | None]	Optional free-text detail or JSON payload.
client_ip	Mapped[str | None]	IP address of the requesting client, if available.

SandboxMeta

Bases: Base

ShoreGuard-side metadata for a sandbox (labels, description).

Sandboxes live on the OpenShell gateway; this table stores metadata that ShoreGuard manages independently.

Attributes:

Name	Type	Description
id	Mapped[int]	Auto-incremented primary key.
gateway_name	Mapped[str]	Name of the gateway the sandbox belongs to.
sandbox_name	Mapped[str]	Name of the sandbox (unique per gateway).
description	Mapped[str | None]	Optional free-text description.
labels_json	Mapped[str | None]	Optional JSON-encoded key-value labels.
created_at	Mapped[datetime]	Timestamp when the metadata was first stored.
updated_at	Mapped[datetime | None]	Timestamp of the last metadata update.

Webhook

Bases: Base

A webhook endpoint for event notifications.

Attributes:

Name	Type	Description
id	Mapped[int]	Auto-incremented primary key.
url	Mapped[str]	Target URL for POST requests (max 2048 chars).
secret	Mapped[str]	HMAC-SHA256 signing secret.
event_types	Mapped[str]	JSON-encoded list of subscribed event types.
is_active	Mapped[bool]	Whether the webhook is enabled.
channel_type	Mapped[str]	Channel type (generic, slack, discord, email).
extra_config	Mapped[str | None]	Optional JSON config for channel-specific settings.
created_by	Mapped[str]	Email or service principal name of the creator.
created_at	Mapped[datetime]	Timestamp when the webhook was created.

WebhookDelivery

Bases: Base

A delivery attempt for a webhook event.

Attributes:

Name	Type	Description
id	Mapped[int]	Auto-incremented primary key.
webhook_id	Mapped[int]	FK to the webhook that was triggered.
event_type	Mapped[str]	The event type that triggered the delivery.
payload_json	Mapped[str]	JSON-encoded event payload.
status	Mapped[str]	Delivery status (pending, success, failed).
response_code	Mapped[int | None]	HTTP response code from the target, if any.
error_message	Mapped[str | None]	Error details on failure, if any.
attempt	Mapped[int]	Current attempt number (1-based).
created_at	Mapped[datetime]	Timestamp when the delivery was created.
delivered_at	Mapped[datetime | None]	Timestamp when delivery succeeded, if any.

PolicyPin

Bases: Base

A policy pin that locks a sandbox's policy at a specific version.

When a pin is active, policy updates and draft approvals are blocked until the pin is removed or expires.

Attributes:

Name	Type	Description
id	Mapped[int]	Auto-incremented primary key.
gateway_name	Mapped[str]	Name of the gateway the sandbox belongs to.
sandbox_name	Mapped[str]	Name of the pinned sandbox.
pinned_version	Mapped[int]	The policy version that is locked.
pinned_by	Mapped[str]	Email or service principal name of the actor who set the pin.
reason	Mapped[str | None]	Optional human-readable reason for pinning.
pinned_at	Mapped[datetime]	Timestamp when the pin was created.
expires_at	Mapped[datetime | None]	Optional expiry timestamp; None means pin never expires.

ApprovalWorkflow

Bases: Base

A multi-stage approval (quorum) configuration for a sandbox.

When a workflow exists, POST .../approvals/{chunk_id}/approve records a vote rather than calling the upstream gateway directly. The upstream approve fires only when the configured quorum is reached.

Attributes:

Name	Type	Description
id	Mapped[int]	Auto-incremented primary key.
gateway_name	Mapped[str]	Gateway the sandbox belongs to.
sandbox_name	Mapped[str]	Sandbox this workflow applies to.
required_approvals	Mapped[int]	Number of distinct approve votes needed.
required_roles_json	Mapped[str]	JSON array of roles eligible to vote (empty = any).
distinct_actors	Mapped[bool]	If true, the same actor cannot vote twice.
escalation_timeout_minutes	Mapped[int | None]	Fire approval.escalated webhook after this many minutes since the first vote on a chunk; None = off.
created_by	Mapped[str]	Identity of the admin who configured the workflow.
created_at	Mapped[datetime]	When the workflow was created.
updated_at	Mapped[datetime]	When the workflow was last updated.

ApprovalDecision

Bases: Base

A single vote cast against an approval chunk under a workflow.

Append-only log; pending/approved/rejected state is derived from the row set. Rows are cleared once the upstream gateway approve fires (on quorum met) or the chunk is rejected.

Attributes:

Name	Type	Description
id	Mapped[int]	Auto-incremented primary key.
workflow_id	Mapped[int]	FK to the active workflow configuration.
gateway_name	Mapped[str]	Gateway the sandbox belongs to (denormalised for lookup).
sandbox_name	Mapped[str]	Sandbox the chunk belongs to (denormalised for lookup).
chunk_id	Mapped[str]	The draft chunk being voted on.
actor	Mapped[str]	Identity of the voting user.
role	Mapped[str]	Role the voter held at vote time.
decision	Mapped[str]	approve or reject.
comment	Mapped[str | None]	Optional free-text comment.
created_at	Mapped[datetime]	When the vote was cast.

PolicyApplyProposal

Bases: Base

A YAML policy apply proposal waiting for workflow quorum.

Created on the first apply call for a sandbox with an active quorum approval workflow, and deleted once the proposal reaches a terminal state (quorum met, rejected, or superseded by a new YAML body). Lets subsequent vote-only calls reference the same proposal by its synthetic chunk_id without requiring the second runner to resubmit the YAML body — useful when the second voter is a human on the UI rather than the same CI pipeline.

Attributes:

Name	Type	Description
id	Mapped[int]	Auto-incremented primary key.
gateway_name	Mapped[str]	Gateway the sandbox belongs to.
sandbox_name	Mapped[str]	Sandbox the apply targets.
chunk_id	Mapped[str]	Synthetic chunk id policy.apply:<sha16> derived from yaml.
yaml_text	Mapped[str]	Raw YAML document body.
expected_hash	Mapped[str | None]	Optimistic-lock etag captured at proposal time.
proposed_by	Mapped[str]	Identity of the actor that opened the proposal.
proposed_at	Mapped[datetime]	When the proposal was created.

SBOMSnapshot

Bases: Base

A CycloneDX SBOM uploaded for a sandbox.

One snapshot per (gateway, sandbox) pair — a new upload replaces the previous snapshot rather than appending. Historical snapshots are intentionally out of scope; if you need them, archive the raw CycloneDX in object storage from CI before uploading, because the raw_json column reflects only the latest upload.

Attributes:

Name	Type	Description
id	Mapped[int]	Auto-incremented primary key.
gateway_name	Mapped[str]	Gateway the sandbox belongs to.
sandbox_name	Mapped[str]	Sandbox the SBOM describes.
bom_format	Mapped[str]	CycloneDX-only for now ("CycloneDX").
spec_version	Mapped[str]	CycloneDX spec version (e.g. "1.5").
serial_number	Mapped[str | None]	Optional CycloneDX serialNumber URN.
uploaded_by	Mapped[str]	Identity of the user who uploaded the snapshot.
uploaded_at	Mapped[datetime]	When the snapshot was uploaded.
component_count	Mapped[int]	Number of components in the SBOM.
vulnerability_count	Mapped[int]	Number of vulnerabilities declared in the SBOM.
max_severity	Mapped[str | None]	Highest severity across all vulnerabilities, or None.
raw_json	Mapped[str]	The original CycloneDX JSON document, retained for download.
components	Mapped[list[SBOMComponent]]	Cascade-delete relationship to SBOMComponent rows.

SBOMComponent

Bases: Base

A single component row denormalised from a CycloneDX SBOM.

Components are stored as flat rows so the components search endpoint can paginate and filter via SQL without re-parsing the raw CycloneDX JSON on each request. The vuln_count and max_severity columns are maintained at ingest time by joining through bom_ref against the document's vulnerabilities array, so the search endpoint never has to open the raw document.

Attributes:

Name	Type	Description
id	Mapped[int]	Auto-incremented primary key.
snapshot_id	Mapped[int]	Foreign key to the parent SBOM snapshot.
bom_ref	Mapped[str | None]	CycloneDX bom-ref of the component (used to join vulns).
name	Mapped[str]	Component name (e.g. "requests").
version	Mapped[str | None]	Component version (e.g. "2.31.0").
purl	Mapped[str | None]	Package URL (e.g. "pkg:pypi/requests@2.31.0").
type	Mapped[str | None]	CycloneDX type (library, framework, container, ...).
licenses	Mapped[str | None]	Comma-joined license identifiers.
vuln_count	Mapped[int]	Number of vulnerabilities affecting this component.
max_severity	Mapped[str | None]	Highest severity across the component's vulnerabilities.
snapshot	Mapped[SBOMSnapshot]	Backref to the parent SBOMSnapshot row.

SandboxBootHook

Bases: Base

A pre- or post-create boot hook attached to a sandbox.

Pre-create hooks act as ShoreGuard-side validation gates: their commands execute via subprocess.run inside the ShoreGuard process before CreateSandbox reaches the gateway, with a whitelisted environment exposing only SG_SANDBOX_NAME, SG_SANDBOX_IMAGE, SG_SANDBOX_POLICY_ID, and the hook's user-defined env entries.

Post-create hooks run inside the new sandbox via the existing ExecSandbox RPC once creation succeeds, intended for warm-up tasks like package updates or telemetry initialisation.

The execution surface is deliberately on the ShoreGuard side because the upstream gRPC contract has no native hook RPC. Once one exists, BootHookService can detect it and delegate without the schema changing.

Attributes:

Name	Type	Description
id	Mapped[int]	Auto-incremented primary key.
gateway_name	Mapped[str]	Gateway the sandbox belongs to.
sandbox_name	Mapped[str]	Sandbox this hook attaches to.
name	Mapped[str]	Human-readable hook name (unique per sandbox+phase).
phase	Mapped[str]	pre_create or post_create.
command	Mapped[str]	Shell command to execute (parsed via shlex).
workdir	Mapped[str]	Working directory inside the sandbox (post-create only).
env_json	Mapped[str]	JSON-encoded extra environment variables.
timeout_seconds	Mapped[int]	Hard wall-clock timeout for the hook.
order	Mapped[int]	Sort key within (sandbox, phase).
enabled	Mapped[bool]	Whether the hook participates in automatic runs.
continue_on_failure	Mapped[bool]	If true, post-create failures don't abort subsequent hooks (pre-create always aborts on failure).
created_by	Mapped[str]	Identity of the user who created the hook.
created_at	Mapped[datetime]	Timestamp when the hook was created.
updated_at	Mapped[datetime]	Timestamp of the last update.
last_run_at	Mapped[datetime | None]	Timestamp of the most recent run.
last_status	Mapped[str | None]	success / failure / skipped / None.
last_output	Mapped[str | None]	Captured stdout+stderr (truncated to 4 KiB).

OperationRecord

Bases: Base

A tracked long-running operation with DB persistence.

Attributes:

Name	Type	Description
id	Mapped[str]	UUID primary key.
status	Mapped[str]	Lifecycle state (pending → running → succeeded/failed, or cancelling → failed).
resource_type	Mapped[str]	Type of resource (sandbox, exec, gateway).
resource_key	Mapped[str]	Resource identifier for duplicate detection.
idempotency_key	Mapped[str | None]	Optional client-provided key for idempotent requests.
progress_pct	Mapped[int]	Progress percentage (0-100).
progress_msg	Mapped[str | None]	Human-readable progress message.
result_json	Mapped[str | None]	JSON-encoded result payload on success.
error_message	Mapped[str | None]	Error description on failure.
error_code	Mapped[str | None]	Machine-readable error code (timeout, cancelled, etc.).
actor	Mapped[str | None]	Identity of the user who started the operation.
gateway_name	Mapped[str | None]	Gateway the operation targets.
created_at	Mapped[datetime]	When the operation was created.
updated_at	Mapped[datetime]	When the operation was last updated.
completed_at	Mapped[datetime | None]	When the operation finished.

db

Database engine, session factory, and embedded Alembic migrations.

init_db

init_db(url: str | None = None) -> Engine

Create the engine, run migrations, and configure the session factory.

Called once during application startup (FastAPI lifespan).

Parameters:

Name	Type	Description	Default
url	str | None	SQLAlchemy database URL. Falls back to default_database_url().	None

Returns:

Name	Type	Description
Engine	Engine	The initialised SQLAlchemy engine.

Raises:

Type	Description
RuntimeError	If database migration fails.

get_engine

get_engine() -> Engine

Return the current engine.

Returns:

Name	Type	Description
Engine	Engine	The active SQLAlchemy engine.

Raises:

Type	Description
RuntimeError	If init_db() has not been called yet.

init_async_db

init_async_db(sync_url: str) -> AsyncEngine

Create an async engine matching the sync database URL.

Parameters:

Name	Type	Description	Default
sync_url	str	The synchronous SQLAlchemy URL used by :func:init_db.	required

Returns:

Name	Type	Description
AsyncEngine	AsyncEngine	The initialised async engine.

dispose_async_engine async

dispose_async_engine() -> None

Dispose the async engine and clear the session factory.

get_async_session_factory

get_async_session_factory() -> async_sessionmaker

Return the async session factory.

Returns:

Name	Type	Description
async_sessionmaker	async_sessionmaker	The active async session factory.

Raises:

Type	Description
RuntimeError	If init_async_db() has not been called yet.