📑Data Models

This document provides a consolidated view of all unique data models.

---

Master Database Schema

Deletion Policy: All "Delete" operations in the UDAI API perform a soft delete. We do not hard-delete records. Instead, the status block on the resource is updated to -1 (Inactive).

1. Users

Field	Type	Sample Value	Validation Rules
user_id	int, unique	1042	Auto-incrementing, must be unique
user_uuid	UUID, unique, primary key	550e8400-e29b-41d4-a716-446655440000	Must be a valid UUID v4, unique
user_udai_id	string, unique	UDAI-USR-001	Format: UDAI-USR-[0-9]+, unique
first_name	string	John	Max 100 characters, no special characters
last_name	string	Doe	Max 100 characters, no special characters
email	string, unique	[email protected]	Valid email format, unique, max 255 chars
email_verified	boolean	true	Must be boolean
password	string, hashed	argon2id$v=19$m=16...	Must be a valid Argon2/Bcrypt hash
phone	string, unique	[phone]	E.164 format, unique
phone_verified	boolean	true	Must be boolean
role	int/enum	3	Must be a valid role enum value
is_certified_pilot	boolean	true	Must be boolean
rpc_number	string, unique	RPC-DGCA-2023-456	Format required by DGCA, unique if present
status	int/enum	1	Must be valid status enum (1=Active, -1=Inactive, etc)
profile_picture	string	https://bucket/profile.jpg	Valid HTTP/S URL or storage path
last_login	timestamp	2025-07-04T10:30:00Z	Valid ISO 8601 timestamp
created_at	timestamp	2025-07-01T08:00:00Z	Valid ISO 8601 timestamp, immutable
created_by	string	system	References a valid user_uuid or system
updated_at	timestamp	2025-07-04T10:30:00Z	Valid ISO 8601 timestamp, >= created_at
updated_by	string	550e8400-e29b-41d4-a716-446655440000	References a valid user_uuid

2. Pilot Credentials (Linked to Users)

Field	Type	Sample Value	Validation Rules
cred_id	int, unique	502	Auto-incrementing, must be unique
cred_uuid	UUID, unique, primary key	f47ac10b-58cc-4372-a567-0e02b2c3d479	Must be a valid UUID v4, unique
user_uuid	UUID, foreign key	550e8400-e29b-41d4-a716-446655440000	Must exist in Users table
category	enum	1	Must be valid category enum
sub_category	enum	2	Must be valid sub_category enum
class	enum	3	Must be valid class enum
status	enum	1	Must be valid status enum
source	enum	1	Must be valid source enum (e.g., self-declared, verified)
registered_at	timestamp	2025-07-01T08:00:00Z	Valid ISO 8601 timestamp
registered_by	string	550e8400...	References a valid user_uuid
created_at	timestamp	2025-07-01T08:00:00Z	Valid ISO 8601 timestamp, immutable
created_by	string	550e8400...	References a valid user_uuid
updated_at	timestamp	2025-07-04T10:30:00Z	Valid ISO 8601 timestamp, >= created_at
updated_by	string	550e8400...	References a valid user_uuid

3. Organisations

Field	Type	Sample Value	Validation Rules
org_id	int, unique	801	Auto-incrementing, must be unique
org_uuid	UUID, unique, primary key	9f3a1b2c-4d5e-6f7a-8b9c-0d1e2f3a4b5c	Must be a valid UUID v4, unique
org_udai_id	string, unique	UDAI-ORG-102	Format: UDAI-ORG-[0-9]+, unique
org_name	string	AeroTech Solutions	Max 150 chars, must be unique
org_address	string	123 Drone Park, BLR	Max 500 characters
org_website	string	https://aerotech.com	Must be a valid HTTP/S URL
org_business_identifiers	json	{"gst": "29ABCDE1234F1Z5"}	Must be a valid JSON object string
org_type	enum	2	Must be valid org_type enum
status	enum	1	Must be valid status enum
created_at	timestamp	2025-07-01T08:00:00Z	Valid ISO 8601 timestamp, immutable
created_by	string	550e8400...	References a valid user_uuid
updated_at	timestamp	2025-07-04T10:30:00Z	Valid ISO 8601 timestamp, >= created_at
updated_by	string	550e8400...	References a valid user_uuid

