Use of JSON/JSONB in the Database

Status: Implemented

Assignee: Alfons Mueller

Problem

Developers currently lack a clear decision on whether to continue using JSON/JSONB fields or normalized database structures when creating schemata.
This uncertainty affects migrations, schema consistency, and data modeling decisions across the backend.

Current State

JSONB Usage in Database

  • Currently used only in drawings.properties column (JSONB type) - backend/src/model/entity/drawings.rs:21
  • Stores polymorphic shape-specific data with 6 drawing types
  • No other tables use JSONB
  • Current JSON usage can be migrated to normalized tables without losing data, but this will require backend rewrites and database work. (creating tables & inserting data)

Why JSONB Was Used - Polymorphic Data Pattern

  • Each drawing has shape_type enum (Rectangle, Ellipse, FreeLine, BezierPolygon, LabelText, Image)
  • Different shapes have different properties (Rectangle has width/height, Ellipse has radius_x/radius_y, etc.)
  • JSONB properties column contains shape-specific attributes
  • Benefits:
    • Avoids sparse table with many nullable columns (one column per shape-specific property)
    • Flexible for adding new shape types
    • Works well with Rust's enum-based type system (DrawingVariant)
  • Type safety: Maintained at application layer via serde deserialization - backend/src/model/dto/drawings.rs:89-97
  • Error handling: Mismatch between shape_type and JSON structure returns 500 error - backend/src/model/dto/drawings_impl.rs:17-22

Common vs. Variant-Specific Attributes

  • Common (stored as columns): id, layer_id, add_date, remove_date, rotation, scale_x, scale_y, x, y, notes
  • Variant-specific (stored in JSONB properties):
    • Rectangle: width, height, color, fill_pattern, stroke_width
    • Ellipse: radius_x, radius_y, color, fill_pattern, stroke_width
    • FreeLine/BezierPolygon: points array, color, fill_pattern, stroke_width
    • LabelText: text, width, height, color
    • Image: path

The question is whether we should continue this practice, extend it to similar polymorphic or optional data (e.g., plant attributes), or phase it out in favor of fully normalized tables.

JSONB Cost vs. Benefit for Polymorphic Data

  • Cost

    • No native FK, UNIQUE, or NOT NULL constraints inside JSON. Checks have to be re-implemented with triggers or generated columns. Currently these checks are not in use but might become necessary in the future if we introduce new JSON applications.
    • Type safety is only done via backend validation.
    • Renames/splits require backfills or application-layer migration across versions.
    • More involved value transformations (RGB to RGBA) require custom PL/pgSQL functions or application-layer migration (no simple SQL).
    • More involved array transformations (Rotating/scaling points in FreeLine/BezierPolygon) require custom functions (no simple UPDATE).

  • Benefit

    • Avoids many nullable columns or many subtype tables when variants overlap only a little.
    • New fields can be added easier. (No new tables)
    • The API can write the whole object in one step, without branching into type-specific tables.

Research findings on JSONB migration capabilities

  1. Extensibility (adding/renaming attributes in JSONB):

    • ✅ Adding new properties: Supported via PostgreSQL subscripting: UPDATE drawings SET properties['fill_pattern_color'] = '"#FFFFFF"'
    • ⚠️ Renaming properties: No built-in support - requires extract old value → delete old key → create new key (multi-step process)

  2. Migration Flexibility (updating existing JSONB data):

    • ✅ Simple updates: Can update JSONB properties via subscripting
    • ⚠️ Value transformations: RGB to RGBA conversion would require custom PL/pgSQL functions or application-layer migration (no simple SQL)
    • ⚠️ Array transformations: Rotating/scaling points in FreeLine/BezierPolygon would need custom functions - can't be done with simple UPDATE
    • Current state: No JSONB-to-JSONB transformations in migrations yet - only column-to-JSONB and JSONB-to-column

  3. Data Mismatch Likelihood:

    • ⚠️ Current risk: Mismatch between shape_type and JSONB structure returns 500 error
    • Mitigation: Type safety enforced at application layer via DrawingVariant and serde deserialization
    • Risk sources: Manual database edits or buggy migrations could create invalid combinations
    • No DB-level validation: PostgreSQL doesn't validate JSONB structure matches the shape_type enum

