/DB

Model the schema

Define the projects and tasks tables as annotated C# classes. Attributes map them to PostgreSQL columns, defaults, keys, and a foreign-key relationship.

updated 5 Jun 20261 min readv0.3.2View as Markdown

In Socigy.OpenSource.DB the C# class is the schema. Each [Table] class is a partial class so the generator can add the typed query methods to it at build time.

The project table

// TaskApi.AppDb/Project.cs
using Socigy.OpenSource.DB.Attributes;

[Table("projects")]
public partial class Project
{
    [PrimaryKey, Default(DbDefaults.Guid.Random)]
    public Guid Id { get; set; }

    [Unique]
    public required string Name { get; set; }

    [Default(DbDefaults.Time.Now)]
    public DateTime CreatedAt { get; set; }
}
  • [Table("projects")] sets the SQL table name. The class must be partial.
  • [PrimaryKey, Default(DbDefaults.Guid.Random)] makes Id the primary key and tells the database to generate a random UUID, so callers never set it.
  • [Unique] adds a unique constraint on name.
  • [Default(DbDefaults.Time.Now)] stamps created_at server-side.

Property names map to snake_case columns automatically: CreatedAt becomes created_at. All of these attributes live in Socigy.OpenSource.DB.Attributes.

The task table

// TaskApi.AppDb/TaskItem.cs
using Socigy.OpenSource.DB.Attributes;

[Table("tasks")]
public partial class TaskItem
{
    [PrimaryKey, Default(DbDefaults.Guid.Random)]
    public Guid Id { get; set; }

    [ForeignKey(typeof(Project), OnDelete = DbValues.ForeignKey.Cascade)]
    public Guid ProjectId { get; set; }

    public required string Title { get; set; }

    public bool Done { get; set; }

    [Default(DbDefaults.Time.Now)]
    public DateTime CreatedAt { get; set; }
}

[ForeignKey(typeof(Project), ...)] on ProjectId references Project's primary key automatically. OnDelete = DbValues.ForeignKey.Cascade deletes a project's tasks when the project is removed. See Constraints for composite keys and other referential actions.

NOTE
Nothing here is reflection or runtime configuration. The generator reads these attributes at compile time and emits plain C#. See How it works.

With the schema modelled, the next step turns it into a real database. Continue to Generate and apply migrations.