4. Organisation Memberships

Field	Type	Sample Value	Validation Rules
membership_id	int, unique	320	Auto-incrementing, must be unique
membership_uuid	UUID, unique, primary key	c3e5d0f1-4b2a-6c8e-9d7f-1a3b5c7d9e0f	Must be a valid UUID v4, unique
membership_udai_id	string, unique	UDAI-MEM-005	Format: UDAI-MEM-[0-9]+, unique
org_uuid	UUID, foreign key	9f3a1b2c-4d5e-6f7a-8b9c-0d1e2f3a4b5c	Must exist in Organisations table
user_uuid	UUID, foreign key	550e8400-e29b-41d4-a716-446655440000	Must exist in Users table
role	enum	3	Must be valid membership role enum
status	enum	1	Must be valid status enum
created_at	timestamp	2025-07-01T08:00:00Z	Valid ISO 8601 timestamp, immutable
created_by	string	9f3a1b2c...	References a valid user_uuid
updated_at	timestamp	2025-07-04T10:30:00Z	Valid ISO 8601 timestamp, >= created_at
updated_by	string	9f3a1b2c...	References a valid user_uuid

5. Org Invitations

Tracks a pending, accepted, declined, expired, or revoked invitation sent to a user (by email or phone) to join an organisation.

Field	Type	Sample Value	Validation Rules
invitation_id	int, unique	450	Auto-incrementing, must be unique
invitation_uuid	UUID, unique, primary key	7b23c4d5-e6f7-8901-ab2c-3d4e5f6a7b8c	Must be a valid UUID v4, unique
invitation_udai_id	string, unique	UDAI-INV-009	Format: UDAI-INV-[0-9]+, unique
org_uuid	UUID, foreign key	9f3a1b2c-4d5e-6f7a-8b9c-0d1e2f3a4b5c	Must exist in Organisations table
invited_by_user_uuid	UUID, foreign key	550e8400-e29b-41d4-a716-446655440000	Must exist in Users table (the sender)
invitee_user_uuid	UUID, foreign key, nullable	null or 123e4567...	If provided, must exist in Users table
identifier_type	enum	email	Must be either "email" or "phone"
identifier	string	[email protected]	Valid format matching identifier_type
role	int/enum	3	Must be a valid membership role enum
invitation_token	string, unique	aBcD123XyZ	Secure random string, auto-generated
expires_at	timestamp	2025-07-08T08:00:00Z	Must be future timestamp, typically +X days from creation
status	int/enum	0	Must be valid invitation status enum
created_at	timestamp	2025-07-01T08:00:00Z	Valid ISO 8601 timestamp, immutable
created_by	string	550e8400...	References a valid user_uuid
updated_at	timestamp	2025-07-04T10:30:00Z	Valid ISO 8601 timestamp, >= created_at
updated_by	string	550e8400...	References a valid user_uuid

6. Drone Models

Field	Type	Sample Value	Validation Rules
model_id	int, unique	605	Auto-incrementing, must be unique
model_uuid	UUID, unique, primary key	f0154cf0-fef0-4c3e-b816-43e34b9cfbc1	Must be a valid UUID v4, unique
model_udai_id	string, unique	UDAI-MOD-23	Format: UDAI-MOD-[0-9]+, unique
model_name	string	AeroSwift XT	Max 100 characters
model_variant	string	v2.Pro	Max 50 characters, nullable
model_version	string	1.0.4	Semantic versioning format preferred
type_certificate_number	string	TC-092-2023	Format matching DGCA specifications
manufacturer_uuid	string, UUID	9f3a1b2c...	Must exist in Organisations table (type=Manufacturer)
category	enum	1	Must be valid drone category enum
sub_category	enum	2	Must be valid sub_category enum
class	enum	3	Must be valid class enum
max_takeoff_weight	double	2.5	Greater than 0
max_dimensions	string	400x400x200	Format: LengthxWidthxHeight (mm)
max_endurance	double	45.0	In minutes, greater than 0
max_range	double	5.0	In kilometers, greater than 0
max_speed	double	15.0	In m/s, greater than 0
max_height	double	120.0	In meters, greater than 0
min_temp	double	-10.0	In Celsius
max_temp	double	50.0	In Celsius, must be > min_temp
operation_envelope	enum	VLOS	VLOS or BVLOS
frequency	string	2.4GHz	Max 50 characters
gcs_model	string	Smart Controller V2	Max 100 characters
gcs_version	string	1.2.0	Semantic versioning format preferred
application	string	Surveillance	Max 255 characters
status	enum	1	Must be valid status enum
source	enum	1	Must be valid source enum
allowed_payload_uuids	array of UUIDs	["f8e9d0c1..."]	All UUIDs must exist in Payloads table
registered_at	timestamp	2025-07-01T08:00:00Z	Valid ISO 8601 timestamp
registered_by	string	550e8400...	References a valid user_uuid
created_at	timestamp	2025-07-01T08:00:00Z	Valid ISO 8601 timestamp, immutable
created_by	string	550e8400...	References a valid user_uuid
updated_at	timestamp	2025-07-04T10:30:00Z	Valid ISO 8601 timestamp, >= created_at
updated_by	string	550e8400...	References a valid user_uuid

