Database Schema

Core database tables and their relationships in AsaHome Cloud.

Entity Relationship Diagram

---

Core Tables

users

Stores user accounts with authentication and customer data synced from Laravel.

Column	Type	Nullable	Default	Description
id	UUID	No	uuid_generate_v4()	Primary key
email	VARCHAR(255)	No	-	Unique email address
password	VARCHAR(255)	No	-	bcrypt hashed password
firstName	VARCHAR(100)	No	-	User's first name
lastName	VARCHAR(100)	No	-	User's last name
role	VARCHAR(20)	No	'user'	user or admin
sync_id	BIGINT	Yes	-	Laravel customer ID (for sync)
mrr	DECIMAL(10,2)	Yes	-	Monthly recurring revenue
isActive	BOOLEAN	No	true	Account status
isEmailVerified	BOOLEAN	No	false	Email verification status
createdAt	TIMESTAMP	No	now()	Creation timestamp
updatedAt	TIMESTAMP	No	now()	Last update timestamp

Indexes

Index	Columns	Type
PK_users	id	Primary Key
UQ_users_email	email	Unique
UQ_users_sync_id	sync_id	Unique (nullable)
IDX_users_role	role	B-tree
IDX_users_isActive	isActive	B-tree

Example

SELECT id, email, firstName, lastName, role, isActive
FROM users
WHERE email = 'user@example.com';

{
  "id": "550e8400-e29b-41d4-a716-446655440000",
  "email": "user@example.com",
  "firstName": "John",
  "lastName": "Doe",
  "role": "user",
  "isActive": true
}

---

devices

Registered AsaHome OS devices.

Column	Type	Nullable	Default	Description
id	UUID	No	uuid_generate_v4()	Primary key
uuid	UUID	No	-	Device UUID (from AsaHome OS)
deviceId	VARCHAR(255)	No	-	Human-readable device ID
name	VARCHAR(255)	No	-	Device display name
ipAddress	VARCHAR(45)	Yes	-	Last known IP address
port	VARCHAR(10)	Yes	'8123'	Device API port
accessToken	TEXT	Yes	-	Encrypted device access token
isOnline	BOOLEAN	No	false	Current connection status
lastSeenAt	TIMESTAMP	Yes	-	Last heartbeat time
metadata	JSONB	Yes	-	Additional device info
createdAt	TIMESTAMP	No	now()	Registration timestamp
updatedAt	TIMESTAMP	No	now()	Last update timestamp

Indexes

Index	Columns	Type
PK_devices	id	Primary Key
UQ_devices_uuid	uuid	Unique
UQ_devices_deviceId	deviceId	Unique
IDX_devices_isOnline	isOnline	B-tree

Metadata Schema

The metadata JSONB column typically contains:

{
  "version": "2024.1.0",
  "location": "Living Room",
  "model": "RPi4",
  "uptime": 86400,
  "entities": 150,
  "lastBackup": "2024-01-15T00:00:00Z"
}

---

device_users

Many-to-many relationship between users and devices with role-based access.

Column	Type	Nullable	Default	Description
id	UUID	No	uuid_generate_v4()	Primary key
userId	UUID	No	-	Foreign key to users
deviceId	UUID	No	-	Foreign key to devices
role	VARCHAR(20)	No	'viewer'	owner, editor, or viewer
isActive	BOOLEAN	No	true	Link status
createdAt	TIMESTAMP	No	now()	Creation timestamp
updatedAt	TIMESTAMP	No	now()	Last update timestamp

Indexes

Index	Columns	Type
PK_device_users	id	Primary Key
UQ_device_users	userId, deviceId	Unique
IDX_device_users_userId	userId	B-tree
IDX_device_users_deviceId	deviceId	B-tree

Role Permissions

Role	View	Control	Settings	Delete	Share
owner	Yes	Yes	Yes	Yes	Yes
editor	Yes	Yes	No	No	No
viewer	Yes	No	No	No	No

---

refresh_tokens

Stores hashed refresh tokens for session management.

Column	Type	Nullable	Default	Description
id	UUID	No	uuid_generate_v4()	Primary key
userId	UUID	No	-	Foreign key to users
token	VARCHAR(255)	No	-	SHA-256 hashed token
deviceId	VARCHAR(255)	Yes	-	Client device identifier
expiresAt	TIMESTAMP	No	-	Token expiration
isRevoked	BOOLEAN	No	false	Revocation status
createdAt	TIMESTAMP	No	now()	Creation timestamp

Indexes

