PROJECT - a tribute inventory system

The eight lessons of this module travelled from the first connection to a database as far as validation at the schema level. The project is where all of it must work together - and where you will see for the first time that decisions made about the schema come back to you a week later, while writing queries.

You will build a tribute inventory system for a Roman legion: who owns what, where it came from, and how it passes from hand to hand.

The domain

Five areas the system must cover:

1. Legionaries - registration with validation, ranks and privileges, the history of each one's operations.
2. The tribute catalogue - types (gold, silver, gems, artefacts), valuation, authenticity assessment, discovery history.
3. Maps - geographic coordinates with range validation, search directions, discovery status, a link to a particular tribute.
4. Transactions and transfers - safe transfer of ownership between legionaries, exchange, a full history of operations.
5. Legions and cohorts - assignment of legionaries, size limits, centurions.

Do not begin with code. Begin with a sheet of paper and write down what relates to what - the one decision in this project whose later revision costs a great deal.

Step 1 - schema and relations

The legionary entity shows three things at once: column types, indexes and constraints at the database level:

1@Entity('legionaries')
2@Check('tribute_count_non_negative', '"tributeCount" >= 0')
3@Index(['name', 'isActive'])
4export class Legionary {
5  @PrimaryGeneratedColumn()
6  id: number;
7
8  @Column({ length: 100, unique: true })
9  name: string;
10
11  @Column({ length: 50 })
12  rank: string;
13
14  @Column({ type: 'int', default: 0 })
15  tributeCount: number;
16
17  @Column({ type: 'decimal', precision: 15, scale: 2, default: 0 })
18  totalTributeValue: number;
19
20  @Column({ default: true })
21  isActive: boolean;
22
23  @CreateDateColumn()
24  joinedAt: Date;
25
26  @OneToMany(() => Tribute, (tribute) => tribute.owner)
27  tributes: Tribute[];
28
29  @ManyToOne(() => Cohort, (cohort) => cohort.legionaries)
30  cohort: Cohort;
31}

Three decisions in this code are worth understanding, because you will repeat them in every project.

A tribute's value is a

decimal

float

precision: 15, scale: 2

@Index(['name', 'isActive'])

@Check

UPDATE

Choose relations by cardinality:

@OneToMany

@ManyToOne

@OneToOne

@ManyToMany

Step 2 - the repository layer

Complex queries belong in a repository of your own:

1@Injectable()
2export class TributeRepository extends Repository<Tribute> {
3  async findValuableByLegionary(legionaryId: number, minValue: number) {
4    return this.createQueryBuilder('tribute')
5      .innerJoinAndSelect('tribute.owner', 'owner')
6      .where('owner.id = :legionaryId', { legionaryId })
7      .andWhere('tribute.value >= :minValue', { minValue })
8      .orderBy('tribute.value', 'DESC')
9      .getMany();
10  }
11}

The division of responsibility is simple: the repository knows how to ask the database something; the service knows when and why. When a

createQueryBuilder

Note the

:legionaryId

:minValue

Step 3 - the transfer, that is, a transaction

Moving a tribute is the heart of this project, because it consists of operations that must all happen or none at all:

1async transferTribute(tributeId: number, fromId: number, toId: number) {
2  return this.dataSource.transaction(async (manager) => {
3    const tribute = await manager.findOne(Tribute, {
4      where: { id: tributeId, owner: { id: fromId } },
5      relations: ['owner'],
6    });
7
8    if (!tribute) {
9      throw new BadRequestException('Tribute not found or not owned by the sender');
10    }
11
12    tribute.owner = await manager.findOneOrFail(Legionary, { where: { id: toId } });
13    await manager.save(tribute);
14
15    await manager.decrement(Legionary, { id: fromId }, 'tributeCount', 1);
16    await manager.increment(Legionary, { id: toId }, 'tributeCount', 1);
17
18    return manager.save(Transaction, {
19      tribute,
20      fromLegionaryId: fromId,
21      toLegionaryId: toId,
22    });
23  });
24}

Without a transaction, a failure halfway through leaves the system in an impossible state: the tribute has a new owner, but the old one's counter was never decremented - or worse, the counter fell and rose while the owner stayed the same. Such discrepancies surface months later, and by then there is no telling which records are true.

And now the greatest trap of the whole project. Inside a transaction use only

manager

this.tributeRepository.save(...)

transaction(async (manager) => ...)

manager

Step 4 - seeders

Seed data fills the database for development and testing:

1export class LegionarySeeder {
2  constructor(private dataSource: DataSource) {}
3
4  async run() {
5    const repo = this.dataSource.getRepository(Legionary);
6
7    const existing = await repo.count();
8    if (existing > 0) {
9      return;
10    }
11
12    await repo.save([
13      { name: 'Marcus Aurelius', rank: 'Centurion' },
14      { name: 'Gaius Julius', rank: 'Optio' },
15    ]);
16  }
17}

One requirement separates a good seeder from a troublesome one: it must be runnable twice. A

count()

upsert

Order matters too: first the independent entities (legions, cohorts), then those that refer to them (legionaries), and last the tributes and transactions. The reverse order ends in a foreign key error.

Validation at three levels

The system has three places where data can be rejected, and each catches something different:

• DTOs with

  class-validator

  - the shape of the HTTP request. Here you reject a missing name or a negative value before anything touches the database.
• The entity -

  unique

  ,

  nullable: false

  ,

  @Check

  . It holds no matter which way the data arrived.
• The service - domain rules the database cannot express: "a tribute cannot be transferred to oneself", "a cohort holds a hundred men".

Duplicating a rule across two levels is not a mistake - it is cheap insurance.

Assessment criteria

The project is finished when it meets six conditions:

1. Entities with relations - all four kinds where they fit, with indexes on the columns you genuinely filter by.
2. Migrations - the schema comes from migrations, not from

   synchronize: true

   .
3. Repositories with at least two queries written in the Query Builder.
4. A transfer inside a transaction, with rollback proved by a test.
5. Seeders that are idempotent and run in the right order.
6. Validation at the three levels described above.

Before you submit, run one test: break a transfer halfway through - give a recipient that does not exist - and check both legionaries' counters in the database. If either changed, somewhere in your code an injected repository stands where

manager

Send the link to your repository when you are done.