7. Payloads

Represents a physical sensor or attachment that can be equipped on a drone. Drone Models define which payloads they support, and Drones define which payloads they are currently equipped with.

Field	Type	Sample Value	Validation Rules
payload_id	int, unique	102	Auto-incrementing, must be unique
payload_uuid	UUID, unique, primary key	f8e9d0c1-b2a3-4455-6677-889900aabbcc	Must be a valid UUID v4, unique
payload_udai_id	string, unique	UDAI-PAY-045	Format: UDAI-PAY-[0-9]+, unique
payload_name	string	Zenmuse H20T	Max 100 characters
payload_type	int/enum	2	Must be a valid payload_type enum (e.g., 2 = Thermal Camera)
manufacturer	string	DJI	Max 100 characters
weight_kg	double	0.85	Weight of the payload in kilograms, >= 0
power_draw_watts	double	12.5	Optional, expected power draw
status	int/enum	1	Must be valid status enum
created_at	timestamp	2025-07-01T08:00:00Z	Valid ISO 8601 timestamp, immutable
created_by	string	550e8400...	References a valid user_uuid
updated_at	timestamp	2025-07-04T10:30:00Z	Valid ISO 8601 timestamp, >= created_at
updated_by	string	550e8400...	References a valid user_uuid

8. Drones

Field	Type	Sample Value	Validation Rules
drone_id	int, unique	2054	Auto-incrementing, must be unique
drone_uuid	UUID, unique, primary key	9f5b610c-98cc-4372-a167-1e02b2c31479	Must be a valid UUID v4, unique
drone_udai_id	string, unique	UDAI-DRN-092	Format: UDAI-DRN-[0-9]+, unique
drone_uin	string, unique	UIN-IND-01239X	DGCA UIN format, nullable/optional if Pending
uin_status	int/enum	1	Must be valid drone_uin_status enum (0=Pending, 1=Generated)
drone_org_internal_uuid	string, unique	ORG-ASSET-09	Optional, unique within organisation
drone_model_uuid	string (UUID), foreign key	f0154cf0...	Must exist in Drone Models table
org_owner_uuid	string (UUID), foreign key	9f3a1b2c...	Must exist in Organisations table
status	enum	1	Must be valid status enum
source	enum	1	Must be valid source enum (e.g., imported, DGCA API)
active_payload_uuids	array of UUIDs	["f8e9d0c1..."]	Must exist in Payloads table & allowed by model
registered_at	timestamp	2025-07-01T08:00:00Z	Valid ISO 8601 timestamp
registered_by	string	550e8400...	References a valid user_uuid
created_at	timestamp	2025-07-01T08:00:00Z	Valid ISO 8601 timestamp, immutable
created_by	string	550e8400...	References a valid user_uuid
updated_at	timestamp	2025-07-04T10:30:00Z	Valid ISO 8601 timestamp, >= created_at
updated_by	string	550e8400...	References a valid user_uuid

9. Drone Transfers

Represents a request to transfer drone ownership from one organisation to another. Once approved, a Drone Ownership record is created.