Migration Likelihood Estimation by Type

In order to decide on an solution it is important to estimate the likelihood of migrations because some types require more effort when JSON is involved.

⚠️ This section is only an estimation and is subject to discussion.

  1. Adding properties: high probability, when new functionality is required
  2. Renaming properties: low probability, properties should be named correctly when introduced (reviewed)
  3. Value transformations: low-medium probability, unlikely for current use in drawings (e.g. could simply introduce separate opacity property) but could possibly be required if new JSON applications are introduced
  4. Array transformations: low-medium probability, unlikely for current use in drawings (coordinate system is established, freeline and bezier fully implemented) but could possibly be required if new JSON applications are introduced

Example Migrations

1. Simple renaming

Renaming the color attribute to fill_color in existing drawings:

UPDATE drawings
SET properties = (properties - 'color')
                || jsonb_build_object('fill_color', properties->'color')
WHERE properties ? 'color';

compared to a simple ALTER TABLE:

ALTER TABLE drawings
RENAME COLUMN color TO fill_color;

2. Extracting JSONB data into tables

In this example we show how a lossless extraction of data from the properties column of drawings might look like.

First we create the tables that will hold the different shapes.
For this example we only create the tables necessary to hold bezier polygons.
All other shape types can be handled in the same way, with bezier polygons already being the most complicated shape type currently implemented.

One table to hold the properties of the bezier polygons (linked to drawings by id):

