# Data Integrity Constraints

## Objective

Enforce your important rules in the database, not just in the form.

Database-level uniqueness, foreign key, check, and not-null constraints backing the business rules the application currently only validates in code.

## Before You Begin

This feature is being added to an application that already exists and already
works. Do not scaffold a new project, and do not assume a blank slate.

Inspect the codebase first and establish:

- The existing application structure and where code of this kind already lives.
- The framework and version in use.
- The existing design system — colours, spacing, typography, and component conventions.
- Existing UI components you can reuse instead of writing new ones.
- The existing database structure, if this feature needs to persist anything.
- The existing authentication and authorization system, if this feature is user-scoped.
- Dependencies already installed, so you don't add a library that duplicates one.
- The existing test setup and conventions.

Only start writing code once you understand the above. If the application
already implements part of this feature, extend it rather than replacing it.

## Implementation Instructions

1. Inventory the rules the app relies on — required fields, uniqueness, valid enum values, positive amounts, referential links — and note which exist only in application code.
2. Before adding each constraint, query for rows that already violate it and decide explicitly whether to fix, delete, or exclude them. Every mature database has violating legacy rows.
3. Add constraints in a way that does not hold a long exclusive lock: create indexes concurrently, add checks as NOT VALID and validate them in a second step.
4. Keep application validation in place alongside the constraint so users get a field-level message, and catch the database error as a backstop for races and imports.
5. Translate constraint violations into human sentences naming the field. A raw unique-index error string must never reach the user.
6. Do NOT add a plain unique index to a table with soft deletes or multiple tenants without scoping it. A global unique email on a table with deleted rows will block a user from ever signing up again.

## UI and UX Requirements

Match the application's existing design system exactly. Reuse its components,
spacing, and typography. This feature should look like it was always there.

## Responsive Requirements

Works on mobile, tablet, and desktop. Touch targets are large enough to hit on a
phone, and nothing overflows horizontally at 320px.

## Accessibility Requirements

- Fully keyboard navigable.
- Correct semantic elements and ARIA roles.
- Visible focus states.
- Meets WCAG AA contrast.
- Dynamic changes are announced to screen readers.
- Respects prefers-reduced-motion.

## Edge Cases

- Uniqueness across nullable columns behaves differently than people expect — NULL is not equal to NULL, so duplicates slip through.
- Soft-deleted rows must be excluded from uniqueness via a partial index, or deletion becomes permanent blocking.
- Multi-tenant uniqueness is almost always scoped to the tenant, not global — check every unique rule for a missing tenant column.
- Foreign keys need an explicit delete behaviour: cascade, nullify, or restrict. Defaulting silently is how orphans or accidental mass deletes happen.
- A race between two simultaneous requests will defeat a check-then-insert; the database constraint is what actually stops it, so the code must handle the raised error.
- Seed data, fixtures, and bulk importers frequently bypass model validation and will start failing the moment constraints land — fix them in the same change.
- Adding a NOT NULL column to a large table without a default can rewrite the table; stage it as nullable, backfill, then constrain.

## Testing

Exercise the feature end to end in the running application. Cover every edge case
above, then run the existing test suite and confirm nothing regressed.

## Acceptance Criteria

- [ ] Every business rule identified as critical has a matching database constraint.
- [ ] Existing violating rows were found and resolved before each constraint was added.
- [ ] Migrations avoid long exclusive locks on large tables.
- [ ] Uniqueness is correctly scoped for tenants, soft deletes, and nullable columns.
- [ ] Foreign keys declare an explicit delete behaviour.
- [ ] Constraint violations surface as field-level user messages, never raw database errors.
- [ ] Concurrent duplicate requests produce one row, not two.
- [ ] The feature matches the existing design system.
- [ ] No existing functionality is broken.

## Adaptation Rules

- Match the existing design system. Do not introduce a new colour palette,
  spacing scale, or component library.
- Reuse existing components and utilities wherever they fit.
- Follow the naming, file layout, and code style already present.
- Do not upgrade, replace, or remove existing dependencies to make this
  feature fit. Adapt the feature to the app, not the app to the feature.
- Do not break existing functionality. If a change is genuinely required in
  existing code, make the smallest one that works and say so.
- If something in these instructions conflicts with how the application is
  built, follow the application and explain the deviation.

## Final Verification

Before you report the work as done:

1. Re-read the acceptance criteria above and check each one against what you
   actually built.
2. Run the application and exercise the feature end to end.
3. Run the existing test suite and confirm you have broken nothing.
4. Check the feature on mobile, tablet, and desktop widths.
5. Check keyboard navigation and focus handling.
6. Summarize what changed: files added, files modified, and anything you
   deliberately did differently because of how this application is built.

If any acceptance criterion is unmet, fix it before reporting completion.