Field	Type	Sample Value	Validation Rules
transfer_id	int, unique	432	Auto-incrementing, must be unique
transfer_uuid	UUID, unique, primary key	d2c3b4a5-6789-01ef-abcd-ef1234567890	Must be a valid UUID v4, unique
transfer_udai_id	string, unique	UDAI-TXR-041	Format: UDAI-TXR-[0-9]+, unique
drone_uuid	string (UUID), foreign key	9f5b610c...	Must exist in Drones table
from_org_uuid	string (UUID), foreign key	9f3a1b2c...	org_uuid of sender, must own drone
to_org_uuid	string (UUID), foreign key	c3e5d0f1...	org_uuid of receiver, must not be sender
initiated_by_user_uuid	string (UUID), foreign key	550e8400...	Must exist in Users table
transfer_type	int/enum	1	1=Permanent, 2=Temporary
transfer_description	string	Lease for 3 months	Max 500 characters, nullable
transfer_date	timestamp	2025-07-04T10:30:00Z	Valid ISO 8601 timestamp
expires_at	timestamp, nullable	2025-10-04T10:30:00Z	Required if transfer_type=2, else null
status	int/enum	1	Must be valid transfer_status enum
created_at	timestamp	2025-07-01T08:00:00Z	Valid ISO 8601 timestamp, immutable
created_by	string	550e8400...	References a valid user_uuid
updated_at	timestamp	2025-07-04T10:30:00Z	Valid ISO 8601 timestamp, >= created_at
updated_by	string	550e8400...	References a valid user_uuid

10. Drone Ownerships

Represents an active or historical drone ownership record for an organisation. Created when a Drone Transfer is accepted.

Field	Type	Sample Value	Validation Rules
ownership_id	int, unique	1210	Auto-incrementing, must be unique
ownership_uuid	UUID, unique, primary key	ab12cd34-ef56-7890-abcd-ef1234567890	Must be a valid UUID v4, unique
ownership_udai_id	string, unique	UDAI-OWN-40	Format: UDAI-OWN-[0-9]+, unique
drone_uuid	UUID, foreign key	9f5b610c...	Must exist in Drones table
org_uuid	UUID, foreign key	c3e5d0f1...	Must exist in Organisations table
transfer_uuid	UUID, foreign key	d2c3b4a5...	Must exist in Drone Transfers table
owned_since	timestamp	2025-07-04T10:30:00Z	Valid ISO 8601 timestamp
status	int/enum	1	Must be valid drone_ownership_status enum
created_at	timestamp	2025-07-01T08:00:00Z	Valid ISO 8601 timestamp, immutable
created_by	string	550e8400...	References a valid user_uuid
updated_at	timestamp	2025-07-04T10:30:00Z	Valid ISO 8601 timestamp, >= created_at
updated_by	string	550e8400...	References a valid user_uuid

11. Missions

A Mission is a high-level operational grouping used for obtaining airspace permissions over a broad area. It can involve multiple drones, pilots, and flight plans, enabling strategic deconfliction. The mission's geographic coverage is stored in the Constraint Engine and referenced here via constraint_uuid.

Field	Type	Sample Value	Validation Rules
mission_id	int, unique	340	Auto-incrementing, must be unique
mission_uuid	UUID, unique, primary key	9b13ce82-99ab-41d4-a716-446655440010	Must be a valid UUID v4, unique
mission_udai_id	string, unique	UDAI-MIS-09	Format: UDAI-MIS-[0-9]+, unique
org_uuid	UUID, foreign key	c3e5d0f1...	Must exist in Organisations table
created_by_user_uuid	UUID, foreign key	550e8400...	Must exist in Users table
mission_name	string	Pipeline Inspection North	Max 150 characters
mission_description	string	Survey of segment 4	Max 1000 characters, nullable
start_time	timestamp	2025-07-05T08:00:00Z	Valid ISO 8601 timestamp, future date
end_time	timestamp	2025-07-07T18:00:00Z	Valid ISO 8601 timestamp, > start_time
constraint_uuid	UUID, foreign key	cst-a1b2c3d4...	Must exist in Constraints table
min_height	double	30.0	Metres AGL, >= 0
max_height	double	120.0	Metres AGL, > min_height
drones	array of JSON	[{"drone_uuid":"..."}]	Array of objects; drone_uuid must exist
pilots	array of UUIDs	["550e..."]	All UUIDs must exist in Users table
airspace_restrictions_covered	array of UUIDs	["zone-123..."]	Must exist in Airspace Zones table if provided
permissions	array of JSON	[{"zone":"..."}]	Must match airspace intersection rules
status	int/enum	1	Must be valid mission_status enum
created_at	timestamp	2025-07-01T08:00:00Z	Valid ISO 8601 timestamp, immutable
created_by	string	550e8400...	References a valid user_uuid
updated_at	timestamp	2025-07-04T10:30:00Z	Valid ISO 8601 timestamp, >= created_at
updated_by	string	550e8400...	References a valid user_uuid