Index	Columns	Type
PK_refresh_tokens	id	Primary Key
IDX_refresh_tokens_token	token	B-tree
IDX_refresh_tokens_userId	userId	B-tree
IDX_refresh_tokens_expiresAt	expiresAt	B-tree

Token Security

Tokens are stored as SHA-256 hashes:

import * as crypto from 'crypto';

const hashedToken = crypto
  .createHash('sha256')
  .update(refreshToken)
  .digest('hex');

---

Additional Tables

audit_logs

Audit trail for security-relevant actions.

Column	Type	Description
id	UUID	Primary key
userId	UUID	User who performed action
action	VARCHAR	Action type (login, logout, etc.)
resource	VARCHAR	Affected resource type
resourceId	UUID	Affected resource ID
ipAddress	VARCHAR	Client IP address
userAgent	TEXT	Client user agent
metadata	JSONB	Additional context
createdAt	TIMESTAMP	Action timestamp

login_attempts

Track login attempts for security monitoring.

Column	Type	Description
id	UUID	Primary key
email	VARCHAR	Attempted email
ipAddress	VARCHAR	Client IP
success	BOOLEAN	Attempt result
failReason	VARCHAR	Failure reason if failed
createdAt	TIMESTAMP	Attempt timestamp

tunnel_sessions

Active WebSocket tunnel sessions.

Column	Type	Description
id	UUID	Primary key
type	VARCHAR	user or device
entityId	UUID	User ID or Device ID
socketId	VARCHAR	Socket.IO socket ID
connectedAt	TIMESTAMP	Connection timestamp
lastActivityAt	TIMESTAMP	Last message timestamp
metadata	JSONB	Connection metadata

---

Common Queries

Get user's devices with roles

SELECT 
  d.id,
  d.uuid,
  d.name,
  d.isOnline,
  d.lastSeenAt,
  du.role
FROM devices d
INNER JOIN device_users du ON d.id = du.deviceId
WHERE du.userId = '550e8400-e29b-41d4-a716-446655440000'
  AND du.isActive = true
ORDER BY d.name;

Get device with all users

SELECT 
  d.*,
  json_agg(json_build_object(
    'userId', u.id,
    'email', u.email,
    'firstName', u.firstName,
    'lastName', u.lastName,
    'role', du.role
  )) as users
FROM devices d
INNER JOIN device_users du ON d.id = du.deviceId
INNER JOIN users u ON du.userId = u.id
WHERE d.uuid = '550e8400-e29b-41d4-a716-446655440000'
  AND du.isActive = true
GROUP BY d.id;

Revoke all tokens for user

UPDATE refresh_tokens
SET isRevoked = true
WHERE userId = '550e8400-e29b-41d4-a716-446655440000'
  AND isRevoked = false;

Clean up expired tokens

DELETE FROM refresh_tokens
WHERE expiresAt < NOW() - INTERVAL '7 days';

Find users synced from Laravel

SELECT id, email, firstName, lastName, sync_id, mrr
FROM users
WHERE sync_id IS NOT NULL
ORDER BY sync_id;

---

TypeORM Entities

User Entity

@Entity('users')
export class User {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @Column({ unique: true })
  email: string;

  @Column()
  password: string;

  @Column()
  firstName: string;

  @Column()
  lastName: string;

  @Column({ default: 'user' })
  role: string;

  @Column({ type: 'bigint', nullable: true, unique: true })
  sync_id: number;

  @Column({ type: 'decimal', precision: 10, scale: 2, nullable: true })
  mrr: number;

  @Column({ default: true })
  isActive: boolean;

  @OneToMany(() => DeviceUser, deviceUser => deviceUser.user)
  deviceUsers: DeviceUser[];

  @CreateDateColumn()
  createdAt: Date;

  @UpdateDateColumn()
  updatedAt: Date;
}

Device Entity

@Entity('devices')
export class Device {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @Column({ type: 'uuid', unique: true })
  uuid: string;

  @Column({ unique: true })
  deviceId: string;

  @Column()
  name: string;

  @Column({ nullable: true })
  ipAddress: string;

  @Column({ default: '8123' })
  port: string;

  @Column({ default: false })
  isOnline: boolean;

  @Column({ type: 'timestamp', nullable: true })
  lastSeenAt: Date;

  @Column({ type: 'jsonb', nullable: true })
  metadata: Record<string, any>;

  @OneToMany(() => DeviceUser, deviceUser => deviceUser.device)
  deviceUsers: DeviceUser[];

  @CreateDateColumn()
  createdAt: Date;

  @UpdateDateColumn()
  updatedAt: Date;
}