CREATE TABLE IF NOT EXISTS public.bezier_polygon_props
(
drawing_id uuid NOT NULL,
color text COLLATE pg_catalog."default",
fill_pattern text COLLATE pg_catalog."default",
stroke_width numeric,
CONSTRAINT "bezier_polygon_props_PK" PRIMARY KEY (drawing_id),
CONSTRAINT "bezier_polygon_props_FK" FOREIGN KEY (drawing_id)
REFERENCES public.drawings (id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
)

And a second table to hold the point data of the bezier polygons (linked to bezier_polygon_props by id):

CREATE TABLE IF NOT EXISTS public.bezier_polygon_point
(
bezier_polygon_id uuid NOT NULL,
idx integer NOT NULL,
x numeric,
y numeric,
CONSTRAINT "bezier_polygon_point_PK" PRIMARY KEY (bezier_polygon_id, idx),
CONSTRAINT "bezier_polygon_point_FK" FOREIGN KEY (bezier_polygon_id)
REFERENCES public.bezier_polygon_props (drawing_id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
)

After creating the necessary tables we can start extracting the data from drawings to the corresponding subtables.
This is an example transaction covering the bezier polygon data stored in drawings.
Similar transactions would have to be done for all shape types.

BEGIN;

-- (a) Insert per-polygon properties
INSERT INTO bezier_polygon_props (drawing_id, color, fill_pattern, stroke_width)
SELECT id,
properties->>'color',
properties->>'fillPattern',
NULLIF(properties->>'strokeWidth','')::numeric
FROM drawings
WHERE shape_type = 'bezier polygon'
AND (properties ? 'color' OR properties ? 'fillPattern' OR properties ? 'strokeWidth')
ON CONFLICT (drawing_id) DO NOTHING;

-- (b) Insert points
INSERT INTO bezier_polygon_point (bezier_polygon_id, idx, x, y)
SELECT d.id,
p.ordinality - 1 AS idx, -- 0-based index
(p.elem->>0)::numeric AS x,
(p.elem->>1)::numeric AS y
FROM drawings d
JOIN LATERAL jsonb_array_elements(d.properties->'points') WITH ORDINALITY AS p(elem, ordinality)
ON TRUE
WHERE d.shape_type = 'bezier polygon';

COMMIT;

After running the transaction, from a drawings table that looks like this

Drawings table data

we get a bezier_polygon_props table looking like this

Bezier props table data

and a bezier_polygon_point table looking like this.

Bezier point table data

Type Safety: JSONB vs. Typed Columns

1. Using JSONB

  • JSONB shifts type safety into the application (Rust + serde) because PostgreSQL does not validate types, constraints, or foreign keys inside JSON.
  • This means JSON is only “safe” as long as all writes go through validated backend code.
  • This backend code has to be written by someone.
  • To keep JSON migratable later, it must stay simple, non-critical, and not contain essential business constraints. (e.g. permissions, required relationships, validity rules,... )
  • Good for: flexible, variant-heavy, optional UI/visual properties

2.Not Using JSON (Normalized Tables)

  • PostgreSQL enforces types, constraints, and referential integrity automatically .
  • Type safety is essentially free.
  • Errors show up at write-time, not runtime.

Constraints

  1. Migrations involving JSONB must remain maintainable. Developers should be able to add, rename, and remove keys with minimal complexity.
  2. Queries on JSONB fields must perform adequately, with feasible indexing strategies when needed.
  3. The risk of data mismatches between application models (Rust types) and stored JSON structures must remain acceptably low.
  4. When writing migrations developers don't have access to full production data. (e.g., private maps) When rolling out migrations they might encounter data which they didn't encounter during development.

Assumptions

  1. The project expects further entities with type-dependent or sparsely populated attributes (e.g., entities where 20–80 % of fields differ per variant).
  2. Most JSONB data will be read and written through the Rust backend, not manually or by external systems.
  3. The current validation pattern using serde will continue to be enforced across any future JSONB usage.

Solutions

Alternative A: Hybrid Approach (Status Quo + Guidelines)

Continue using JSONB for polymorphic or sparse data like drawings.properties.
Establish project-wide guidelines for when JSONB is allowed, how to migrate it safely, and how to enforce validation.

Polymorphic data in this context means:

  • Data whose shape depends on a discriminator (e.g., shape_type, plant_type, variant),
  • Each variant sharing only a few common fields while having distinct attributes.

Sparse data means:

  • Entities with numerous optional or rarely used attributes where adding individual columns would lead to excessive nulls.

JSONB may be used for such data if:

  • The number of variant-specific keys per entity exceeds roughly 4–5 fields, and
  • Queries on those fields are infrequent or limited to contained lookups.

Contained lookups may for example check for the existence of a JSON field or include a simple equals,

SELECT id
FROM drawings
WHERE properties ? 'color';

SELECT id
FROM drawings
WHERE properties->>'fill_pattern' = 'solid';

but not operations of higher complexity for which normal columns would be more efficient.

SELECT AVG((properties->>'stroke_width')::numeric)
FROM drawings
WHERE (properties->>'fill_pattern') = 'solid';

Example:
Say you wanted to store optional plant traits.
This could be done in plants.extra_properties JSONB.

erDiagram

plants {
  BIGINT id PK "PRIMARY KEY"
  TEXT   unique_name "NOT NULL, UNIQUE"
  TEXT[] common_name_en
  TEXT[] common_name_de
  TEXT   family
  JSONB  extra_properties "optional, variant/sparse traits"
}

Example contents of extra_properties:

{
  "water_need": 3,
  "shade_tolerance": 4,
  "edible": true,
  "toxicity_level": "none",
  "soil": { "ph_min": 6.0, "ph_max": 7.5 },
  "notes": "Prefers partial shade"
}

Pros:

  • Preserves flexibility for polymorphic or sparse data models.
  • New or variant-specific fields can be added without changing the table structure.
  • Avoids adding lots of columns for entities with many optional fields.
  • Minimal migration work from current state. (Only check current use of JSON adheres to new guidelines)

Cons:

  • Migration scripts for JSONB fields are more complex than ALTER TABLE operations.
  • JSONB migrations modify untyped data inside a blob, so the intent and outcome are less visible than standard column migrations. (Table doesn't change)
  • JSONB migrations require more testing work to guarantee the same safety you would get "for free" with typed columns and constraints.
  • The risk of data inconsistencies increases because validation and consistency checking move from the database layer (strongly typed columns) to the application layer (runtime deserialization).
  • Relies on developer discipline in following agreed-upon guidelines. (Mostly validation practice, rest requires similar discipline as normal columns)

Alternative B: Restrict JSONB to Drawings Only

Keep JSONB usage strictly limited to the drawings table and require normalized columns for all future features.

Example:
Say you wanted to store optional plant traits.
Instead of plants.extra_properties JSONB, create a typed side table:

erDiagram

plants {
  INT id PK
  TEXT unique_name "NOT NULL"
  TEXT[] common_name_en
  TEXT[] common_name_de
  TEXT family
}

plant_traits {
  BIGINT  plant_id PK "PRIMARY KEY, FK -> plants.id"
  SMALLINT water_need        "0..5"
  SMALLINT shade_tolerance   "0..5"
  BOOLEAN  edible
  TEXT     toxicity_level
  TEXT     notes
}

plants ||--o| plant_traits : "has traits"

Pros:

  • Changes are handled through standard, type-safe migrations instead JSON manipulation.
  • Ensures stronger data integrity through explicit types, constraints, and foreign keys. (Handled by DB)
  • Makes the schema more self-documenting for developers and tools.

Cons:

  • Each polymorphic variant requires its own table.
  • ORM models have to be changed when changes are made.
  • Database schemata have to be changed as data models evolve.
  • Tables might become wide with many nullable columns if data has many optional fields.

Alternative C: Phase Out JSONB

Migrate drawings into fully normalized relational structures and only use fully normalized tables in the future.

Example:
How drawings could be structured if we were to implement Alternative C.

erDiagram

drawings {
  BIGINT       id PK "PRIMARY KEY"
  TEXT         shape_type "ENUM: rectangle|ellipse|free_line|bezier_polygon|label_text|image"
  NUMERIC      x
  NUMERIC      y
  NUMERIC      rotation
  NUMERIC      scale_x
  NUMERIC      scale_y
  TIMESTAMPTZ  created_at
  TIMESTAMPTZ  updated_at
}

rectangle_props {
  BIGINT  drawing_id PK "PRIMARY KEY, FK -> drawings.id"
  NUMERIC width
  NUMERIC height
  TEXT    color
  TEXT    fill_pattern
  NUMERIC stroke_width
}

ellipse_props {
  BIGINT  drawing_id PK "PRIMARY KEY, FK -> drawings.id"
  NUMERIC radius_x
  NUMERIC radius_y
  TEXT    color
  TEXT    fill_pattern
  NUMERIC stroke_width
}

free_line_props {
  BIGINT  drawing_id PK "PRIMARY KEY, FK -> drawings.id"
  TEXT    color
  TEXT    fill_pattern
  NUMERIC stroke_width
}

free_line_point {
  BIGINT  free_line_id FK "FK -> free_line_props.drawing_id"
  INT     idx          "order in path"
  NUMERIC x
  NUMERIC y
  PK      "PRIMARY KEY (free_line_id, idx)"
}

bezier_polygon_props {
  BIGINT  drawing_id PK "PRIMARY KEY, FK -> drawings.id"
  TEXT    color
  TEXT    fill_pattern
  NUMERIC stroke_width
}

bezier_polygon_point {
  BIGINT  bezier_polygon_id FK "FK -> bezier_polygon_props.drawing_id"
  INT     idx          "order in path"
  NUMERIC x
  NUMERIC y
  NUMERIC cp1x
  NUMERIC cp1y
  NUMERIC cp2x
  NUMERIC cp2y
  PK      "PRIMARY KEY (bezier_polygon_id, idx)"
}

label_text_props {
  BIGINT  drawing_id PK "PRIMARY KEY, FK -> drawings.id"
  TEXT    text
  NUMERIC width
  NUMERIC height
  TEXT    color
}

image_props {
  BIGINT  drawing_id PK "PRIMARY KEY, FK -> drawings.id"
  TEXT    path
}

drawings ||--o| rectangle_props        : "rectangle data"
drawings ||--o| ellipse_props          : "ellipse data"
drawings ||--o| free_line_props        : "free line data"
drawings ||--o| bezier_polygon_props   : "bezier polygon data"
drawings ||--o| label_text_props       : "label text data"
drawings ||--o| image_props            : "image data"

free_line_props      ||--|{ free_line_point        : "points"
bezier_polygon_props ||--|{ bezier_polygon_point   : "points"

Pros:

  • Changes are handled through standard, type-safe migrations instead JSON manipulation.
  • Enforces full schema integrity through explicit types, constraints, and foreign keys. (Handled by DB)
  • Makes the schema more self-documenting for developers and tools.
  • it is probably about 20% faster

Cons:

  • Refactoring the drawings table and related backend code into a normalized design would require significant restructuring effort.
  • Each polymorphic variant requires its own table.
  • ORM models have to be changed when changes are made.
  • Database schemata have to be changed as data models evolve.
  • Tables might become wide with many nullable columns if data has many optional fields. The above solution for drawings avoids this by introducing a separate table for each shape.

Decision

It was decided to go with Alternative C: Phase Out JSONB as a typesafe, futureproof option.

Rationale

Implications

  • The properties field of drawings has to be migrated to a setup using normalized tables for the different shapes.
    • The new tables have to be added to the database.
    • The backend has to be adapted to work with the new database structure.

Notes

DDL for new Shapes Tables

-- =========================
-- BEZIER POLYGON
-- =========================
CREATE TABLE IF NOT EXISTS public.bezier_polygon_props
(
drawing_id uuid NOT NULL,
color text COLLATE pg_catalog."default",
fill_pattern text COLLATE pg_catalog."default",
stroke_width numeric,
CONSTRAINT "bezier_polygon_props_PK" PRIMARY KEY (drawing_id),
CONSTRAINT "bezier_polygon_props_FK" FOREIGN KEY (drawing_id)
REFERENCES public.drawings (id) MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS public.bezier_polygon_point
(
bezier_polygon_id uuid NOT NULL,
idx integer NOT NULL,
x numeric,
y numeric,
CONSTRAINT "bezier_polygon_point_PK" PRIMARY KEY (bezier_polygon_id, idx),
CONSTRAINT "bezier_polygon_point_FK" FOREIGN KEY (bezier_polygon_id)
REFERENCES public.bezier_polygon_props (drawing_id) MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE
);

-- =========================
-- FREE LINE
-- =========================
CREATE TABLE IF NOT EXISTS public.free_line_props
(
    drawing_id   uuid NOT NULL,
    color        text COLLATE pg_catalog."default",
    fill_pattern text COLLATE pg_catalog."default",
    stroke_width numeric,
    CONSTRAINT "free_line_props_PK" PRIMARY KEY (drawing_id),
    CONSTRAINT "free_line_props_FK" FOREIGN KEY (drawing_id)
        REFERENCES public.drawings (id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS public.free_line_point
(
    free_line_id uuid    NOT NULL,
    idx          integer NOT NULL,
    x            numeric,
    y            numeric,
    CONSTRAINT "free_line_point_PK" PRIMARY KEY (free_line_id, idx),
    CONSTRAINT "free_line_point_FK" FOREIGN KEY (free_line_id)
        REFERENCES public.free_line_props (drawing_id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE
);

-- =========================
-- RECTANGLE
-- =========================
CREATE TABLE IF NOT EXISTS public.rectangle_props
(
    drawing_id   uuid NOT NULL,
    color        text COLLATE pg_catalog."default",
    fill_pattern text COLLATE pg_catalog."default",
    stroke_width numeric,
    width        numeric,
    height       numeric,
    CONSTRAINT "rectangle_props_PK" PRIMARY KEY (drawing_id),
    CONSTRAINT "rectangle_props_FK" FOREIGN KEY (drawing_id)
        REFERENCES public.drawings (id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE
);

-- =========================
-- ELLIPSE
-- =========================
CREATE TABLE IF NOT EXISTS public.ellipse_props
(
    drawing_id   uuid NOT NULL,
    color        text COLLATE pg_catalog."default",
    fill_pattern text COLLATE pg_catalog."default",
    stroke_width numeric,
    radius_x     numeric,
    radius_y     numeric,
    CONSTRAINT "ellipse_props_PK" PRIMARY KEY (drawing_id),
    CONSTRAINT "ellipse_props_FK" FOREIGN KEY (drawing_id)
        REFERENCES public.drawings (id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE
);

-- =========================
-- LABEL TEXT
-- =========================
CREATE TABLE IF NOT EXISTS public.label_text_props
(
    drawing_id uuid NOT NULL,
    text       text COLLATE pg_catalog."default",
    color      text COLLATE pg_catalog."default",
    width      numeric,
    height     numeric,
    CONSTRAINT "label_text_props_PK" PRIMARY KEY (drawing_id),
    CONSTRAINT "label_text_props_FK" FOREIGN KEY (drawing_id)
        REFERENCES public.drawings (id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE
);

-- =========================
-- IMAGE
-- =========================
CREATE TABLE IF NOT EXISTS public.image_props
(
    drawing_id uuid NOT NULL,
    path       text COLLATE pg_catalog."default",
    CONSTRAINT "image_props_PK" PRIMARY KEY (drawing_id),
    CONSTRAINT "image_props_FK" FOREIGN KEY (drawing_id)
        REFERENCES public.drawings (id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE
);

Backfill Data from Properties into new Tables

-- =========================
-- BEZIER POLYGON
-- =========================
BEGIN;

-- (a) Insert per-polygon properties
INSERT INTO bezier_polygon_props (drawing_id, color, fill_pattern, stroke_width)
SELECT id,
properties->>'color',
properties->>'fillPattern',
NULLIF(properties->>'strokeWidth','')::numeric
FROM drawings
WHERE shape_type = 'bezier polygon'
AND (properties ? 'color' OR properties ? 'fillPattern' OR properties ? 'strokeWidth')
ON CONFLICT (drawing_id) DO NOTHING;

-- (b) Insert points
INSERT INTO bezier_polygon_point (bezier_polygon_id, idx, x, y)
SELECT d.id,
p.ordinality - 1 AS idx, -- 0-based index
(p.elem->>0)::numeric AS x,
(p.elem->>1)::numeric AS y
FROM drawings d
JOIN LATERAL jsonb_array_elements(d.properties->'points') WITH ORDINALITY AS p(elem, ordinality)
ON TRUE
WHERE d.shape_type = 'bezier polygon';

-- =========================
-- FREE LINE
-- =========================
INSERT INTO free_line_props (drawing_id, color, fill_pattern, stroke_width)
SELECT  id,
        properties->>'color',
        properties->>'fillPattern',
        NULLIF(properties->>'strokeWidth','')::numeric
FROM drawings
WHERE shape_type = 'free line'
ON CONFLICT (drawing_id) DO NOTHING;

INSERT INTO free_line_point (free_line_id, idx, x, y)
SELECT  d.id,
        p.ordinality - 1 AS idx,
        (p.elem->>0)::numeric AS x,
        (p.elem->>1)::numeric AS y
FROM drawings d
JOIN LATERAL jsonb_array_elements(d.properties->'points') WITH ORDINALITY AS p(elem, ordinality)
  ON TRUE
WHERE d.shape_type = 'free line'
ON CONFLICT DO NOTHING;

-- =========================
-- RECTANGLE
-- =========================
INSERT INTO rectangle_props (drawing_id, color, fill_pattern, stroke_width, width, height)
SELECT  id,
        properties->>'color',
        properties->>'fillPattern',
        NULLIF(properties->>'strokeWidth','')::numeric,
        NULLIF(properties->>'width','')::numeric,
        NULLIF(properties->>'height','')::numeric
FROM drawings
WHERE shape_type = 'rectangle'
ON CONFLICT (drawing_id) DO NOTHING;

-- =========================
-- ELLIPSE
-- =========================
INSERT INTO ellipse_props (drawing_id, color, fill_pattern, stroke_width, radius_x, radius_y)
SELECT  id,
        properties->>'color',
        properties->>'fillPattern',
        NULLIF(properties->>'strokeWidth','')::numeric,
        NULLIF(properties->>'radiusX','')::numeric,
        NULLIF(properties->>'radiusY','')::numeric
FROM drawings
WHERE shape_type = 'ellipse'
ON CONFLICT (drawing_id) DO NOTHING;

-- =========================
-- LABEL TEXT
-- =========================
INSERT INTO label_text_props (drawing_id, text, color, width, height)
SELECT  id,
        properties->>'text',
        properties->>'color',
        NULLIF(properties->>'width','')::numeric,
        NULLIF(properties->>'height','')::numeric
FROM drawings
WHERE shape_type = 'label text'
ON CONFLICT (drawing_id) DO NOTHING;

-- =========================
-- IMAGE
-- =========================
INSERT INTO image_props (drawing_id, path)
SELECT  id,
        properties->>'path'
FROM drawings
WHERE shape_type = 'image'
ON CONFLICT (drawing_id) DO NOTHING;

COMMIT;