12. Flight Plans

A Flight Plan defines the specific operational parameters for a single flight. A Mission contains one or more Flight Plans. The flight's geographic coverage is stored in the Constraint Engine and referenced here via constraint_uuid.

Field	Type	Sample Value	Validation Rules
plan_id	int, unique	890	Auto-incrementing, must be unique
plan_uuid	UUID, unique, primary key	f47ac10b-58cc-4372-a567-0eb2c3d47910	Must be a valid UUID v4, unique
plan_udai_id	string, unique	UDAI-PLN-10	Format: UDAI-PLN-[0-9]+, unique
mission_uuid	UUID, foreign key	9b13ce82...	Must exist in Missions table
org_uuid	UUID, foreign key	c3e5d0f1...	Must exist in Organisations table
drone_uuid	UUID, foreign key	9f5b610c...	Must exist in Drones table
user_uuid	UUID, foreign key	550e8400...	Must exist in Users table (the pilot)
payload_id	string	PAY-CA-01	String payload reference
payload_type	string	RGB Camera	Payload description/type
schedule_start_time	timestamp	2025-07-06T09:00:00Z	Valid ISO 8601 timestamp, >= mission start
schedule_end_time	timestamp	2025-07-06T11:00:00Z	Valid ISO 8601 timestamp, <= mission end
constraint_uuid	UUID, foreign key	cst-d4e5f6...	Must exist in Constraints table
min_height	double	30.0	Metres AGL, >= 0
max_height	double	80.0	Metres AGL, > min_height
airspace_restrictions_covered	array of UUIDs	["zone-123..."]	Must exist in Airspace Zones table
permissions	array of JSON	[{"zone":"..."}]	Inherited from or linked to Mission
flight_status	int/enum	1	Must be valid flight_plan_status enum
status	int/enum	1	Must be valid status enum
created_at	timestamp	2025-07-01T08:00:00Z	Valid ISO 8601 timestamp, immutable
created_by	string	550e8400...	References a valid user_uuid
updated_at	timestamp	2025-07-04T10:30:00Z	Valid ISO 8601 timestamp, >= created_at
updated_by	string	550e8400...	References a valid user_uuid

13. Permissions

Represents a request to access a specific restricted or controlled airspace segment, linked to a Mission or Flight Plan. One Permission record per airspace segment per airspace manager. Tracks whether access has been granted or denied.

Field	Type	Sample Value	Validation Rules
permission_id	int, unique	921	Auto-incrementing, must be unique
permission_uuid	UUID, unique, primary key	e5f6g7h8-1234-abcd-8901-cdef56789012	Must be a valid UUID v4, unique
permission_udai_id	string, unique	UDAI-PRM-105	Format: UDAI-PRM-[0-9]+, unique
parent_type	string	MISSION	Must be "MISSION" or "FLIGHT_PLAN"
parent_uuid	UUID	9b13ce82...	Must exist in parent table
zone_uuid	UUID, foreign key	zone-123...	Must exist in Airspace Zones table
manager_org_uuid	UUID, foreign key	c3e5d0f1...	Must exist in Organisations table (the approving authority)
airspace_type	string	RESTRICTED_ZONE	E.g., RESTRICTED_ZONE, PROHIBITED_ZONE
permission_status	int/enum	1	Must be valid permission_status enum
permission_reference	string	AAI-NOC-442	Reference ID given by airspace manager
valid_from	timestamp	2025-07-05T08:00:00Z	Valid ISO 8601 timestamp
valid_to	timestamp	2025-07-07T18:00:00Z	Valid ISO 8601 timestamp, > valid_from
remarks	string, nullable	Approved with caution	Max 1000 characters
created_at	timestamp	2025-07-02T08:00:00Z	Valid ISO 8601 timestamp, immutable
created_by	string	550e8400...	References a valid user_uuid
updated_at	timestamp	2025-07-04T10:30:00Z	Valid ISO 8601 timestamp, >= created_at
updated_by	string	550e8400...	References a valid user_uuid

