Database Overview

Database Technology

PhotoFlow uses PostgreSQL as its primary database, deployed on Kubernetes with persistent storage.

Connection Details

Environment	Host	Port	Database
Development	10.0.0.62	5432	pfl-dev
UAT	-	5432	pfl-uat
Production	-	5432	pfl-prod

Database Administration

PgAdmin URL: https://pgadmin.thontm.site
Access via VPN required for direct database connections

Schema Overview

PhotoFlow uses a shared database with logical separation per service through Entity Framework Core contexts.

Entity Relationship Diagram

Core Tables

Albums

Primary table for album management.

Column	Type	Description
Id	uuid	Primary key
Name	varchar(255)	Album name
Status	int	Album status enum
AlbumState	int	State machine state
OrgId	uuid	Organization FK
AssigneeId	uuid	Assigned user FK
ProviderId	uuid	Cloud provider FK
HashedPassword	varchar	Access password hash
IsArchive	bool	Archive flag
CreatedAt	timestamp	Creation time
ModifiedAt	timestamp	Last modified

AlbumTags

Represents workflow stages with associated content.

Column	Type	Description
Id	uuid	Primary key
AlbumId	uuid	Parent album FK
Type	int	Tag type (Raw, Stock, Rate, Retouch, Final)
Status	int	Tag status enum
Link	jsonb	Google Drive URL info
ProviderId	uuid	Cloud provider FK
FolderId	uuid	Storage folder FK
SyncInitiatedByUserId	uuid	User who started sync

PhotoProcs

Individual photo records within an album tag.

Column	Type	Description
Id	uuid	Primary key
AlbumTagId	uuid	Parent tag FK
FileName	varchar	Original filename
ExternalId	varchar	Google Drive file ID
Status	int	Photo status enum
Voted	bool	Client selection flag
SortOrder	int	Display order
ThumbnailUrl	varchar	Cached thumbnail URL

FailureRecords

Tracks rollback failures for recovery.

Column	Type	Description
Id	uuid	Primary key
EntityId	uuid	Related entity ID
EntityType	varchar	Entity type name
FromState	varchar	Previous state
ToState	varchar	Target state
ErrorMessage	text	Error details
Severity	int	Failure severity level
RetryCount	int	Retry attempts
LastRetryAt	timestamp	Last retry time
Status	int	Record status

AuditLogs

Performance monitoring and activity tracking.

Column	Type	Description
Id	uuid	Primary key
ServiceName	varchar	Source service
Action	varchar	Action name
EntityType	varchar	Entity type
EntityId	varchar	Entity ID
UserId	varchar	Acting user
ExecutionTimeMs	bigint	Operation duration
Status	varchar	Success/Failed
ErrorMessage	text	Error if failed
RequestData	jsonb	Request payload
ResponseData	jsonb	Response data

Indexes

Performance Indexes

-- Albums
CREATE INDEX IX_Albums_OrgId ON "Albums" ("OrgId");
CREATE INDEX IX_Albums_AlbumState ON "Albums" ("AlbumState");
CREATE INDEX IX_Albums_CreatedAt ON "Albums" ("CreatedAt" DESC);

-- AlbumTags
CREATE INDEX IX_AlbumTags_AlbumId ON "AlbumTags" ("AlbumId");
CREATE INDEX IX_AlbumTags_Type_Status ON "AlbumTags" ("Type", "Status");

-- PhotoProcs
CREATE INDEX IX_PhotoProcs_AlbumTagId ON "PhotoProcs" ("AlbumTagId");
CREATE INDEX IX_PhotoProcs_Voted ON "PhotoProcs" ("Voted") WHERE "Voted" = true;

-- AuditLogs
CREATE INDEX IX_AuditLogs_ServiceName_CreatedAt ON "AuditLogs" ("ServiceName", "CreatedAt" DESC);
CREATE INDEX IX_AuditLogs_Status ON "AuditLogs" ("Status") WHERE "Status" = 'Failed';

Migration Strategy

PhotoFlow uses Entity Framework Core Migrations for schema management.

Running Migrations

# Photoflow Service
dotnet ef migrations add "MigrationName" \
    --project src/Services/Photoflow/Photoflow.Infrastructure \
    --startup-project src/Services/Photoflow/Photoflow.API

dotnet ef database update \
    --project src/Services/Photoflow/Photoflow.Infrastructure \
    --startup-project src/Services/Photoflow/Photoflow.API

# Storage Service
dotnet ef migrations add "MigrationName" \
    --project src/Services/Storage/Storage.Infrastructure \
    --startup-project src/Services/Storage/Storage.API

# Identity Service
dotnet ef migrations add "MigrationName" \
    --project src/Services/Identity/Identity.Infrastructure \
    --startup-project src/Services/Identity/Identity.API

# Organization Service
dotnet ef migrations add "MigrationName" \
    --project src/Services/Organization/Organization.Infrastructure \
    --startup-project src/Services/Organization/Organization.API

Removing Migrations

dotnet ef migrations remove \
    --project src/Services/Photoflow/Photoflow.Infrastructure \
    --startup-project src/Services/Photoflow/Photoflow.API

Multi-tenancy

PhotoFlow implements multi-tenancy at the application level using OrgId:

public interface IMultiTenantEntity
{
    Guid OrgId { get; set; }
}

// Global query filter
modelBuilder.Entity<AlbumEntity>()
    .HasQueryFilter(a => a.OrgId == _currentOrgId);

Soft Delete

Entities implement soft delete via IsDeleted flag:

public abstract class BaseEntity<TId>
{
    public bool IsDeleted { get; set; }
    public DateTime? DeletedAt { get; set; }
}

// Global query filter
modelBuilder.Entity<AlbumEntity>()
    .HasQueryFilter(a => !a.IsDeleted);

Performance Queries

Slow Operations Analysis

SELECT 
    "Operation", 
    AVG("ExecutionTimeMs") as AvgTime,
    MAX("ExecutionTimeMs") as MaxTime,
    COUNT(*) as Count
FROM "AuditLogs" 
WHERE "Status" = 'Completed'
GROUP BY "Operation"
ORDER BY AvgTime DESC;

Failed Operations

SELECT * FROM "AuditLogs" 
WHERE "Status" = 'Failed'
ORDER BY "CreatedAt" DESC
LIMIT 100;

Architecture
Photoflow Service
Local Development

Last modified: 25 February 2026