14. Flight Sessions

A Flight Session represents a live operational window during which a drone actively executes a Flight Plan. A Flight Plan can have one or more sessions (e.g. re-attempts or paused/resumed flights).

Field	Type	Sample Value	Validation Rules
session_id	int, unique	150	Auto-incrementing, must be unique
session_uuid	UUID, unique, primary key	9f3a1b2c-4d5e-6f7a-8b9c-0d1e2f3a4b5c	Must be a valid UUID v4, unique
session_udai_id	string, unique	UDAI-SES-38	Format: UDAI-SES-[0-9]+, unique
plan_uuid	UUID, foreign key, nullable	f47ac10b...	Optional for ad-hoc flights. Must exist in Flight Plans if provided.
drone_uuid	UUID, foreign key	9f5b610c...	Must exist in Drones table
user_uuid	UUID, foreign key	550e8400...	Must exist in Users table (the pilot)
session_token	string	tok_aBcD...	Secure token generated for telemetry ingest
session_start_time	timestamp	2025-07-06T09:12:00Z	Valid ISO 8601 timestamp
session_end_time	timestamp	2025-07-06T10:45:00Z	Valid ISO 8601 timestamp, > session_start_time
session_expiry	timestamp	2025-07-06T11:00:00Z	Valid ISO 8601 timestamp
drone_start_latitude	double	28.6139	Valid latitude (-90 to 90)
drone_start_longitude	double	77.2090	Valid longitude (-180 to 180)
drone_end_latitude	double	28.6145	Valid latitude (-90 to 90)
drone_end_longitude	double	77.2100	Valid longitude (-180 to 180)
pilot_start_latitude	double	28.6130	Valid latitude (-90 to 90)
pilot_start_longitude	double	77.2085	Valid longitude (-180 to 180)
pilot_end_latitude	double	28.6130	Valid latitude (-90 to 90)
pilot_end_longitude	double	77.2085	Valid longitude (-180 to 180)
total_area	double	120.5	Sq km or hectares, >= 0
total_distance	double	15.0	Kilometers flown, >= 0
total_endurance	double	93.0	Flight minutes, >= 0
timestamp	timestamp	2025-07-06T09:12:00Z	Record creation time

15. Flight Telemetry

Not stored in the relational database.

Flight telemetry (drone position, heading, speed, altitude at ~1 Hz) is handled entirely outside the relational/geospatial database:

• Ingestion: Drone GCS sends telemetry frames via WebSocket to the UDAI platform in real time

• Live fan-out: The platform fans out live frames to authorised subscribers (Airspace Monitors, Airspace Managers) via WebSocket — see WebSocket Telemetry Endpoints

• Buffering & storage: Frames are buffered in-memory and periodically flushed to structured files or blob storage (e.g. Parquet/GeoParquet per session) for post-flight analysis

• No SQL/PostGIS writes occur at telemetry frequency

The only relational DB record updated during a live session is the Flight Session row (start/end times, final computed totals).

16. Airspace Zones

Defines a geographically bounded airspace segment with a restriction type and operational limits. The zone's geometry is stored in the Constraint Engine and referenced here via constraint_uuid.

Field	Type	Sample Value	Validation Rules
zone_id	int, unique	85	Auto-incrementing, must be unique
zone_uuid	UUID, unique, primary key	a1b2c3d4-e5f6-7890-abcd-ef1234567890	Must be a valid UUID v4, unique
zone_udai_id	string, unique	UDAI-ZON-14	Format: UDAI-ZON-[0-9]+, unique
zone_name	string	Airport Red Zone - DEL	Max 150 characters
restriction_type	int/enum	1	Must be valid airspace_restriction_type enum
airspace_zone_type	int/enum	2	1= रेड, 2= एयरपोर्ट, 3= इनर 4= आउटर, etc.
constraint_uuid	UUID, foreign key	cst-z1y2...	Must exist in Constraints table
min_height	double	0.0	Metres AGL, >= 0
max_height	double	400.0	Metres AGL, > min_height
active_from	timestamp, nullable	null	Must be timestamp if temporary restriction
active_to	timestamp, nullable	null	Must be timestamp if temporary restriction
status	int/enum	1	Must be valid status enum
created_at	timestamp	2025-07-01T08:00:00Z	Valid ISO 8601 timestamp, immutable
created_by	string	system	References a valid user_uuid or system
updated_at	timestamp	2025-07-04T10:30:00Z	Valid ISO 8601 timestamp, >= created_at
updated_by	string	system	References a valid user_uuid or system

17. Airspace Zone Memberships

Links an organisation to an Airspace Zone. An organisation of type Airspace Manager has authority to manage and evaluate permission requests for the zone. An organisation of type Airspace Monitor can view live activity within the zone.

Field	Type	Sample Value	Validation Rules
membership_id	int, unique	12	Auto-incrementing, must be unique
membership_uuid	UUID, unique, primary key	f0154cf0-fef0-4c3e-b816-43e34b9cfbc1	Must be a valid UUID v4, unique
membership_udai_id	string, unique	UDAI-ARM-03	Format: UDAI-ARM-[0-9]+, unique
zone_uuid	UUID, foreign key	a1b2c3d4...	Must exist in Airspace Zones table
org_uuid	UUID, foreign key	9f3a1b2c...	Must exist in Organisations table
membership_type	int/enum	1	1=Manager, 2=Monitor
assigned_by_user_uuid	UUID, foreign key	550e8400...	Must exist in Users table
status	int/enum	1	Must be valid status enum
created_at	timestamp	2025-07-02T08:00:00Z	Valid ISO 8601 timestamp, immutable
created_by	string	550e8400...	References a valid user_uuid
updated_at	timestamp	2025-07-04T10:30:00Z	Valid ISO 8601 timestamp, >= created_at
updated_by	string	550e8400...	References a valid user_uuid

18. Constraints

The Constraint Engine stores all geospatial shapes for the UDAI platform in a single PostGIS table. Each constraint record represents the geometry of one business object (Mission, Flight Plan, Airspace Zone, etc.). Business tables store only a constraint_uuid FK instead of inline GeoJSON.

This enables a single ST_Intersects query across all constraint types simultaneously — the foundation for strategic deconfliction and airspace awareness.

Field	Type	Sample Value	Validation Rules
constraint_id	int, unique	8542	Auto-incrementing, must be unique
constraint_uuid	UUID, unique, primary key	cst-d4e5f6g7-h8i9-j0k1-l2m3-n4o5p6q7r8s9	Must be a valid UUID v4, unique
constraint_type	int/enum	MISSION	MISSION, FLIGHT_PLAN, AIRSPACE_ZONE, GEOFENCE, DRONE_CORRIDOR, WEATHER_CELL
ref_uuid	UUID	9b13ce82...	UUID of business record this belongs to
geometry_2d	GeoJSON Polygon	{"type":"Polygon",...}	Must be a valid PostGIS/GeoJSON Polygon
min_height	double	30.0	Metres AGL, lower bound volume, >= 0
max_height	double	120.0	Metres AGL, upper bound volume, > min_height
active_from	timestamp, nullable	2025-07-05T08:00:00Z	Valid ISO 8601 timestamp if active
active_to	timestamp, nullable	2025-07-07T18:00:00Z	Valid ISO 8601 timestamp, > active_from
metadata	JSONB	{"priority": "high", "restriction_level": 2}	Application specific JSON metadata
created_at	timestamp	2025-07-01T08:00:00Z	Valid ISO 8601 timestamp, immutable
updated_at	timestamp	2025-07-04T10:30:00Z	Valid ISO 8601 timestamp, >= created_at

| updated_at | timestamp | 2025-07-04T10:30:00Z | Valid ISO 8601 timestamp, >= created_at |